py-mysql2pgsql
py-mysql2pgsql copied to clipboard
philipsoutham
COPY from stdin failed: error in .read() call
Hi, i'm trying to dump a big mysql database into a postgresql database, and i'm ending up with the following error
>> py-mysql2pgsql -v -f mysql2pgsql.yml
>>>>>>>>>> STARTING <<<<<<<<<<
START CREATING TABLES
START - CREATING TABLE PING
FINISH - CREATING TABLE PING
START - CREATING TABLE error
FINISH - CREATING TABLE error
START - CREATING TABLE ping
FINISH - CREATING TABLE ping
START - CREATING TABLE product
FINISH - CREATING TABLE product
START - CREATING TABLE updateRequest
FINISH - CREATING TABLE updateRequest
DONE CREATING TABLES
START WRITING TABLE DATA
START - WRITING DATA TO PING
8438.17 rows/sec [1440000]
FINISH - WRITING DATA TO PING
START - WRITING DATA TO error
FINISH - WRITING DATA TO error
START - WRITING DATA TO ping
Traceback (most recent call last):
File "/usr/bin/py-mysql2pgsql", line 5, in <module>
pkg_resources.run_script('py-mysql2pgsql==0.1.6', 'py-mysql2pgsql')
File "/usr/lib/python2.7/site-packages/pkg_resources.py", line 499, in run_script
self.require(requires)[0].run_script(script_name, ns)
File "/usr/lib/python2.7/site-packages/pkg_resources.py", line 1239, in run_script
execfile(script_filename, namespace, namespace)
File "/usr/lib/python2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/EGG-INFO/scripts/py-mysql2pgsql", line 38, in <module>
mysql2pgsql.Mysql2Pgsql(options).convert()
File "/usr/lib/python2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/mysql2pgsql.py", line 31, in convert
Converter(reader, writer, self.file_options, self.run_options.verbose).convert()
File "/usr/lib/python2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/converter.py", line 51, in convert
self.writer.write_contents(table, self.reader)
File "/usr/lib/python2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/__init__.py", line 86, in decorated_function
ret = f(*args, **kwargs)
File "/usr/lib/python2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/postgres_db_writer.py", line 193, in write_contents
self.copy_from(f, '"%s"' % table.name, ['"%s"' % c['name'] for c in table.columns])
File "/usr/lib/python2.7/site-packages/py_mysql2pgsql-0.1.6-py2.7.egg/mysql2pgsql/lib/postgres_db_writer.py", line 116, in copy_from
columns=columns
psycopg2.extensions.QueryCanceledError: COPY from stdin failed: error in .read() call
CONTEXT: COPY ping, line 693
No further information on the postgresql receiving side but :
ERROR: COPY from stdin failed: error in .read() call
CONTEXT: COPY ping, line 693
STATEMENT: COPY "ping"("id","ccVersion","detectedOs","login","number","pingType","productVersion","receivedAt","uuid","version","product_id") FROM stdin WITH DELIMITER AS ' ' NULL AS '\\N'
Any idea ?
Regards,
Olivier.
Is this where it fails consistently? I've had mystery failures before if the the query on the mysql side was cancelled for some reason either due to time outs or someone killing the pid.
Hello
I'm having a similar issue:
[root@DBSlave ~]# py-mysql2pgsql -v -f /root/mysql2pgsql.yml
STARTING <<<<<<<<<<
START CREATING TABLES
START - CREATING TABLE event
FINISH - CREATING TABLE event
DONE CREATING TABLES
START WRITING TABLE DATA
START - WRITING DATA TO event
Traceback (most recent call last):
File "/usr/bin/py-mysql2pgsql", line 38, in
[root@DBSlave ~]#
Maybe this is related to encoding stuff on the original mysql database?
Update:
I found this on postgres log:
WARNING: nonstandard use of \ in a string literal at character 309 HINT: Use the escape string syntax for backslashes, e.g., E''. ERROR: COPY from stdin failed: error in .read() call CONTEXT: COPY event, line 1 STATEMENT: COPY "event"("id","department_id","shift_id","start_date","end_date","num_users","deleted","comment","start_time","end_time","pub_date","answer_deadline","approval_type","owner_id","status","copy_of_id","resent_at","notification_sent_at","extended_at","last_change") FROM stdin WITH DELIMITER AS ' ' NULL AS '\N'
Any ideas of what is going on?
Update 2:
I fixed the problem by uninstalling py-mysql2pgsql, upgrading my python installation from 2.6 to 2.7 and installing again py-mysql2pgsql. That did the trick
ahh thx, strange...
2012/11/21 Oskar Kossuth [email protected]
Update 2:
I fixed the problem by uninstalling py-mysql2pgsql, upgrading my python installation from 2.6 to 2.7 and installing again py-mysql2pgsql. That did the trick
— Reply to this email directly or view it on GitHubhttps://github.com/philipsoutham/py-mysql2pgsql/issues/22#issuecomment-10597157.
Olivier, it seems you are using py-mysql2pgsql version 0.1.6. I'm using version 0.1.5 ( py_mysql2pgsql-0.1.5-py2.7.egg-info). try that one, maybe it can fix your problem
I'm having a similar issue
DONE CREATING TABLES
START WRITING TABLE DATA
START - WRITING DATA TO address_types
Traceback (most recent call last):
File "/usr/local/bin/py-mysql2pgsql", line 5, in
@grigy try what i did: I fixed the problem by uninstalling py-mysql2pgsql, upgrading my python installation from 2.6 to 2.7 and installing again py-mysql2pgsql. That did the trick
KK, I'm in. ready to be hit with the logs.
- "WARNING: nonstandard use of \ in a string literal at character 309" is mostly a result of using psycopg2 and newer postgresql version that yells at regexps in regular strings. This is only a warning though.
- Most errors like "COPY from stdin failed" can be caused both by postgresql and mysql backends but only the last error is reported. In most cases postgresql says that the data wasn't received and mysql stays silent. Try checking MySQL logs for errors.
I'm having this problem with all the mysql tables using auto_increment. I was also having it with longblog but hacked around that allowing migrate to test in pgsql which is what the application seems to want.
Still researching the auto_increment issue but though I'd post to see if anyone else has been able to resolve that.
The issue is not with auto_increment at all. It is caused by the content of blob columns in mysql. Still researching.
this is pure data content issue. nothing to do with the utility itself which works perfectly
One thing that did the trick for me:
use the file copy option in your yaml config file.
if file is given, output goes to file, else postgres
file: /tmp/dbexport.pgsql postgres: hostname: localhost port: 5432 username: steeve password: 12345 database: marvelouscars
And then import data saved with pgsql:
psql -U steeve marvelouscars < /tmp/dbexport.pgsql
hope this help.
NB: I tryed all the tricks above without success, the problem seems to be when data are copied to postgresql, maybe a warning that the script cannot handle and turn in an error.
