MySQL Bugs: #30801: Warnings on data import do not help to locate faulty data

Bug #30801	Warnings on data import do not help to locate faulty data
Submitted:	4 Sep 2007 16:18	Modified:	12 Oct 2007 17:22
Reporter:	Kristian Koehntopp	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S4 (Feature request)
Version:	5.0.46	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I am importing a mysqldump made with mysql-4.1 into a 5.0.46 installation. Because 5.0 is stricter in checking data, I am getting data conversion warnings. The warnings are incomplete and do nothing to help me locate the faulty data.

Example:

Warning (Code 1366): Incorrect string value: '\xC2\x9Fe 30' for column 'street' at row 6319

a. the table name is missing
b. in the dump, row 6319 is hard to find
c. the string value is shortened.

Example:

Warning (Code 1264): Out of range value adjusted for column 'max_time' at row 5

a. the table name is missing
b. in the dump, row 5 is hard to find
c. the value is not given at all.

How to repeat:
Create a dump that contains data that cannot be read into mysql-5.
Try to find the problem in your dump from the error messages alone.

Suggested fix:
a. always include the table name
b. if the table has a PK, make the PK part of the error message. This will make it very easy to find the row in the source database or the dump.
c. do not shorten or omit faulty data in the error message.

Could you please provide a small dump file which allow to repeat the
behavior reported?. Thanks in advance.

nvag-blade0107:~ # mysqldump -u root -p -w"PK=69153" bogner_shop CATALOG_ARTICLE
-- MySQL dump 10.9
--
-- Host: localhost    Database: bogner_shop
-- ------------------------------------------------------
-- Server version       4.1.14-Max-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `CATALOG_ARTICLE`
--

DROP TABLE IF EXISTS `CATALOG_ARTICLE`;
CREATE TABLE `CATALOG_ARTICLE` (
  `PK` bigint(20) NOT NULL default '0',
  `ARTICLE_ID` bigint(20) NOT NULL default '0',
  `LANGUAGE_PK` bigint(20) NOT NULL default '0',
  `CLIENT_PK` bigint(20) NOT NULL default '0',
  `INH_CONTROL` int(11) NOT NULL default '0',
  `NAME` varchar(250) collate latin1_general_ci default NULL,
  `ACTIVE_START` datetime NOT NULL default '0000-00-00 00:00:00',
  `ACTIVE_END` datetime NOT NULL default '0000-00-00 00:00:00',
  `ART_NUMBER` varchar(250) collate latin1_general_ci default NULL,
  `AD_CODE` varchar(250) collate latin1_general_ci default NULL,
  `ART_SIZE` varchar(250) collate latin1_general_ci default NULL,
  `SIZE_TEXT` varchar(250) collate latin1_general_ci default NULL,
  `COLOUR` varchar(250) collate latin1_general_ci default NULL,
  `COLOUR_TEXT` varchar(250) collate latin1_general_ci default NULL,
  `VARIATION` varchar(250) collate latin1_general_ci default NULL,
  `GRAVURE_HEADLINE` varchar(255) collate latin1_general_ci default NULL,
  `GRAVURE_LENGTH` int(11) default NULL,
  `OLD_PRICE` float(9,2) default NULL,
  `CURRENT_PRICE` float(9,2) default NULL,
  `NEW_PRICE` float(9,2) default NULL,
  `NEW_PRICE_DATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `VAT_CODE` int(11) NOT NULL default '0',
  `DELIVERY_DATE` datetime NOT NULL default '0000-00-00 00:00:00',
  `DELIVERY_TYPE_CODE` int(11) NOT NULL default '0',
  `STOCK_AMOUNT` float default NULL,
  `STOCK_MIN_AMOUNT` float default NULL,
  `STOCK_UNIT` varchar(250) collate latin1_general_ci default NULL,
  `STOCK_TYPE_CODE` int(11) NOT NULL default '0',
  `STOCK_EXPECTED_RECEIVING` datetime NOT NULL default '0000-00-00 00:00:00',
  `WEIGHT` float default NULL,
  `LENGTH` float default NULL,
  `HEIGHT` float default NULL,
  `WIDTH` float default NULL,
  `UPDATED` tinyint(4) default NULL,
  `ART_MANUFACTURER_NUMBER` varchar(50) collate latin1_general_ci default NULL,
  `ADDITIONAL_PRICE` float(9,2) default NULL,
  `STATUS` int(11) default NULL,
  PRIMARY KEY  (`PK`),
  KEY `ind_CLIENT_PK` (`CLIENT_PK`),
  KEY `ind_MANUFACTURER_NUMBER` (`ART_MANUFACTURER_NUMBER`),
  KEY `ART_NUMBER_SIZE` (`ART_NUMBER`,`ART_SIZE`),
  KEY `ind_LANGUAGE_PK` (`LANGUAGE_PK`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Dumping data for table `CATALOG_ARTICLE`
--
-- WHERE:  PK=69153

/*!40000 ALTER TABLE `CATALOG_ARTICLE` DISABLE KEYS */;
LOCK TABLES `CATALOG_ARTICLE` WRITE;
INSERT INTO `CATALOG_ARTICLE` VALUES (69153,6476,2,9,0,'Bogner Fire + Ice WomenÂs Watch Impuls','0000-00-00 00:00:00','0000-00-00 00:00:00','64752','01','0','','RosÃ©/Multicolor','Rose/Multicolor','','',0,0.00,85.00,0.00,'0000-00-00 00:00:00',0,'0000-00-00 00:00:00',0,0,0,'',0,'0000-00-00 00:00:00',0,0,0,0,0,'',0.00,0);
UNLOCK TABLES;
/*!40000 ALTER TABLE `CATALOG_ARTICLE` ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

The field name is

mysql> select hex(NAME) from CATALOG_ARTICLE where PK=69153
    -> ;
+------------------------------------------------------------------------------+
| hex(NAME)                                                                    |
+------------------------------------------------------------------------------+
| 426F676E65722046697265202B2049636520576F6D656E927320576174636820496D70756C73 |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Create the data in NAME using an UNHEX() of that number.

Reading that dump file with "warnings" enabled should produce

Query OK, 1 row affected, 1 warning (0.00 sec)

Warning (Code 1366): Incorrect string value: '\xC2\x92s Wa...' for column 'NAME' at row 1
Query OK, 0 rows affected (0.00 sec)

I'd like to see the PK mentioned here in order to make it easier to locate the faults data item.

The issue itself, latin1 0x99 dumped incorrectly by mysql-4.1.14 as 0xc299 has been resolved and worked around in bug 30808 (closed). This issue here is only about making is easier to locate the rows.

Thank you for the feedback.

Thank you for a valid feature request.