[Smeagle]

Hi,

I am importing into MyISAM Tables. On Table took about 4 hours alone. Table is defined like this:

CREATE TABLE `test` (

`typnr` int(10) NOT NULL,

`genartnr` int(10) NOT NULL,

`lkz` char(3) NOT NULL,

`nkw` tinyint(1) NOT NULL,

) ENGINE=MyISAM DEFAULT CHARSET=latin1

so nothing special here. But it is quite large:

Rows Avg_row_length Data_length

29018323 13 377238199

I do this import job every 3 Months, last cycle with 3.71 took 1,5hours and this cycle, first time with the 5.29 Version, 9 hours.

One part of the problem is, that now indexes are created on the copied database. As I understand, INSERTing Data on a table with indexes should be slower? BUT: The table above had no indexes (I removed them before import to be sure) and it was very slow.

Have you read my first post? How can I disable copying indexes? Is see the setting “import indexes” but cannot change it as it is greyed oput. I need completely different indexes on my copy of the database than on the original database, so i have to remove them anyways after importing…

Thanks,

– Oliver

[Smeagle]

Hi,

another feature request:

Please make it as fast, as “ODBC Import” was in Version 3.71… The Import wich took ~1 1/2 Hours in Version 3.71 and “ODBC Import” is now Running in 5.29 and “Migration Toolkit” for 7 hours. And still running. He is now on Row 1.000.000 of 28.000.000 of one large table. 🙁 This will take a lot more hours i guess. Are there any ways to speed this up?

Its the exact same configuration I used before with 3.71:

MSSQL 2005 on a Server with 4 CPUS, SQLyog is running on this server. MySQL 5.0.27 is running on anther server, this one with only 1 CPU. So Migration Toolkit copies from local MSSQL server to remote MySql server.

– Oliver

[Smeagle]

Thanks for you explanation!

– Oliver

[Smeagle]

sorry, posted twice.

[Smeagle]

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

– Oliver

[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

[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

[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 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

[Smeagle]

Hi,

thanks for that info (explain) it's better than nothing 🙂

I understand, a query analyzer would be very hard to do… But it's the feature I miss most, after switching vom MSSQL…

I now noticed why I had problems in optimizing indicies: mySQL seems not to use a newly created index at the first few querys after generating it!? If I send the same query a couple of times after setting up index it gets faster and faster…. 🙁

– Oliver

[Smeagle]

Hmm,

🙁

I have to correct myself:

It still isn't working. Last time I forgot to delete the original database, so the table structure was reused.

If I import into a empty database, sqlyog still converts “varchar(>254)” to “blob (>254)”

This will not work. I would suggest to use “text” or “blob” (without length) for varchars with more than 254 characters length.

– Oliver

[Smeagle]

Ups,

just noticed, there is a new version… Tried it, and: it works!

Thanks,

– Oliver

[Smeagle]

Hello!

is there a solution? I have to do this every morning. I have to split the import into severel “.sql”s and have to edit them one after another. As we are talking about a database with ~500MB, this takes some time.

I would like to simply start an import directly into mysql, that is not possible now.

Please!

– Oliver

[Smeagle]

Ack.

It just takes a very long time. Sorry. 🙂

But… If I run the batch with mysqld its _much_ faster, so I thought, it must be hanging…

– Oliver

[Smeagle]

Hiya,

I know, it works when I change the “blob(xxx)” to “text”. It would also work with “blob”, but as the original datatype was “varchar”, I set it to “text”. But I import a _large_ dump from ms sql to mysql often, and it's not nice to have to do this everytime “by hand”…

– Oliver

[Author]

Posts