mysql / mysql-utilities

MySQL Utilities is a set of easy-to-use scripts intended to make working with MySQL servers easier.
GNU General Public License v2.0
267 stars 161 forks source link

mysqldbcopy error, when use option --not-null-blobs #51

Open zj5220924 opened 4 years ago

zj5220924 commented 4 years ago
[root@mysqlm ~]# /usr/bin/mysqldbcopy --source=test:test@172.30.200.221:3306 --destination=dba:dba@172.30.200.201:3307 sql_review --drop-first --skip-gtid --character-set=utf8mb4 --not-null-blobs
WARNING: Using a password on the command line interface can be insecure.
WARNING: The following tables have blob fields set to NOT NULL.
    sql_review.mysql_slow_query_review_221 Column fingerprint
    sql_review.mysql_slow_query_review_221 Column sample
    sql_review.mysql_slow_query_review_history_221 Column sample

# Source on 172.30.200.221: ... connected.
# Destination on 172.30.200.201: ... connected.
# WARNING: The server supports GTIDs but you have elected to skip executing the GTID_EXECUTED statement. Please refer to the MySQL online reference manual for more information about how to handle GTID enabled servers with backup and restore operations.
# Copying database sql_review 
# Copying TABLE sql_review.mysql_slow_query_review_221
# Copying TABLE sql_review.mysql_slow_query_review_history_221
# Copying GRANTS from sql_review
ERROR: Query failed. 1146 (42S02): Table 'none.mysql_slow_query_review_221' doesn't exist
zj5220924 commented 4 years ago

fixed

vim /usr/lib/python2.7/site-packages/mysql/utilities/command/dbcopy.py ..................... def check_blobs_not_null(server, db_list, warn=False): .....................

blob_fields.append((row[0], row[1], row[2], db[1]))

            blob_fields.append((row[0], row[1], row[2], db[0]))

.....................

zj5220924 commented 4 years ago

and then, there is another issue,

[root@mysqlm ~]# /usr/bin/mysqldbcopy --source=test:test@172.30.200.221:3306 --destination=dba:dba@172.30.200.201:3307 sql_review --drop-first --skip-gtid --character-set=utf8mb4 --not-null-blobs && date
WARNING: Using a password on the command line interface can be insecure.
WARNING: The following tables have blob fields set to NOT NULL.
    sql_review.mysql_slow_query_review_221 Column fingerprint
    sql_review.mysql_slow_query_review_221 Column sample
    sql_review.mysql_slow_query_review_history_221 Column sample

# Source on 172.30.200.221: ... connected.
# Destination on 172.30.200.201: ... connected.
# WARNING: The server supports GTIDs but you have elected to skip executing the GTID_EXECUTED statement. Please refer to the MySQL online reference manual for more information about how to handle GTID enabled servers with backup and restore operations.
# Copying database sql_review 
# Copying TABLE sql_review.mysql_slow_query_review_221
# Copying TABLE sql_review.mysql_slow_query_review_history_221
# Copying GRANTS from sql_review
# Copying data for TABLE sql_review.mysql_slow_query_review_221
# Copying data for TABLE sql_review.mysql_slow_query_review_history_221
Killed
zj5220924 commented 3 years ago

fixed --not-null-blobs bugs

vim /usr/lib/python2.7/site-packages/mysql/utilities/common/table.py

587 if row[col] is None: 588 value = 'is NULL' 589 else: 590 value = "= '{0}'".format(row[col]) 591 where_values.append("{0} {1}".format(col_name, value))

vim /usr/lib/python2.7/site-packages/mysql/utilities/command/dbcopy.py 98 col_str_new = row.strip().strip(",").replace("NOT NULL","NULL")