[peterlaursen]

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

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

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]

a sync tool will have to access the tunnel file A LOT of times before it has finished.

Looks like system 'forgets' privileges after some reads. Or just can't handle so many reads so fast.

I am not in doubt that this is an issue with the configuration. But that is not same as SQLyog could not be improved to handle such situations.

You will need to give very detailed information about every piece of software involved (version, configurations) . And if it is a *nix then some Forum for the actual *nix version/family probably is best chance.

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

[peterlaursen]

Now … more research .. and it gets hairier!

I installed php 4.3.11 on my local.

Versatile issue does not occur!

The 'Turkish' issue doesnot either

But the 'value out of range' with a autoincrement does!

Next I tried PHP 4.3.9 .. but My Apache 2.0.55 won't start with it!

Summary:

*No issue with SQLyog 4.1 on either PHP version anywhere

*No issue with any SQLyog on Webyo server Apache 2.0.54, PHP 4.3.9

* on my local with Apache 2.0.55 and php 4.3.11 “'value out of range' with a autoincrement” but not Versatile and Turkish with SQLyog 5.0+

* on my local with Apache 2.0.55 and php 4.4.2 and 5.1.2 all three issues occur with SQLyog 5.0+.

* on my personal webhost Apache 1.3.34 and php 4.4.2 all three issues occur with SQLyog 5.0+

(I don't claim to have it all – there could be configurations, active PEAR scripts, Zend extensions etc. that all have things to say here!)

[peterlaursen]

So it is the SELECT from INFORMATION_SCHEMA who is the 'sinner' here.

It it posible for you to export the STRUCTURE only (no data needed) and let Ritesh and maybe even me try?

Did you run all update scripts when MySQL was updated ?

Tried the Mysqlcheck program ?

[peterlaursen]

Those are created at connection:

/*[8:09:22 AM][ 94 ms]*/ show variables like '%character%'

/*[8:09:22 AM][ 62 ms]*/ Set character_set_connection=latin1

/*[8:09:22 AM][ 47 ms]*/ Set character_set_results=latin1

/*[8:09:22 AM][ 63 ms]*/ Set character_set_client=latin1

/*[8:09:22 AM][ 46 ms]*/ set sql_mode=''

This is executed when Object Browser is 'filled'

/*[8:09:22 AM][ 63 ms]*/ show databases

This is executed when you select a DB (clicking on it or choosing from drop-down

/*[8:09:26 AM][ 32 ms]*/ use 'my_database'

This is executed when you expand by clicking the plus (+) to the left of the DB.

/*[8:13:01 AM][215000 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'my_database' and `TABLE_TYPE` = 'BASE TABLE'

[peterlaursen]

VERY IMPORTANT, I believe!

++++++++++++++++++++

On the new Webyog server running MySQL 4.1 and PHP 4.3.9:

NO PROBLEMS!

All in this thread and all in 'Turkish characters' -thread works on this server.

Looks like some change took place with with PHP 4.4 and 5.1 in relation to 4.3 (don't know about 5.0). But strange to me that it can affect the SQL that SQLyog writes.

Maybe just some flag or test or function call that we need in SQLyogTunnel.

[peterlaursen]

and MySQL 4.0 too.

So no SET NAMES issue and no INFOMATION_SCHEMA issue either.

[peterlaursen]

exact the same same with MySQL 4.1.18 and PHP 4.4.2

[peterlaursen]

Please note this change:

Confirmed

with 5.02/5.03 I can insert and delete these data using HTTP, with 5.1 I can't.

EDIT: WRONG! Broken with 5.0!

All tests on MySQL 5.1.6, PHP 5.1.2.

[peterlaursen]

And finally: no DELETE-issue with 4.1 and HTTP with my example or Versatile's

[peterlaursen]

One issue more (MySQL 5.1.6):

With HTTP this

Code:

insert into `t1` (`id`,`txt`) values ( '','c')

generates the error

Quote:

Error Code : 1264

Out of range value for column 'id' at row 1

(when executed from SQL-pane)

4.1 wrote

Quote:

insert into `test`.`t1` ( `id`, `txt` ) values (  NULL,  'd' )

.. and that works even with HTTP

I'll repeat: skip autocrement colums with the insert! 😀

Why code around problems when you simply can SKIP coding – even a coder should see the logic! But this SHOULD work with HTTP of course!

[peterlaursen]

HTTP-connection insert: insert into `t1` (“,“) values ( '','a')

Direct connection: insert into `t1` (`id`,`txt`) values ( '','a')

That must have been broken with PUCLIC beta! I don't believe the non-public ones that I tested did this!

EDIT: obviously wrong. read on!

[Author]

Posts