Home » Database » MDB2 » Bug #10095

[2007-02-14 12:35 UTC] darian at criticode dot com

Description:
------------
A field of type "text", with no length, specified in xml created from MDB2_Schema is incorrectly translated into the MySQL type VARCHAR(65532) rather than TEXT.

Test script:
---------------
<field>
<name>Value</name>
<type>text</type>
<notnull>false</notnull>
</field>

Expected result:
----------------
I was expecting this field definition, passed to createDatabase as part of a table definition, to yield an SQL column creation statement of type TEXT.

Actual result:
--------------
A column of type VARCHAR(65532) was yielded, resulting in SQL causing the following error:

_doQuery: [Error message: Could not execute statement]
[Last executed query: CREATE TABLE config (Config_ID INT UNSIGNED DEFAULT 0 NOT NULL, Name VARCHAR(30) DEFAULT NULL, Value VARCHAR(65532) DEFAULT NULL)]
[Native code: 1118]
[Native message: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs]

I temporarily addressed the problem by commenting out the first three lines of the "text" case in MDB2_Driver_Datatype_mysql::getTypeDeclaration as follows:

/*if (empty($field['length']) && array_key_exists('default', $field)) {
$field['length'] = $db->varchar_max_length;
}*/

I spent some time trying to set $db->varchar_max_length appropriately, but it seems it get set to 65532 from MDB2_Driver_mysql::connect(), now MDB2_Driver_mysql::_getServerCapabilities() as of CVS changeset 1.174. I couldn't figure out its intended use, and gave up and just commented out the offending block.

[2007-02-14 12:37 UTC] darian at criticode dot com

Sorry, in the "Expected result" section of this bug report, that's MDB2_Schema::createDatabase().

[2007-02-16 16:10 UTC] darian at criticode dot com

Perhaps. However, the schema xml is correctly parsed and presented to the driver with the aformentioned field correctly labeled as "text", and is passed down to getTypeDeclaration() as such.

I think the problem lies with MDB2_Driver_Datatype_mysql::getTypeDeclaration().

[2007-02-26 17:19 UTC] darian at criticode dot com

It still has the issue if <default> is removed. I noticed the conditional that checks for "default" in $field, and tried removing <default> as one of my first workarounds. That didn't correct the problem.

[2007-02-27 23:36 UTC] darian at criticode dot com

Here it is:

<?xml version="1.0" encoding="ISO-8859-1" ?>
<database>

<name>psidb</name>
<create>true</create>
<overwrite>false</overwrite>

<table>

<name>config</name>

<declaration>

<field>
<name>Config_ID</name>
<type>integer</type>
<unsigned>true</unsigned>
<length>4</length>
<notnull>true</notnull>
<default>0</default>
</field>

<field>
<name>Name</name>
<type>text</type>
<length>30</length>
<notnull>false</notnull>
<default></default>
</field>

<field>
<name>Value</name>
<type>text</type>
<notnull>false</notnull>
<default></default>
</field>

<index>
<name>PRIMARY</name>
<primary>true</primary>
<field>
<name>Config_ID</name>
<sorting>ascending</sorting>
</field>
</index>

</declaration>

</table>
</database>

[2007-02-28 00:17 UTC] darian at criticode dot com

That XML doesn't work for me. Fails with the same error as before:

[Last executed query: CREATE TABLE config (Config_ID INT UNSIGNED DEFAULT 0 NOT NULL, Name VARCHAR(30) DEFAULT NULL, Value VARCHAR(65532) DEFAULT NULL)]
[Native code: 1118]
[Native message: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs]

What version os MDB2_* are you using? I have the following:

MDB2 2.3.0 stable
MDB2_Driver_mysql 1.3.0 stable
MDB2_Driver_pgsql 1.3.0 stable
MDB2_Driver_querysim 0.6.0 beta
MDB2_Driver_sqlite 1.3.0 stable
MDB2_Schema 0.7.1 beta

[2007-02-28 00:22 UTC] darian at criticode dot com

Also, the generated SQL is run against MySQL 5.0.22. What version are you running against?

[2007-02-28 02:23 UTC] darian at criticode dot com

Your example worked.

I see what the problem is: I needed force_defaults => 'false' as well as removal of <default></default>.

All is well. Thanks Igor.