[peterlaursen]

I have a MySQL 5.0.16 running on SuSE Linux 10.

When I connect to this MySQl from another machine running WinXP/SQLyog I can easily ALTER TABLE to varchar size greater than 255 (the upper limit before MySQL 5).

i think it is an issue with your MYSQL installation on LINUX. Did you update from a previous MySQl version? Did you then run the update scripts after updating.

I think you should try to dump your data, remove MySQL on LINUX (including deleting the /datadir), and then reinstall and import data.

I don't know the .deb install packages. it could be a problem here when upgrading. There is no such problems with the .rpm install packages used by RH/FC/SuSE.

[peterlaursen]

@ritesh

The error message appearing in SQLyog 5.01 when following the procedure as desribed by dany is like quoted by him. Sqlyog.err does not give more info.

Did you repeat steps 1-4 as described by dany?

If no error occurs with the 5.02 tree with the same procedure there is no need for further discussin!

[peterlaursen]

Confirmed with this simple code

Code:

*
SQLyog Enterprise – MySQL GUI v5.01
Host – 5.0.17-nt-max : Database – yy
*********************************************************************
Server version : 5.0.17-nt-max
*/

create database if not exists `yy`;

USE `yy`;

/*Table structure for table `tablename1` */

DROP TABLE IF EXISTS `tablename1`;

CREATE TABLE `tablename1` (
 `id` bigint(20) NOT NULL auto_increment,
 `t` varchar(50) default NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `tablename1` */

However, if you add in between 3) and 4)

3a: select an existing database (from dropdown or by highligting in Object Browser) – – – it works OK.

Try also to open 'History' tab while importing. At the very moment you click 'Import from SQL-statements' this SQL is added to the history

Code:

select database()

I don't understand this SQL and how it was supposed to work! 😮

[peterlaursen]

Thanks – that clarified a lot!

There is no Primary Key!! It says (only) UNIQUE KEY `STYLE` (`STYLE`).

If you change it to be PRIMARYthen your problem is over! PRIMARY KEYS are defined from ALTER TABLE, not MANAGE INDEXES.

Now read this article:

http://www.webyog.com/articles/Using_SQLyo…L_Databases.pdf

and find the paragraph 'Sync without a Primary Key' (it is near the end).

You will understand then that you are trying to create two records from one. However the server prevents this since there can only be one row with the same UNIQUE KEY.

BTW.. it is very easy to update MySQL on SuSE! Also to MySQL 5.x. I am running SuSE 10 myself, and I have updated several times (it came with 4.1.14 – now I am running 5.0.17). Just click the RPM from KDE or GNOME and the YAST installer does the rest! Dump your data first! But 4.1.10 is not bad I think.

[peterlaursen]

@brianvic

you write

Quote:

The entry is a unique primary key.

is it a PRIMARY KEY or a UNIQUE INDEX?

Please paste in the 'create statement for the table' EXACTLY as from the SQLyog OBJECTS pane. Don't edit it! Copy-paste!

[peterlaursen]

By the way

Quote:

I am using SQLyog Enterprise V5 with MySQL 4.1.1.

MySQL 4.1.1 is a VERY early alpha version (first in 4.1 series) and very buggy. You realy should upgrade the servers. Latest is 4.1.16 (to stay in 4.1.x)

[peterlaursen]

just an update:

The blanks in the file path does not seem to create troube with recent versions of the MySQL client. But remember that the folder 'Program Files' is different in localized (non-english) Windows' versions.

I posted this http://bugs.mysql.com/bug.php?id=15835&thanks=4 to the MySQl bugs repository.

[peterlaursen]

😀

The SQLyog sync tool won't sync across 4.x and 5.x. So you will have to upgrade the remote server or 'downgrade' the local.

[peterlaursen]

Quote:

same system for over a year with out any problems

No! It is impossible! You must have installed MySQL 5.0.16 on local within the last 2 weeks!

[peterlaursen]

Quote:

On local I'm running Mysql 5.0 and SqlYog 5.0.16 (I just noticed in SqlYog in the help>about it says 4.04 but select version() returns 5.0.16), windows xp.

Server is running Mysql 4.0 and SqlYog 4.0.13, windows 2000

Mismatch! There is no SQLyog 5.016 or 4.013! And servers are not running SQLyog at all! SQLyog is a MySQL client.

'Select version()' returns the MySQL server version. It is then 5.0.16 on local and 4.0.13 on remote server. SQLyog version is then 4.04 – that is what the help menu from SQLyog shows.

It is exactly as I guessed it. You are trying to sync a MySQL server 5.0 (local) to a MySQL 4.0 (remote). It is not possible! The first link to the Webyog FAQ that I posted explains why! The most recent versions of SQLyog (from 4.1 as far as i remember) abort in this situation – older versions as yours attempt to do the sync and then the 'NULL mismatch' error occurs.

MySQL 5.0.16 is very recent (it was uploaded to MySQL FTP-mirrors 2005-12-06) so you must have updated your local MySQL since then! That was what I asked you in the first question.

To sync with a remote 4.x server you must run a local 4.x. And you should consider to upgrade SQLyog. It is free if you have a legal/registered copy of SQLyog. But it still won't sync MySQL 4.0 with 5.0.

[peterlaursen]

Maybe it would be an idea to let the user customize the SQL-statements. When it comes to WHERE, ORDER BY and LIMIT etc., I think the needs and preferences will be different for each user.

With an embedded database (that I happen to know soon will be introduced with SQLyog 🙂 ) even the number of available statements available from pop-up-menu could be variable. Basically it just takes a two columns -table with columns like 'displayed_as' and 'sql_content'.

It also could be an option of the 'favorites' folder -feature to replace the 'personal folder' that som statements here were availabale from ObjectBrowser pop-up.

You sometimes have a bad habit of hard-coding things!

[peterlaursen]

But why

Code:

`ID` int(100) NOT NULL default '0',

and not

Code:

`ID` int(100) NOT NULL auto_increment,

Also an int(100) is a strange thing! A Int is standard 10 characters(4 bytes)

ranging from -2147483648 to 2147483647 (signed) or from 0 to 4294967295 (unsigned). You just get a lot of (90 to be exact!) zerofills the way you do.

read about numerical types: http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html

I don't know if this could be part of the problem. I don't think so. The existence of the ' DEFAULT CHARSET=latin1' indicates that local MySQL version is 4.1 and higher , server version 4.0 or lower.

I can get this error with old SQLyog versions and when syncing from MySQL 5.0 to 4.0.

Would you please check your versions EXACTLY! ALL of them! Don't GUESS/THINK but CHECK it 😉

[peterlaursen]

I can reproduce this when only one DB is specified in the connection dialogue!

[peterlaursen]

Pretty strange.

But please answer:

What are the MySQL version(s) ?

And the SQLyog version?

Server running *nix and local Windows ?

I shall try to reproduce it then.

This

Quote:

One thing I do notice in the create statement on the localhost is at the bottom it says CHARSET=latin1. It does not say that on the server. What I find weard about this is that sja created this table when it synched.

….hmmm

[peterlaursen]

You may also read this:

http://www.webyog.com/faq/10_68_en.html

But SQLyog before (I think) 4.1 did not abort in this situation, and thus the error could occur.

And did you ever read this one?

http://www.webyog.com/articles/Using_SQLyo…L_Databases.pdf

[Author]

Posts