Dow't work with big tables > ~9 000 000 rows.

[alexshnup]

(venv) [root@centoscloud ~]# chameleon.py create_schema
Jul 6 02:05:06: [DEBUG] - mysql_lib.py (475): getting table metadata
Jul 6 02:05:06: [INFO] - mysql_lib.py (478): table copy limited to tables: document
Jul 6 02:05:06: [INFO] - pg_lib.py (624): Installing service schema base
(venv) [root@centoscloud ~]# chameleon.py add_source --config default
Jul 6 02:05:19: [DEBUG] - mysql_lib.py (475): getting table metadata
Jul 6 02:05:19: [INFO] - mysql_lib.py (478): table copy limited to tables: document
(venv) [root@centoscloud ~]# chameleon.py init_replica --config default
Jul 6 02:05:33: [DEBUG] - mysql_lib.py (475): getting table metadata
Jul 6 02:05:33: [INFO] - mysql_lib.py (478): table copy limited to tables: document
Jul 6 02:05:33: [INFO] - global_lib.py (307): Importing mysql schema
Jul 6 02:05:33: [INFO] - mysql_lib.py (580): locking the tables
Jul 6 02:05:33: [INFO] - mysql_lib.py (590): copying table document
Jul 6 02:05:33: [DEBUG] - mysql_lib.py (595): estimating rows in document
Jul 6 02:05:33: [DEBUG] - mysql_lib.py (624): document will be copied in 24 slices of 390168 rows
Jul 6 02:05:33: [DEBUG] - mysql_lib.py (631): Executing query for table document
Jul 6 02:05:33: [DEBUG] - mysql_lib.py (636): Starting extraction loop for table document
Jul 6 02:05:43: [INFO] - mysql_lib.py (657): table document error in PostgreSQL copy, saving slice number for the fallback to insert statements
Jul 6 02:05:43: [INFO] - mysql_lib.py (513): Table document copied 4 %
Jul 6 02:05:55: [INFO] - mysql_lib.py (513): Table document copied 8 %
Jul 6 02:06:05: [INFO] - mysql_lib.py (657): table document error in PostgreSQL copy, saving slice number for the fallback to insert statements
Jul 6 02:06:05: [INFO] - mysql_lib.py (513): Table document copied 12 %
Jul 6 02:06:17: [INFO] - mysql_lib.py (513): Table document copied 16 %
Jul 6 02:06:28: [INFO] - mysql_lib.py (513): Table document copied 20 %
Jul 6 02:06:39: [INFO] - mysql_lib.py (513): Table document copied 25 %
Jul 6 02:06:50: [INFO] - mysql_lib.py (657): table document error in PostgreSQL copy, saving slice number for the fallback to insert statements
Jul 6 02:06:50: [INFO] - mysql_lib.py (513): Table document copied 29 %
Jul 6 02:07:01: [INFO] - mysql_lib.py (513): Table document copied 33 %
Jul 6 02:07:12: [INFO] - mysql_lib.py (513): Table document copied 37 %
Jul 6 02:07:23: [INFO] - mysql_lib.py (513): Table document copied 41 %
Jul 6 02:07:34: [INFO] - mysql_lib.py (513): Table document copied 45 %
Jul 6 02:07:46: [INFO] - mysql_lib.py (657): table document error in PostgreSQL copy, saving slice number for the fallback to insert statements
Jul 6 02:07:46: [INFO] - mysql_lib.py (513): Table document copied 50 %
Jul 6 02:07:57: [INFO] - mysql_lib.py (513): Table document copied 54 %
Jul 6 02:08:08: [INFO] - mysql_lib.py (513): Table document copied 58 %
Jul 6 02:08:19: [INFO] - mysql_lib.py (513): Table document copied 62 %
Jul 6 02:08:29: [INFO] - mysql_lib.py (657): table document error in PostgreSQL copy, saving slice number for the fallback to insert statements
Jul 6 02:08:29: [INFO] - mysql_lib.py (513): Table document copied 66 %
Jul 6 02:08:40: [INFO] - mysql_lib.py (513): Table document copied 70 %
Jul 6 02:08:51: [INFO] - mysql_lib.py (513): Table document copied 75 %
Jul 6 02:09:02: [INFO] - mysql_lib.py (513): Table document copied 79 %
Jul 6 02:09:14: [INFO] - mysql_lib.py (513): Table document copied 83 %
Jul 6 02:09:25: [INFO] - mysql_lib.py (513): Table document copied 87 %
Jul 6 02:09:36: [INFO] - mysql_lib.py (513): Table document copied 91 %
Jul 6 02:09:47: [INFO] - mysql_lib.py (513): Table document copied 95 %
Jul 6 02:09:58: [INFO] - mysql_lib.py (513): Table document copied 100 %
Jul 6 02:10:09: [INFO] - mysql_lib.py (657): table document error in PostgreSQL copy, saving slice number for the fallback to insert statements
Jul 6 02:10:09: [INFO] - mysql_lib.py (513): Table document copied 104 %
Jul 6 02:10:19: [INFO] - mysql_lib.py (657): table document error in PostgreSQL copy, saving slice number for the fallback to insert statements
Jul 6 02:10:19: [INFO] - mysql_lib.py (513): Table document copied 108 %
Jul 6 02:10:30: [INFO] - mysql_lib.py (513): Table document copied 112 %
Jul 6 02:10:41: [INFO] - mysql_lib.py (513): Table document copied 116 %
Jul 6 02:10:53: [INFO] - mysql_lib.py (513): Table document copied 120 %
Jul 6 02:10:57: [INFO] - mysql_lib.py (513): Table document copied 125 %
Traceback (most recent call last):
File "/usr/bin/chameleon.py", line 41, in
replica.init_replica()
File "/usr/lib/python2.7/site-packages/pg_chameleon/lib/global_lib.py", line 257, in init_replica
self.copy_table_data()
File "/usr/lib/python2.7/site-packages/pg_chameleon/lib/global_lib.py", line 489, in copy_table_data
self.my_eng.copy_table_data(self.pg_eng, self.global_config.copy_max_memory)
File "/usr/lib/python2.7/site-packages/pg_chameleon/lib/mysql_lib.py", line 670, in copy_table_data
self.insert_table_data(pg_engine, ins_arg)
File "/usr/lib/python2.7/site-packages/pg_chameleon/lib/mysql_lib.py", line 556, in insert_table_data
pg_engine.insert_data(table_name, insert_data , self.my_tables)
File "/usr/lib/python2.7/site-packages/pg_chameleon/lib/pg_lib.py", line 437, in insert_data
self.pg_conn.pgsql_cur.execute(sql_head,column_values)
ValueError: A string literal cannot contain NUL (0x00) characters.

I've tried many different configuration options for the configuration file, but large tables can not be initialized. On small tables everything works fine.
P.S. Percona5.6 (cascading replica), PostgreSQL 9.5, Python3.6

[the4thdoctor]

Hi, thanks for reporting the issue.
Could be possible to get a copy of this table? or at least the table definition? so I can reproduce the bug locally.
Many thanks

[the4thdoctor]

File "/usr/lib/python2.7/site-packages/pg_chameleon/lib/mysql_lib.py", line 556, in insert_table_data pg_engine.insert_data(table_name, insert_data , self.my_tables) File "/usr/lib/python2.7/site-packages/pg_chameleon/lib/pg_lib.py", line 437, in insert_data self.pg_conn.pgsql_cur.execute(sql_head,column_values) ValueError: A string literal cannot contain NUL (0x00) characters.

This error happens when the fallback insert tries to complete the data load. I'm pretty sure this should be the bug.

From the paths it seems to me you are working with python 2.7 instead of 3.6.

[the4thdoctor]

I've been able to reproduce the bug. The issue is caused by the presence of NUL (0x00) character.

On mysql
create table null_value (value text); insert into null_value values (CHAR(0x00 using utf8));
When initialising the replica pgchameleon fallsback on inserts which fails with the message
ValueError: A string literal cannot contain NUL (0x00) characters.

[the4thdoctor]

Fixed in this commit. I will release the version 1.4 with the fix this week end (need to finish the refactoring of sync_replica/add_tables).

[alexshnup]

Thank you.
Now it's ok.

Jul 6 15:26:37: [INFO] - mysql_lib.py (513): Table document copied 121 %
Jul 6 15:26:39: [INFO] - mysql_lib.py (513): Table document copied 122 %
Jul 6 15:26:41: [INFO] - mysql_lib.py (513): Table document copied 123 %
Jul 6 15:26:42: [INFO] - mysql_lib.py (513): Table document copied 124 %
Jul 6 15:28:36: [ERROR] - pg_lib.py (444): unexpected error when processing the row
Jul 6 15:28:36: [ERROR] - pg_lib.py (445): - > Table: document
Jul 6 15:28:36: [ERROR] - pg_lib.py (446): - > Insert list: "person_id","document_type_id","document_number","change_time","comment"
Jul 6 15:28:36: [ERROR] - pg_lib.py (447): - > Insert values: [2565922, 1, '\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00', None, '']
Jul 6 15:30:14: [ERROR] - pg_lib.py (444): unexpected error when processing the row
Jul 6 15:30:14: [ERROR] - pg_lib.py (445): - > Table: document
Jul 6 15:30:14: [ERROR] - pg_lib.py (446): - > Insert list: "person_id","document_type_id","document_number","change_time","comment"
Jul 6 15:30:14: [ERROR] - pg_lib.py (447): - > Insert values: [7860167, 0, '\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00', None, '']
Jul 6 15:31:19: [ERROR] - pg_lib.py (444): unexpected error when processing the row
Jul 6 15:31:19: [ERROR] - pg_lib.py (445): - > Table: document
Jul 6 15:31:19: [ERROR] - pg_lib.py (446): - > Insert list: "person_id","document_type_id","document_number","change_time","comment"
Jul 6 15:31:19: [ERROR] - pg_lib.py (447): - > Insert values: [11071818, 2, '\x00', None, '']
Jul 6 15:32:02: [ERROR] - pg_lib.py (444): unexpected error when processing the row
Jul 6 15:32:02: [ERROR] - pg_lib.py (445): - > Table: document
Jul 6 15:32:02: [ERROR] - pg_lib.py (446): - > Insert list: "person_id","document_type_id","document_number","change_time","comment"
Jul 6 15:32:02: [ERROR] - pg_lib.py (447): - > Insert values: [13328233, 2, '\x00', None, '']
Jul 6 15:33:11: [ERROR] - pg_lib.py (444): unexpected error when processing the row
Jul 6 15:33:11: [ERROR] - pg_lib.py (445): - > Table: document
Jul 6 15:33:11: [ERROR] - pg_lib.py (446): - > Insert list: "person_id","document_type_id","document_number","change_time","comment"
Jul 6 15:33:11: [ERROR] - pg_lib.py (447): - > Insert values: [20715068, 1, '\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00', None, '']
Jul 6 15:34:40: [ERROR] - pg_lib.py (444): unexpected error when processing the row
Jul 6 15:34:40: [ERROR] - pg_lib.py (445): - > Table: document
Jul 6 15:34:40: [ERROR] - pg_lib.py (446): - > Insert list: "person_id","document_type_id","document_number","change_time","comment"
Jul 6 15:34:40: [ERROR] - pg_lib.py (447): - > Insert values: [21759128, 1, '\x00\x00\x00\x00\x00\x00\x00\x00', None, '']
Jul 6 15:34:53: [INFO] - mysql_lib.py (671): releasing the lock
Jul 6 15:34:53: [INFO] - pg_lib.py (720): saving master data id source: 1 log file: mysql-bin.000010 log position:120679387 Last event: None
Jul 6 15:34:53: [INFO] - pg_lib.py (725): cleaning not replayed batches for source 1
Jul 6 15:34:53: [DEBUG] - pg_lib.py (738): master data: table file t_log_replica_2_src_1, log name: mysql-bin.000010, log position: 120679387
Jul 6 15:34:53: [INFO] - pg_lib.py (394): creating the indices
Jul 6 15:34:53: [DEBUG] - pg_lib.py (397): processing table document

[alexshnup]

From the paths it seems to me you are working with python 2.7 instead of 3.6.

Yes i tried on Python2.7 and Python3.6

[the4thdoctor]

Cool :)
Closing the issue.