summaryrefslogtreecommitdiff
path: root/mysql-test/t/loadxml.test
blob: 0bd97a81649e17587cd0c6bc8bf6e9d57a70559b (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
#
# 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;