search

willbryant / kitchen_sync

Fast unidirectional synchronization - make or efficiently update a copy of a database, without slow dumping & reloading
MIT License
282 stars 35 forks source link

Syncing tables with non-lowercase letters causes the sequence table creation to fail #2

Closed bagedevimo closed 9 years ago

bagedevimo commented 9 years ago

Unconfirmed, just logging for later. Delete if this is a pain / mess.

It looks like if you create a table, for example: "myTable", ks won't sync it because it correctly creates the sequence but seems to be lowercasing the sequence name somewhere when it tries to tell the column to use it.

I need to learn more about how sequences work to confirm this is an actual issue.

bagedevimo commented 9 years ago

SQL that appears to cause the problem: 

DROP TABLE `myTable`;

CREATE TABLE `myTable` (
  `id` int(8) unsigned NOT NULL auto_increment,
  `guid` varchar(36) NOT NULL,
  `name` varchar(255) default NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

INSERT INTO `myTable` (`guid`,`name`) VALUES ("BECBB850-D99D-9C87-4DAE-4E89EB337B5F","Wyatt"),("490B1AB1-4E88-1078-CD21-CB746EDC047A","Tucker"),("7B1D674B-A361-A656-B7B9-A312A7E85F78","Dale"),("4F17E4DF-8037-83F0-7B07-018BC83C5E52","Allen"),("AE6E8BF8-42AC-B68A-71F4-33A0356F8475","Emerson"),("9ADBFCF5-71B6-5CC4-DD3B-ED95C6BAF8B3","Lucas"),("46F4365C-BA39-3B12-1DDE-6201FFFB3EFE","Colby"),("427C68DF-E67C-54B2-386D-D5576EF9BBDE","Kyle"),("FDE78D55-A0CD-E35C-F5D0-20479EB0FF45","Armando"),("C2D44840-3C59-934C-5C1F-4E6118C27E0F","Myles");
INSERT INTO `myTable` (`guid`,`name`) VALUES ("542D3AD9-3F15-21EB-4D56-7EE7D4476629","Jackson"),("B4D18614-B7CE-9387-62AD-7C3BAF3FED19","Carl"),("572880BE-2405-084F-BAF6-8CA9D8F82C3E","Caleb"),("8EB14135-7B69-221C-0CD9-8B59838C0616","Burton"),("2BBDD4E5-1E48-8969-1581-10AF40C10341","Curran"),("175B9ED3-7597-04AF-B67C-AC08F509F970","Cairo"),("FFE79A6C-30E1-5110-198B-CAA164C448FE","Darius"),("C5DDA961-7B5A-9FD2-E26E-3F746694D9A8","Oscar"),("CCE1472C-5F3C-FCC4-D798-7E2E34B3747B","Reese"),("D1F54E93-CFC9-1B55-8742-B73FD2E07125","Wylie");
INSERT INTO `myTable` (`guid`,`name`) VALUES ("221B41B3-4092-6EEF-1533-4FFF5A2AB8AD","Odysseus"),("130CF160-EF6A-4FDF-B821-109CB87BEC82","Emerson"),("EEE9D44F-634E-6009-72BA-26DADCD592D4","Knox"),("799519A2-76B5-9FC9-B998-ABE42EFBDE67","Brenden"),("0553B33F-F675-9C17-FFBB-26D1183E2690","Kieran"),("4FE14C46-E30B-4723-2708-DE5F0224C07C","Bruno"),("B8E849FE-8C50-E5FF-F45D-E897A0DD77A3","Cruz"),("728CA371-E6FD-88B9-2679-660257ACF014","Solomon"),("A0C81ECF-63AB-85F0-6CC7-E02DD4CECD35","Kane"),("1E0EC0DE-9498-1BE9-7855-C54AFD7D43B0","Nathaniel");
INSERT INTO `myTable` (`guid`,`name`) VALUES ("55774578-9581-4B24-1B12-9DAB10FD1B74","Hiram"),("49EE5ECE-DD4E-DD69-7048-AAA63F146FEA","Ian"),("07ABB346-10B1-4245-7F27-C5A5ABCDD817","Gregory"),("D44925EF-1026-5B5E-F316-B54E54FD7377","Stone"),("BDE82DEB-5A5F-9D78-051C-DF2921EC286F","Emery"),("6C66CB5E-6A2B-2D4B-4508-4B44C51119D8","Thane"),("ADF55DA3-36F4-1DC9-3CBF-9CEB4FABB3D8","Ignatius"),("7731E7DE-89EE-F833-4308-8DF6A204AEF0","Leonard"),("0AC782C0-790E-E1B0-015F-ECAF57081B72","Mannix"),("EDBD0ACC-ABD4-C552-A536-E84663BC8213","Jakeem");
INSERT INTO `myTable` (`guid`,`name`) VALUES ("C55160C4-0A74-3472-B037-82E49911A4C5","Abbot"),("6E786C6D-D5D2-4745-B180-D3ECCE2DCB67","Jason"),("EFCF3B3D-685A-7AB4-DC96-716D83C683D4","Stone"),("1D88C46E-674F-2674-EE05-FEFDB8E5EE63","Colt"),("6781C1BA-0C1A-70E8-883B-B1CB7658E731","Micah"),("F2FDEA1C-D848-5714-03C6-EF6411D209B6","Lucius"),("3C1C1587-39B6-EF24-7A1E-505CDA326CD5","Gabriel"),("92493326-89FA-9B69-2455-FA844476BB68","Keith"),("C5FBC79A-2679-669A-E5F8-5741DF72FC99","Dylan"),("45B623E9-3699-2FC1-6572-C583F6937DCB","Judah");
INSERT INTO `myTable` (`guid`,`name`) VALUES ("7023D52E-A87E-93C9-ECCF-160437A08083","Yasir"),("EE5D55AD-CB3E-457F-8EB8-B49BCB8DCE28","Garrett"),("E3AF7604-38DF-DFDD-B638-1EDC0BDF1BF3","Eagan"),("4B8EBCAA-562D-66D0-1865-09C49D2B4340","Ezra"),("7AB725A3-1B53-981C-F6FC-37D4E74C06EE","Kane"),("079FC776-0584-8FF7-4A81-97D984B87CB4","Raja"),("43469614-B85B-07E9-A82F-ABBE57D5B266","Ulysses"),("ABDC1E05-8690-DACB-1376-A0A75703C301","Thor"),("21DC6C1E-527B-DD14-F0FB-C4918C21D14C","Lewis"),("A793000D-E99D-8453-A199-D1EBE754D52A","Boris");
INSERT INTO `myTable` (`guid`,`name`) VALUES ("60A507A5-6133-E374-3B23-660E8C33B76C","Noah"),("83AB3FF1-8F73-BAE0-5865-6F06E1E407DF","Hector"),("AC042775-7D6C-C9A7-E177-0D1979B2CED5","Howard"),("9AE9417D-E4D6-2A5E-392C-7FBE0747B7F9","Reed"),("BD14C0A5-FF55-D020-9CD1-F2C896CEE00B","Ishmael"),("862AAA9A-962F-1939-ACF2-CEE77291E6D4","Cullen"),("DF014A07-A5FC-5415-8FAF-C0446D1E220D","Reuben"),("0379B041-6290-9F89-DAFF-63F69D848279","Grady"),("2D4042C7-B481-33D6-79CE-2A0E31101B7D","Allen"),("91C51840-DFAC-3BCA-58CA-3896195508E4","Cody");
INSERT INTO `myTable` (`guid`,`name`) VALUES ("2C37BA4E-426A-55AB-CF4D-878990291C22","Melvin"),("D0D1A72D-423B-6576-DB9F-AB8279182D79","Xander"),("7C360D29-E647-D293-EDD3-754A0022D31B","Hop"),("4181B694-48BB-3551-5117-DB1ACF8E17FD","Hilel"),("9BDDF41F-1336-CAB7-5652-39B9804E9A3B","Dalton"),("D51FE623-ACA8-7F2C-83D3-473C6F3A771C","Wang"),("963FCDF2-7340-A83B-7800-43B9A20602B2","Blake"),("4108147C-021C-E314-8E81-E2CCB5D62258","Judah"),("760AF6D7-356F-288E-13EC-DD278E008720","Barclay"),("F673C5C7-FE00-D1C9-3031-7B8B28A48C23","Rudyard");
INSERT INTO `myTable` (`guid`,`name`) VALUES ("C2E0C409-F68C-F00E-B87F-B64554072D08","Philip"),("9D14F896-EAD6-F064-1503-7C31EF76CF8C","Acton"),("9A3241F5-0DD5-8E0B-F8FB-9BB0399A72E4","Kennedy"),("28C3BA8F-C356-D142-307E-47433CDE0C47","Nathan"),("7F8CE1A6-F1C7-A9FF-84F5-4E7D7DD19C1E","Clinton"),("56E7F2CB-BD7D-CDD2-2E70-B15CDE522B23","Stone"),("673372ED-2397-EE7A-458E-147B176E470A","Amery"),("A3F0062F-578D-42BF-5FE9-CB4F0BC31ADC","Amir"),("6FFB6373-2F3B-1B21-F9B7-F524F287C11B","Ivan"),("620DDD3F-D199-1940-331A-2A33C503D6F3","Octavius");
INSERT INTO `myTable` (`guid`,`name`) VALUES ("38D68EB1-14E7-91C8-5A85-26262DFC20C7","Baxter"),("04FB90E3-4B94-8180-5D50-FBBF74DD08A9","Beau"),("607E2B45-C7B6-32E8-CFCB-2C9A0BB0A83A","Colton"),("221AF8D6-1418-A7A7-0210-6D6D0275EAF4","Jameson"),("8D1EBE58-D10A-2349-865B-B6E225A51C5E","Xavier"),("E0EA29C1-79CC-2175-6FF0-A6897B40152A","Dustin"),("0946472E-0567-8035-77A4-996A470ED145","Marshall"),("158A4A04-64BE-2FC5-E282-C1BE1984162C","Micah"),("C266E293-F9B2-6FA5-C0C3-83812F46FCEF","Merrill"),("F2A91454-2D22-C597-C79F-82F34901A559","Jerry");

I used the command, ks --alter --verbose.

Again, needs more confirmation.

willbryant commented 9 years ago

Could you convert to a ruby test case?

bagedevimo commented 9 years ago

I can't even figure out how I did this last time, so I'm inclined to close it under user error.

willbryant commented 9 years ago

Oops, wrong issue number.

willbryant commented 9 years ago

It seems to work for me. One thing to bear in mind is that OS X uses a case-preserving but case-insensitive filesystem (by default), so if you already have a schema object called mytable, it won't be possible to also have myTable.

will@frypan:~/powershop/powershop$ ks --from mysql://root@localhost/test --to mysql://root@localhost/test2
Kitchen Sync
The database schema doesn't match.  Use the --alter option if you would like to automatically apply the following schema changes:

CREATE TABLE myTable (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `guid` varchar(36) NOT NULL,
  `name` varchar(255),
  PRIMARY KEY(`id`))

Database schema needs migration
Connection closed
Kitchen Syncing failed.
will@frypan:~/powershop/powershop$ ks --from mysql://root@localhost/test --to mysql://root@localhost/test2 --alter
Kitchen Sync
Finished Kitchen Syncing.
will@frypan:~/powershop/powershop$ mysql test2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.5.29-log Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create table my;
myTable       myTable.guid  myTable.id    myTable.name  mysql         
mysql> show create table myTable;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                              |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| myTable | CREATE TABLE `myTable` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `guid` varchar(36) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> Bye