diff options
Diffstat (limited to 'mysql-test/main/loadxml.test')
| -rw-r--r-- | mysql-test/main/loadxml.test | 164 |
1 files changed, 164 insertions, 0 deletions
diff --git a/mysql-test/main/loadxml.test b/mysql-test/main/loadxml.test new file mode 100644 index 00000000000..623deea6ec6 --- /dev/null +++ b/mysql-test/main/loadxml.test @@ -0,0 +1,164 @@ +# +# Tests for "LOAD XML" - a contributed patch from Erik Wetterberg. +# + +# Running the $MYSQL_DUMP tool against an embedded server does not work. +--source include/not_embedded.inc + +--disable_warnings +drop table if exists t1, t2; +--enable_warnings + +create table t1 (a int, b varchar(64)); + + +--echo -- Load a static XML file +load xml infile '../../std_data/loadxml.dat' into table t1 +rows identified by '<row>'; +select * from t1 order by a; +delete from t1; + + +--echo -- Load a static XML file with 'IGNORE num ROWS' +load xml infile '../../std_data/loadxml.dat' into table t1 +rows identified by '<row>' ignore 4 rows; +select * from t1 order by a; + + +--echo -- Check 'mysqldump --xml' + 'LOAD XML' round trip +--exec $MYSQL_DUMP --xml test t1 > "$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml" 2>&1 +delete from t1; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval load xml infile '$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml' into table t1 rows identified by '<row>'; +select * from t1 order by a; + +--echo --Check that default row tag is '<row> +delete from t1; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval load xml infile '$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml' into table t1; +select * from t1 order by a; + +--echo -- Check that 'xml' is not a keyword +select 1 as xml; + + +# +# Bug #42520 killing load .. infile Assertion failed: ! is_set(), file .\sql_error.cc, line 8 +# + +--disable_query_log +delete from t1; +insert into t1 values (1, '12345678900987654321'), (2, 'asdfghjkl;asdfghjkl;'); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +--exec $MYSQL_DUMP --xml test t1 > "$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml" 2>&1 +--enable_query_log + +connect (addconroot, localhost, root,,); +connection addconroot; +create table t2(fl text); +--let $PSEUDO_THREAD_ID=`select @@pseudo_thread_id ` + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--send_eval LOAD XML LOCAL INFILE "$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml" INTO TABLE t2 ROWS IDENTIFIED BY '<person>'; + +sleep 3; + + +connection default; + +--disable_query_log +--eval kill $PSEUDO_THREAD_ID +connection addconroot; +# Read response from connection to avoid packets out-of-order when disconnecting +# Note, that connection can already be dead due to previously issued kill +--error 0,2013 +--reap +disconnect addconroot; +connection default; +--enable_query_log +# +# Clean up +# +remove_file $MYSQLTEST_VARDIR/tmp/loadxml-dump.xml; +drop table t1; +drop table t2; + +# +# Bug #36750 LOAD XML doesn't understand new line (feed) characters in multi line text fields +# + +create table t1 ( + id int(11) not null, + text text, + primary key (id) +) engine=MyISAM default charset=latin1; +load xml infile '../../std_data/loadxml2.dat' into table t1; +select * from t1; +drop table t1; + +--echo # +--echo # Bug#51571 load xml infile causes server crash +--echo # +CREATE TABLE t1 (a text, b text); +LOAD XML INFILE '../../std_data/loadxml.dat' INTO TABLE t1 +ROWS IDENTIFIED BY '<row>' (a,@b) SET b=concat('!',@b); +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; + + +--echo # +--echo # Bug#16171518 LOAD XML DOES NOT HANDLE EMPTY ELEMENTS +--echo # +CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 VARCHAR(3), col4 VARCHAR(4)); +LOAD XML INFILE '../../std_data/bug16171518_1.dat' INTO TABLE t1; +SELECT * FROM t1 ORDER BY col1, col2, col3, col4; +DROP TABLE t1; + +CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 INTEGER); +LOAD XML INFILE '../../std_data/bug16171518_2.dat' INTO TABLE t1; +SELECT * FROM t1 ORDER BY col1, col2, col3; +DROP TABLE t1; + +--echo # +--echo # MDEV-12696 Crash with LOAD XML and non-updatable VIEW column +--echo # + +CREATE TABLE t1 (c1 TEXT); +CREATE VIEW v1 AS SELECT CONCAT(c1,'') AS c1, NULL AS c2 FROM t1; +--error ER_NONUPDATEABLE_COLUMN +LOAD XML INFILE '../../std_data/loaddata/mdev12696.xml' INTO TABLE v1 (c1); +--error ER_NONUPDATEABLE_COLUMN +LOAD XML INFILE '../../std_data/loaddata/mdev12696.xml' INTO TABLE v1 (c2); +DROP VIEW v1; +DROP TABLE t1; + +--echo # +--echo # MDEV-14628 Wrong autoinc value assigned by LOAD XML in the NO_AUTO_VALUE_ON_ZERO mode +--echo # + +SET sql_mode=NO_AUTO_VALUE_ON_ZERO; +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT); +LOAD XML INFILE '../../std_data/loaddata/mdev14628a.xml' INTO TABLE t1 ROWS IDENTIFIED BY '<row>'; +SELECT * FROM t1 ORDER BY b; +DROP TABLE t1; +SET sql_mode=DEFAULT; + +SET sql_mode=''; +CREATE TABLE t1 (id INT, g GEOMETRY NOT NULL); +--error ER_WARN_NULL_TO_NOTNULL +LOAD XML INFILE '../../std_data/loaddata/mdev14628b.xml' INTO TABLE t1 ROWS IDENTIFIED BY '<row>'; +SELECT * FROM t1; +DROP TABLE t1; +SET sql_mode=DEFAULT; |