MySQL Bugs: #35557: Error in create an DB with large text-filed

Bug #35557	Error in create an DB with large text-filed
Submitted:	25 Mar 2008 16:27	Modified:	20 Jul 2009 10:54
Reporter:	Thomas Forstmeier	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	Mysql 5.0.27	OS:	Windows (WINXP, WIN2003 server)
Assigned to:		CPU Architecture:	Any
Tags:	odbc51_postga

Description:
when i convert a table form Dataflex with an text-field large 1024 byte an
above.
i get a error in the odbc-driver 
i testet windows Mysql-Server 5.0.15  work fine
         windows Mysql-server 5.0.18  work fine
         windows Mysql-server 5.0.27 thru 5.0.51a all with error.

on Linux with Mysql-Server 5.0.51a the driver work always fine.

How to repeat:
i test it on an windows xp workstation and on windows 2003 server with
all ms updates.

Thank you for the bug report. Could you please provide the Connector/ODBC
version are you using and a test case to repeat on our side. Thanks in advance.

here our vdf 12.1 db

Attachment: t7.zip (application/x-zip-compressed, text), 57.72 KiB.

Hello 
we used ODBC-Driver Versionn 
1. 3.51.23.00
2. 3.51.24.00
4. 5.01.02.00

here an Link for downloading vdf 12.1 that we are used
http://www.dataaccess.com/download.asp?pageid=1055

i have copied an vdf12.1 db who make the Error by converting to My-Sql on 
Windows with ODBC on Linux (5.0.51a) works fine.

i Hope thats help to locate teh Error:

MfG Thomas

Thomas. Do you really expect me to start learning DataFlex? Can you please provide me with entire project that I can load into DataFlex directly (attach it to report).

Thomas, I am online for at least next 5 hours with occasional short breaks.

I have made your previous comment private to protect your login data! If that's not necessary, inform me and I'll make it public again.

As for your offer, I don't think we can work that way as my test box for this problem is far far inside MySQL intranet and firewalls... Can't you just attach project sample showing this problem?

Hello Tonic,

iam now in the office, we can start
what should i do ?

thomas, it's easy. Create and attach project sample showing this problem so I can load it on my box.

hello tonic
unzip the file in c with folders
1. open vdf12.1 studio
2. open file/workspace in folder vdf12.1/hoch/hoch.sws
3. open tool/database bilder
4. in db bilder maintance/reindex select 1-9 klick reindex all online index
5. now database/odbc/convert to odbc
6. make in odbc-admin an connect to my-sql windows
7. convert db 7 an look at the error
9. convert db 9 ti my-sql and no error occourd

odbc-error

Attachment: odbc-error.zip (application/x-zip-compressed, text), 460.38 KiB.

Thanks Thomas, let's see what we can do for you.

Thomas, I'm having trouble opening your workspace, mostly files are missing (invalid file name): Codenmt.src, cdemenu.src, Termin.src, fileupdate.src, Test.src, Syl1.src

Is there a setting (a path or so) I should change?

hello Tonic,

is ok so you dont need this files
are always programm sources.

the db-bilder programm ist waht you need

Ok, trying.

Hello Tonci,

when a table was right convert to my-sql and you want the same
table convert twice you must remove the entry in the filelist
db-bilder filelist/edit/ select the file t7 / t9 and klick edit
remove the entry ODBC_DRV: in the field "File-Name" 
here is the point who vdf the db-driver select.

when removed you can convert twice

Hello Tonic,

any success in search the Bug ?

Thomas, sorry I'm on vacation. Will pick up on this problem first thing next week.

Thomas, my lousy internet connection prevents me from analyzing this problem properly (test box is on intranet) so one of the devs is checking it out now.

Thomas,

I am not sure yet whether it's good news or not, but I couldn't repeat error (btw you never told what is that error).

However I tested w/ latest 5.1 driver only.
Tested against 5.0.26 - should work fine even according your report
5.0.20 and 5.0.51a also worked fine for me. Tested on win2003.

Could you please update driver to 5.1.3 and try w/ it?

Hello Lawrin,

Mysql Server 5.0.51a on Windows Xp Prof. and ODBC 5.1.3, always the same
Error with i reportet first.

Look at the mysql-log.zip he has an Log from ODBC and from VDF-ODBC

here the file

here the mysql-log.zip

Attachment: mysql-log.zip (application/x-zip-compressed, text), 991 bytes.

Sorry for delay with answer.

I looked error logs you sent be. Seems like the problem is that DataFlex constructs "CREATE TABLE" statement with DEFAULT value for TEXT field. MySQL doesn't allow that.

Also since I couldn't repeat bug and converting worked for me fine, I can guess maybe it can be fixed w/ DataFlex upgrade? I used 12.1.124.11 version I think.

Just want to make more clear: error occurs in MySQL server, and that is expected behaviour.

Also MySQL ODBC driver doesn't provide any information on whether particular field type can have default value. I even think there is no such possibility in ODBC interface.

Hello Lawrin,

that ist not an Error in Dataflex, is must be an Error in Mysql,
The Error accour in windows version obove 5.0.18.
In Mysql on Linux it work fine.
I Think it ist an Mysql Bug in the window version above 5.0.18.

MfG Thomas

Hello form MySql,

what is about the Bug with discribe in this tread,
i have test MySql 5.0.51b on windows without any changes?

Pleas give me an report obout your work 

thanks

Lawrin went on other tasks and I do not have this DataFlex whatever to test with. In any case, as Lawrin already pointed out, constructions coming from that tool are in no way acceptable in MySQL server:
ERROR 12289: General error. HY000 (1101)--[MySQL][ODBC 5.1 Driver][mysqld-5.0.51a-community-nt]BLOB/TEXT column 'TEXT' can't have a default value

MySQL server 5.1.31 throws warning:
mysql> CREATE TABLE `T7` (`RECNUM` integer auto_increment NOT NULL, `POS_ID_NR`
numeric(10,0) DEFAULT 0 NOT NULL, `DATUM` date DEFAULT '0001-01-01' NOT NULL, `Z
WECK` char(12) DEFAULT '' NOT NULL, `TEXT` long varchar DEFAULT '' NOT NULL, `AN
L_DAT` date DEFAULT '0001-01-01' NOT NULL, `ANL_STD` smallint DEFAULT 0 NOT NULL
, `ANL_MIN` smallint DEFAULT 0 NOT NULL, `ANL_SEC` smallint DEFAULT 0 NOT NULL,
`ANL_ERFASSER` integer DEFAULT 0 NOT NULL, `ANL_WIE` char(8) DEFAULT '' NOT NULL
, `UP_DAT` date DEFAULT '0001-01-01' NOT NULL, `UP_STD` smallint DEFAULT 0 NOT N
ULL, `UP_MIN` smallint DEFAULT 0 NOT NULL, `UP_SEC` smallint DEFAULT 0 NOT NULL,
 `UP_ERFASSER` integer DEFAULT 0 NOT NULL, `UP_WIE` char(8) DEFAULT '' NOT NULL,
 UNIQUE INDEX `T7000` (`RECNUM`));
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 1101 | BLOB/TEXT column 'TEXT' can't have a default value |
+---------+------+----------------------------------------------------+
1 row in set (0.00 sec)

In any case, this is not repeatable with latest released c/ODBC 5.1.5:
CREATE TABLE `T7` (`RECNUM` integer auto_increment NOT NULL, `POS_ID_NR` numeric(10,0) DEFAULT 0 NOT NULL, `DATUM` date DEFAULT '0001-01-01' NOT NULL, `ZWECK` char(12) DEFAULT '' NOT NULL, `TEXT` long varchar DEFAULT '' NOT NULL, `ANL_DAT` date DEFAULT '0001-01-01' NOT NULL, `ANL_STD` smallint DEFAULT 0 NOT NULL, `ANL_MIN` smallint DEFAULT 0 NOT NULL, `ANL_SEC` smallint DEFAULT 0 NOT NULL, `ANL_ERFASSER` integer DEFAULT 0 NOT NULL, `ANL_WIE` char(8) DEFAULT '' NOT NULL, `UP_DAT` date DEFAULT '0001-01-01' NOT NULL, `UP_STD` smallint DEFAULT 0 NOT NULL, `UP_MIN` smallint DEFAULT 0 NOT NULL, `UP_SEC` smallint DEFAULT 0 NOT NULL, `UP_ERFASSER` integer DEFAULT 0 NOT NULL, `UP_WIE` char(8) DEFAULT '' NOT NULL, UNIQUE INDEX `T7000` (`RECNUM`))

SQLExecDirect:
	In:	hstmt = 0x000000000087E4F0, szSqlStr = "", cbSqlStr = -3
	Return:	SQL_SUCCESS=0