Last update problem : process freeze over a 32k rows table.
flacombe opened this issue · 7 comments
Hi,
Thank you to have implemented comment migration today.
Is there any special option to set to enable it?
For now, I see no "-- Comment on column" in the output and no comment is moved on the pgsql db.
It sounds the last update broke up something : currently the migration process freeze over a quite large table (32k rows). I see no pending request on mysql side but on pgsql there is the request built in arrangeColumnsData() method. Are you sure pgsql likes it ?
There is a csv file corresponding to the processed table in the temporary_directory.
Here what I see in shell :
"FromMySqlToPostgreSql" - the database migration tool
Copyright 2015 Anatoly Khaytovich <anatolyuss@gmail.com>
-- Migration began...
-- New schema "public" was successfully created...
-- 93 tables detected
-- Currently processing table: ...
-- Table "public"."...." is created.
-- Populating table "public"."..."
-- Total rows to insert into "public"."...": 50
-- Currently processing table: ...
-- Table "public"."..." is created.
-- Populating table "public"."...."
-- Total rows to insert into "public"."....": 0
-- Currently processing table: ...
-- Table "public"."...." is created.
-- Populating table "public"."...."
-- Total rows to insert into "public"."....": 32774
And then it waits, output nothing for a while... maybe 15 minutes
The for loop at line 900 of FromMySqlToPostgreSql.php might know something about this issue :)
Therefore you have PHP notice like :
PHP Notice: Undefined variable: sql in /tmp/frommysqltopgsql/migration/FromMySqlToPostgreSql/FromMySqlToPostgreSql.php on line 568
Thank you in advance for any feedback !
Hi,
Conversion of comments should work out-of-the-box unless an exception will throw.
Please, send me the errors-only.log file (zipped).
Hi,
Had a look to errors-only.log but I can't forward it to you directly.
There are too many data of mine inside sorry :(
Nevertheless, I'm ok to help you to find out the problem.
I find only two kind of errors (they are all both repeated dozen of time) in the file :
-- FromMySqlToPostgreSql::populateTableWorker
-- PDOException code: 42501
-- File: /tmp/frommysqltopgsql/migration/FromMySqlToPostgreSql/FromMySqlToPostgreSql.php
-- Line: 775
-- Message: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: you must be superuser to use COPY from or to a file
HINT: Everyone can use COPY to stout or from stdin.
Pgsql \copy command is available to everyone too.
-- SQL: COPY "xxxxx"."...." FROM '/tmp/frommysqltopgsql/temporary_directory/....0.csv' DELIMITER ',' CSV;
And the second one :
-- FromMySqlToPostgreSql::populateTableByPrepStmtWorker
-- PDOException code: 42703
-- File: /tmp/frommysqltopgsql/migration/FromMySqlToPostgreSql/FromMySqlToPostgreSql.php
-- Line: 658
-- Message: SQLSTATE[42703]: Undefined column: 7 ERREUR: column « IF(`create_date` IN('0000-00-00', '0000-00-00 00:00:00'), '-INF » from relation « .... » n'existe pas
LINE 1: ...INTO "xxxxx"."...." ("record_id","IF(`creat...
^
-- SQL: INSERT INTO "xxxxxx"."..." ("record_id","IF(`create_date` IN('0000-00-00', '0000-00-00 00:00:00'), '-INFINITY', `create_date`)","IF(`update_date` IN('0000-00-00', '0000-00-00 00:00:00'), '-INFINITY', `update_date`)",[...],"IF(`exec_lastDate` IN('0000-00-00', '0000-00-00 00:00:00'), '-INFINITY', `aaaaaaaaa`)") VALUES(:0,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13);
It sounds like the second error is directly related to arrangeColumnsData() method.
All the best
Ok.
Make sure, that username, you use in your PostgreSQL connection string, defined as superuser (usually "postgres")
More info: http://www.postgresql.org/docs/current/static/app-createuser.html
Hi,
Ok the user i've used wasn't superuser and I fixed it.
But for now I have this new error :
-- FromMySqlToPostgreSql::populateTableWorker
-- PDOException code: 58P01
-- File: /tmp/frommysqltopgsql/migration/FromMySqlToPostgreSql/FromMySqlToPostgreSql.php
-- Line: 775
-- Message: SQLSTATE[58P01]: Undefined file: 7 ERREUR: couldn't open the file « /tmp/frommysqltopgsql/temporary_directory/....0.csv » to read : No file or rep of that type
-- SQL: COPY "public"."..." FROM '/tmp/frommysqltopgsql/temporary_directory/...0.csv' DELIMITER ',' CSV;
My dir /tmp/frommysqltopgsql/temporary_directory/ is chmod 777.
FromMySqlToPostgreSql creates temporary files which must be accessible by PostgreSQL server.
That is why FromMySqlToPostgreSql should reside on the same machine where PostgreSQL server is installed.
Hi !
Really sorry to haven't seen that before :s
Indeed PHP and DB was on different hosts...
It works well on the same machine :)
And it's damn fast with temporary files INSERT
Regarding comment processing, it's ok except this error which appears sometimes when a MySQL comment contains a ' in it.
-- FromMySqlToPostgreSql::processComment
-- PDOException code: 42601
-- File: /home/user/pgsql_migration/migration/FromMySqlToPostgreSql/FromMySqlToPostgreSql.php
-- Line: 981
-- Message: SQLSTATE[42601]: Syntax error: 7 ERREUR: syntax error near or on « objet »
LINE 1: ...."iRcom_sitr_paNodes"."obj_id" IS 'label:Numero d'objet logi...
^
-- SQL: COMMENT ON COLUMN "public"."...."."aaaaaa" IS 'label:Numero d'objet logique';
Feel free to take care of this to improve the coverage of migration.
Thank you !
thank you, AnatolyUss
it works like a charm!
oopps...i told that too soon!
sorry...
All the schema of the tables are ok; but no data has been migrated. any suggestion?
many thanks in advance.