Space Missing In Sync File

[upeters]

Hello,

I noticed that when I compare two structures of MySQL 4.1.x databases, there seems to be a small glitch in the sync SQL file created by the Structure Synchronization Tool.

I added a primary key to an existing table, and set the new field to autoincrement and unsigned int, and the file saved to sync the older database has no space after the “auto_increment” keyword. This syntax error prevents the sync file to run, for example:

/* Alter table in Second database */

alter table `myferret`.`leilao_bid`

change `lance` `lance` timestamp(14) NULL after `currentbid`,

change `criacao` `criacao` timestamp(14) NULL DEFAULT '00000000000000' after `lance`,

add column `id` int(10) unsigned NOT NULL auto_incrementafter `criacao`,

add PRIMARY KEY (`id`);

/* Alter table in Second database */

alter table `myferret`.`rifa_marcados`

add column `id` int(10) unsigned NOT NULL auto_incrementafter `email`,

add PRIMARY KEY (`id`);

Cheers,

Ulrich

[peterlaursen]

Are you sure you use the latest version?

With 5.13 beta2 I get

/* Alter table in Second database */

alter table `test3`.`user`

add column `UserId` bigint(20) unsigned NOT NULL auto_increment first,

change `GroupId` `GroupId` bigint(20) unsigned NOT NULL after `UserId`,

add PRIMARY KEY (`UserId`);

There were such bugs in the early 5.1.x tree.

If you still think there is a bug, then please post complete 'create statements' for two tables to reproduce with.

[upeters]

peterlaursen wrote on May 23 2006, 05:37 PM:

Are you sure you use the latest version?

With 5.13 beta2 I get …

Hello,

I am using the latest non-beta version (5.12), I purchased a license for the Enterprise less than a week ago. Is this beta stable and can be installed over the current version, or will I likely break something? What do you reccomend?

Ulrich

[peterlaursen]

I recommend 5.13 beta2. Compared to 5.12 it is only bugfixes.

The most important one is the fix for slow save from DATA pane as reported here:

http://www.webyog.com/forums/index.php?showtopic=2166

Links to download from here:

http://www.webyog.com/forums/index.php?sho…t=0&#entry10244

You can 'install over' or to another folder as you prefer.

[upeters]

peterlaursen wrote on May 23 2006, 06:13 PM:

I recommend 5.13 beta2. Compared to 5.12 it is only bugfixes.

Hello,

just to make sure, I performed a new structure comparison, and saved the file with version 5.12. The same error I reported before was again present in the sync file. Then I installed the latest beta, and ran the same comparison again and saved in a new sync file. The latter sync file looks fine, so it seems that this error will be already corrected in the next stable version. Thank you for your assistance.

Cheers,

Ulrich

[upeters]

Hi again,

it seems that I wrote too early. Now there is a new error in v5.13 beta 2 (at least in Enterprise not-trial):

Here is the sync file created with stable version 5.12:

/* Alter table in Second database */

alter table `ferrets`.`categorias`

add column `id` int(10) unsigned NOT NULL auto_incrementafter `descr`,

add PRIMARY KEY (`id`);

/* Alter table in Second database */

alter table `ferrets`.`dolar`

change `id` `id` tinyint(4) unsigned NOT NULL auto_incrementfirst,

change `dhora` `dhora` timestamp(14) NULL after `dolar`,

add PRIMARY KEY (`id`);

Here is the sync file made with version 5.13 beta 2, downloaded a few minutes ago:

/* Alter table in Second database */

alter table `ferrets`.`categorias`

add column `id` int(10) unsigned NOT NULL auto_increment after `descr`,

add PRIMARY KEY (`id`),

/* Alter table in Second database */

alter table `ferrets`.`dolar`

change `id` `id` tinyint(4) unsigned NOT NULL auto_increment first,

change `dhora` `dhora` timestamp(14) NULL after `dolar`,

add PRIMARY KEY (`id`),

Please note that the missing space after “auto_increment” is gone, but now the statements are finished with a comma, where it should be a semicolon. Can you reproduce this error?

Ulrich

[sarat]

I am not able to reproduce this error

can you please paste here those source and destination table structures

[upeters]

sarat wrote on May 24 2006, 02:17 AM:

I am not able to reproduce this error

can you please paste here those source and destination table structures

Hello,

I am attaching both databases to this post. The “remote” database is the source, the “local” database should be the destination.

Ulrich

[peterlaursen]

To me it looks as something went wrong with that upload.

I get a weird file down!

Are you sure that the upload finished?

[upeters]

peterlaursen wrote on May 24 2006, 10:32 AM:

To me it looks as something went wrong with that upload.

I get a weird file down!

Are you sure that the upload finished?

I downloaded it from the forum, and got a zip file, 16997 bytes, containing two files, “ferrets-remote.sql” and “ferrets-local.sql”, both about 40 kB. It seems to me that the upload worked correctly, but if you still are unable to get the two files, I placed them here:

http://www.ferrets.com.br/files/databases.zip

http://www.ferrets.com.br/files/ferrets-remote.sql

http://www.ferrets.com.br/files/ferrets-local.sql

[peterlaursen]

Weird.

Looks like an issue with my 'Download Manager' then.

After import and compare .. 'copy to clipboard' or 'save to file' I get:

One way:

/* Alter table in Second database */

alter table `ferret2`.`categorias`

drop column `id`,

drop key `PRIMARY`;

/* Alter table in Second database */

alter table `ferret2`.`dolar`

change `id` `id` tinyint(4) NOT NULL DEFAULT '0' first,

drop key `PRIMARY`;

the other way:

/* Alter table in First database */

alter table `ferret1`.`categorias`

add column `id` int(10) unsigned NOT NULL auto_increment after `descr`,

add PRIMARY KEY (`id`);

/* Alter table in First database */

alter table `ferret1`.`dolar`

change `id` `id` tinyint(4) unsigned NOT NULL auto_increment first,

add PRIMARY KEY (`id`);

[peterlaursen]

So what do we do from here ??

[upeters]

peterlaursen wrote on May 24 2006, 11:25 AM:

So what do we do from here ??

Hi,

it might be possible that this error shows only if using different versions of the MySQL database, like in my situation. The remote/source is 4.0.26 and my local servers are 4.1.19 and 4.1.10a-classic-nt (got the same error on both, using different computers)… I could set up a temporary readonly user for you at the remote, and you could see if you get the error (or anything else wrong) then when trying to sync with the local database. May I contact you per e-mail?

Ulrich

[peterlaursen]

It is better that you create a 'ticket' .. we can then better coordinate!

http://www.webyog.com/support/ttx.cgi

But I can test with the same versions! Will do!

Do you use tunnelling to the remote host ?

Write-up:

Issue confirmed:

4.0.26 remote and 4.1.19 local, no tunnelling!

/* Alter table in Second database */

alter table `jazcyk_test`.`categorias`

drop column `id`,

drop key `PRIMARY`,

/* Alter table in Second database */

alter table `jazcyk_test`.`dolar`

change `id` `id` tinyint(4) NOT NULL DEFAULT '0' first,

change `dhora` `dhora` timestamp NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP after `dolar`,

drop key `PRIMARY`,

/* Alter table in First database */

alter table `ferret`.`categorias`

add column `id` int(10) unsigned NOT NULL auto_increment after `descr`,

add PRIMARY KEY (`id`),

/* Alter table in First database */

alter table `ferret`.`dolar`

change `id` `id` tinyint(4) unsigned NOT NULL auto_increment first,

change `dhora` `dhora` timestamp(14) NULL after `dolar`,

add PRIMARY KEY (`id`),

@ritesh+@sarat: data (ferrets-remote.sql) available at surftown.dk (MySQL 4.0.26)

[upeters]

peterlaursen wrote on May 24 2006, 11:56 AM:

Issue confirmed:

4.0.26 remote and 4.1.19 local, no tunnelling!

Great, so it seems that you got the same symptom now. Thank you for your help! Hopefully this will help to nail down this error… without having to change databases. 🙂

Cheers,

Ulrich

[Ritesh]

Will take a look tomorrow.

[Ritesh]

This is interesting.

I just tried with 4.0.26 and 4.1.7 on remote and local and it works. Will now download and work with v4.1.19.

[Ritesh]

This is strange.

I tried with the same dump between 4.0.26 and 4.1.19, 4.0.18 and 4.1.19 and both the cases I am getting the correct output.

The versions I tried are:

4.0.18-max-nt

4.1.19-community-nt

4.0.26-debug

[peterlaursen]

@ritesh

both upeters and I tested with a remote 4.0.26 Linux server without tunnel.

As I wrote the data are available at surftown.dk ..

Server here is '4.0.26-standard-log' on GNU/Linux

It does not happen with tunnelling …

(and still reproducable .. now with a 5.0.21 running at local)

With HTTP-tunnelling;

/* Alter table in Second database */

alter table `jazcyk_test`.`categorias`

drop column `id`,

drop key `PRIMARY`;

/* Alter table in Second database */

alter table `jazcyk_test`.`dolar`

change `id` `id` tinyint(4) NOT NULL DEFAULT '0' first,

change `dhora` `dhora` timestamp NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP after `dolar`,

drop key `PRIMARY`;

Without tunnel:

/* Alter table in First database */

alter table `ferret1`.`categorias`

add column `id` int(10) unsigned NOT NULL auto_increment after `descr`,

add PRIMARY KEY (`id`),

/* Alter table in First database */

alter table `ferret1`.`dolar`

change `id` `id` tinyint(4) unsigned NOT NULL auto_increment first,

change `dhora` `dhora` timestamp(14) NULL after `dolar`,

add PRIMARY KEY (`id`),

@Ritesh .. mail me your ip and I can arrange direct acces to surftown …

[peterlaursen]

Now .. I synced from to remote 4.0.26 to a local 4.0.27 .. no problem.

Next started 4.0.27 and and 4.1.19 on each their port on the same computer and sync yields (with direct connection to both):

/* Alter table in Second database */

alter table `test5`.`categorias`

drop column `id`,

drop key `PRIMARY`,

/* Alter table in Second database */

alter table `test5`.`dolar`

change `id` `id` tinyint(4) NOT NULL DEFAULT '0' first,

change `dhora` `dhora` timestamp NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP after `dolar`,

drop key `PRIMARY`,

/* Alter table in First database */

alter table `ferret`.`categorias`

add column `id` int(10) unsigned NOT NULL auto_increment after `descr`,

add PRIMARY KEY (`id`),

/* Alter table in First database */

alter table `ferret`.`dolar`

change `id` `id` tinyint(4) unsigned NOT NULL auto_increment first,

change `dhora` `dhora` timestamp(14) NULL after `dolar`,

add PRIMARY KEY (`id`),

[Ritesh]

I have been trying all these in direct connection only.

@Peter: I have mailed you the IP.

[peterlaursen]

And I have now given you direct access with all available priv's to the test database there.

And sent you a mail with connection details.

[peterlaursen]

Working with locals 4.0.27 and 4.1.19:

If I add a 'Tablename1' in 4.0.27 I get

/* Alter table in Second database */

alter table `ferret`.`categorias`

add column `id` int(10) unsigned NOT NULL auto_increment after `descr`,

add PRIMARY KEY (`id`),

/* Alter table in Second database */

alter table `ferret`.`dolar`

change `id` `id` tinyint(4) unsigned NOT NULL auto_increment first,

change `dhora` `dhora` timestamp(14) NULL after `dolar`,

add PRIMARY KEY (`id`),

/* Create table in Second database */

create table `ferret`.`tablename1` (

`id` bigint(20) unsigned NOT NULL DEFAULT '0' ,

PRIMARY KEY (`id`)

)Engine=MyISAM;

/* Alter table in First database */

alter table `test5`.`categorias`

drop column `id`,

drop key `PRIMARY`,

/* Alter table in First database */

alter table `test5`.`dolar`

change `id` `id` tinyint(4) NOT NULL DEFAULT '0' first,

change `dhora` `dhora` timestamp NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP after `dolar`,

drop key `PRIMARY`,

If I add a 'Tablename1' in 4.1.19 I get

/* Alter table in Second database */

alter table `ferret`.`categorias`

add column `id` int(10) unsigned NOT NULL auto_increment after `descr`,

add PRIMARY KEY (`id`),

/* Alter table in Second database */

alter table `ferret`.`dolar`

change `id` `id` tinyint(4) unsigned NOT NULL auto_increment first,

change `dhora` `dhora` timestamp(14) NULL after `dolar`,

add PRIMARY KEY (`id`),

/* Alter table in First database */

alter table `test5`.`categorias`

drop column `id`,

drop key `PRIMARY`,

/* Alter table in First database */

alter table `test5`.`dolar`

change `id` `id` tinyint(4) NOT NULL DEFAULT '0' first,

change `dhora` `dhora` timestamp NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP after `dolar`,

drop key `PRIMARY`,

/* Create table in First database */

create table `test5`.`tablename1` (

`id` bigint(20) unsigned NOT NULL DEFAULT '0' ,

PRIMARY KEY (`id`)

)Type=MyISAM;

— Where did the 'DROP table TableName1' go when syncing from DB without TableName1 to the one with?

— Looks like the tool does not realize that is has finished …

[upeters]

peterlaursen wrote on May 25 2006, 05:42 AM:

@ritesh

both upeters and I tested with a remote 4.0.26 Linux server without tunnel.

As I wrote the data are available at surftown.dk ..

Server here is '4.0.26-standard-log' on GNU/Linux

It does not happen with tunnelling …

Hello,

I was using HTTP/PHP tunneling on the source/remote server (4.0.26) and a direct connection on the destination/local server (4.1.19) when it first happened. I still can reproduce the error under these conditions. I get the exact same result when I use a direct connection to the remote server, so it seems that it happens always, with or without tunnel.

I have setup the readonly user on the remote database, in case you want to run a test with it I could send you the connection details.

Ulrich

[Ritesh]

Extra space bug reproduced and working to fix it.

[peterlaursen]

Confirmed fixed with beta3:

/* Alter table in Second database */

alter table `test5`.`categorias`

drop column `id`,

drop key `PRIMARY`;

/* Alter table in Second database */

alter table `test5`.`dolar`

change `id` `id` tinyint(4) NOT NULL DEFAULT '0' first,

change `dhora` `dhora` timestamp NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP after `dolar`,

drop key `PRIMARY`;

/* Alter table in First database */

alter table `ferret`.`categorias`

add column `id` int(10) unsigned NOT NULL auto_increment after `descr`,

add PRIMARY KEY (`id`);

/* Alter table in First database */

alter table `ferret`.`dolar`

change `id` `id` tinyint(4) unsigned NOT NULL auto_increment first,

change `dhora` `dhora` timestamp(14) NULL after `dolar`,

add PRIMARY KEY (`id`);

[Author]

Posts