[Ritesh]

This is normal that when inserting 0, MySQL takes it as NULL and inserts the next valid value which is 1.

NO_AUTO_VALUE_ON_ZERO option will override this issue. If mysqldump is doping then its an official solution by MySQL.

We will implement this in v5.1 BETA 3.

[Ritesh]

Can you post the table definition resources table for both the source and target server?

Just the CREATE TABLE statement will do.

[Ritesh]

show tables will display all the tables/views but our query i.e.

Code:

select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'my_database' and `TABLE_TYPE` = 'BASE TABLE'

will only show the tables.

[Ritesh]

What happens when you execute:

Code:

select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'my_database' and `TABLE_TYPE` = 'BASE TABLE'

from the MySQL command line?

[Ritesh]

The extra CREATE TABLE statement are correct. This is done because a VIEW might reference another VIEW which does not yet exist. We have to create temporary table to overcome this issue.

Even MySQLDUMP follows the same.

While fixing this we introduced another bug in v5.1 BETA 1 where there are no extra CREATE TABLE. This is wrong.

The extra CREATE VIEWS that you see are temporary and will be deleted while you import the full dump.

The issue reported by DMealy is actually no BUG and it is correct.

[Ritesh]

Which version of MySQL are you connecting to?

[Ritesh]

I vote for auto-complete 😀

[Ritesh]

You cannot specify CURRENT_USER as default value in CREATE TABLE statement. Only CURRENT_TIMESTAMP is applicable.

If you specify CURRENT_USER as default value in CREATE/ALTER option, it will execute it as CREATE TABLE aaa ( a …. default 'CURRENT_USER' ).

In the DATA tab, it will show CURRENT_USER as default value (implemented in v5.1 BETA 2). When executed, it successfuly understands the constant and will insert data like: insert into (….) values ( CURRENT_USER ).

[Ritesh]

The four special KEYWORDS that SQLyog will now recognise are:

CURRENT_TIMESTAMP

CURRENT_USER

localtimestamp

UTC_TIMESTAMP

I couldn't find a congregated list of all such keywords.

[Ritesh]

Fixed in BETA 2 development tree.

[Ritesh]

You just need to copy SQLyog.ini from the FREE installation folder to the Enterprise installation folder.

Better still, install it in the same folder only 🙂

BTW, you will be sent a link to the Enterprise binary over mail for your safekeeping.

[Ritesh]

Sorry my mistake.

CURRENT_USER() is indeed a function. But CURRENT_USER is also a special keyword like CURRENT_TIMESTAMP.

[Ritesh]

Just read you last post. CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), and FROM_UNIXTIME() are handled correctly and they are taken from Keywords.db.

[Ritesh]

There is no function like CURRENT_USER(). CURRENT_USER is a special keyword like CURRENT_TIMESTAMP.

Currently, SQLyog correctly understands CURRENT_TIMESTAMP and CURRENT_USER and if a user enters one of above, SQLyog will not put it within ''.

session_user() is a function and SQLyog correctly handles it.

The two keywords that SQLyog handles are CURRENT_TIMESTAMP and CURRENT_USER and they are coded into the binary. They are not taken from Keywords.db.

We have improved SQLyog as it will now display (NULL) by default for columns whose default value has been defined to be NULL. It use to be BLANK earlier and thus on updation, SQLyog would generate statement like:

insert (….) values ( '' );

Now by default, we will put (NULL) in the column and on execution it will issue a query like:

insert (….) values ( NULL );

We use (NULL) because this has been a standard with SQLyog from the first version and we don't plan to change it.

[Ritesh]

Since the sync is mostly done between two databases/hosts, one might be running on case-sensitive option and one might be running on case-insensitive option. We are not sure which one to use then?

[Author]

Posts