forum.vmspython.org Forum Index forum.vmspython.org
Forum system
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

MYSQL V5.1.46: INSERT/UPDATE => 64K fail with error 2013

 
Post new topic   Reply to topic    forum.vmspython.org Forum Index -> MySQL for OpenVMS
View previous topic :: View next topic  
Author Message
karcher



Joined: 11 Jan 2011
Posts: 7
Location: Madison, WI USA

PostPosted: Tue Jan 11, 2011 7:36 pm    Post subject: MYSQL V5.1.46: INSERT/UPDATE => 64K fail with error 2013 Reply with quote

After fresh installs of either Alpha (VMS 8.3) and IA64 (VMS 8.4) *MYSQL051-V4602-0-1.PCSI, INSERT or UPDATE queries that approach 64KB in length fail with:

MySQL error 2013
Lost connection to MySQL server during query

Occurs with both MYISAM and INNODB engines.

Table definition:

CREATE TABLE `entry` (
`entry_id` int(10) unsigned NOT NULL auto_increment,
`img_data` mediumblob COMMENT 'storage for image',
PRIMARY KEY (`entry_id`));

PHP:
Code:

data = str_repeat('X', 65000);
$query = "INSERT INTO entry (img_data)  VALUES('$data');";

if (mysql_query($query)) echo "Query Sucessful: " . strlen($query) . " bytes";
   else {
        echo "<pre>MySQL error " . mysql_errno() . "\n" . mysql_error().
             "\nQuery size: " . strlen($query) . " Query: \n$query</pre>";
        exit;
   }


Result in browser window:

MySQL error 2013
Lost connection to MySQL server during query
Query size: 65041 Query:
INSERT INTO entry (img_data) VALUES('XXX...

The same code works on V5.1.9 and 4.1.14

MYSQLD section from my.cnf:

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer = 16M
max_connections = 100
max_allowed_packet = 16M
read_buffer_size = 256K
read_rnd_buffer_size = 512K
table_cache = 128
sort_buffer_size = 512K
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
query_cache_size = 10485760
datadir=/mysql051_root/data/
default-storage-engine=InnoDB
lower_case_table_names=1
myisam-recover
log-warnings = 2
log-bin=sati-bin
server-id = 1
tmpdir = /mysql051_root/mysql_server/tmp/
innodb_data_home_dir = /mysql051_root/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /mysql051_root/data/
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_lock_wait_timeout = 50
innodb_file_per_table = 1
Back to top
View user's profile Send private message Send e-mail
jfp



Joined: 12 Jul 2004
Posts: 618

PostPosted: Wed Jan 12, 2011 3:12 pm    Post subject: Reply with quote

Thanks for reporting,

I will take a look, meanwhile you can continue to use 5.1.9.


JF
Back to top
View user's profile Send private message Send e-mail Visit poster's website
nr



Joined: 21 Jan 2011
Posts: 4

PostPosted: Fri Jan 21, 2011 10:52 am    Post subject: Reply with quote

Hello,

I run 5.1.46 on AXP and got similar errors when using PHP MySQL client version 5.0.22.
Accessing a row of a table with a mediumblob or longblob field containing more than 64K fails with MySQL error 2013;
SHOW TABLE STATUS LIKE '<table>' fails with MySQL error 2006.

Using the commandline client mysql 5.1.46 everything seems runnig fine.


nr


Last edited by nr on Fri Jan 21, 2011 6:26 pm; edited 2 times in total
Back to top
View user's profile Send private message
jfp



Joined: 12 Jul 2004
Posts: 618

PostPosted: Fri Jan 21, 2011 11:15 am    Post subject: Reply with quote

Hello,

Quote:
Using the commandline client mysql 5.1.46 everything runs fine.


So, this probably mean that it's a problem in the MySQL client library used by PHP, not in MySQL server.

It would be interesting to test with a simple C client using the client library provide with 5.1.49 (or .46) and see if the problem is still here.

An Python test would probably be ok, on AXP I used 4.1.46 and on IA64 5.1.49.

I will take a look next week.

JF
Back to top
View user's profile Send private message Send e-mail Visit poster's website
nr



Joined: 21 Jan 2011
Posts: 4

PostPosted: Fri Jan 21, 2011 2:59 pm    Post subject: Reply with quote

Hello and thank's for your answer!

Now I get a reproducable error with server 5.1.46 on AXP and commandline client mysql.exe;1 Ver 14.14 Distrib 5.1.46, for OpenVMS (Alpha):

Code:

mysql> show create table t_blob;
CREATE TABLE "t_blob" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "data" longblob,
  PRIMARY KEY ("id"))                                           
....
mysql> delete from t_blob;
Query OK, 3 rows affected (0.00 sec)

mysql> insert into t_blob set data=repeat('X',88000);
Query OK, 1 row affected (0.00 sec)

mysql>  select * from t_blob limit 1;

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>


This error seems to be independent of the client. It occurs also with 3.23.47 and 5.0.22 clients on Win32.

On a 5.1.51 server on Win32 I could not reproduce an error like this.

Update
- The same error occurs with a small C Client API programm: mysql_fetch_row throws mysql error 2013
- With 5.1.46 client on AXP: 65529 Bytes work, 65530 Bytes don't work
- With an old 3.23.47 client on Win32: 81799 Bytes work, 81800 Bytes don't work.

Those limits are very near to 64k and 80k. Different TCP/IP behaviour, when a Win32-client is involved?


Any suggestions?

nr
Back to top
View user's profile Send private message
jfp



Joined: 12 Jul 2004
Posts: 618

PostPosted: Mon Jan 24, 2011 12:43 pm    Post subject: Reply with quote

Thanks for the reproducer.

On IA64 VMS 8.3 MySQL 5.1.46 the test pass without any problem.

On AXP VMS 7.3-2 the test failed.

So I suspect a CRTL limitation on VMS 7.3-2 lifted on 8.3.

If I remember correctly I have seen such update in the socket interface.

JF
Back to top
View user's profile Send private message Send e-mail Visit poster's website
bisonmalembouche



Joined: 01 Jun 2005
Posts: 74

PostPosted: Mon Jan 24, 2011 4:01 pm    Post subject: Reply with quote

In the releases notes of VMS 8.3

http://h71000.www7.hp.com/doc/83final/6677/ovms_83_rel_notes.pdf

I see

5.9 C Run-Time Library
The following sections describe changes and corrections to the C Run-Time Library (RTL).
...
...
5.9.20 Problem with Read/Write for Socket Transfers Greater Than 64K
V8.3
Support is added for socket transfers greater than 64K bytes for the
following
socket routines:
send recv read
sendto recvfrom write
sendmsg recvmsg
Back to top
View user's profile Send private message Send e-mail
nr



Joined: 21 Jan 2011
Posts: 4

PostPosted: Tue Jan 25, 2011 11:01 am    Post subject: Reply with quote

Hello,

@jfp, first of all, thanks for your great work at MySQL on VMS!
This restriction means, that it is impossible to handle transfers greater than 64K via MySQL client API on VMS 7.3.
So we can forget MEDIUMBLOB and LONGBLOB until upgrade to VMS 8.3. I'm lucky not to need BLOB's > ~30K in the moment.

But I don't understand yet, why a MySQL client on W32 can handle up to 80K with a MySQL server on VMS 7.3 and a MySQL client on VMS can't.

@bisonmalembouche, thanks for the link! Reading this I remember that I had encountered such a limitation years ago, when writing and testing IP socket communication programs.

nr
Back to top
View user's profile Send private message
jescab



Joined: 28 Jan 2008
Posts: 252

PostPosted: Tue Jan 25, 2011 4:43 pm    Post subject: Reply with quote

> But I don't understand yet, why a MySQL client on W32 can handle
> up to 80K with a MySQL server on VMS 7.3 and a MySQL client on VMS can't.

The obvious guess is that the limitation are in the client parts of MySQL
for OpenVMS and not in the server parts...
Back to top
View user's profile Send private message
karcher



Joined: 11 Jan 2011
Posts: 7
Location: Madison, WI USA

PostPosted: Sat Jan 29, 2011 12:53 am    Post subject: Reply with quote

jfp wrote:


So, this probably means that it's a problem in the MySQL client library used by PHP, not in MySQL server.

JF


My evidence that it works from php with MySQL 5.1.9 and 4.1.14 but not 5.1.46 doesn't support this. The command line test works on 5.1.46 for me too but that doesn't mean something is not different about the 5.1.46 server.

I'm running VMS 8.4 IA64 with CSWS_PHP V2.20
Back to top
View user's profile Send private message Send e-mail
karcher



Joined: 11 Jan 2011
Posts: 7
Location: Madison, WI USA

PostPosted: Wed Feb 02, 2011 5:10 am    Post subject: Reply with quote

jfp wrote:


So, this probably means that it's a problem in the MySQL client library used by PHP, not in MySQL server.

JF


After the performing 66K and larger inserts successfully using C, python and php v5.3 (ubuntu) clients, it looks like JF is correct and the problem lies in HP's mysql client (v4.1.14) for php (v5.2.13). Why the older mysql servers don't have the problem I can't explain.

After going through output from tcpdump of the traffic that returns the 2013 error to the php client there's some additional error information that is NOT returned to the client (hidden in the tcp packets). Just after the end of the insert the server responds with:

1064:You have an error in your SQL syntax

followed by:

1156: Got packets out of order

The latter is the smoking gun that points at HP's php mysql client.

I plan to mess with some the VMS tcp settings to see if that might have any effect for which I'm not hopeful. If not I'll be reporting this to HP's VMS apache group and not expecting a fix anytime soon.

Thanks again to JF for all your effort on bringing mysql to vms.

--Carl
Back to top
View user's profile Send private message Send e-mail
jfp



Joined: 12 Jul 2004
Posts: 618

PostPosted: Wed Feb 02, 2011 3:45 pm    Post subject: Reply with quote

Thanks to all testing and reporting.

JF
Back to top
View user's profile Send private message Send e-mail Visit poster's website
karcher



Joined: 11 Jan 2011
Posts: 7
Location: Madison, WI USA

PostPosted: Wed Aug 31, 2011 8:27 pm    Post subject: Reply with quote

karcher wrote:


After the performing 66K and larger inserts successfully using C, python and php v5.3 (ubuntu) clients, it looks like JF is correct and the problem lies in HP's mysql client (v4.1.14) for php (v5.2.13). Why the older mysql servers don't have the problem I can't explain.

After going through output from tcpdump of the traffic that returns the 2013 error to the php client there's some additional error information that is NOT returned to the client (hidden in the tcp packets). Just after the end of the insert the server responds with:

1064:You have an error in your SQL syntax

followed by:

1156: Got packets out of order

The latter is the smoking gun that points at HP's php mysql client.

I plan to mess with some the VMS tcp settings to see if that might have any effect for which I'm not hopeful. If not I'll be reporting this to HP's VMS apache group and not expecting a fix anytime soon.

Thanks again to JF for all your effort on bringing mysql to vms.

--Carl


I'm pleased to report that HP has provided a new php_mysql.exe image (QXCM1001111012_PHP_MYSQL.EXE_IA64) that fixed the problem!
Back to top
View user's profile Send private message Send e-mail
karcher



Joined: 11 Jan 2011
Posts: 7
Location: Madison, WI USA

PostPosted: Wed Aug 31, 2011 8:28 pm    Post subject: Reply with quote

karcher wrote:


After the performing 66K and larger inserts successfully using C, python and php v5.3 (ubuntu) clients, it looks like JF is correct and the problem lies in HP's mysql client (v4.1.14) for php (v5.2.13). Why the older mysql servers don't have the problem I can't explain.

After going through output from tcpdump of the traffic that returns the 2013 error to the php client there's some additional error information that is NOT returned to the client (hidden in the tcp packets). Just after the end of the insert the server responds with:

1064:You have an error in your SQL syntax

followed by:

1156: Got packets out of order

The latter is the smoking gun that points at HP's php mysql client.

I plan to mess with some the VMS tcp settings to see if that might have any effect for which I'm not hopeful. If not I'll be reporting this to HP's VMS apache group and not expecting a fix anytime soon.

Thanks again to JF for all your effort on bringing mysql to vms.

--Carl


I'm pleased to report that HP has provided a new php_mysql.exe image (QXCM1001111012_PHP_MYSQL.EXE_IA64) that fixed the problem!
Back to top
View user's profile Send private message Send e-mail
jfp



Joined: 12 Jul 2004
Posts: 618

PostPosted: Wed Sep 07, 2011 8:22 am    Post subject: Reply with quote

Great Exclamation

Thanks for reporting.

JF
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    forum.vmspython.org Forum Index -> MySQL for OpenVMS All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group