Spurious Defaults

[steve.rapaport]

When I use the Structure Synchronization tool to alter tables in a second database, they come out a bit different, particularly in the defaults, e.g.:

First database:

`automatic_imports` tinyint(4) NOT NULL

Second database:

`automatic_imports` tinyint(4) NOT NULL DEFAULT '0'

That default was apparently added by the Structure Sync Tool when I copied some SQL from one database to create the other.

But now, the two databases are still not structurally the same, due to the “changed” default, so I can't sync data between them.

In fact, the Structure Sync tool still marks them as different!

Please don't ask me to go through all the old database fields in the First database and change the defaults on each one, because I am not in charge of the databases, someone else is. But I am in charge of keeping them synchronized!

Can someone fix this or can I change an option somewhere?

Thanks,

Steve 🙁

[peterlaursen]

I am no sure I fully understand. But I tried different datatypes, and the SQL generated was

Code:

/* Create table in Second database */
create table `test2`.`t1` (
`id` bigint(20) NOT NULL  auto_increment ,
`txt` varchar(50) NULL   ,
`ti` tinyint(4) NOT NULL   ,
`in` int(11) NOT NULL   ,
`ti1` tinyint(1) NULL   ,
PRIMARY KEY (`id`)
)Type=MyISAM;

This script does not askf for default '0'. However a TINYint is the MySQL mostly used for BOOLEAN. So some server version may have that 'rule' 'built in' that unless specifically set at TRUE then it must be FALSE. As far as i remember the convention is that 0 = false and -1 = true.

What are the server versions? COMPLETE and EXACT versions? Then maybe the changelogs will reveal something!

[steve.rapaport]

In the first database, the mysql server is

mysql Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using readline 5.0

running on

Linux storage5 2.6.9-22.ELsmp #1 SMP Mon Sep 19 18:32:14 EDT 2005 i686 i686 i386 GNU/Linux

on the second databse it's

mysql Ver 14.7 Distrib 4.1.15, for pc-linux-gnu (i686) using readline 4.3

running on

Linux skseamless 2.4.21-4.ELsmp #1 SMP Fri Oct 3 17:52:56 EDT 2003 i686 i686 i386 GNU/Linux

Sample table descriptions next?

[peterlaursen]

hehe … I don't know, I it would help. But I can arrange a structure sync between 4.1.18 on Windows and 5.0.18 on SuSe Linux10 (a 2.6.13 kernel). Thatis is prety close. And I would not mind trying that over the weekend.

So two table definitions and a stepwise description on what to do.

But if I can reproduce what next? Then you can be happy to be unhappy?

I very much doubt that SQLyog has created that default directly or indirectly..

[peterlaursen]

MySQL docs:

incompatible behaviour before and after 5.02.

http://dev.mysql.com/doc/refman/5.0/en/dat…e-defaults.html

Quote:

Prior to MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause.

If the column cannot take NULL as the value, MySQL defines the column with an explicit DEFAULT clause, using the implicit default value for the column data type. Implicit defaults are defined as follows:

For numeric types other than those declared with the AUTO_INCREMENT attribute, the default is 0 …

….

As of MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.

If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause

So a sync 5.0.18 >> 4.1.x wo do exactly as you describe. SQLyog can't help it. It DOES NOT create defaults. The 4.1 server does.

[peterlaursen]

BTW .. you can't run a datasync 4.1 <> 5.0 with SQLyog either because of different concat_ws() implementation.

[steve.rapaport]

Thank you! Now I understand.

[Author]

Posts