'show create table' no longer works after upgrade to MySQL 5

[ffcDev]

Hi,

I recently upgraded my MySQL server from a version 4 to version 5, and now "show create table " no longer works.

The column "Create Table" in the returned dataset used to be an ftMemo with the table's create statement. Now it returns an ftString with just the name of the table.

Here's my setup info:

Server:
MySQL v5.0.51a
Linux Ubuntu v8.04

Client:
dbexpmda.dll v4.20.0.7
Delphi 7 (using dbexpmda.dll in Direct Mode)
Windows XP sp2

thanks,
Bill.

[Dimon]

On executing the "SHOW CREATE TABLE" query, different versions of MySQL server can return different field types for the "Create Table" field.
Probably your old server returned the ftMemo type for this field, and your new one returns the ftString type. But in any case should contain the table's create statement.

[ffcDev]

Yea, i don't mind that it's an ftString, but the create statement is messed up. I also get different results with different dbexpmda.dll versions.

I originally had dbexpmda.dll v4.0.0.2 and it was returning truncated create statements. (truncating around 900 characters.) So I upgraded to dbexpmda.dll v4.20.0.7 and it got worse. I no longer get a truncated create statement, i simply get the name of the table, without any create command, fields, indexes, etc. However if the table has a small create statement (lets say, under 900 characters) it works fine.

Can you try it on a table with a large create statement?

thanks,
Bill.

=== This Works ==========================
CREATE TABLE `NamePrefix` (
`NamePrefixID` int(11) NOT NULL default '0',
`Prefix` varchar(10) default NULL,
PRIMARY KEY (`NamePrefixID`)
) ENGINE=InnoDB

show create table NamePrefix

=====================================
the above example returns this:

Code: Select all

Table      Create Table
---------- -----------------------------------------------------
NamePrefix CREATE TABLE `NamePrefix` (
             `NamePrefixID` int(11) NOT NULL default '0',
             `Prefix` varchar(10) default NULL,
             PRIMARY KEY  (`NamePrefixID`)
           ) ENGINE=InnoDB DEFAULT CHARSET=latin1 

=== This does not =========================
CREATE TABLE `WebAgent` (
`WebAgentID` int(11) NOT NULL,
`AgentID` int(11) default NULL,
`Prefix` varchar(10) NOT NULL,
`FirstName` varchar(20) NOT NULL,
`MiddleName` varchar(20) NOT NULL,
`LastName` varchar(20) NOT NULL,
`Suffix` varchar(10) NOT NULL,
`NickName` varchar(20) NOT NULL,
`PrimaryPhone` varchar(12) NOT NULL,
`PrimaryExtension` varchar(5) NOT NULL,
`EMail` varchar(50) NOT NULL,
`AcceptsEMail` tinyint(4) NOT NULL default '0',
`ValidEMail` tinyint(4) NOT NULL default '0',
`ValidateCode` int(11) default NULL,
`EnteredDate` date default NULL,
`ChangedDate` date default NULL,
`WebsitePassword` varchar(20) NOT NULL,
`CompanyName` varchar(50) NOT NULL,
`Address` varchar(50) NOT NULL,
`City` varchar(50) NOT NULL,
`State` varchar(4) NOT NULL,
`Zip` varchar(5) NOT NULL,
`Zip4` varchar(4) NOT NULL,
`UserAgent` varchar(255) NOT NULL,
`LoginDate` date default NULL,
`MarketingMethodID` int(11) default NULL,
PRIMARY KEY (`WebAgentID`)
) ENGINE=InnoDB

show create table WebAgent

=======================================

the above example returns this:

Code: Select all

Table    Create Table 
-------- ------------ 
WebAgent WebAgent 

[Dimon]

Thank you for information. This problem arises because MySQL v5.0.51a returns "Create Table" field length incorrectly.
We have fixed the problem. This fix will be included in the next dbxMda build.