[V9.5] Bug Default Value On Varchar

[frochard]

Hi,

Version 9.5 does not allow empty string '' as default value for a varchar field

If you set default value to “”, you get in fact '””' as default value

I Go back to v 9.3

[peterlaursen]

Issue confirmed. WIll be fixed with priority.

[sathish]

Hello,

We have released version 9.51 with the fix for this. Please refer: http://www.webyog.com/blog/2012/01/03/sqlyog-mysql-gui-9-51-released/

Regards,

Sathish

[frochard]

'sathish' wrote:

We have released version 9.51 with the fix for this. Please refer: http://www.webyog.com/blog/2012/01/03/sqlyog-mysql-gui-9-51-released/

Not : The bug is partially fixed :

1°) Create a table vith a varchar field and default value is '' -> OK it works

2°) Show the table definition – the default value '' is not show

3°) Don't modify the default value but change the size of the field. and save the change -> The default value disapear

I go back again to the 9.3 version

[ashwin]

Issue confirmed. This bug fix will be available in the next public release.

[Cheater]

'ashwin' wrote:

Issue confirmed. This bug fix will be available in the next public release.

I also experienced this bug in versions 9.5*. Because of that, I was also using version 9.3.

As of version 9.63 (currently the newest version), this is still an issue. (maybe in another form)

My DDL of the table says:

`title` varchar(255) NOT NULL,

`code` varchar(255) NOT NULL

but the editor shows '' (2 single quotes). That should not be displayed.

I ran some tests and noted this:

1. When there is NO default value (as in the DDL above), I can NOT set a default value to '' (2 single quotes), because they are already there in the EDIT COLUMNS view.

2. I can set the default value of a column ('title') to, for instance, 'a'. (I type in: a … NOT the single quotes, otherwise i get 4 quotes in my DDL (''a'')). My DDL then shows this value:

`title` varchar(255) NOT NULL default 'a',

`code` varchar(255) NOT NULL

3. I can then edit the table and set the default value, explicit to '' (I type in: quote quote)). The DDL shows:

`title` varchar(255) NOT NULL default '',

`code` varchar(255) NOT NULL

4. I can, for the column 'title', clear the quotes in de default field (the column 'code' also shows 2 quotes, but these are just not in the DDL, as shown above in 3.), update my table, and get a DDL that shows:

`title` varchar(255) NOT NULL,

`code` varchar(255) NOT NULL

I am aware of MySQL's default value techniques, but this GUI issue(s) will not lead to correct table management.

I am writing my own web-based, table compare tool (because the powertool in SQLyog doesn't provide enough options). In my tool I simply compare 2 DDL's. The DDL is the only truth for me as it is formed by MySQL self. Now, I cannot trust the Edit columns view, as it shows 2 single quotes, were they (in some cases) shouldn't.

[peterlaursen]

“I simply compare 2 DDL's”. I you use SHOW CREATE TABLE we cannot use this. This will fail in some cases if server versions are not the same (a single SPACE-character difference in how it is formatted is enough and there are ltos of similar issues between MySQL versions. Not to mention when servers are pre-4.1 and and 4.1+).

[Cheater]

'peterlaursen' wrote:

“I simply compare 2 DDL's”. I you use SHOW CREATE TABLE we cannot use this. This will fail in some cases if server versions are not the same (a single SPACE-character difference in how it is formatted is enough and there are ltos of similar issues between MySQL versions. Not to mention when servers are pre-4.1 and and 4.1+).

I understand your worries about version and setting mismatches and problems with that. However, I use this tool for personal use, in a controlled environment were versions and settings are, how I would like them.

Nevertheless, let's stay ontopic and discuss the issue described in this topic.

[ashwin]

Quote:

My DDL of the table says:

`title` varchar(255) NOT NULL,

`code` varchar(255) NOT NULL

but the editor shows '' (2 single quotes). That should not be displayed.

We are not clear with this. Could you please attach a screen-shot here?

Quote:

1. When there is NO default value (as in the DDL above), I can NOT set a default value to '' (2 single quotes), because they are already there in the EDIT COLUMNS view.

You could set the Default to '' (2 single quotes) which means empty string. But, could you please tell what do you mean by “because they are already there in EDIT COLUMNS view”? You mean 'Manage Columns'?

Quote:

2. I can set the default value of a column ('title') to, for instance, 'a'. (I type in: a … NOT the single quotes, otherwise i get 4 quotes in my DDL (''a'')).

You would be getting like this '''a''' if you have Default like- 'a' (single quote a as Default). You get extra quotes because the single quote is actually escaped. Refer this- http://dev.mysql.com/doc/refman/5.0/en/string-literals.html

which says- A “'” inside a string quoted with “'” may be written as “''”. If you execute SHOW CREATE TABLE you will get the same result.

Quote:

3. I can then edit the table and set the default value, explicit to '' (I type in: quote quote)). The DDL shows:

`title` varchar(255) NOT NULL default '',

`code` varchar(255) NOT NULL

This Default is Empty string. ''(quote quote) means Empty string.

Quote:

4. I can, for the column 'title', clear the quotes in de default field (the column 'code' also shows 2 quotes, but these are just not in the DDL

We are not clear with this as well. Please attach a screen-shot. “Column `code` shows 2 quotes”. But where? Alter table tab? INFO tab?

[Author]

Posts