MySQL Bugs: #67439: warning 1263 reported when load xml

Bug #67439	warning 1263 reported when load xml
Submitted:	31 Oct 2012 14:35	Modified:	21 Nov 2012 13:30
Reporter:	David Ni	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: XML functions	Severity:	S1 (Critical)
Version:	5.5.28	OS:	Windows (XP SP3)
Assigned to:		CPU Architecture:	Any
Tags:	warning1263, XML

Description:
mysql -X -h localhost -u root -p portaldb -e "select * from cond_documententry;" >test.xml
so I used above command to export the cond_documententry table;but when I tried to import it back to cond_documententry table,
mysql -h localhost -u root -p portaldb --show-warnings -e "load xml local infile 'test.xml' into table cond_documententry";

it failed and returned lots of "warning 1263" message,the data failed to insert,I am sure there is no NULL value in the exported XML file,how could this happened?I am sure also the column name are case-sensitive matched.

How to repeat:
see the description.

the exported xml file

Attachment: test.xml (text/xml), 8.93 KiB.

mysql> load xml local infile 'test.xml' into table cond_documententry;
Query OK, 0 rows affected, 45 warnings (0.00 sec)
Records: 5  Deleted: 0  Skipped: 5  Warnings: 45

mysql> show warnings;
+---------+------+--------------------------------------------------------------
---------------------------------+
| Level   | Code | Message
                                 |
+---------+------+--------------------------------------------------------------
---------------------------------+
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentEntryId' at row 1      |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentName' at row 1         |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentDesc' at row 1         |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentTypeCode' at row 1     |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentStatusCode' at row 1   |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'internalId' at row 1           |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'internalPath' at row 1         |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'createdOn' at row 1            |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'plannedObservationId' at row 1 |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentEntryId' at row 2      |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentName' at row 2         |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentDesc' at row 2         |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentTypeCode' at row 2     |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentStatusCode' at row 2   |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'internalId' at row 2           |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'internalPath' at row 2         |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'createdOn' at row 2            |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'plannedObservationId' at row 2 |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentEntryId' at row 3      |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentName' at row 3         |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentDesc' at row 3         |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentTypeCode' at row 3     |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentStatusCode' at row 3   |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'internalId' at row 3           |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'internalPath' at row 3         |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'createdOn' at row 3            |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'plannedObservationId' at row 3 |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentEntryId' at row 4      |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentName' at row 4         |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentDesc' at row 4         |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentTypeCode' at row 4     |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentStatusCode' at row 4   |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'internalId' at row 4           |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'internalPath' at row 4         |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'createdOn' at row 4            |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'plannedObservationId' at row 4 |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentEntryId' at row 5      |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentName' at row 5         |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentDesc' at row 5         |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentTypeCode' at row 5     |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentStatusCode' at row 5   |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'internalId' at row 5           |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'internalPath' at row 5         |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'createdOn' at row 5            |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'plannedObservationId' at row 5 |
+---------+------+--------------------------------------------------------------
---------------------------------+

also before importing,I truncated the table.

I would like to rise it to severity 1.need a solution immediately.

Thank you for the report.

This is duplicate of bug #67542 which I noticed, thus verified earlier. Please subscribe to that bug, so you will receive updates about the fix.

no,this is not the same with bug #67542.
you can notice that in the warning message,for example:
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
 'documentEntryId' at row 1      |
actually, documentEntryId has the values on every row in the xml file,but it returns
NULL supplied to NOT NULL column 'documentEntryId',we can't import the xml file,but not just the wrong value

Thank you for the feedback.

Bug I still think this is duplicate of bug #67542: MySQL does not correctly handle empty elements in XML. If you in doubt you can always check your test when bug #67542 is fixed and re-open the report if yours is not fixed. I will add a note for myself to make such a check too.