Bug #19879 Strange things happening with VARCHAR columns in the model
Submitted: 17 May 2006 12:43 Modified: 17 Jun 2006 13:11
Reporter: Stefaan Lesage (Basic Quality Contributor) Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Workbench Preview Severity:S1 (Critical)
Version:1.0.6 Beta OS:Microsoft Windows (Windows XP)
Assigned to: MySQL Verification Team CPU Architecture:Any

[17 May 2006 12:43] Stefaan Lesage
Description:
When exporting the SQL Create Script for an existing model, some strange things happen to VarChar fields.  It will be hard to describe, since I can't really find an accurate way to reproduce it ( it happens for some columns, and it doesn't for some others ).

For some VarChar fields the WorkBench tool seems to be generating the following SQL Statement :

`F_SPELLDISPELTYPE_FRFR` VARCHAR(255) CHARACTER SET  COLLATE  NOT NULL

adding an empty characterset and collation to the statement, and then again for another field it did everything correctly :

`F_SPELLDISPELTYPE_ENGB` VARCHAR(255) NOT NULL

People reported a similar error when synchronising a model with the DB.  And I had that in the begining as well, that is why I stopped using synchronisation for now ( it did lots of strange things ) and tried with the SQL Script instead.

How to repeat:
Well, until now I havn't been able to recreate a good test case.  It happens sometimes, and in some other cases it didn't.

I posted on the forums somewhere an idea about what I think could be going wrong.  Here is what I wrote there ( maybe this can help you ) :

Hi Guys,

There seems to be something wrong with how the model gets stored / updated somehow.  I can't simulate the problem, but it appears that sometimes the wrong properties are stored in the WorkBench file for VARCHAR fields.  It is very hard to track down, and I've been trying to simulate it, but it seems to happen quite randomly.

Now I found that you can fix this issue if you modify the MWB file manually.  I know this is not the best solution, but until I can submit a bug report which they can simulate, I have found no other solution at all.

Your model is actually a plain XML file, so you should be able to load it up in a text editor.  For your safety, make a backup of the model before you actually change anything ( and close the WorkBench tool as well ).

A good VarChar column ( which doesn't give the error you mentioned ) looks like this in your file :

<value type="dict" struct-name="db.mysql.Column">
  <value type="string" key="_id">{BFE42B97-BC9D-4E40-B5DA-8A3D14B62302}</value>
  <value type="int" key="autoIncrement">0</value>
  <value type="list" content-type="dict" content-struct-name="db.CheckConstraint" key="checks"/>
  <value type="string" key="collationName">latin1_swedish_ci</value>
  <value type="string" key="comment">The English name of the Spell Dispel Type</value>
  <value type="string" key="datatypeName">VARCHAR</value>
  <value type="string" key="defaultValue">''</value>
  <value type="int" key="defaultValueIsNull">0</value>
  <value type="list" content-type="string" key="flags"/>
  <value type="int" key="isNullable">0</value>
  <value type="int" key="length">255</value>
  <value type="string" key="name">F_SPELLDISPELTYPE_ENGB</value>
  <value type="string" key="oldName"></value>
  <value type="string" key="owner" option="ref">{530B2BF8-F6D0-487D-A639-FDC1E578F88A}</value>
  <value type="int" key="precision">0</value>
  <value type="int" key="scale">0</value>
  <value type="string" key="simpleType" option="ref">{7198A7BD-968D-4BD9-B85C-4DE7504F0D83}</value>
  <value type="string" key="structuredDatatype" option="ref"></value>
</value>

A similar column which will report the error you mentioned when synchronsing ( als happens if you export the Create SQL the same mistake is there ) looks like this :

<value type="dict" struct-name="db.mysql.Column">
  <value type="string" key="_id">{47D9F73C-81C2-45E9-A4FD-EF33B3AF2F89}</value>
  <value type="int" key="autoIncrement">0</value>
  <value type="string" key="characterSetName"></value>
  <value type="list" content-type="dict" content-struct-name="db.CheckConstraint" key="checks"/>
  <value type="string" key="collationName"></value>
  <value type="string" key="comment">The French name of the Spell Dispel Type</value>
  <value type="string" key="datatypeExplicitParams"></value>
  <value type="string" key="datatypeName">VARCHAR</value>
  <value type="string" key="defaultValue">''</value>
  <value type="int" key="defaultValueIsNull">0</value>
  <value type="list" content-type="string" key="flags"/>
  <value type="int" key="isNullable">0</value>
  <value type="int" key="length">255</value>
  <value type="string" key="name">F_SPELLDISPELTYPE_FRFR</value>
  <value type="string" key="oldName"></value>
  <value type="string" key="owner" option="ref">{530B2BF8-F6D0-487D-A639-FDC1E578F88A}</value>
  <value type="int" key="precision">0</value>
  <value type="int" key="scale">0</value>
  <value type="string" key="simpleType" option="ref">{7198A7BD-968D-4BD9-B85C-4DE7504F0D83}</value>
  <value type="string" key="structuredDatatype" option="ref"></value>
</value>

As you can see the wrong one has a line with a characterSetName value in it and an empty collation name in it.  And this is where it goes wrong I believe.  Probably the system thinks it is a different CharacterSet for the field compared to that of the table, and it tries to add the CHARACTER SET ( characterset ) COLLATE ( collation ) to the resulting SQL statement, and since the attributes for those are empty in the model file, it fails somewhere.

In my cast I simply had to remove the CharacterSet line from the XML for my varchar columns and use the correct CollationName to get it fixed.

As I said I only used the Export Create Script option for now, because I couldn't get the synchronisation working correctly, but maybe you guys can try it out to see if this solves your problem.

I will be adding a bug report and try to explain this to the devs, but it will be hard for them to track it down, if I can't find a descent way to simulate it.

Regards,

Stefaan Lesage
[17 May 2006 13:11] MySQL Verification Team
Thank you for the bug report. Please re-open this bug report when you will
able for to provide a repeatable test case.

Thanks in advance.
[17 Jun 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".