Re: For the same MSSQL table migration script works in CentOS 6.6, but fails partially in CentOS 7.2, 7.3 and 7.4
Posted by: Jacob Nikom
Date: March 01, 2018 03:26PM
Date: March 01, 2018 03:26PM
Hello Milosz,
Thank you for your help.
I added --force-utf8-for-source option to my script.
Here is the script with this option:
# Source and target DB passwords
arg_source_password="***********"
arg_target_password="***********"
if [ -z "$arg_source_password" ] && [ -z "$arg_target_password" ] ; then
echo WARNING: Both source and target RDBMSes passwords are empty. You should edit this file to set them.
fi
# Source server parameters
# arg_source_database_server="10.20.100.13"
src_server="AZSQLDB.invaluable.corp"
src_port="1433"
src_database="rfcAnalysis"
src_uid="OSDprod"
# Target server parameters
# arg_target_database_server="root@127.0.0.1:3317"
arg_target_database_server="root@127.0.0.1:3306"
echo "arg_target_database_server = $arg_target_database_server"
# Whether target tables should be truncated before copy
arg_truncate_target="--truncate-target"
# Migration operation parameters
arg_worker_count=2
# Enable debugging output
arg_debug_output=--log-level=debug3
# Disable triggers
arg_disable_triggers=--dont-disable-triggers
export LD_LIBRARY_PATH=.:/local/lib:/usr/lib:/usr/lib/X11:/usr/local/lib:/usr/local/pgsql/lib:/usr/lib/debug/usr/lib64/mysql-workbench:/usr/lib64/mysql-workbench
/usr/libexec/mysql-workbench/wbcopytables-bin \
--odbc-source="DRIVER={MyFreeTDSDriver};SERVER=$src_server;PORT=$src_port;DATABASE=$src_database;UID=$src_uid;TDS_VERSION=7.1" \
--target="$arg_target_database_server" \
--source-password="$arg_source_password" \
--target-password="$arg_target_password" \
--thread-count=$arg_worker_count $arg_truncate_target $arg_debug_output \
--force-utf8-for-source \
--table '[rfcAnalysis]' '[dbo].[houseAddress]' '`rfcAnalysis`' '`houseAddress`' '[aAddressID]' '`aAddressID`' 'CAST([aRecordStat] as NCHAR(1)) as [aRecordStat], [aAddressID], [aAddressParentID], CAST([aType] as NCHAR(1)) as [aType], [aHouseID], [aSeq], CAST([aPubStatus] as NCHAR(1)) as [aPubStatus], CAST([aUseStatus] as NCHAR(1)) as [aUseStatus], CAST([aContactFirstName] as NVARCHAR(50)) as [aContactFirstName], CAST([aContactLastName] as NVARCHAR(50)) as [aContactLastName], CAST([aTitle] as NVARCHAR(75)) as [aTitle], CAST([aDept] as NVARCHAR(75)) as [aDept], CAST([aLocAlias] as NVARCHAR(100)) as [aLocAlias], [aScraperAlias], CAST([aFacilityName] as NVARCHAR(75)) as [aFacilityName], CAST([aEbayLocAlias] as NVARCHAR(45)) as [aEbayLocAlias], [aHeadOffice], [aBranchOffice], [aSellingLoc], [aSellingDft], [aPreviewLoc], [aPreviewDft], [aOfficeLoc], [aOfficeDft], CAST([aStreet1] as NVARCHAR(75)) as [aStreet1], CAST([aStreet2] as NVARCHAR(75)) as [aStreet2], CAST([aCity] as NVARCHAR(50)) as [aCity], CAST([aStateID] as NVARCHAR(4)) as [aStateID], CAST([aProvince] as NVARCHAR(30)) as [aProvince], CAST([aPostCode] as NVARCHAR(15)) as [aPostCode], CAST([aCountryID] as NVARCHAR(2)) as [aCountryID], CAST([aTelNo] as NVARCHAR(50)) as [aTelNo], CAST([aFaxNo] as NVARCHAR(50)) as [aFaxNo], CAST([aTollFree] as NVARCHAR(20)) as [aTollFree], CAST([aEMail] as NVARCHAR(64)) as [aEMail], [aExpertContactID], [aConsignContactID], [aBiddingContactID], [aCurrencyID], CAST([aTimeZone] as NVARCHAR(4)) as [aTimeZone], [aGMToffset], [aCommIncluded], [aCommissionID], [aDirections], CAST([aBuyPremium] as NVARCHAR(250)) as [aBuyPremium], [aPremiumPlusVAT], CAST([aViewingNotes] as NVARCHAR(2500)) as [aViewingNotes], CAST([aDisplayNotes] as NVARCHAR(2500)) as [aDisplayNotes], CAST([aSaleNotes] as NVARCHAR(2500)) as [aSaleNotes], [aNotes], CAST([aInstructions] as NVARCHAR(1200)) as [aInstructions], CAST([aSourceType] as NVARCHAR(8)) as [aSourceType], CAST([aSourcePath] as NVARCHAR(1000)) as [aSourcePath]'
echo "xxx Finshed bash script to migrate houseAddress table from SQL Server to MySQL rfcAnalysis database" `date`
Here is the output of the execution of this script:
shell> copy_migrated_tables_houseAddress_2018_03_01_rhel7.sh
xxx Start bash script to migrate houseAddress table from SQL Server to MySQL rfcAnalysis database Thu Mar 1 15:57:34 EST 2018
arg_target_database_server = root@127.0.0.1:3306
Logger set to level 'debug3'. '1111111'
15:57:34 [INF][ copytable]: Connecting to MySQL server at 127.0.0.1:3306 with user root
15:57:34 [INF][ copytable]: Connection to MySQL opened
15:57:34 [DB1][ copytable]: Detected server version=5.6.39-enterprise-commercial-advanced
15:57:34 [DB1][ copytable]: Detected max_allowed_packet=134217728
15:57:34 [DB1][ copytable]: Retrieving trigger list
15:57:34 [INF][ copytable]: Opening ODBC connection to [unknown] 'DRIVER={MyFreeTDSDriver};SERVER=AZSQLDB.invaluable.corp;PORT=1433;DATABASE=rfcAnalysis;UID=OSDprod;TDS_VERSION=7.1;PWD=XXX'
15:57:34 [INF][ copytable]: ODBC connection to 'DRIVER={MyFreeTDSDriver};SERVER=AZSQLDB.invaluable.corp;PORT=1433;DATABASE=rfcAnalysis;UID=OSDprod;TDS_VERSION=7.1;PWD=' opened
15:57:34 [INF][ copytable]: Connecting to MySQL server at 127.0.0.1:3306 with user root
15:57:34 [INF][ copytable]: Connection to MySQL opened
15:57:34 [DB1][ copytable]: Detected server version=5.6.39-enterprise-commercial-advanced
15:57:34 [DB1][ copytable]: Detected max_allowed_packet=134217728
15:57:34 [INF][ copytable]: Opening ODBC connection to [unknown] 'DRIVER={MyFreeTDSDriver};SERVER=AZSQLDB.invaluable.corp;PORT=1433;DATABASE=rfcAnalysis;UID=OSDprod;TDS_VERSION=7.1;PWD=XXX'
15:57:34 [DB1][ copytable]: Executing query: SELECT count(*) FROM [rfcAnalysis].[dbo].[houseAddress]
15:57:34 [DB1][ copytable]: Executing query: SELECT CAST([aRecordStat] as NCHAR(1)) as [aRecordStat], [aAddressID], [aAddressParentID], CAST([aType] as NCHAR(1)) as [aType], [aHouseID], [aSeq], CAST([aPubStatus] as NCHAR(1)) as [aPubStatus], CAST([aUseStatus] as NCHAR(1)) as [aUseStatus], CAST([aContactFirstName] as NVARCHAR(50)) as [aContactFirstName], CAST([aContactLastName] as NVARCHAR(50)) as [aContactLastName], CAST([aTitle] as NVARCHAR(75)) as [aTitle], CAST([aDept] as NVARCHAR(75)) as [aDept], CAST([aLocAlias] as NVARCHAR(100)) as [aLocAlias], [aScraperAlias], CAST([aFacilityName] as NVARCHAR(75)) as [aFacilityName], CAST([aEbayLocAlias] as NVARCHAR(45)) as [aEbayLocAlias], [aHeadOffice], [aBranchOffice], [aSellingLoc], [aSellingDft], [aPreviewLoc], [aPreviewDft], [aOfficeLoc], [aOfficeDft], CAST([aStreet1] as NVARCHAR(75)) as [aStreet1], CAST([aStreet2] as NVARCHAR(75)) as [aStreet2], CAST([aCity] as NVARCHAR(50)) as [aCity], CAST([aStateID] as NVARCHAR(4)) as [aStateID], CAST([aProvince] as NVARCHAR(30)) as [aProvince], CAST([aPostCode] as NVARCHAR(15)) as [aPostCode], CAST([aCountryID] as NVARCHAR(2)) as [aCountryID], CAST([aTelNo] as NVARCHAR(50)) as [aTelNo], CAST([aFaxNo] as NVARCHAR(50)) as [aFaxNo], CAST([aTollFree] as NVARCHAR(20)) as [aTollFree], CAST([aEMail] as NVARCHAR(64)) as [aEMail], [aExpertContactID], [aConsignContactID], [aBiddingContactID], [aCurrencyID], CAST([aTimeZone] as NVARCHAR(4)) as [aTimeZone], [aGMToffset], [aCommIncluded], [aCommissionID], [aDirections], CAST([aBuyPremium] as NVARCHAR(250)) as [aBuyPremium], [aPremiumPlusVAT], CAST([aViewingNotes] as NVARCHAR(2500)) as [aViewingNotes], CAST([aDisplayNotes] as NVARCHAR(2500)) as [aDisplayNotes], CAST([aSaleNotes] as NVARCHAR(2500)) as [aSaleNotes], [aNotes], CAST([aInstructions] as NVARCHAR(1200)) as [aInstructions], CAST([aSourceType] as NVARCHAR(8)) as [aSourceType], CAST([aSourcePath] as NVARCHAR(1000)) as [aSourcePath] FROM [rfcAnalysis].[dbo].[houseAddress] ORDER BY [aAddressID]
15:57:34 [DB2][ copytable]: Columns from source table [rfcAnalysis].[dbo].[houseAddress] (53):
15:57:34 [DB2][ copytable]: 1 - aRecordStat: nchar (type=SQL_WCHAR, len=4)
15:57:34 [DB2][ copytable]: 2 - aAddressID: int (type=SQL_INTEGER, len=10)
15:57:34 [DB2][ copytable]: 3 - aAddressParentID: int (type=SQL_INTEGER, len=10)
15:57:34 [DB2][ copytable]: 4 - aType: nchar (type=SQL_WCHAR, len=4)
15:57:34 [DB2][ copytable]: 5 - aHouseID: int (type=SQL_INTEGER, len=10)
15:57:34 [DB2][ copytable]: 6 - aSeq: smallint (type=SQL_SMALLINT, len=5)
15:57:34 [DB2][ copytable]: 7 - aPubStatus: nchar (type=SQL_WCHAR, len=4)
15:57:34 [DB2][ copytable]: 8 - aUseStatus: nchar (type=SQL_WCHAR, len=4)
15:57:34 [DB2][ copytable]: 9 - aContactFirstName: nvarchar (type=SQL_WVARCHAR, len=200)
15:57:34 [DB2][ copytable]: 10 - aContactLastName: nvarchar (type=SQL_WVARCHAR, len=200)
15:57:34 [DB2][ copytable]: 11 - aTitle: nvarchar (type=SQL_WVARCHAR, len=300)
15:57:34 [DB2][ copytable]: 12 - aDept: nvarchar (type=SQL_WVARCHAR, len=300)
15:57:34 [DB2][ copytable]: 13 - aLocAlias: nvarchar (type=SQL_WVARCHAR, len=400)
15:57:34 [DB2][ copytable]: 14 - aScraperAlias: nvarchar (type=SQL_WVARCHAR, len=400)
15:57:34 [DB2][ copytable]: 15 - aFacilityName: nvarchar (type=SQL_WVARCHAR, len=300)
15:57:34 [DB2][ copytable]: 16 - aEbayLocAlias: nvarchar (type=SQL_WVARCHAR, len=180)
15:57:34 [DB2][ copytable]: 17 - aHeadOffice: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 18 - aBranchOffice: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 19 - aSellingLoc: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 20 - aSellingDft: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 21 - aPreviewLoc: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 22 - aPreviewDft: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 23 - aOfficeLoc: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 24 - aOfficeDft: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 25 - aStreet1: nvarchar (type=SQL_WVARCHAR, len=300)
15:57:34 [DB2][ copytable]: 26 - aStreet2: nvarchar (type=SQL_WVARCHAR, len=300)
15:57:34 [DB2][ copytable]: 27 - aCity: nvarchar (type=SQL_WVARCHAR, len=200)
15:57:34 [DB2][ copytable]: 28 - aStateID: nvarchar (type=SQL_WVARCHAR, len=16)
15:57:34 [DB2][ copytable]: 29 - aProvince: nvarchar (type=SQL_WVARCHAR, len=120)
15:57:34 [DB2][ copytable]: 30 - aPostCode: nvarchar (type=SQL_WVARCHAR, len=60)
15:57:34 [DB2][ copytable]: 31 - aCountryID: nvarchar (type=SQL_WVARCHAR, len=8)
15:57:34 [DB2][ copytable]: 32 - aTelNo: nvarchar (type=SQL_WVARCHAR, len=200)
15:57:34 [DB2][ copytable]: 33 - aFaxNo: nvarchar (type=SQL_WVARCHAR, len=200)
15:57:34 [DB2][ copytable]: 34 - aTollFree: nvarchar (type=SQL_WVARCHAR, len=80)
15:57:34 [DB2][ copytable]: 35 - aEMail: nvarchar (type=SQL_WVARCHAR, len=256)
15:57:34 [DB2][ copytable]: 36 - aExpertContactID: int (type=SQL_INTEGER, len=10)
15:57:34 [DB2][ copytable]: 37 - aConsignContactID: int (type=SQL_INTEGER, len=10)
15:57:34 [DB2][ copytable]: 38 - aBiddingContactID: int (type=SQL_INTEGER, len=10)
15:57:34 [DB2][ copytable]: 39 - aCurrencyID: tinyint UNSIGNED (type=SQL_TINYINT, len=3)
15:57:34 [DB2][ copytable]: 40 - aTimeZone: nvarchar (type=SQL_WVARCHAR, len=16)
15:57:34 [DB2][ copytable]: 41 - aGMToffset: decimal (type=SQL_NUMERIC, len=3)
15:57:34 [DB2][ copytable]: 42 - aCommIncluded: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 43 - aCommissionID: smallint (type=SQL_SMALLINT, len=5)
15:57:34 [DB2][ copytable]: 44 - aDirections: ntext (type=SQL_WLONGVARCHAR, len=4294967292, long_data)
15:57:34 [DB2][ copytable]: 45 - aBuyPremium: nvarchar (type=SQL_WVARCHAR, len=1000)
15:57:34 [DB2][ copytable]: 46 - aPremiumPlusVAT: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 47 - aViewingNotes: nvarchar (type=SQL_WVARCHAR, len=10000)
15:57:34 [DB2][ copytable]: 48 - aDisplayNotes: nvarchar (type=SQL_WVARCHAR, len=10000)
15:57:34 [DB2][ copytable]: 49 - aSaleNotes: nvarchar (type=SQL_WVARCHAR, len=10000)
15:57:34 [DB2][ copytable]: 50 - aNotes: ntext (type=SQL_WLONGVARCHAR, len=4294967292, long_data)
15:57:34 [DB2][ copytable]: 51 - aInstructions: nvarchar (type=SQL_WVARCHAR, len=4800)
15:57:34 [DB2][ copytable]: 52 - aSourceType: nvarchar (type=SQL_WVARCHAR, len=32)
15:57:34 [DB2][ copytable]: 53 - aSourcePath: nvarchar (type=SQL_WVARCHAR, len=4000)
BEGIN:`rfcAnalysis`.`houseAddress`:Copying 53 columns of 23610 rows from table [rfcAnalysis].[dbo].[houseAddress]
15:57:34 [DB2][ copytable]: Columns from target table `rfcAnalysis`.`houseAddress` (53) [skipped: 0]:
15:57:34 [DB2][ copytable]: 1 - aRecordStat: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 2 - aAddressID: MYSQL_TYPE_LONG
15:57:34 [DB2][ copytable]: 3 - aAddressParentID: MYSQL_TYPE_LONG
15:57:34 [DB2][ copytable]: 4 - aType: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 5 - aHouseID: MYSQL_TYPE_LONG
15:57:34 [DB2][ copytable]: 6 - aSeq: MYSQL_TYPE_SHORT
15:57:34 [DB2][ copytable]: 7 - aPubStatus: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 8 - aUseStatus: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 9 - aContactFirstName: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 10 - aContactLastName: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 11 - aTitle: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 12 - aDept: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 13 - aLocAlias: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 14 - aScraperAlias: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 15 - aFacilityName: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 16 - aEbayLocAlias: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 17 - aHeadOffice: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 18 - aBranchOffice: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 19 - aSellingLoc: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 20 - aSellingDft: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 21 - aPreviewLoc: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 22 - aPreviewDft: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 23 - aOfficeLoc: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 24 - aOfficeDft: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 25 - aStreet1: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 26 - aStreet2: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 27 - aCity: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 28 - aStateID: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 29 - aProvince: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 30 - aPostCode: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 31 - aCountryID: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 32 - aTelNo: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 33 - aFaxNo: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 34 - aTollFree: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 35 - aEMail: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 36 - aExpertContactID: MYSQL_TYPE_LONG
15:57:34 [DB2][ copytable]: 37 - aConsignContactID: MYSQL_TYPE_LONG
15:57:34 [DB2][ copytable]: 38 - aBiddingContactID: MYSQL_TYPE_LONG
15:57:34 [DB2][ copytable]: 39 - aCurrencyID: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 40 - aTimeZone: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 41 - aGMToffset: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 42 - aCommIncluded: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 43 - aCommissionID: MYSQL_TYPE_SHORT
15:57:34 [DB2][ copytable]: 44 - aDirections: MYSQL_TYPE_BLOB
15:57:34 [DB2][ copytable]: 45 - aBuyPremium: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 46 - aPremiumPlusVAT: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 47 - aViewingNotes: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 48 - aDisplayNotes: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 49 - aSaleNotes: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 50 - aNotes: MYSQL_TYPE_BLOB
15:57:34 [DB2][ copytable]: 51 - aInstructions: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 52 - aSourceType: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 53 - aSourcePath: MYSQL_TYPE_STRING
15:57:34 [INF][ copytable]: Truncating table `rfcAnalysis`.`houseAddress`
15:57:34 [INF][ copytable]: ODBC connection to 'DRIVER={MyFreeTDSDriver};SERVER=AZSQLDB.invaluable.corp;PORT=1433;DATABASE=rfcAnalysis;UID=OSDprod;TDS_VERSION=7.1;PWD=' opened
15:57:34 [INF][ copytable]: Connecting to MySQL server at 127.0.0.1:3306 with user root
15:57:34 [INF][ copytable]: Connection to MySQL opened
15:57:34 [DB1][ copytable]: Detected server version=5.6.39-enterprise-commercial-advanced
15:57:34 [DB1][ copytable]: Detected max_allowed_packet=134217728
15:57:36 [WRN][ copytable]: [42000 - 2403]: [FreeTDS][SQL Server]Some character(s) could not be converted into client's character set. Unconverted bytes were changed to question marks ('?')
END:`rfcAnalysis`.`houseAddress`:Finished copying 23610 rows in 0m07s
15:57:41 [INF][ copytable]: Re-enabling triggers for schema 'rfcAnalysis'
15:57:41 [DB1][ copytable]: Retrieving trigger definitions
15:57:41 [INF][ copytable]: No triggers found for 'rfcAnalysis'
FINISHED
xxx Finshed bash script to migrate houseAddress table from SQL Server to MySQL rfcAnalysis database Thu Mar 1 15:57:41 EST 2018
shell>
As you see it could not
Here is the character sets on the server:
MySQL [(none)]> show variables like "%character%";
+--------------------------+-------------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /ods/local/mysql-advanced-5.6.39-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+-------------------------------------------------------------------------+
8 rows in set (0.00 sec)
MySQL [(none)]>
Here is the putput of the command:
mysql> SELECT * FROM rfcAnalysis.houseAddress LIMIT 33;
+-------------+------------+------------------+-------+----------+------+------------+------------+-------------------+------------------+-----------------------+-------------------------------------------------+-----------+---------------+----------------------------------------+---------------+-------------+---------------+-------------+-------------+-------------+-------------+------------+------------+----------------------------+--------------------+-------------------------+----------+-----------+------------+------------+-------------------+-------------------+-----------+-------------------------------+------------------+-------------------+-------------------+-------------+-----------+------------+---------------+---------------+-------------+------------------------------------------------------------+-----------------+---------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
| aRecordStat | aAddressID | aAddressParentID | aType | aHouseID | aSeq | aPubStatus | aUseStatus | aContactFirstName | aContactLastName | aTitle | aDept | aLocAlias | aScraperAlias | aFacilityName | aEbayLocAlias | aHeadOffice | aBranchOffice | aSellingLoc | aSellingDft | aPreviewLoc | aPreviewDft | aOfficeLoc | aOfficeDft | aStreet1 | aStreet2 | aCity | aStateID | aProvince | aPostCode | aCountryID | aTelNo | aFaxNo | aTollFree | aEMail | aExpertContactID | aConsignContactID | aBiddingContactID | aCurrencyID | aTimeZone | aGMToffset | aCommIncluded | aCommissionID | aDirections | aBuyPremium | aPremiumPlusVAT | aViewingNotes | aDisplayNotes | aSaleNotes | aNotes | aInstructions | aSourceType | aSourcePath |
+-------------+------------+------------------+-------+----------+------+------------+------------+-------------------+------------------+-----------------------+-------------------------------------------------+-----------+---------------+----------------------------------------+---------------+-------------+---------------+-------------+-------------+-------------+-------------+------------+------------+----------------------------+--------------------+-------------------------+----------+-----------+------------+------------+-------------------+-------------------+-----------+-------------------------------+------------------+-------------------+-------------------+-------------+-----------+------------+---------------+---------------+-------------+------------------------------------------------------------+-----------------+---------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
| a | 39 | 0 | L | 40 | 0 | | a | | | | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 358 Broadway | | Cambridge | MA | | 02139 | US | 00 1 617 354 7919 | | | info@sbauctioneers.com | 0 | 0 | 0 | 1 | EST | -5.0 | 0 | 0 | | 10.00% | 0 | | | | | | | |
| a | 43 | 0 | C | 11 | 1 | | a | Robert | Eldred | President | American, Marine, Books | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | 0 | | | US | 508-385-3116 | | | info@eldreds.com | 0 | 0 | 0 | 0 | ??? | 99.0 | 0 | 0 | | | 0 | | | | | | | |
| a | 44 | 0 | C | 11 | 2 | | a | John | Schofield | Vice President | Asian Art, European Furniture and Art, Fine Art | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | 0 | | | US | 508-385-3116 | | | info@eldreds.com | 0 | 0 | 0 | 0 | ??? | 99.0 | 0 | 0 | | | 0 | | | | | | | |
| a | 45 | 0 | C | 11 | 3 | | a | Susan | Schofield | Treasurer | Paul Jacoulet Prints | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | 0 | | | US | 508-385-3116 | | | sue@eldreds.com | 0 | 0 | 0 | 0 | ??? | 99.0 | 0 | 0 | | | 0 | | | | | | | |
| a | 46 | 0 | C | 11 | 4 | | a | Mary Ann | Eldred | Director of Marketing | Antiques Postcards | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | 0 | | | US | 508-385-3116 | | | info@eldreds.com | 0 | 0 | 0 | 0 | ??? | 99.0 | 0 | 0 | | | 0 | | | | | | | |
| a | 47 | 0 | C | 11 | 5 | | a | Eric | Mulak | Appraiser | Sporting and Collectibles | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | 0 | | | US | 508-385-3116 | | | info@eldreds.com | 0 | 0 | 0 | 0 | ??? | 99.0 | 0 | 0 | | | 0 | | | | | | | |
| a | 64 | 0 | C | 16 | 1 | | a | Ronald | Bourgeault | Auctioneer | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | 0 | | | US | 603-433-8400 | | | contact@northeastauctions.com | 0 | 0 | 0 | 0 | ??? | 99.0 | 0 | 0 | | | 0 | | | | | | | |
| a | 68 | 0 | C | 16 | 2 | | a | Devin | Moisan | Assistant Auctioneer | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | 0 | | | US | | | | | 0 | 0 | 0 | 0 | ??? | 99.0 | 0 | 0 | | | 0 | | | | | | | |
| a | 77 | 0 | C | 31 | 1 | | a | Paul | Gorzocoski III | Auctioneer | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | 0 | | | US | (413) 498-4420 | | | gorz@shaysnet.com | 0 | 0 | 0 | 0 | ??? | 99.0 | 0 | 0 | | | 0 | | | | | | | |
+-------------+------------+------------------+-------+----------+------+------------+------------+-------------------+------------------+-----------------------+-------------------------------------------------+-----------+---------------+----------------------------------------+---------------+-------------+---------------+-------------+-------------+-------------+-------------+------------+------------+----------------------------+--------------------+-------------------------+----------+-----------+------------+------------+-------------------+-------------------+-----------+-------------------------------+------------------+-------------------+-------------------+-------------+-----------+------------+---------------+---------------+-------------+------------------------------------------------------------+-----------------+---------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
It did not convert characters from aTimeZone (varchar(4)), while the script on
CentOS 6.6 did.
Do you know why it did not comver the characters?
Regards,
Jacob Nikom
Thank you for your help.
I added --force-utf8-for-source option to my script.
Here is the script with this option:
# Source and target DB passwords
arg_source_password="***********"
arg_target_password="***********"
if [ -z "$arg_source_password" ] && [ -z "$arg_target_password" ] ; then
echo WARNING: Both source and target RDBMSes passwords are empty. You should edit this file to set them.
fi
# Source server parameters
# arg_source_database_server="10.20.100.13"
src_server="AZSQLDB.invaluable.corp"
src_port="1433"
src_database="rfcAnalysis"
src_uid="OSDprod"
# Target server parameters
# arg_target_database_server="root@127.0.0.1:3317"
arg_target_database_server="root@127.0.0.1:3306"
echo "arg_target_database_server = $arg_target_database_server"
# Whether target tables should be truncated before copy
arg_truncate_target="--truncate-target"
# Migration operation parameters
arg_worker_count=2
# Enable debugging output
arg_debug_output=--log-level=debug3
# Disable triggers
arg_disable_triggers=--dont-disable-triggers
export LD_LIBRARY_PATH=.:/local/lib:/usr/lib:/usr/lib/X11:/usr/local/lib:/usr/local/pgsql/lib:/usr/lib/debug/usr/lib64/mysql-workbench:/usr/lib64/mysql-workbench
/usr/libexec/mysql-workbench/wbcopytables-bin \
--odbc-source="DRIVER={MyFreeTDSDriver};SERVER=$src_server;PORT=$src_port;DATABASE=$src_database;UID=$src_uid;TDS_VERSION=7.1" \
--target="$arg_target_database_server" \
--source-password="$arg_source_password" \
--target-password="$arg_target_password" \
--thread-count=$arg_worker_count $arg_truncate_target $arg_debug_output \
--force-utf8-for-source \
--table '[rfcAnalysis]' '[dbo].[houseAddress]' '`rfcAnalysis`' '`houseAddress`' '[aAddressID]' '`aAddressID`' 'CAST([aRecordStat] as NCHAR(1)) as [aRecordStat], [aAddressID], [aAddressParentID], CAST([aType] as NCHAR(1)) as [aType], [aHouseID], [aSeq], CAST([aPubStatus] as NCHAR(1)) as [aPubStatus], CAST([aUseStatus] as NCHAR(1)) as [aUseStatus], CAST([aContactFirstName] as NVARCHAR(50)) as [aContactFirstName], CAST([aContactLastName] as NVARCHAR(50)) as [aContactLastName], CAST([aTitle] as NVARCHAR(75)) as [aTitle], CAST([aDept] as NVARCHAR(75)) as [aDept], CAST([aLocAlias] as NVARCHAR(100)) as [aLocAlias], [aScraperAlias], CAST([aFacilityName] as NVARCHAR(75)) as [aFacilityName], CAST([aEbayLocAlias] as NVARCHAR(45)) as [aEbayLocAlias], [aHeadOffice], [aBranchOffice], [aSellingLoc], [aSellingDft], [aPreviewLoc], [aPreviewDft], [aOfficeLoc], [aOfficeDft], CAST([aStreet1] as NVARCHAR(75)) as [aStreet1], CAST([aStreet2] as NVARCHAR(75)) as [aStreet2], CAST([aCity] as NVARCHAR(50)) as [aCity], CAST([aStateID] as NVARCHAR(4)) as [aStateID], CAST([aProvince] as NVARCHAR(30)) as [aProvince], CAST([aPostCode] as NVARCHAR(15)) as [aPostCode], CAST([aCountryID] as NVARCHAR(2)) as [aCountryID], CAST([aTelNo] as NVARCHAR(50)) as [aTelNo], CAST([aFaxNo] as NVARCHAR(50)) as [aFaxNo], CAST([aTollFree] as NVARCHAR(20)) as [aTollFree], CAST([aEMail] as NVARCHAR(64)) as [aEMail], [aExpertContactID], [aConsignContactID], [aBiddingContactID], [aCurrencyID], CAST([aTimeZone] as NVARCHAR(4)) as [aTimeZone], [aGMToffset], [aCommIncluded], [aCommissionID], [aDirections], CAST([aBuyPremium] as NVARCHAR(250)) as [aBuyPremium], [aPremiumPlusVAT], CAST([aViewingNotes] as NVARCHAR(2500)) as [aViewingNotes], CAST([aDisplayNotes] as NVARCHAR(2500)) as [aDisplayNotes], CAST([aSaleNotes] as NVARCHAR(2500)) as [aSaleNotes], [aNotes], CAST([aInstructions] as NVARCHAR(1200)) as [aInstructions], CAST([aSourceType] as NVARCHAR(8)) as [aSourceType], CAST([aSourcePath] as NVARCHAR(1000)) as [aSourcePath]'
echo "xxx Finshed bash script to migrate houseAddress table from SQL Server to MySQL rfcAnalysis database" `date`
Here is the output of the execution of this script:
shell> copy_migrated_tables_houseAddress_2018_03_01_rhel7.sh
xxx Start bash script to migrate houseAddress table from SQL Server to MySQL rfcAnalysis database Thu Mar 1 15:57:34 EST 2018
arg_target_database_server = root@127.0.0.1:3306
Logger set to level 'debug3'. '1111111'
15:57:34 [INF][ copytable]: Connecting to MySQL server at 127.0.0.1:3306 with user root
15:57:34 [INF][ copytable]: Connection to MySQL opened
15:57:34 [DB1][ copytable]: Detected server version=5.6.39-enterprise-commercial-advanced
15:57:34 [DB1][ copytable]: Detected max_allowed_packet=134217728
15:57:34 [DB1][ copytable]: Retrieving trigger list
15:57:34 [INF][ copytable]: Opening ODBC connection to [unknown] 'DRIVER={MyFreeTDSDriver};SERVER=AZSQLDB.invaluable.corp;PORT=1433;DATABASE=rfcAnalysis;UID=OSDprod;TDS_VERSION=7.1;PWD=XXX'
15:57:34 [INF][ copytable]: ODBC connection to 'DRIVER={MyFreeTDSDriver};SERVER=AZSQLDB.invaluable.corp;PORT=1433;DATABASE=rfcAnalysis;UID=OSDprod;TDS_VERSION=7.1;PWD=' opened
15:57:34 [INF][ copytable]: Connecting to MySQL server at 127.0.0.1:3306 with user root
15:57:34 [INF][ copytable]: Connection to MySQL opened
15:57:34 [DB1][ copytable]: Detected server version=5.6.39-enterprise-commercial-advanced
15:57:34 [DB1][ copytable]: Detected max_allowed_packet=134217728
15:57:34 [INF][ copytable]: Opening ODBC connection to [unknown] 'DRIVER={MyFreeTDSDriver};SERVER=AZSQLDB.invaluable.corp;PORT=1433;DATABASE=rfcAnalysis;UID=OSDprod;TDS_VERSION=7.1;PWD=XXX'
15:57:34 [DB1][ copytable]: Executing query: SELECT count(*) FROM [rfcAnalysis].[dbo].[houseAddress]
15:57:34 [DB1][ copytable]: Executing query: SELECT CAST([aRecordStat] as NCHAR(1)) as [aRecordStat], [aAddressID], [aAddressParentID], CAST([aType] as NCHAR(1)) as [aType], [aHouseID], [aSeq], CAST([aPubStatus] as NCHAR(1)) as [aPubStatus], CAST([aUseStatus] as NCHAR(1)) as [aUseStatus], CAST([aContactFirstName] as NVARCHAR(50)) as [aContactFirstName], CAST([aContactLastName] as NVARCHAR(50)) as [aContactLastName], CAST([aTitle] as NVARCHAR(75)) as [aTitle], CAST([aDept] as NVARCHAR(75)) as [aDept], CAST([aLocAlias] as NVARCHAR(100)) as [aLocAlias], [aScraperAlias], CAST([aFacilityName] as NVARCHAR(75)) as [aFacilityName], CAST([aEbayLocAlias] as NVARCHAR(45)) as [aEbayLocAlias], [aHeadOffice], [aBranchOffice], [aSellingLoc], [aSellingDft], [aPreviewLoc], [aPreviewDft], [aOfficeLoc], [aOfficeDft], CAST([aStreet1] as NVARCHAR(75)) as [aStreet1], CAST([aStreet2] as NVARCHAR(75)) as [aStreet2], CAST([aCity] as NVARCHAR(50)) as [aCity], CAST([aStateID] as NVARCHAR(4)) as [aStateID], CAST([aProvince] as NVARCHAR(30)) as [aProvince], CAST([aPostCode] as NVARCHAR(15)) as [aPostCode], CAST([aCountryID] as NVARCHAR(2)) as [aCountryID], CAST([aTelNo] as NVARCHAR(50)) as [aTelNo], CAST([aFaxNo] as NVARCHAR(50)) as [aFaxNo], CAST([aTollFree] as NVARCHAR(20)) as [aTollFree], CAST([aEMail] as NVARCHAR(64)) as [aEMail], [aExpertContactID], [aConsignContactID], [aBiddingContactID], [aCurrencyID], CAST([aTimeZone] as NVARCHAR(4)) as [aTimeZone], [aGMToffset], [aCommIncluded], [aCommissionID], [aDirections], CAST([aBuyPremium] as NVARCHAR(250)) as [aBuyPremium], [aPremiumPlusVAT], CAST([aViewingNotes] as NVARCHAR(2500)) as [aViewingNotes], CAST([aDisplayNotes] as NVARCHAR(2500)) as [aDisplayNotes], CAST([aSaleNotes] as NVARCHAR(2500)) as [aSaleNotes], [aNotes], CAST([aInstructions] as NVARCHAR(1200)) as [aInstructions], CAST([aSourceType] as NVARCHAR(8)) as [aSourceType], CAST([aSourcePath] as NVARCHAR(1000)) as [aSourcePath] FROM [rfcAnalysis].[dbo].[houseAddress] ORDER BY [aAddressID]
15:57:34 [DB2][ copytable]: Columns from source table [rfcAnalysis].[dbo].[houseAddress] (53):
15:57:34 [DB2][ copytable]: 1 - aRecordStat: nchar (type=SQL_WCHAR, len=4)
15:57:34 [DB2][ copytable]: 2 - aAddressID: int (type=SQL_INTEGER, len=10)
15:57:34 [DB2][ copytable]: 3 - aAddressParentID: int (type=SQL_INTEGER, len=10)
15:57:34 [DB2][ copytable]: 4 - aType: nchar (type=SQL_WCHAR, len=4)
15:57:34 [DB2][ copytable]: 5 - aHouseID: int (type=SQL_INTEGER, len=10)
15:57:34 [DB2][ copytable]: 6 - aSeq: smallint (type=SQL_SMALLINT, len=5)
15:57:34 [DB2][ copytable]: 7 - aPubStatus: nchar (type=SQL_WCHAR, len=4)
15:57:34 [DB2][ copytable]: 8 - aUseStatus: nchar (type=SQL_WCHAR, len=4)
15:57:34 [DB2][ copytable]: 9 - aContactFirstName: nvarchar (type=SQL_WVARCHAR, len=200)
15:57:34 [DB2][ copytable]: 10 - aContactLastName: nvarchar (type=SQL_WVARCHAR, len=200)
15:57:34 [DB2][ copytable]: 11 - aTitle: nvarchar (type=SQL_WVARCHAR, len=300)
15:57:34 [DB2][ copytable]: 12 - aDept: nvarchar (type=SQL_WVARCHAR, len=300)
15:57:34 [DB2][ copytable]: 13 - aLocAlias: nvarchar (type=SQL_WVARCHAR, len=400)
15:57:34 [DB2][ copytable]: 14 - aScraperAlias: nvarchar (type=SQL_WVARCHAR, len=400)
15:57:34 [DB2][ copytable]: 15 - aFacilityName: nvarchar (type=SQL_WVARCHAR, len=300)
15:57:34 [DB2][ copytable]: 16 - aEbayLocAlias: nvarchar (type=SQL_WVARCHAR, len=180)
15:57:34 [DB2][ copytable]: 17 - aHeadOffice: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 18 - aBranchOffice: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 19 - aSellingLoc: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 20 - aSellingDft: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 21 - aPreviewLoc: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 22 - aPreviewDft: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 23 - aOfficeLoc: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 24 - aOfficeDft: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 25 - aStreet1: nvarchar (type=SQL_WVARCHAR, len=300)
15:57:34 [DB2][ copytable]: 26 - aStreet2: nvarchar (type=SQL_WVARCHAR, len=300)
15:57:34 [DB2][ copytable]: 27 - aCity: nvarchar (type=SQL_WVARCHAR, len=200)
15:57:34 [DB2][ copytable]: 28 - aStateID: nvarchar (type=SQL_WVARCHAR, len=16)
15:57:34 [DB2][ copytable]: 29 - aProvince: nvarchar (type=SQL_WVARCHAR, len=120)
15:57:34 [DB2][ copytable]: 30 - aPostCode: nvarchar (type=SQL_WVARCHAR, len=60)
15:57:34 [DB2][ copytable]: 31 - aCountryID: nvarchar (type=SQL_WVARCHAR, len=8)
15:57:34 [DB2][ copytable]: 32 - aTelNo: nvarchar (type=SQL_WVARCHAR, len=200)
15:57:34 [DB2][ copytable]: 33 - aFaxNo: nvarchar (type=SQL_WVARCHAR, len=200)
15:57:34 [DB2][ copytable]: 34 - aTollFree: nvarchar (type=SQL_WVARCHAR, len=80)
15:57:34 [DB2][ copytable]: 35 - aEMail: nvarchar (type=SQL_WVARCHAR, len=256)
15:57:34 [DB2][ copytable]: 36 - aExpertContactID: int (type=SQL_INTEGER, len=10)
15:57:34 [DB2][ copytable]: 37 - aConsignContactID: int (type=SQL_INTEGER, len=10)
15:57:34 [DB2][ copytable]: 38 - aBiddingContactID: int (type=SQL_INTEGER, len=10)
15:57:34 [DB2][ copytable]: 39 - aCurrencyID: tinyint UNSIGNED (type=SQL_TINYINT, len=3)
15:57:34 [DB2][ copytable]: 40 - aTimeZone: nvarchar (type=SQL_WVARCHAR, len=16)
15:57:34 [DB2][ copytable]: 41 - aGMToffset: decimal (type=SQL_NUMERIC, len=3)
15:57:34 [DB2][ copytable]: 42 - aCommIncluded: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 43 - aCommissionID: smallint (type=SQL_SMALLINT, len=5)
15:57:34 [DB2][ copytable]: 44 - aDirections: ntext (type=SQL_WLONGVARCHAR, len=4294967292, long_data)
15:57:34 [DB2][ copytable]: 45 - aBuyPremium: nvarchar (type=SQL_WVARCHAR, len=1000)
15:57:34 [DB2][ copytable]: 46 - aPremiumPlusVAT: bit UNSIGNED (type=SQL_BIT, len=1)
15:57:34 [DB2][ copytable]: 47 - aViewingNotes: nvarchar (type=SQL_WVARCHAR, len=10000)
15:57:34 [DB2][ copytable]: 48 - aDisplayNotes: nvarchar (type=SQL_WVARCHAR, len=10000)
15:57:34 [DB2][ copytable]: 49 - aSaleNotes: nvarchar (type=SQL_WVARCHAR, len=10000)
15:57:34 [DB2][ copytable]: 50 - aNotes: ntext (type=SQL_WLONGVARCHAR, len=4294967292, long_data)
15:57:34 [DB2][ copytable]: 51 - aInstructions: nvarchar (type=SQL_WVARCHAR, len=4800)
15:57:34 [DB2][ copytable]: 52 - aSourceType: nvarchar (type=SQL_WVARCHAR, len=32)
15:57:34 [DB2][ copytable]: 53 - aSourcePath: nvarchar (type=SQL_WVARCHAR, len=4000)
BEGIN:`rfcAnalysis`.`houseAddress`:Copying 53 columns of 23610 rows from table [rfcAnalysis].[dbo].[houseAddress]
15:57:34 [DB2][ copytable]: Columns from target table `rfcAnalysis`.`houseAddress` (53) [skipped: 0]:
15:57:34 [DB2][ copytable]: 1 - aRecordStat: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 2 - aAddressID: MYSQL_TYPE_LONG
15:57:34 [DB2][ copytable]: 3 - aAddressParentID: MYSQL_TYPE_LONG
15:57:34 [DB2][ copytable]: 4 - aType: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 5 - aHouseID: MYSQL_TYPE_LONG
15:57:34 [DB2][ copytable]: 6 - aSeq: MYSQL_TYPE_SHORT
15:57:34 [DB2][ copytable]: 7 - aPubStatus: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 8 - aUseStatus: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 9 - aContactFirstName: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 10 - aContactLastName: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 11 - aTitle: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 12 - aDept: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 13 - aLocAlias: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 14 - aScraperAlias: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 15 - aFacilityName: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 16 - aEbayLocAlias: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 17 - aHeadOffice: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 18 - aBranchOffice: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 19 - aSellingLoc: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 20 - aSellingDft: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 21 - aPreviewLoc: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 22 - aPreviewDft: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 23 - aOfficeLoc: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 24 - aOfficeDft: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 25 - aStreet1: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 26 - aStreet2: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 27 - aCity: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 28 - aStateID: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 29 - aProvince: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 30 - aPostCode: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 31 - aCountryID: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 32 - aTelNo: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 33 - aFaxNo: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 34 - aTollFree: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 35 - aEMail: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 36 - aExpertContactID: MYSQL_TYPE_LONG
15:57:34 [DB2][ copytable]: 37 - aConsignContactID: MYSQL_TYPE_LONG
15:57:34 [DB2][ copytable]: 38 - aBiddingContactID: MYSQL_TYPE_LONG
15:57:34 [DB2][ copytable]: 39 - aCurrencyID: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 40 - aTimeZone: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 41 - aGMToffset: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 42 - aCommIncluded: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 43 - aCommissionID: MYSQL_TYPE_SHORT
15:57:34 [DB2][ copytable]: 44 - aDirections: MYSQL_TYPE_BLOB
15:57:34 [DB2][ copytable]: 45 - aBuyPremium: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 46 - aPremiumPlusVAT: MYSQL_TYPE_TINY
15:57:34 [DB2][ copytable]: 47 - aViewingNotes: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 48 - aDisplayNotes: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 49 - aSaleNotes: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 50 - aNotes: MYSQL_TYPE_BLOB
15:57:34 [DB2][ copytable]: 51 - aInstructions: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 52 - aSourceType: MYSQL_TYPE_STRING
15:57:34 [DB2][ copytable]: 53 - aSourcePath: MYSQL_TYPE_STRING
15:57:34 [INF][ copytable]: Truncating table `rfcAnalysis`.`houseAddress`
15:57:34 [INF][ copytable]: ODBC connection to 'DRIVER={MyFreeTDSDriver};SERVER=AZSQLDB.invaluable.corp;PORT=1433;DATABASE=rfcAnalysis;UID=OSDprod;TDS_VERSION=7.1;PWD=' opened
15:57:34 [INF][ copytable]: Connecting to MySQL server at 127.0.0.1:3306 with user root
15:57:34 [INF][ copytable]: Connection to MySQL opened
15:57:34 [DB1][ copytable]: Detected server version=5.6.39-enterprise-commercial-advanced
15:57:34 [DB1][ copytable]: Detected max_allowed_packet=134217728
15:57:36 [WRN][ copytable]: [42000 - 2403]: [FreeTDS][SQL Server]Some character(s) could not be converted into client's character set. Unconverted bytes were changed to question marks ('?')
END:`rfcAnalysis`.`houseAddress`:Finished copying 23610 rows in 0m07s
15:57:41 [INF][ copytable]: Re-enabling triggers for schema 'rfcAnalysis'
15:57:41 [DB1][ copytable]: Retrieving trigger definitions
15:57:41 [INF][ copytable]: No triggers found for 'rfcAnalysis'
FINISHED
xxx Finshed bash script to migrate houseAddress table from SQL Server to MySQL rfcAnalysis database Thu Mar 1 15:57:41 EST 2018
shell>
As you see it could not
Here is the character sets on the server:
MySQL [(none)]> show variables like "%character%";
+--------------------------+-------------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /ods/local/mysql-advanced-5.6.39-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+-------------------------------------------------------------------------+
8 rows in set (0.00 sec)
MySQL [(none)]>
Here is the putput of the command:
mysql> SELECT * FROM rfcAnalysis.houseAddress LIMIT 33;
+-------------+------------+------------------+-------+----------+------+------------+------------+-------------------+------------------+-----------------------+-------------------------------------------------+-----------+---------------+----------------------------------------+---------------+-------------+---------------+-------------+-------------+-------------+-------------+------------+------------+----------------------------+--------------------+-------------------------+----------+-----------+------------+------------+-------------------+-------------------+-----------+-------------------------------+------------------+-------------------+-------------------+-------------+-----------+------------+---------------+---------------+-------------+------------------------------------------------------------+-----------------+---------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
| aRecordStat | aAddressID | aAddressParentID | aType | aHouseID | aSeq | aPubStatus | aUseStatus | aContactFirstName | aContactLastName | aTitle | aDept | aLocAlias | aScraperAlias | aFacilityName | aEbayLocAlias | aHeadOffice | aBranchOffice | aSellingLoc | aSellingDft | aPreviewLoc | aPreviewDft | aOfficeLoc | aOfficeDft | aStreet1 | aStreet2 | aCity | aStateID | aProvince | aPostCode | aCountryID | aTelNo | aFaxNo | aTollFree | aEMail | aExpertContactID | aConsignContactID | aBiddingContactID | aCurrencyID | aTimeZone | aGMToffset | aCommIncluded | aCommissionID | aDirections | aBuyPremium | aPremiumPlusVAT | aViewingNotes | aDisplayNotes | aSaleNotes | aNotes | aInstructions | aSourceType | aSourcePath |
+-------------+------------+------------------+-------+----------+------+------------+------------+-------------------+------------------+-----------------------+-------------------------------------------------+-----------+---------------+----------------------------------------+---------------+-------------+---------------+-------------+-------------+-------------+-------------+------------+------------+----------------------------+--------------------+-------------------------+----------+-----------+------------+------------+-------------------+-------------------+-----------+-------------------------------+------------------+-------------------+-------------------+-------------+-----------+------------+---------------+---------------+-------------+------------------------------------------------------------+-----------------+---------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
| a | 39 | 0 | L | 40 | 0 | | a | | | | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 358 Broadway | | Cambridge | MA | | 02139 | US | 00 1 617 354 7919 | | | info@sbauctioneers.com | 0 | 0 | 0 | 1 | EST | -5.0 | 0 | 0 | | 10.00% | 0 | | | | | | | |
| a | 43 | 0 | C | 11 | 1 | | a | Robert | Eldred | President | American, Marine, Books | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | 0 | | | US | 508-385-3116 | | | info@eldreds.com | 0 | 0 | 0 | 0 | ??? | 99.0 | 0 | 0 | | | 0 | | | | | | | |
| a | 44 | 0 | C | 11 | 2 | | a | John | Schofield | Vice President | Asian Art, European Furniture and Art, Fine Art | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | 0 | | | US | 508-385-3116 | | | info@eldreds.com | 0 | 0 | 0 | 0 | ??? | 99.0 | 0 | 0 | | | 0 | | | | | | | |
| a | 45 | 0 | C | 11 | 3 | | a | Susan | Schofield | Treasurer | Paul Jacoulet Prints | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | 0 | | | US | 508-385-3116 | | | sue@eldreds.com | 0 | 0 | 0 | 0 | ??? | 99.0 | 0 | 0 | | | 0 | | | | | | | |
| a | 46 | 0 | C | 11 | 4 | | a | Mary Ann | Eldred | Director of Marketing | Antiques Postcards | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | 0 | | | US | 508-385-3116 | | | info@eldreds.com | 0 | 0 | 0 | 0 | ??? | 99.0 | 0 | 0 | | | 0 | | | | | | | |
| a | 47 | 0 | C | 11 | 5 | | a | Eric | Mulak | Appraiser | Sporting and Collectibles | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | 0 | | | US | 508-385-3116 | | | info@eldreds.com | 0 | 0 | 0 | 0 | ??? | 99.0 | 0 | 0 | | | 0 | | | | | | | |
| a | 64 | 0 | C | 16 | 1 | | a | Ronald | Bourgeault | Auctioneer | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | 0 | | | US | 603-433-8400 | | | contact@northeastauctions.com | 0 | 0 | 0 | 0 | ??? | 99.0 | 0 | 0 | | | 0 | | | | | | | |
| a | 68 | 0 | C | 16 | 2 | | a | Devin | Moisan | Assistant Auctioneer | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | 0 | | | US | | | | | 0 | 0 | 0 | 0 | ??? | 99.0 | 0 | 0 | | | 0 | | | | | | | |
| a | 77 | 0 | C | 31 | 1 | | a | Paul | Gorzocoski III | Auctioneer | | | | | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | 0 | | | US | (413) 498-4420 | | | gorz@shaysnet.com | 0 | 0 | 0 | 0 | ??? | 99.0 | 0 | 0 | | | 0 | | | | | | | |
+-------------+------------+------------------+-------+----------+------+------------+------------+-------------------+------------------+-----------------------+-------------------------------------------------+-----------+---------------+----------------------------------------+---------------+-------------+---------------+-------------+-------------+-------------+-------------+------------+------------+----------------------------+--------------------+-------------------------+----------+-----------+------------+------------+-------------------+-------------------+-----------+-------------------------------+------------------+-------------------+-------------------+-------------+-----------+------------+---------------+---------------+-------------+------------------------------------------------------------+-----------------+---------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+
It did not convert characters from aTimeZone (varchar(4)), while the script on
CentOS 6.6 did.
Do you know why it did not comver the characters?
Regards,
Jacob Nikom
Subject
Written By
Posted
February 22, 2018 12:51PM
February 23, 2018 06:41AM
February 23, 2018 01:36PM
February 23, 2018 02:02PM
February 27, 2018 01:14AM
February 27, 2018 02:59PM
February 28, 2018 09:08AM
Re: For the same MSSQL table migration script works in CentOS 6.6, but fails partially in CentOS 7.2, 7.3 and 7.4
March 01, 2018 03:26PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.