Problem With Mysql 5 Import/sync

[shawn_h]

Hello,

I am running a brand spanking new version of MySQL 5.0.13 rc3 on my Mac OSX 1.3.9 server. I downloaded MySQL's source code and compiled locally on the server. The database is working great; however, when I try to use the SQLyog ODBC import tool to import tables from an ODBC source, it fails.

I figured after some googling that the reason is the default keyword. When I removed from the xml job files lines like this: NULL everything started to work again.

I would imagine this is a bug; however, it is not a show stopper. Somewhere during the ODBC import/sync process, the admin guy should be able to disable the default values. To have to go through each column of each table and clear out the work NULL under the DEFAULT column is just too much time consuming.

I am running the latest version of SQLyog Enterprice (beta) on a Windoze XP machine.

Thanks.

[peterlaursen]

I am afraid you must explain a little bit more!

I just tested with MySQL 5.0.13 (on windows) and SQLyog 4.2 beta 2 and import from MS-Access. The empty defaults like in the XML does not prevent correct functioning here.

However undoubtedly you came across som information that solved your problem.

Could you point to those resources more precisely?

Is it a 5.0.13 only problem, a Mac OS-X only problem or what ?

Does it take a specific server configuration to reproduce?

What is your ODBC-source ?

Also a small XML-file that does not work with on your system and does work without them would be useful.

You will need to give information so that problem can be reproduced! Also why not link to the resources that you googled? We don't read thought. Though it sometimes would be practical! 😉

[shawn_h]

peterlaursen wrote on Sep 30 2005, 09:56 PM:

I am afraid you must explain a little bit more!

I just tested with MySQL 5.0.13 (on windows) and SQLyog 4.2 beta 2 and import from MS-Access.  The empty defaults like in the XML does not prevent correct functioning here.

However undoubtedly you came across som information that solved your problem.

Could you point to those resources more precisely? 

Is it a 5.0.13 only problem, a Mac OS-X only problem or what ?

Does it take a specific server configuration to reproduce?

What is your ODBC-source ?

Also a small XML-file that does not work with on your system and does work without them would be useful.

You will need to give information so that problem can be reproduced!  Also why not link to the resources that you googled?  We don't read thought.   Though it sometimes would be practical!   😉

[post=”7384″]<{POST_SNAPBACK}>[/post]

Ok, I try to answer most of your questions (as I am not working over the weekend) that you have asked above:

The error I was getting is a 1067 error. When googled, I came across a post that said DEFAULTS should be removed when creating new tables in Mysql 5.x. So, this gave me a clue as I was also creating tables from my ODBC source. So, after I removed all the default statements from xml job files, it started to create the tables and ran smoothly.

Before, I did this, I tested against 4.x without any problem. So, I firmly believe it's a 5.x problem .

My ODBC source connects to an ISAM database through some proprietary drivers on windows platform. These same drivers have been working without a problem with version 4.x.

As far as I know, it doesn't take any specific server configuration to reproduce the error. Having said that, I only tested on my compiled version of the new MySQL server on Mac osx so I cannot for sure say that it can be reproduced on Linux and/or windoze; however, seemingly it appears it's a client server type of problem; not related to underlying os. Before, I googled, I tried changing the storage engine without any success.

I'll send you a sample of my xml file Monday when I go back to work.

You mean you don't have a sixth sense and cannot read my mind across the ocean, that's too bad,ey! 😉

[peterlaursen]

Quote:

I came across a post that said DEFAULTS should be removed when creating new tables in Mysql 5.x

That sounds strange! Of course a column could have a default-value. But of course empty default-statments in the XML does not create any default value with the database. When SJA interprets the XML I believe the is just skipped when the SQL is created. But Ritesh must confirm that!

I have been using MySQL 5 since 5.0.4 alpha I think, and never came across anything like it. Of course the new Migration Tool is new code, but I can't believe that it does otherwise than an ordinary “create table” statement.

You don't think you could possibly find that “post that you came across” again? I really would like to read what it says exactly, and what sources it references.

But send the XML to Ritesh at [email protected].

[peterlaursen]

I also googled a little and found this:

http://techrepublic.com.com/5100-10878_11-5148530.html

ISAM = Indexed Sequential Access Method.

It sounds like the DATAFLEX/POWERFLEX systems that I worked a lot with 15 years ago!

But probably not very relevant! After all data are transferred by the ODBC-driver.The key point is what SQL is generated by the SJA when interpreting the XML. And it is the “create table …” that fails for some reason.

[shawn_h]

peterlaursen wrote on Oct 1 2005, 09:33 PM:

I also googled a little and found this:

http://techrepublic.com.com/5100-10878_11-5148530.html

ISAM = Indexed Sequential Access Method.

It sounds like the DATAFLEX/POWERFLEX systems that I worked a lot with 15 years ago!

But probably not very relevant!  After all data are transferred by the ODBC-driver.The key point is what SQL is generated by the SJA when interpreting the XML.  And it is the “create table …” that fails for some reason.

[post=”7389″]<{POST_SNAPBACK}>[/post]

I do not believe that is related to what I am talking about; however, for your benefit, I did find the link that prompted me to remove NULL entries in my job files and here is it:

http://www.karakas-online.de/forum/viewtopic.php?t=2732

i'll send a copy of one of my job files shortly.

[Ritesh]

Thanks 🙂

I think SQLyog is specifying DEFAULT value for auto_increment column. This option is not supported in v5.x and also does not make sense.

BETA 5 will fix it where it will not specify DEFAULT value on auto_increment column.

[peterlaursen]

i quote from that link:

Quote:

In the later versions of MySQL (maybe starting 5.x), you cannot set a default value to an “auto increment” field (if you think about it, it doesn't make much sense either).

That makes sense!

But you are sure that you did not specify some DEFAULT-value from the Migration Tool GUI yourself? I still doubt that an empty statement in the XML generally is a problem. But a 0 is with an autoincremented field.

[shawn_h]

peterlaursen wrote on Oct 3 2005, 11:03 AM:

i quote from that link:

That makes sense! 

But you are sure that you did not specify some DEFAULT-value from the Migration Tool GUI yourself?  I still doubt that an empty statement in the XML generally is a problem.  But a 0 is with an autoincremented field.

[post=”7408″]<{POST_SNAPBACK}>[/post]

I am positive I didn't specify any default values at all. it generated the default values all by itself and the y were all like this: NULL. This should not be there if dba deosn't want it to be included.

Here's a copy of my job file! The empty lines you see in there further down the file is because I had to remove all the default statements. Of course, I put them back in there in the begining of the file for you guys to see what I had to remove.

But it's really simple: with the default statements set to NULL, my jobs are failing and without them, they'll run. They shouldn't be there to begin with and if they're there it should be because the dba decided to include them in.

Does this make sense? 🙂

[peterlaursen]

shawn!!!

Send that file to [email protected] and delete the attachement here.

Never expose your user credentials like this!

[shawn_h]

peterlaursen wrote on Oct 3 2005, 12:29 PM:

shawn!!!

Send that file to [email protected] and delete the attachement here.

Never expose your user credentials like this!

[post=”7410″]<{POST_SNAPBACK}>[/post]

Don't worry, All the credentials are fake! However, I'll go ahead and delete the file regardless. Thansk 😉

[peterlaursen]

But I got a copy of the file, after all!

It must be a problem with that ISAM database and ODBC-driver.

NULL is nonsense!

NULL is nothing. Default = nothing …. BVADR!!! 🙁

Now I think I got the point.

NULL !!!

It is not the default statement that is a problem. is OK, but NULL is not

Now with 5.0.13 MySQL is a little bit consequent but your ODBC-driver is not!

In my opinion it is not a SQLyog issue, but an issue with a poor ODBC-driver!

But let Ritesh have a look at it too!

[peterlaursen]

Or maybe rather a problem with the source database.

is it not possible to remove the DEFAULTS from the table-definitions on the source database (if they are there)? If the database implementation is so that DEFAULT = NULL is the only way to specify 'No default' then there is a problem! You never know what kind of “workarounds” have been made in the early ages of ODBC-implementation!

[shawn_h]

peterlaursen wrote on Oct 3 2005, 01:01 PM:

Or maybe rather a problem with the source database.

is it not possible to remove the DEFAULTS from the table-definitions on the source database (if they are there)?  If the database implementation is so that DEFAULT = NULL is the only way to specify 'No default' then there is a problem!  You never know what kind of “workarounds” have been made in the early ages of ODBC-implementation!

[post=”7413″]<{POST_SNAPBACK}>[/post]

You're missing the boat. The problem is not in ODBC at all. The problem is with table creation and DDL that SQLYog generates to create the objects. This statement “NULL” should not be included in the DDL automatically!!!

There shouldn't be any “” statements at all in the DDL unless the dba chooses so either for a specific column and/or for a specifc table (would be nice to have this so that the dba can turn it on or off at will for the whole table). That's all!

Maybe earlier version of MySQL let that go without spitting it back up; however, the new version apparently doesn't allow it.

I am sorry but I hope I am making myself clear on this! 😛

[peterlaursen]

I don't think I am missing anything. If the source database returns DEFAULT = NULL when queried for the table definition (through ODBC) then the wizard writes NULL to the XML file.

This jobfile for instance:

Code:

localhost
root ******* 3306 test

test





no

is generated by the wizard of SQLyog 4.2 for attached Access database.

There are no superfluous default statements.

The defaults only come in the jobfile if the ODBC-driver and the source database return defaults when queried about table structure. Your source database and related so-called “ODBC”-driver tell SQLyog that the table definitons have defaults for those columns. And then of course SQLyog write it to the jobfile.

There are a lot of early database systems that came to existence before and the early years of the ODBC-standard (we are talking about the beginning of the 90'ties) that were later made “ODBC-compatible”. Most of them have non-standard-issues. I guess that is the problem here. But SQLyog has implemented workarounds for similar issues (and regular bugs) with other databases. I shal not exclude that there is one more of the kind possible.

[shawn_h]

peterlaursen wrote on Oct 3 2005, 03:34 PM:

There are a lot of early database systems that came to existence before and the early years of the ODBC-standard (we are talking about the beginning of the 90'ties) that were later made “ODBC-compatible”.  Most of them have non-standard-issues.  I guess that is the problem here.  But SQLyog has implemented workarounds for similar issues (and regular bugs) with other databases.  I shal not exclude that there is one more of the kind possible.

[post=”7416″]<{POST_SNAPBACK}>[/post]

I agree with you on the ODBC compliancy issues specially in the earlier stages; however, the fact remains that when a dba chooses to have default statements for columns generated by SQLYog, then those statements should be there, but if the dba doesn't choose to have any and/or no default statements, then there shouldn't be any included. The choice should be the dba's, don't you agree? I mean I don't have to go through each xml job file and edit those statements out every time I want to generate a new job file! It doesn't make sense to have to do that.

[peterlaursen]

To get information about the table structure SQLyog performs a QUERY against the metadata tables of the source database. There is no other way. I think that the ODBC-source in this case returns a default = NULL where there really is NONE. Bad coding simply!

However, since this can cause problem with MySQL version 5, maybe SQLyog should implement som “filtering” for illegal defaults, such as NULL-defaults and defaults with an autoincrement variable. However, I don't have any idea of how widespread the problem is.

Let Ritesh have a look into the issue 🙄

[Ritesh]

Has been fixed in BETA 5.

The problem arises when SQLyog specifies a DAFAULT value with auto_increment column and NULL as a default value.

[peterlaursen]

Quote:

The problem arises when SQLyog specifies a DEFAULT value with auto_increment column and NULL as a default value.

Yup. Nobody were in doubt, I guess! But what we have been discussing her is WHY it happens. I have not been able to reproduce it with ACCESS as the source, so what is returned by the source DB when queried about table structure must be part of the problem?

Another part of the problem probably is that MySQL 5 is not so “relaxed” as previous versions in this respect. That is only one example. With this and future releases of MySQL we will se that MySQL approaches “Standard SQL” still more for each release. I for instance expect a much more 'strict' type-checking for the future. Will cause a lot of problems with clients and scripts that have been writtten for 3.23 and 4.0 …

@ Ritesh was it a bug with SQLyog in your opinion that has been fixed, or is it a workaround for diversities in SQL-dialects that have been implemented ?

And by the way, this

Quote:

The problem arises when SQLyog specifies a DEFAULT value with auto_increment column and NULL as a default value.

should be

Quote:

The problem arises when SQLyog specifies a DEFAULT value with auto_increment column or NULL as a default value.

and >> or !!!

The resource on the internet that Shawn found is about auto_increment columns. Shawn's own problem is 'NULL as a default value'. Not necessessarily 'NULL as a default value for auto_increment columns'.

[Ritesh]

Quote:

Ritesh was it a bug with SQLyog in your opinion that has been fixed, or is it a workaround for diversities in SQL-dialects that have been implemented ?

Its not a bug nor a workaround. Its an improvement in SQLyog Migration Toolkit 😀

Even in MySQL versions < 5.x, an auto_increment column with a default value does not make sense. MySQL < 5.x was less strict and thus the queries worked. It will not in v5.x. From v4.2 BETA 5, SQLyog will not issue a DEFAULT statement when its an auto_increment column.

Quote:

Shawn's own problem is 'NULL as a default value'. Not necessessarily 'NULL as a default value for auto_increment columns'.

You can have NULL as deafult value and it works. I dont think this was a problem with other columns.

BTW, can somebody mail me the XML file? I didnt receive it in [email protected]

[peterlaursen]

I have it …

And you do in 5 minutes!

[peterlaursen]

You should have it by now. And please note that there is no

Quote:

yes

in the file.

Shawn deleted all instances of

Quote:

NULL

And it worked. It did not before.

I still believe that we have two issues with MySQL verion 5 here!

1) an autoincrement have have a default

1) a default-value can't be NULL.

[Ritesh]

Thanks. I will work upon it in some time 🙂

Still feeling lethargic from yesterday's fever :huh:

[shawn_h]

Ritesh wrote on Oct 5 2005, 04:13 AM:

Thanks. I will work upon it in some time 🙂

Still feeling lethargic from yesterday's fever  :huh:

[post=”7434″]<{POST_SNAPBACK}>[/post]

My recommendation is that the default statements not be generated at all even if the source table contains them. It should be left to the dba to decide if a column should need a default value in the new table that is going to be created by the current job. And that does include the generation of NULL statements. If there is no need for it, let's not put it in there. Or maybe make it optional per table and/or jobfile by havig a form control with the capability to verride it per column, ey?

Pretty simple, ey? 😉

[peterlaursen]

@Shawn

Can you confirm that beta5 works with you?

[shawn_h]

peterlaursen wrote on Oct 6 2005, 04:20 AM:

@Shawn

Can you confirm that beta5 works with you?

[post=”7447″]<{POST_SNAPBACK}>[/post]

I haven't tested beta5! Where can I find the code? Is it under the downloads section? 😕

I'll look!

[peterlaursen]

At the top of the Forums (1st category)

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

[shawn_h]

I just downloaded and installed Beta5. No joy!

Here's what I get during the import in the last window titled “Completing the database Import Wizard”:

SQLyog Job Agent Version 4.2

Copyright © Webyog Softworks Pvt. Ltd.. All Rights Reserved.

Job started at Thu Oct 06 09:22:50 2005

DBMS Information: SOME_CONNECTION

Importing table schema: ABSE…

ERROR: 1067, Invalid default value for 'ID'

Check d:Program FilesSQLyog Enterprise Beta5sja.log for complete error details.

Total time taken – 10 sec(s)

And here's what is in the log:

Job started at Thu Oct 06 09:22:50 2005

ERROR: 1067, Invalid default value for 'ID'

Basically the same thing. Shouldn't the log be a little more descriptive of the error/issue/problem??? 😕 After all, it does say check the log for more details!!! 😉

[peterlaursen]

Quote:

Shouldn't the log be a little more descriptive of the error/issue/problem???

Everybody agrees on that. Too much work to do for too few people basically, I think!

Send a fresh XML-file and the log-file (with no 'fakes') to [email protected]!

And I also think, that the people at Webyog should now have detailed information about the source database (not just an 'ISAM-database' and the ODBC-driver! it it the metatable-structure of that DB that is interesting!).

But I'll let Ritesh take over!

[Ritesh]

Which database you are trying to import data from?

Can you send us sample data and the corresponding XML file? I dont want to guesstimate on the problem.

The problem seems to be with DEFAULT value assigned to columns.

[peterlaursen]

well .. the problem is – as Shawn explained it -that there is this statement

NULL

in the XML-file for each column that does not have a default!

When Shawn deletes those lines from the XML-file it works!

Obviously MySQL 5 does not accept this as it in more respects is more 'strict' in syntax-checking than previous versions.

That should be clear!! If not then read from the beginning 😛

And I still think it is because of poor design of the source database and/or the ODBC-driver! The source must return default=NULL when metatables are queried. How else did it get in the XML-file? There are no such statements in the XML when importing from Access for instance.

But maybe The SQLyog Sync Wizard could “filter out” those statements.

When the first 4.1 beta came out I warned you that there would continue to be non-standard ODBC issues from now till forever. The number of 'propretary' ODBC implemetations with administrative software from the 90'ties are too numerous to mention. I have been working a lot with systems like DATAFLEX and POWERFLEX from that period where ODBC (at best) means 'Only Do Bad Connections' 😀

[peterlaursen]

proposal.

I the code of SQLyog I believe you build a string for each line that is written to the XML-file. And when each line has been built you do something like

Code:

write(file,string)

that simply could be

Code:

if not string = 'NULL' write(file,string)

then those problematic NULL 's are filtered out and not written.

You can translate from PPL (Peter's Programming Language 😀 ) to C yourself.

[shawn_h]

Ritesh wrote on Oct 6 2005, 10:15 AM:

Which database you are trying to import data from?

Can you send us sample data and the corresponding XML file? I dont want to guesstimate on the problem.

The problem seems to be with DEFAULT value assigned to columns.

[post=”7466″]<{POST_SNAPBACK}>[/post]

I'll send you the files here soon but not today.

[shawn_h]

R,

I just emailed a couple of files to you: 1- xml job file; 2- sqlyog generated log file.

Hope these help!

Also, I only have access to the source data through my windows driver. As I'd said before, the source data is and ISAM file structure on an hp alpha machine. The driver is the only way for me to communicate with the server that hosts the database. 😉

[Author]

Posts