Problem: Copy Db From Mysql 5.0.27 To Mysql 4.0.16

[Smeagle]

Hi,

I used SQLyog 5.25. I just downloaded 5.27 but have not “retested”, yet.

I need to copy a database from a MySQL Server 5.0.27 to MySQL 4.0.16. Both servers are running on the same machine, on different harddrives.

I tried backup/restore: not working.

I tried “Copy Database to different host”. Took a long time, seemed to be working at first, but i got a lot of tables (>200) and lots of data (~600M:cool: and at testing I found a very, very simple table with problems:

Table has only one field of type integer. No Indexes/relations etc. and has only about 20 rows like this:

1

2

5

8

9

10

17

18

20

23

(and so on)

the copy looked like this:

18

13

2

0

17

0

6

0

(end!)

So it had less rows, some of the records were “0” and the order was changed.

I copied this table again and this time it worked.

My Question:

– Can I trust this process, if even this simplest table possible has problems? Most of the other tables are really large and complex, not as easy to check…

– Is there an easier, faster way to copy that database?

– I am now trying “Database Synchronization wizard”… Right at the first table there was a warning: “No primary key found in table `….`. This might result in error.” – Do I really have to adapt the DB to the tool? And after ~20 minutes the Wizard is still working on the first table, this will take weeks 🙁

Thanks for any advice,

– Oliver

[Smeagle]

Hi again,

I played around a little… (whith SQLYog 5.27 this time)

There is one structural difference between the databases, when copied with “Copy Database to different host”:

the new copy has a “default 0” added to every “INT” and “TINYINT”…

Even after I tried to synchronize the structure with “Structure Synchronization Tool” this stays the same.

– Oliver

[peterlaursen]

Code:

I tried backup/restore: not working.

this is not usable information!! To solve issues we need to know what happens, not what does not happen.

The issue probably is that the CREATE STATEMENT for a table is different between 4.0 and 5.x.  You will need to edit the SQL file!

Code:

the new copy has a “default 0” added to every “INT” and “TINYINT”…
Even after I tried to synchronize the structure with “Structure Synchronization Tool” this stays the same.

Lets see what happens.  probably the server inserts this default om some versions!

Code:

to MySQL 4.0.16

🙁   Upgrade!! at least to 4.0.26 or 4.0.27

[peterlaursen]

Please try to execute (from any client)

Code:

create table `xxx`.`t1` ( `f1` bigint , `f2` int , `f3` tinyint )

on the 4.0.16 server.  What becomes the CREATE STATEMENT for the table?

If I create this table on MySQL 5.0.37 and 'copy to other host' to a 4.0.26 server the create statement becomes on the 4.0 server

Code:

CREATE TABLE `tab1` (
`f1` bigint(20) default NULL,
`f2` int(11) default NULL,
`f3` tinyint(4) default NULL
) TYPE=

I would say that this (default becoming '0') looks like a server issue with a very old server version!

But please try to repeat the above and report.

[Smeagle]

Hi,

>> I tried backup/restore: not working.

> this is not usable information!!

I understand mysql backup/restore is not possible between different versions? I don't mean “Backup as SQL Batch”, my database is much too large for that.

>> the new copy has a “default 0” added to every “INT” and “TINYINT”…

> Lets see what happens. probably the server inserts this default om some versions!

It's the same with 5.27. I even cant remove this with the Alter Table Function: I remove the “0” under “Default”, click “Alter”, window closes – but no change.

But this behaviour should be no problem I think. I would prefer a “1:1” Copy of the database, though…

>> to MySQL 4.0.16

> Upgrade!! at least to 4.0.26 or 4.0.27

At this point this is no option at is a special version of mysql. But I will talk to MicroWeb Support if it is possible to get a newer version into their tool.

http://www.indigostar.com/microweb.htm

BTW, with sqlYog 3 and MySQL 4.1 -> 4.0.16 it worked… But now I updated the Master-DB to 5.0, I cannot go back to 4.1 on that machine.

Thanks for your help!

– Oliver

[Smeagle]

Hi,

On 4.0.16:

Code:

/*Column Information For – fte.ora_sprache*/
——————————————–

Field Type Collation Null Key Default Extra Privileges Comment
——————– ——- ——— —— —— ——- —— ——————————- ——-
ora_sprache_sprachnr int(10) (NULL) NO select,insert,update,references
ora_sprache_beznr int(10) (NULL) NO select,insert,update,references

/*Index Information For – fte.ora_sprache*/
——————————————-

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
—— ———- ——– ———— ———– ——— ———– ——– —— —— ———- ——-

/*DDL Information For – fte.ora_sprache*/
—————————————–

Table Create Table
———– ——————————————–
ora_sprache CREATE TABLE `ora_sprache` (
`ora_sprache_sprachnr` int(10) NOT NULL,
`ora_sprache_beznr` int(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

after copy on 5.0.27:

Code:

/*Column Information For – fte_test.ora_sprache*/
————————————————-

Field Type Null Key Default Extra Privileges
——————– ——- —— —— ——- —— ——————————-
ora_sprache_sprachnr int(10) 0 select,insert,update,references
ora_sprache_beznr int(10) 0 select,insert,update,references

/*Index Information For – fte_test.ora_sprache*/
————————————————

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
—— ———- ——– ———— ———– ——— ———– ——– —— —— ———- ——-

/*DDL Information For – fte_test.ora_sprache*/
———————————————-

Table Create Table
———– ——————————————————–
ora_sprache CREATE TABLE `ora_sprache` (
`ora_sprache_sprachnr` int(10) NOT NULL default '0',
`ora_sprache_beznr` int(10) NOT NULL default '0'
) TYPE=MyISAM

[peterlaursen]

1)

the CREATE TABLE statement on 4.1 and higher has an ENGINE and DEFAULT CHARSET specification that MySQL 4.0 and 3.23 does not understand.  So a dump from 4.1 and higher cannot be imported on 4.0 and lower

You will need to delete/comment this string: “ENGINE=InnoDB DEFAULT CHARSET=latin1”

We could enclose in conditional coments like “/*40101 ENGINE=InnoDB DEFAULT CHARSET=latin1*/”

and this very moment I cannot tell why we do not!

2)

“It's the same with 5.27. I even cant remove this with the Alter Table Function: I remove the “0” under “Default”, click “Alter”, window closes – but no change.

But this behaviour should be no problem I think. I would prefer a “1:1″ Copy of the database, though…”

Please follow my procedure.  Create the table from commandline/SQLyog SQL pane, and next execute “show create table”.  Does the SERVER create that default?  Nothing like it is reproducable here!  Do not shoot the piano player (SQLyog) if you really should shoot the bartender (MySQL)!

You are right that a export/import, a copy or a sync all should create a 'one to one copy'.  And it normally does!

3)

“At this point this is no option at is (= as it is ?) a special version of mysql”.  Yes at that time ISP's had the bad habit of 'patching' MySQL.  Now the code has been too complicated.  

And thanks for that, because most of what they did was a mess!

NOTE:

we need a step by step 'reproducable test case'.

[Smeagle]

Uh,

I just tried “Backup to SQL Dump” again (I tried this a few days before and it took “forever” so I stopped it) and now its working fast. Hmm. Sorry, I think I can safely use that.

I followed your procedure, and that worked. But the table created with your statement HAS a default: (Null). The Tables I tried to copy don't have a default at all. That's why I send one of the tables that didn't work…

Your Procedure:

On 5.0 I did:

Code:

create table `fte`.`t1` ( `f1` bigint , `f2` int , `f3` tinyint )

and got:

Code:

/*Column Information For – fte.t1*/
———————————–

Field Type Collation Null Key Default Extra Privileges Comment
—— ———- ——— —— —— ——- —— ——————————- ——-
f1 bigint(20) (NULL) YES (NULL) select,insert,update,references
f2 int(11) (NULL) YES (NULL) select,insert,update,references
f3 tinyint(4) (NULL) YES (NULL) select,insert,update,references

/*Index Information For – fte.t1*/
———————————-

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
—— ———- ——– ———— ———– ——— ———– ——– —— —— ———- ——-

/*DDL Information For – fte.t1*/
——————————–

Table Create Table
—— —————————————-
t1 CREATE TABLE `t1` (
`f1` bigint(20) default NULL,
`f2` int(11) default NULL,
`f3` tinyint(4) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

after copy on 4.0.16 I got:

Code:

/*Column Information For – fte_test.t1*/
—————————————-

Field Type Null Key Default Extra Privileges
—— ———- —— —— ——- —— ——————————-
f1 bigint(20) YES (NULL) select,insert,update,references
f2 int(11) YES (NULL) select,insert,update,references
f3 tinyint(4) YES (NULL) select,insert,update,references

/*Index Information For – fte_test.t1*/
—————————————

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
—— ———- ——– ———— ———– ——— ———– ——– —— —— ———- ——-

/*DDL Information For – fte_test.t1*/
————————————-

Table Create Table
—— ———————————
t1 CREATE TABLE `t1` (
`f1` bigint(20) default NULL,
`f2` int(11) default NULL,
`f3` tinyint(4) default NULL
) TYPE=MyISAM

– Oliver

[peterlaursen]

I I executed on 4.0.26

Code:

CREATE TABLE `ora_sprache` (
`ora_sprache_sprachnr` int(10) NOT NULL,
`ora_sprache_beznr` int(10) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

it becomes

Code:

CREATE TABLE `ora_sprache` (
`ora_sprache_sprachnr` int(10) NOT NULL default '0',
`ora_sprache_beznr` int(10) NOT NULL default '0'
) TYPE=MyISAM

It is not because your version is old.  This is how MySQL 4.0 creates numericals defined as NOT NULL.  Simply!

Not a SQLyog issue!!!

But not realy a MySQL issue either.  Also on MySQL 5.x data become '0' in most sql_modes.  

You can put it that way that due to sql_modes MySQL 5.x does not need to rewrite

the create statement for NOT NULL colums! Also string variables defined NOT NULL become like “`ora_sprache_sprachnr` char(10) NOT NULL default '' ” (default )

What would you expect with NOT NULL variables if sql_modes are not available on the server side and if a column is defined NOT NULL. Then entering data would raise an error unless all columns were entered with every single INSERT.

[Smeagle]

…and I am shooting noone here, just looking for help, as I am not very good with mysql…

– Oliver

[Smeagle]

sorry, posted twice.

[peterlaursen]

OK .. not shooting .. 🙄

You should understand that this is SERVER behaviour that a CLIENT cannot change.

Your last example misses the point as variables are declared NULL.

Your own example used variables declared NOT NULL.

(and that I missed in the first shot)

Now consider:  if NULL is not allowed, then the server will need some 'rule' to decide what data that were never entered (because incomplete rows were entered) should be.  In 4.0 this is controlled by column defaults, that the server generates.  In 5.0 it is controlled by the (global and connection) sql_modes.  A 'one to one' copy of NOT NULL variables from 5.0 to 4.0 cannot be established.  4.0 adds a default '0' or DEFAULT for NOT NULL variables.  5.x need not this because it has sql_modes.

Now try copy from one 5.x server to another and it does not happen.  

What illustrates that this is a server issue/behaviour – not a SQLyog issue.

[Smeagle]

Thanks for you explanation!

– Oliver

[Author]

Posts