Description:
When running mysqldbcopy on real-world data, it crashes while attempting to insert data. The inserts seem to be related to special characters (apostrophes, etc as in marketing copy), but it also occurs in tables with simple alphanumeric data.
The errors are mainly 'problem inserting data'
i.e:
"Problem inserting data. Error = Query failed. 1064 (42000): You have an error in your SQL syntax...
but I also see data being broken out of the value and interpreted as a column name, i.e:
"Unknown column 'sprint' in 'field list'", 0)
and even data being interpreted as a function:
"UtilError: ('Problem inserting data. Error = Query failed. 1305 (42000): FUNCTION Mobile does not exist', 0)"
Maybe the values are not returned properly escaped.
Then the program lost the mysql handle:
mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query
A longer dump is below.
This tool looks like it could really make our lives easier, if it can be made stable.
Thank you.
Anthony
How to repeat:
# Copying data for TABLE moms3.active_message
# Using multi-threaded insert option. Number of threads = 4.
Process Process-1:
Traceback (most recent call last):
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/multiprocessing/process.py", line 232, in _bootstrap
self.run()
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/multiprocessing/process.py", line 88, in run
self._target(*self._args, **self._kwargs)
File "/Library/Python/2.7/site-packages/mysql/utilities/common/table.py", line 625, in _bulk_insert
"Error = %s" % e.errmsg)
UtilError: ("Problem inserting data. Error = Query failed. 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 4, 1, 0, '2012-09-14 14:17:24', '2012-09-14 14:17:24'), (124563371, ipr06c16, ' at line 1", 0)
....
UtilError: ("Problem inserting data. Error = Query failed. 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 4, 1, 0, '2012-09-07 12:44:31', '2012-09-07 12:44:31'), (121246413, ke32qdvw, ' at line 1", 0)
Process Process-3:
....
UtilError: ("Problem inserting data. Error = Query failed. 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 4, 1, 0, '2012-09-01 10:34:03', '2012-09-01 10:34:03'), (121433663, o9z3wqzm, ' at line 1", 0)
# Copying data for TABLE moms3.aggregator
Process Process-6:
...
UtilError: ("Problem inserting data. Error = Query failed. 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IPX, ericsson, '2010-04-22 21:52:51'), (9, sybase, Sybase, sybase, '2012-02-15 ' at line 1", 0)
# Copying data for TABLE moms3.aggregator_carrier
Process Process-7:
...
UtilError: ('Problem inserting data. Error = Query failed. 1305 (42000): FUNCTION Mobile does not exist', 0)
# Copying data for TABLE moms3.aggregator_shortcode_carrier
# Copying data for TABLE moms3.billing_ledger
Process Process-9:
Traceback (most recent call last):
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/multiprocessing/process.py", line 232, in _bootstrap
self.run()
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/multiprocessing/process.py", line 88, in run
self._target(*self._args, **self._kwargs)
File "/Library/Python/2.7/site-packages/mysql/utilities/common/table.py", line 625, in _bulk_insert
"Error = %s" % e.errmsg)
UtilError: ("Problem inserting data. Error = Query failed. 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' '2012-09-09 08:45:33', '2012-09-09 08:45:33'), (14062027, 119809055, 4, 0.99, ' at line 1", 0)
# Copying data for TABLE moms3.blacklist
Process Process-4:
Traceback (most recent call last):
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/multiprocessing/process.py", line 232, in _bootstrap
self.run()
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/multiprocessing/process.py", line 88, in run
self._target(*self._args, **self._kwargs)
File "/Library/Python/2.7/site-packages/mysql/utilities/common/table.py", line 625, in _bulk_insert
"Error = %s" % e.errmsg)
UtilError: ("Problem inserting data. Error = Query failed. 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 4, 1, 0, '2012-09-19 10:15:04', '2012-09-19 10:15:04'), (123025615, whgl84z2, ' at line 1", 0)
Process Process-5:
Traceback (most recent call last):
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/multiprocessing/process.py", line 232, in _bootstrap
self.run()
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/multiprocessing/process.py", line 88, in run
self._target(*self._args, **self._kwargs)
File "/Library/Python/2.7/site-packages/mysql/utilities/common/table.py", line 625, in _bulk_insert
"Error = %s" % e.errmsg)
....
"Error = %s" % e.errmsg)
UtilError: ("Problem inserting data. Error = Query failed. 1054 (42S22): Unknown column 'sprint' in 'field list'", 0)
# Copying data for TABLE moms3.blob_hash
Traceback (most recent call last):
File "/usr/local/bin/mysqldbcopy", line 203, in <module>
dbcopy.copy_db(source_values, dest_values, db_list, options)
File "/Library/Python/2.7/site-packages/mysql/utilities/command/dbcopy.py", line 319, in copy_db
options.get("threads", False))
File "/Library/Python/2.7/site-packages/mysql/utilities/common/database.py", line 527, in copy_data
tbl.copy_data(self.destination, self.cloning, new_db, connections)
File "/Library/Python/2.7/site-packages/mysql/utilities/common/table.py", line 719, in copy_data
for rows in self.retrieve_rows(num_conn):
File "/Library/Python/2.7/site-packages/mysql/utilities/common/table.py", line 770, in retrieve_rows
rows = cur.fetchmany(segment_size)
File "/Library/Python/2.7/site-packages/mysql/connector/cursor.py", line 609, in fetchmany
row = self.fetchone()
File "/Library/Python/2.7/site-packages/mysql/connector/cursor.py", line 734, in fetchone
row = self._fetch_row()
File "/Library/Python/2.7/site-packages/mysql/connector/cursor.py", line 579, in _fetch_row
(foo, eof) = self._nextrow = self._connection.get_row()
File "/Library/Python/2.7/site-packages/mysql/connector/connection.py", line 468, in get_row
(rows, eof) = self.get_rows(count=1)
File "/Library/Python/2.7/site-packages/mysql/connector/connection.py", line 451, in get_rows
rows = self._protocol.read_text_result(self._socket, count)
File "/Library/Python/2.7/site-packages/mysql/connector/protocol.py", line 234, in read_text_result
packet = sock.recv()
File "/Library/Python/2.7/site-packages/mysql/connector/network.py", line 169, in recv_plain
raise errors.InterfaceError(errno=2013)
mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query
real 6m14.043s
user 6m26.977s
sys 1m23.255s
Suggested fix:
encapulate or escape the data before inserting.
Description: When running mysqldbcopy on real-world data, it crashes while attempting to insert data. The inserts seem to be related to special characters (apostrophes, etc as in marketing copy), but it also occurs in tables with simple alphanumeric data. The errors are mainly 'problem inserting data' i.e: "Problem inserting data. Error = Query failed. 1064 (42000): You have an error in your SQL syntax... but I also see data being broken out of the value and interpreted as a column name, i.e: "Unknown column 'sprint' in 'field list'", 0) and even data being interpreted as a function: "UtilError: ('Problem inserting data. Error = Query failed. 1305 (42000): FUNCTION Mobile does not exist', 0)" Maybe the values are not returned properly escaped. Then the program lost the mysql handle: mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query A longer dump is below. This tool looks like it could really make our lives easier, if it can be made stable. Thank you. Anthony How to repeat: # Copying data for TABLE moms3.active_message # Using multi-threaded insert option. Number of threads = 4. Process Process-1: Traceback (most recent call last): File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/multiprocessing/process.py", line 232, in _bootstrap self.run() File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/multiprocessing/process.py", line 88, in run self._target(*self._args, **self._kwargs) File "/Library/Python/2.7/site-packages/mysql/utilities/common/table.py", line 625, in _bulk_insert "Error = %s" % e.errmsg) UtilError: ("Problem inserting data. Error = Query failed. 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 4, 1, 0, '2012-09-14 14:17:24', '2012-09-14 14:17:24'), (124563371, ipr06c16, ' at line 1", 0) .... UtilError: ("Problem inserting data. Error = Query failed. 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 4, 1, 0, '2012-09-07 12:44:31', '2012-09-07 12:44:31'), (121246413, ke32qdvw, ' at line 1", 0) Process Process-3: .... UtilError: ("Problem inserting data. Error = Query failed. 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 4, 1, 0, '2012-09-01 10:34:03', '2012-09-01 10:34:03'), (121433663, o9z3wqzm, ' at line 1", 0) # Copying data for TABLE moms3.aggregator Process Process-6: ... UtilError: ("Problem inserting data. Error = Query failed. 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IPX, ericsson, '2010-04-22 21:52:51'), (9, sybase, Sybase, sybase, '2012-02-15 ' at line 1", 0) # Copying data for TABLE moms3.aggregator_carrier Process Process-7: ... UtilError: ('Problem inserting data. Error = Query failed. 1305 (42000): FUNCTION Mobile does not exist', 0) # Copying data for TABLE moms3.aggregator_shortcode_carrier # Copying data for TABLE moms3.billing_ledger Process Process-9: Traceback (most recent call last): File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/multiprocessing/process.py", line 232, in _bootstrap self.run() File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/multiprocessing/process.py", line 88, in run self._target(*self._args, **self._kwargs) File "/Library/Python/2.7/site-packages/mysql/utilities/common/table.py", line 625, in _bulk_insert "Error = %s" % e.errmsg) UtilError: ("Problem inserting data. Error = Query failed. 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' '2012-09-09 08:45:33', '2012-09-09 08:45:33'), (14062027, 119809055, 4, 0.99, ' at line 1", 0) # Copying data for TABLE moms3.blacklist Process Process-4: Traceback (most recent call last): File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/multiprocessing/process.py", line 232, in _bootstrap self.run() File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/multiprocessing/process.py", line 88, in run self._target(*self._args, **self._kwargs) File "/Library/Python/2.7/site-packages/mysql/utilities/common/table.py", line 625, in _bulk_insert "Error = %s" % e.errmsg) UtilError: ("Problem inserting data. Error = Query failed. 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 4, 1, 0, '2012-09-19 10:15:04', '2012-09-19 10:15:04'), (123025615, whgl84z2, ' at line 1", 0) Process Process-5: Traceback (most recent call last): File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/multiprocessing/process.py", line 232, in _bootstrap self.run() File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/multiprocessing/process.py", line 88, in run self._target(*self._args, **self._kwargs) File "/Library/Python/2.7/site-packages/mysql/utilities/common/table.py", line 625, in _bulk_insert "Error = %s" % e.errmsg) .... "Error = %s" % e.errmsg) UtilError: ("Problem inserting data. Error = Query failed. 1054 (42S22): Unknown column 'sprint' in 'field list'", 0) # Copying data for TABLE moms3.blob_hash Traceback (most recent call last): File "/usr/local/bin/mysqldbcopy", line 203, in <module> dbcopy.copy_db(source_values, dest_values, db_list, options) File "/Library/Python/2.7/site-packages/mysql/utilities/command/dbcopy.py", line 319, in copy_db options.get("threads", False)) File "/Library/Python/2.7/site-packages/mysql/utilities/common/database.py", line 527, in copy_data tbl.copy_data(self.destination, self.cloning, new_db, connections) File "/Library/Python/2.7/site-packages/mysql/utilities/common/table.py", line 719, in copy_data for rows in self.retrieve_rows(num_conn): File "/Library/Python/2.7/site-packages/mysql/utilities/common/table.py", line 770, in retrieve_rows rows = cur.fetchmany(segment_size) File "/Library/Python/2.7/site-packages/mysql/connector/cursor.py", line 609, in fetchmany row = self.fetchone() File "/Library/Python/2.7/site-packages/mysql/connector/cursor.py", line 734, in fetchone row = self._fetch_row() File "/Library/Python/2.7/site-packages/mysql/connector/cursor.py", line 579, in _fetch_row (foo, eof) = self._nextrow = self._connection.get_row() File "/Library/Python/2.7/site-packages/mysql/connector/connection.py", line 468, in get_row (rows, eof) = self.get_rows(count=1) File "/Library/Python/2.7/site-packages/mysql/connector/connection.py", line 451, in get_rows rows = self._protocol.read_text_result(self._socket, count) File "/Library/Python/2.7/site-packages/mysql/connector/protocol.py", line 234, in read_text_result packet = sock.recv() File "/Library/Python/2.7/site-packages/mysql/connector/network.py", line 169, in recv_plain raise errors.InterfaceError(errno=2013) mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query real 6m14.043s user 6m26.977s sys 1m23.255s Suggested fix: encapulate or escape the data before inserting.