MySQL 4.1.1-alpha-nt: problems with subqueries

Hello,

 

 

 

The MySQL server is stopped by Microsoft Windows XP when using one specific
subquery.

 

MySQL version: 4.1.1-alpha-nt

Microsoft Windows XP Home Edition     Version 5.1.2600 Service Pack 1 Build
2600

 

Method to reproduce:

 

(1a) Send batchfile from "Windows C:\ Commandprompt" window from directory
c:\mysql\bin with the following command:

Mysql -vvv < c:\mysql_batchfiles\select_werkverdelinginnodb_w9.txt >
c:\mysql_batchfiles\result_select_werkverdelinginnodb_w9.txt

The result in the MS-DOS command window is:

ERROR 2013 at line: lost connection to mysql server during query.

            

(1b) Typing the same query manually gives the same result. (First method:
log in MySQL from "Windows C:\ Commandprompt" window. Second method: MySQL
Control Center 0.9.4-beta.)

 

Information included in this mail:

 

(1) Query batchfile and outfile mentioned above. These files, and other
MySQL files mentioned in this mail, are included in file "error report
subquery 20040109.zip".

 

(2) Microsoft Windows XP created automatically an error report for automatic
transmission to Microsoft. 

This error report is available on request, but not included in this mail.
(File size for "msWindowsXP_wer18_tmp_dir00.zip" is 1.2 MB)

 

(3) Afterwards a report was made on the status of MySQL, using the tool
winmysqladmin.exe, and saved with filename "winmysqladmin report
20040109.txt".

 

(4) The error interrupt occurred at date 2004-01-09 time 08:21 and the MySQL
server was started manually with NET START MYSQL at date 2004-01-09 time
08:29.

 

(4a) Information taken from file c:\mysql\data\mysqlerr.log:
040108 21:01:44  InnoDB: Starting shutdown...

040108 21:01:46  InnoDB: Shutdown completed; log sequence number 0 479963

040108 21:01:46  MySql: Shutdown Complete

 

MySql: ready for connections.

Version: '4.1.1-alpha-nt'  socket: ''  port: 3306

MySql: ready for connections.

Version: '4.1.1-alpha-nt'  socket: ''  port: 3306

            

(4b) Information taken from file c:\mysql\data\ton-lvpcruf4206.err:

040108 20:57:04  InnoDB: Started; log sequence number 0 479963

040109  7:39:02  InnoDB: Started; log sequence number 0 479963

040109  8:29:30  InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

040109  8:29:30  InnoDB: Starting log scan based on checkpoint at

InnoDB: log sequence number 0 479973.

InnoDB: Doing recovery: scanned up to log sequence number 0 479973

040109  8:29:30  InnoDB: Flushing modified pages from the buffer pool...

040109  8:29:30  InnoDB: Started; log sequence number 0 479973



 

 

The MySQL server gives wrong results at other subqueries.

 

All files mentioned below are included in the zip file "error report
subquery 20040109.zip" sent with this mail.

 

Method to reproduce:

 

(1)     Create two databases, using the following two batchfiles:

(These two databases, and the expected results, are taken from a SQL
training book.)

create_werkverdelingInnoDB.txt

create_bibliotheekInnoDB.txt

 

(2)     Use the following two batchfiles to send the queries, and produce
outfiles:

Batchfiles:

select_werkverdelinginnodb_query_boek.txt

select_bibliotheekinnodb_query_boek.txt

Outfiles for MySQL 4.1.0-alpha-max-nt:

result_werkverdelinginnodb.txt

result_bibliotheekinnodb.txt

Outfiles for MySQL 4.1.1-alpha-nt:

result_select_werkverdelinginnodb_query_boek.txt

result_select_bibliotheekinnodb_query_boek.txt

 

(3)     Results for database "werkverdelinginnodb" in the Outfile:

(Query numbers can be found in the select batchfile)

Query number   MySQL 4.1.0-alpha-max-nt        MySQL 4.1.1-alpha-nt

W1                   OK                                          OK

W2                   OK                                          OK

W3                   OK                                          OK

W4                   OK                                          OK

W5                   OK                                          OK

W6                   OK                                          OK

W7                   OK                                          OK

W8                   OK                                          OK

W9                   OK                                          see remark
below

W10                 OK                                          OK

W11                 OK                                          OK

W12                 OK                                           OK

W13                 OK                                          OK

W14                 OK                                          OK

W15                 OK                                          faulty
result

W16                 OK                                          OK

W17                 OK                                          faulty
result

W18                 OK                                          OK

W19                 OK                                          faulty
result

W20                 OK                                          faulty
result

W21                 OK                                          OK

W22                 OK                                          OK

W23                 OK                                          OK

W24                 OK                                          OK

W25                 OK                                          OK

W26                 OK                                          OK

 

Remark for query number W9: This query is changed to #comments# in the
batchfile, because it causes an error interrupt from Microsoft Windows XP
(as explained in the first part of this mail) when MySQL 4.1.1-alpha-nt is
used. The query result is OK when MySQL 4.1.0-alpha-max-nt is used.

 

(4)     Results for database "werkverdelinginnodb" when queries where typed
manually, using MySQL Control Center 0.9.4-beta with MySQL 4.1.1-alpha-nt:

"ERROR 2013: Lost connection to MySQL server during query" 

For query W9 when MySQL 4.1.1-alpha-nt and Microsoft Windows XP are used.

"ERROR 1235: This version of MySQL doesn't yet support 'LIMIT &
IN/ALL/ANY/SOME subquery"

For query W10, W11, W12, W15, W17, W19, W20, W21, W22, W24B, W26B.

The other results are identical to the batchfile. 

 

(5)     Results for database "bibliotheekinnodb" in the Outfile:

(Query numbers can be found in the select batchfile)

Query number   MySQL 4.1.0-alpha-max-nt        MySQL 4.1.1-alpha-nt

B1                    OK                                          OK

B2                    OK                                          OK

B3                    OK                                          OK

B4                    OK                                          OK

B5                    OK                                          OK

B6                    OK                                          OK

B7                    OK                                          OK

B8                    OK                                          OK

B9                    OK                                          OK

B10                  OK                                          OK

B11                  OK                                          OK

B12                  OK                                          OK

B13                  OK                                          OK

B14                  OK                                          OK

B15                  OK                                          OK

B16                  faulty result                             OK

B17                  faulty result                             OK

B18                  OK                                          OK

B19                  OK                                          faulty
result

B20                  faulty result                             faulty result

B21                  faulty result                             faulty result

 

The expected result for query B20 is:

bnummer          bnaam
3                      dokter x
7                      op avontuur
8                      zeilen
10                    zelf sleutelen
11                    elektronica

 

The expected result for query B21 is:

boek.bnummer  bnaam
10                    zelf sleutelen
11                    elektronica

 

(6)     Results for database "bibliotheekinnodb" when queries where typed
manually, using MySQL Control Center 0.9.4-beta with MySQL 4.1.1-alpha-nt:

 "ERROR 1235: This version of MySQL doesn't yet support 'LIMIT &
IN/ALL/ANY/SOME subquery"

For query B14A, B14B, B15, B16, B17, B19, B20, B21.

The other results are identical to the batchfile. 

 

 

 

Regards,

Ton Blom

 <mailto:[email protected]> [email protected]

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]