Bug #47010 Mysql mediumtext update problem on ADDNEW method
Submitted: 31 Aug 2009 9:36 Modified: 29 May 2013 6:07
Reporter: Fatih D Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:5.1 OS:Windows (windows 2008 web server)
Assigned to: Lawrenty Novitsky CPU Architecture:Any
Tags: Mysql text mediumtext longtext update problem with on ADDNEW method

[31 Aug 2009 9:36] Fatih D
Description:
i cant update mediumtext field on addnew method. i can update char varchar or tinytext. but i cant update text mediumtext or longtext.

i use win2008 server with iis7(i use iis6 ) with mysql 5 databe with mysql5 connector.

here is the partially my code :
Code:

<%
MM_editCmd.AddNew()		
		MM_editCmd.Fields("infotr") = "test1"
		MM_editCmd.Fields("infode") = "test2"
%>

then i did check my database. so :

infotr field is mediumtext and value is NULL
infode field is tinytext and value is test2

what is that problem ? why asp cant update mediumtext field ? my code working already on win2003 with mysql5 with mysql 3,51 connector.

edit: 
i can update varchar(4000) field. but i cant update varchar(5000) field.

i can insert data to mediumtext field with another method. only i cant do it with update method on addnew. 

How to repeat:
no repeat.
[31 Aug 2009 11:03] MySQL Verification Team
Thank you for the bug report. Could you please provide a complete test case (create table, complete test code application, my.ini file and exactly server version). Thanks in advance.
[31 Aug 2009 11:16] Fatih D
<%
Partially Code 1:
  MM_fieldsStr  = "gosterim_yeri_ve_tipi|value|category_id|value|name|value|info"&session("adminayar_site_dili")&"|value|discount|value|prctip|value|kdvtip|value|stockx|value|garanti|value|satin_alinabilirmi|value|yayin_suresi|value|diger_site_sutunda_kaydir|value|sehir|value|ilce|value|gunun_urunu|value|ayin_urunu|value|haftanin_urunu|value|listede_kalin_yazi|value|listede_farkli_renk|value"
  MM_columnsStr = "gosterim_yeri_ve_tipi|',none,''|category_id|none,none,NULL|name|',none,''|info"&session("adminayar_site_dili")&"|',none,''|discount|none,none,NULL|prctip|none,none,NULL|kdvtip|none,none,NULL|stock|',none,''|garanti|none,none,NULL|satin_alinabilirmi|none,none,NULL|yayin_suresi|none,none,NULL|diger_site_sutunda_kaydir|none,1,0|sehir|none,none,NULL|ilce|none,none,NULL|gunun_urunu|none,none,NULL|ayin_urunu|none,none,NULL|haftanin_urunu|none,none,NULL|listede_kalin_yazi|none,none,NULL|listede_farkli_renk|none,none,NULL"

Partially Code 2:
	 Dim MyConn, RS, connStr
	 Set MyConn=Server.CreateObject("ADODB.Connection")
	 connStr = MM_urunler_STRING
	 MyConn.Open connStr
	 set MM_editCmd = Server.CreateObject("ADODB.Recordset")
	 MM_editCmd.Open "products", MyConn, 2, 3, adCmdTableDirectstrSQL
	 MM_editCmd.AddNew()		
		'UPDATE PROCESS		
		For i = LBound(MM_fields) To UBound(MM_fields) Step 2
			IF Len(MM_fields(i+1)) > 0 AND MM_fields(i+1)<> "''" THEN
				MM_editCmd.Fields(MM_columns(i)) = MM_fields(i+1)
			END IF
    	Next		
		'TEST UPDATE		
		MM_editCmd.Fields("infotr") = "test1" ' THIS VALUE CANT UPDATE, FIELD TYPE MEDIUMTEXT
		MM_editCmd.Fields("infode") = "test2" ' THIS VALUE CAN UPDATE. FIELD TYPE IS VARCHAR(50)
		'response.end
	MM_editCmd.Update
	MM_editCmd.Close()
	Set rs = Nothing
	set MM_editCmd=Nothing
%>
[31 Aug 2009 11:22] Tonci Grgin
Faith, this has nothing to do with server (well, it has but with MSSQL). So please tell me which connector are you using and what is your exact connections string. Are you using "Limit column size to signed 32-bit range" option?

You got to be aware that MySQL types are much much larger than what MSSQL can store and Microsoft, of course, wrote API's with their server in mind...
[31 Aug 2009 11:26] Fatih D
OS : WINDOWS 2008 WEB SERVER
MYSQL VERSION : 5.1.11
MYSQL CONNECTOR ODBC : 5.1

my.ini 
---------------------------------
[client]

port=3306

[mysql]

default-character-set=utf8

[mysqld]

port=3306
basedir="D:/Program Files/MySQL/MySQL Server 5.1/"

datadir="D:/ProgramData/MySQL/MySQL Server 5.1/Data/"

default-character-set=utf8

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=800

query_cache_size=186M

table_cache=1520

tmp_table_size=66M

thread_cache_size=38

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=66M

key_buffer_size=290M

read_buffer_size=64K
read_rnd_buffer_size=256K

sort_buffer_size=256K

innodb_additional_mem_pool_size=12M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=6M

innodb_buffer_pool_size=562M

innodb_log_file_size=113M

innodb_thread_concurrency=10
[31 Aug 2009 11:28] Fatih D
Hi,

thank you for your replies. (and my name is Fatih)

MM_urunler_STRING = "Driver={MySQL ODBC 5.1 Driver}; Server=127.0.0.1; port=3306; Database=XXXXXXXX; UID=XXXXXXXXXXXXXXX; PWD=XXXXXXXXXXX; Option=3"
[31 Aug 2009 11:30] Fatih D
how can i check this option ?

"Limit column size to signed 32-bit range"
[31 Aug 2009 11:31] Tonci Grgin
Fatih, I apologize for misspelling your name.

Now, try adding Option=411058714 instead of Option=3 and retest.
[31 Aug 2009 13:48] Fatih D
i did change connection string for Option=411058714:

MM_urunler_STRING = "Driver={MySQL ODBC 5.1 Driver}; Server=127.0.0.1; port=3306;
Database=XXXXXXXX; UID=XXXXXXXXXXXXXXX; PWD=XXXXXXXXXXX; Option=411058714"

and i test again but nothing change.
[31 Aug 2009 13:53] Fatih D
i can update only with this method:

	set updatee= Server.CreateObject("ADODB.Command")
	updatee.ActiveConnection = MM_urunler_STRING
	updatee.CommandText = "UPDATE products SET infotr='form values'"
	updatee.CommandType = 1
	updatee.CommandTimeout = 0
	updatee.Prepared = true
	updatee.Execute()

this method working but addnew method not working.
[31 Aug 2009 13:57] Tonci Grgin
Fatih, must be some ADDNEW limitation then, let me see...
[31 Aug 2009 14:10] Tonci Grgin
Apparently, you tried to update a string that was longer than the buffer.

ADO's Update method relies upon the RecordSet to generate an Update statement based on the original query and any perceived "changes" (and value of UdateCriteria). These methods are convenient, but, unfortunately, like all auto-generated code, they are heavily dependent on the generator to make valid assumptions concerning construction and datatypes. In this case, datatypes are much wider than MSSQL ones...

Now, let's see what's happening for real.
[31 Aug 2009 14:15] Tonci Grgin
Fatih, what's the original error code and text?

I guess, since c/ODBC 5.1 is fully unicode, it uses up to 3x more space than 3.51 does (single-byte charset) thus your updates grow in size beyond what's estimated by ADO...
[31 Aug 2009 14:17] Fatih D
no error code. page processed succesfully. but field cant updated.
[1 Sep 2009 14:03] Tonci Grgin
Fatih, in this case, I will need to see your general query log from MySQL server around the time UPDATE takes place. It would also be good for you to attach ODBC DM trace file.
[1 Oct 2009 23:44] 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".
[29 May 2013 6:07] Bogdan Degtyariov
I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.