| Bug #35469 | server crash with LOAD DATA INFILE to a VIEW | ||
|---|---|---|---|
| Submitted: | 20 Mar 2008 20:36 | Modified: | 7 Apr 2008 17:35 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.51a/5.1 | OS: | Any |
| Assigned to: | Alexander Nozdrin | CPU Architecture: | Any |
[20 Mar 2008 20:38]
Peter Laursen
queries in client and WinVista message
Attachment: crash.jpg (image/jpeg, text), 105.73 KiB.
[20 Mar 2008 20:45]
Peter Laursen
please disregard 1st image and have a look at 2nd instead.
[20 Mar 2008 20:47]
Peter Laursen
correct image this time .. I hope!
Attachment: crash.jpg (image/jpeg, text), 103.32 KiB.
[20 Mar 2008 21:41]
Peter Laursen
-- three examples that dont crash:
-- simple inserts are ok
drop table if exists `loadtst`;
CREATE TABLE `loadtst` (
`id` bigint(20) default NULL,
`var1` varchar(20) default NULL,
`var2` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
drop view if exists loadtstvw;
create view loadtstvw as select * from loadtst;
insert into loadtstvw (id,var1,var2) values (1,'a','b'),(2,'x','y');
show full fields from `test`.`loadtst`;
-- wow .. I have a server
-- specifying the view by listing colums too
drop table if exists `loadtst`;
CREATE TABLE `loadtst` (
`id` bigint(20) default NULL,
`var1` varchar(20) default NULL,
`var2` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
drop view if exists loadtstvw;
create view loadtstvw as select id,var1,var2 from loadtst;
insert into loadtstvw (id,var1,var2) values (1,'a','b'),(2,'x','y');
show full fields from `test`.`loadtst`;
-- also a view with a subset of table
drop table if exists `loadtst`;
CREATE TABLE `loadtst` (
`id` bigint(20) default NULL,
`var1` varchar(20) default NULL,
`var2` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
drop view if exists loadtstvw;
create view loadtstvw as select id,var1 from loadtst;
insert into loadtstvw (id,var1) values (1,'a'),(2,'x');
show full fields from `test`.`loadtst`;
[20 Mar 2008 21:52]
Peter Laursen
it seem that it is too late now!
drop table if exists `loadtst`;
CREATE TABLE `loadtst` (
`id` bigint(20) default NULL,
`var1` varchar(20) default NULL,
`var2` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
drop view if exists loadtstvw;
create view loadtstvw as select id,var1,var2 from loadtst;
load data local infile 'C:\\Users\\Peter\\Desktop\\load.csv' into table `test`.`loadtstvw`
fields escaped by '\\' terminated by ',' enclosed by '"' lines terminated by '\r\n' (`id`,
`var1`, `var2`);
show full fields from `test`.`loadtst`; -- server crash too
drop table if exists `loadtst`;
CREATE TABLE `loadtst` (
`id` bigint(20) default NULL,
`var1` varchar(20) default NULL,
`var2` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
drop view if exists loadtstvw;
create view loadtstvw as select id,var1 from loadtst;
load data local infile 'C:\\Users\\Peter\\Desktop\\load.csv' into table `test`.`loadtstvw`
fields escaped by '\\' terminated by ',' enclosed by '"' lines terminated by '\r\n' (`id`,
`var1`);
show full fields from `test`.`loadtst`; -- server crash too!
[20 Mar 2008 21:54]
MySQL Verification Team
I wasn't able to repeat the crash on XP with the server version reported and with some week older source version, however the below error messages were showed so I will test on Windows Vista: c:\dbs>5.0\bin\mysqld --standalone --console 080320 18:41:46 InnoDB: Started; log sequence number 0 27988319 080320 18:41:47 [Note] 5.0\bin\mysqld: ready for connections. Version: '5.0.56-nt-debug' socket: '' port: 3306 Source distribution Error: Freeing pointer out of range at line 3900, '.\item_func.cc' Error: Freeing pointer out of range at line 3900, '.\item_func.cc' Error: Freeing pointer out of range at line 3900, '.\item_func.cc' Error: Freeing pointer out of range at line 3900, '.\item_func.cc' Error: Freeing pointer out of range at line 3900, '.\item_func.cc' Error: Freeing pointer out of range at line 3900, '.\item_func.cc'
[20 Mar 2008 21:59]
Peter Laursen
The error that bothered me whne starting to research this was this (image in next post). The table had absolutely NO indexes and the view was created after every index was dropped. I was importing to a table with 4 columns of which one was a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP (if that matters .. it should not). I wonder if this could be a caching issue with the server? But now I am only able to crash the server ..... :-(
[20 Mar 2008 22:00]
Peter Laursen
image to be viewed in context of previous post
Attachment: incomplete.jpg (image/jpeg, text), 28.82 KiB.
[20 Mar 2008 22:18]
Peter Laursen
changing charset (latin1) and ENGINE (MyISAM) changes nothing. It still crashes.
[20 Mar 2008 22:28]
Peter Laursen
after system reboot same situation.
[20 Mar 2008 22:36]
Peter Laursen
error log also tells about the crash: 080320 23:16:12 InnoDB: Started; log sequence number 0 59880546 080320 23:16:12 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections. Version: '5.0.51a-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL) InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 080320 23:17:04 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... I have lots of those of course .. one for every crash probably!
[20 Mar 2008 22:56]
Peter Laursen
cmd line crashes the server too, of course.
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.51a-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test;
Database changed
mysql> drop table if exists `loadtst`;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE `loadtst` (
-> `id` bigint(20) default NULL,
-> `var1` varchar(20) default NULL,
-> `var2` varchar(20) default NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.13 sec)
mysql>
mysql> drop view if exists loadtstvw;
Query OK, 0 rows affected (0.00 sec)
mysql> create view loadtstvw as select * from loadtst;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> load data local infile 'C:\\load.csv' into table `test`.`loadtstvw`
-> fields escaped by '\\' terminated by ',' enclosed by '"' lines terminated
by '\r\n' (`id`,
-> `var1`, `var2`);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
mysql> show full fields from `test`.`loadtst`;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[21 Mar 2008 5:20]
MySQL Verification Team
Crash with heap corruption
Attachment: heap-corruption.png (image/png, text), 115.00 KiB.
[21 Mar 2008 5:24]
MySQL Verification Team
I will test 5.1 too.
c:\dbs>5.0\bin\mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.60-nt Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> load data local infile 'C:\\dbs\\load.csv' into table `test`.`loadtstvw`
-> fields escaped by '\\' terminated by ',' enclosed by '"' lines terminated by '\r\n' (`id`,
-> `var1`, `var2`);
ERROR 2013 (HY000): Lost connection to MySQL server during query
[21 Mar 2008 13:08]
Peter Laursen
another test case that does not involve SHOW statements:
drop table if exists t1;
CREATE TABLE `t1` (
`id` bigint(20) default NULL,
`t1` varchar(20) default NULL,
`t2` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
drop view if exists v1;
create view v1 as select * from t1;
load data local infile 'C:\\Users\\Peter\\Desktop\\t.csv' into table `tre2`.`v1` fields escaped by '\\' terminated by ',' enclosed by '"' lines terminated by '\r\n' (`t1`, `t2`);
-- imported
-- let's try again
load data local infile 'C:\\Users\\Peter\\Desktop\\t.csv' into table `tre2`.`v1` fields escaped by '\\' terminated by ',' enclosed by '"' lines terminated by '\r\n' (`t1`, `t2`);
-- Lost connection to MySQL server during query
[21 Mar 2008 16:10]
MySQL Verification Team
Thank you for the bug report. I was able to repeat the crash only on Windows Vista.
[21 Mar 2008 17:00]
Peter Laursen
Was Server 2008 tested? I would expect that it is the same as Vista!
[26 Mar 2008 19:12]
Omer Barnir
workaround: in some cases loading into the base table can address the issue
[26 Mar 2008 20:12]
Peter Laursen
Won't help me. Our customer has given access/privileges to a VIEW only!
[28 Mar 2008 15:57]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/44598 ChangeSet@1.2601, 2008-03-28 18:59:13+03:00, anozdrin@quad.opbmk +5 -0 Fix for Bug#35469: server crash with LOAD DATA INFILE to a VIEW. The problem was that LOAD DATA code (sql_load.cc) didn't take into account that there may be items, representing references to other columns. This is a usual case in views. The crash happened because Item_direct_view_ref was casted to Item_user_var_as_out_param, which is not a base class. The fix is to 1) Handle references properly; 2) Ensure that an item is treated as a user variable only when it is a user variable indeed; 3) Report an error if LOAD DATA is used to load data into non-updatable column.
[31 Mar 2008 14:52]
Bugs System
Pushed into 5.0.60
[31 Mar 2008 14:54]
Bugs System
Pushed into 5.1.24-rc
[31 Mar 2008 19:37]
Paul DuBois
Noted in 5.0.60, 5.1.24 changelogs. Using LOAD DATA INFILE with a view could crash the server. Resetting report to Patch queued waiting for push into 6.0.x.
[3 Apr 2008 13:02]
Bugs System
Pushed into 6.0.5-alpha
[7 Apr 2008 17:35]
Paul DuBois
Noted in 6.0.5 changelog.
Description: I have some problems with LOAD DATA INFILE to a VIEW. All sorts of errors occur (such as server claims the external file does not respect indexes of the underlying table - even if the table has NO indexes!). This is hard to debug because crashes occur randomly in the GUI client (SQLyog) with SHOW statements sent by this client to retrieve information to populate the GUI interface. This crash is always reproducable however. Note: This report is a report of a server crash with a crash on a SHOW statement after LOAD DATA. I will try to report other issues in another report! How to repeat: drop table if exists `loadtst`; CREATE TABLE `loadtst` ( `id` bigint(20) default NULL, `var1` varchar(20) default NULL, `var2` varchar(20) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; drop view if exists loadtstvw; create view loadtstvw as select * from loadtst; load data local infile 'C:\\Users\\Peter\\Desktop\\load.csv' into table `test`.`loadtstvw` fields escaped by '\\' terminated by ',' enclosed by '"' lines terminated by '\r\n' (`id`, `var1`, `var2`); show full fields from `test`.`loadtst`; -- the MySQL server has gone astray ... erhh .. away The extrenal file looks like: "1","a","b" "2","x","y" Suggested fix: I think there a several issues with LOAD DATA INFILE and Views .. Probably some 'insider' can spot this better that I, so do not wait!