[peterlaursen]

I think I found a related issue!

This:

DELIMITER $$;

DROP PROCEDURE IF EXISTS `test`.`yyyyyyyyyy`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `yyyyyyyyyy`(a char)

BEGIN

set @test = a;

END$$

DELIMITER ;$$

creates the procedure. However it does not work with autocomplete UNLESS I MANUALLY REBUILD TAGS.

@shoyu: could I ask you to try 'rebuild tags' from 'powertools menu?

.. however it does not hang here (with that SP definition)

[peterlaursen]

I tried creating a SP named 'yyy' and still cannot reproduce.

If you have a SP 'yyy' what parameters does it take?

What is MySQL version? Are you sure that you followed all update instructions when/if updating form a previous version?

[peterlaursen]

1)

Could you post the 'create statement for the table'?

I would like to see if there is a Priamry Key and if the column is NULL or NOT NULL.

2) Were there data in the table when the TIMESTAMP column was crated?

Now .. if the TIMESTAMP column has been added after data were entered in other columns it will be given values 00000000000000. Actually I have a request pending on that at bugs.mysql.com: http://bugs.mysql.com/bug.php?id=17392

SQLyog 5.1.x checks for the 'on update CURRENT_TIMESTAMP' in the table definition when saving (and skips the column in the INSERT statement if so) – but this clause is not available with MySQL 3.23!

To update an existing cell having 00000000000000 with SQLyog you can do either

1)write the time there yourself in YYYYMMDDHHMMSS -format

2)write 'CURRENT_TIMESTAMP' (without quotes). Now SQLyog omits the column in the INSERT statement and the server will update the field.

If you depend on a better TIMESTAMP handling you should not use MySQL 3.x !! 4.1 or higher!

[peterlaursen]

this is an autocomplete issue. I think it does not if you disable auto-complete? Have you had earlier 5.x versions installed?

I suggest you try this:

Delete (or rename) those items:

1) The TAGS folder in installation Folder

2) The keywords.db file in installation folder

Next install Beta8 again. A new keywords.db will be copied by the installer and the new TAG files will be built next you open a connection.

[peterlaursen]

I think you missed testing on a real computer 🙂

No, what you did was correct!

I now tried on both of my 2 Windows installations. Both are WinXP SP2. Both use native windows mouse- and keyboard-drivers (no Logitech and such 'smart' thing here!). It happens on one of those PC's (my 'mini') – Not the other.

So this is system dependent. And may be a rare thing as this 'mini' is a pretty special thing – with a VIA Nehemiah processor. Both PC's have an ordinary VIA chipset and use common 4-in-1 driver sets for the mainboard.

Is this behaviour CPU microcode dependant?

Let it open for a while and let us see if other users report same thing.

[peterlaursen]

Quote:

Do you mean in autocomplete?

Nope – it is only you that has his head filled with 'auto-complete' 😛

I request 'smart keyword handling' with the literal string 'now()' – just like 'NULL' , 'CURRENT_TIMESTAMP', 'CURRENT_USER' etc.

Writing 'now()' (without quotes) in a TIMESTAMP cell would then send 'now()' (also without quotes) and the server will insert CURRENT_TIMESTAMP. Or put it another way: just as 'localtimestamp' is a synonym for 'CURRENT_TIMESTAMP', 'now()' is too. But 'now()' is the easiest to type.

[peterlaursen]

MySQL 3 does not support 'default CURRENT_TIMESTAMP on update CURRENT TIMESTAMP' with the table definition.

This SQL seems to work however:

Code:

Alter table `tttt`.`tt` ,change `ts` `ts` timestamp (14) DEFAULT 'now()' NOT NULL;

.. although 'show create table' does not return it

then the create_time will be inserted for each new row.

If you want to update with CURRENT_TIMESTAMP when updating a row with SQLyog 5.1 just write CURRENT_TIMESTAMP. 5.02 does not support this!

http://webyog.com/faq/8_99_en.html

@Ritesh:

I request 'smart support' for 'now()' as it is much faster to type than 'CURRENT_TIMESTAMP'!

[peterlaursen]

5.12 beta 7 does

(edited)

Quote:

update `t1` set `id`='1',`t`='blabla' where `id`='1' and `t`=''

— or whatever values id and t hold

.. even if there is no default for any column in the table and no PK

If there is a PK only the PK column is used in the WHERE …

[peterlaursen]

You may delete the database before restoring … but most likely you would prefer to rename the tables in it instead … Right-click each table and 'rename table' from 'tablename' to 'tablename_bak' for instance. Once you get the backup restored, and you are sure that that is what you want you can delete the *bak tables.

Now when exporting please consider how many options you have … You have chosen NOT to include 'drop table' statements in the backup file! That is why 'create table' generates an error. Yo cannot create something that is!

Also 5.1.x is recommended if you have non-english and non-ASCII characters in your data, as it backs-up using uft8 encoding.

[peterlaursen]

Quote:

first of all remember to change the last update date in the FAQ 🙂

I bypassed the FAQ administrative backend and edited the database directly as it is faster:

Quote:

I think it is very strange that SQLyog does not understands the “Default” keyword if you define field b (in my example) as a char(10) without enabling the “NOT NULL” flag.

Well it does I think ???? See attached. There is no 'NOT NULL' for the varchar column and it inserts 'blabla' OK. Would you mind explain

Quote:

… the cell is filled with “(Default)”

I hate those paranthesis'es myself. Wish Ritesh did never invent (NULL) . NULL is clear as DEFAULT is. (NULL) and (DEFAULT) is a weird construction like (Ritesh). (Dany) and (Peter) is 😆 Nobody shall call me (Peter) ….

[peterlaursen]

FAQ write-up accordingly!

[peterlaursen]

Confirmed! But is not a bug – but an undocumented feature I think

With your description:

The update command executed is

update `TableName1` set `a`='1',`b`='' where `a`='1' and `b`=''

and you expected

update `TableName1` set `a`='1',`b`='Default' where `a`='1' and `b`=''

Now if you change to:

5) Insert “`Default`” in field b of the first row. — notice the use of backquotes

it does:

update `TableName1` set `a`='1',`b`='Default' where `a`='1' and `b`=''

******************************************

Now refer to the FAQ: http://webyog.com/faq/8_99_en.html

******************************************

Next do (give column a default-value):

alter table `test`.`TableName` change `b` `b` char (10) DEFAULT 'my_default' NOT NULL COLLATE latin1_swedish_ci

Delete all b-values and

repeat step 5+6: and it displays 'my_default'

******************************************

Conclusion:

1) Writing 'default' sets cell to column-default.

2) To write 'default' as a literal you must enclose in backquotes like '`default`' – just like you must write '`null`' or '`current_user`' to enter literals 'null' and 'current_user'.

Even I was not aware of that when writing this FAQ. Ritesh and Sarat never told me 😉

********************************************************************************

***

[peterlaursen]

thanks … it is then not related to the sqlyog.exe file at all.

I'll wait for Ritesh to read tomorrow.

[peterlaursen]

hmmm….

I think we have a similar report in our ticket system. So looks like there is 'something to it'.

I see two possibilities

1) it is a problem wuth sja.exe itself.

2) porblem occurs when sqkyog.exe 'gives control' to sja.exe

Can you run the saved job from command-line?

[peterlaursen]

Not fixed in Beta6 🙁

[Author]

Posts