New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Escaping table names #34

Closed
maciekjaros opened this Issue Oct 24, 2017 · 10 comments

Comments

Projects
None yet
2 participants
@maciekjaros

maciekjaros commented Oct 24, 2017

I would like to synchronize tables with reserved keyword as names - for example "order", "user". Escaping it in config file by '`' didn't work. I don't know nothing about creating and synchronizing it in pgSQL cause I didn't pass init_replica. I would appreciate your help, as I would like to use this solution in the near future in a fairly large e-commerce system production.

@the4thdoctor

This comment has been minimized.

Show comment
Hide comment
@the4thdoctor

the4thdoctor Oct 24, 2017

Owner

hi, can you provide me a table create statement I can test?
Thanks

Owner

the4thdoctor commented Oct 24, 2017

hi, can you provide me a table create statement I can test?
Thanks

@maciekjaros

This comment has been minimized.

Show comment
Hide comment
@maciekjaros

maciekjaros Oct 24, 2017

Sure, there is example:

CREATE TABLE `order` (
	`order_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`order_state` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
	`order_created` DATETIME DEFAULT NULL,
	`order_modified` DATETIME DEFAULT NULL,
	`order_deadline` DATETIME DEFAULT NULL,
	`order_closed` DATETIME DEFAULT NULL,
	`order_confirmed` DATETIME DEFAULT NULL,
	`order_trashed` DATETIME DEFAULT NULL,
	PRIMARY KEY (`order_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Keywords in MySQL should be escaped by `... but in pgSQL by double quote :) I cannot achieve it by config file. It is not bad idea to always escape table and column names. In pgSQL it also results in case sensitivity - I suspect you are doing this because the column names are written with a distinction between uppercase and lowercase. Perhaps you only need to escape names of columns and tables in MySQL to solve this.

maciekjaros commented Oct 24, 2017

Sure, there is example:

CREATE TABLE `order` (
	`order_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`order_state` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
	`order_created` DATETIME DEFAULT NULL,
	`order_modified` DATETIME DEFAULT NULL,
	`order_deadline` DATETIME DEFAULT NULL,
	`order_closed` DATETIME DEFAULT NULL,
	`order_confirmed` DATETIME DEFAULT NULL,
	`order_trashed` DATETIME DEFAULT NULL,
	PRIMARY KEY (`order_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Keywords in MySQL should be escaped by `... but in pgSQL by double quote :) I cannot achieve it by config file. It is not bad idea to always escape table and column names. In pgSQL it also results in case sensitivity - I suspect you are doing this because the column names are written with a distinction between uppercase and lowercase. Perhaps you only need to escape names of columns and tables in MySQL to solve this.

@the4thdoctor the4thdoctor self-assigned this Oct 24, 2017

the4thdoctor added a commit that referenced this issue Oct 24, 2017

@the4thdoctor

This comment has been minimized.

Show comment
Hide comment
@the4thdoctor

the4thdoctor Oct 24, 2017

Owner

the fix will appear in the version 1.8.1 (probably released this sunday).
Thanks for reporting the issue.
If you can check the problem is solved I'd close the ticket.
Thanks

Owner

the4thdoctor commented Oct 24, 2017

the fix will appear in the version 1.8.1 (probably released this sunday).
Thanks for reporting the issue.
If you can check the problem is solved I'd close the ticket.
Thanks

@the4thdoctor

This comment has been minimized.

Show comment
Hide comment
@the4thdoctor

the4thdoctor Oct 24, 2017

Owner

the version 2.0 is not affected by the bug

Owner

the4thdoctor commented Oct 24, 2017

the version 2.0 is not affected by the bug

@the4thdoctor the4thdoctor removed the v2.0 label Oct 24, 2017

@the4thdoctor the4thdoctor added this to the ver1.8.1 milestone Oct 24, 2017

@maciekjaros

This comment has been minimized.

Show comment
Hide comment
@maciekjaros

maciekjaros Oct 25, 2017

There is another place where you have to do it.

Oct 25 07:22:22: [DEBUG] - mysql_lib.py (728): Executing query for table order
Oct 25 07:22:22: [ERROR] - mysql_lib.py (731): error when pulling data from order. sql executed: SELECT (...) as data FROM order;

Table name is not escaped :)

pg_chameleon/pg_chameleon/lib/mysql_lib.py at line 723
sql_out="SELECT "+columns_csv+" as data FROM `"+table_name+"`;"

After fixing that init_replica doesn't copy data:

Oct 25 08:12:13: [INFO] - mysql_lib.py (754): table order error in PostgreSQL copy, saving slice number for the fallback to insert statements
Oct 25 08:12:13: [INFO] - mysql_lib.py (607): Table "order" copied 50 %
Oct 25 08:12:23: [INFO] - mysql_lib.py (754): table order error in PostgreSQL copy, saving slice number for the fallback to insert statements
Oct 25 08:12:23: [INFO] - mysql_lib.py (607): Table "order" copied 100 %
Oct 25 08:12:23: [INFO] - mysql_lib.py (769): the table order does not exist

maciekjaros commented Oct 25, 2017

There is another place where you have to do it.

Oct 25 07:22:22: [DEBUG] - mysql_lib.py (728): Executing query for table order
Oct 25 07:22:22: [ERROR] - mysql_lib.py (731): error when pulling data from order. sql executed: SELECT (...) as data FROM order;

Table name is not escaped :)

pg_chameleon/pg_chameleon/lib/mysql_lib.py at line 723
sql_out="SELECT "+columns_csv+" as data FROM `"+table_name+"`;"

After fixing that init_replica doesn't copy data:

Oct 25 08:12:13: [INFO] - mysql_lib.py (754): table order error in PostgreSQL copy, saving slice number for the fallback to insert statements
Oct 25 08:12:13: [INFO] - mysql_lib.py (607): Table "order" copied 50 %
Oct 25 08:12:23: [INFO] - mysql_lib.py (754): table order error in PostgreSQL copy, saving slice number for the fallback to insert statements
Oct 25 08:12:23: [INFO] - mysql_lib.py (607): Table "order" copied 100 %
Oct 25 08:12:23: [INFO] - mysql_lib.py (769): the table order does not exist
@the4thdoctor

This comment has been minimized.

Show comment
Hide comment
@the4thdoctor

the4thdoctor Oct 25, 2017

Owner

let me have a look ;)

Owner

the4thdoctor commented Oct 25, 2017

let me have a look ;)

the4thdoctor added a commit that referenced this issue Oct 25, 2017

@the4thdoctor

This comment has been minimized.

Show comment
Hide comment
@the4thdoctor

the4thdoctor Oct 25, 2017

Owner

I've added an test record and the data is copied correctly on postgres

Owner

the4thdoctor commented Oct 25, 2017

I've added an test record and the data is copied correctly on postgres

@maciekjaros

This comment has been minimized.

Show comment
Hide comment
@maciekjaros

maciekjaros Oct 26, 2017

It works. Thank you. I have not tried replication yet.

maciekjaros commented Oct 26, 2017

It works. Thank you. I have not tried replication yet.

@the4thdoctor

This comment has been minimized.

Show comment
Hide comment
@the4thdoctor

the4thdoctor Oct 26, 2017

Owner

The table names are escaped when replayed so probably it should work fine. I'm waiting for your feedback before closing the issue though.
thank you

Owner

the4thdoctor commented Oct 26, 2017

The table names are escaped when replayed so probably it should work fine. I'm waiting for your feedback before closing the issue though.
thank you

@the4thdoctor

This comment has been minimized.

Show comment
Hide comment
@the4thdoctor

the4thdoctor Nov 2, 2017

Owner

Any update on this issue? If the replica works I'd close the ticket. Thanks

Owner

the4thdoctor commented Nov 2, 2017

Any update on this issue? If the replica works I'd close the ticket. Thanks

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment