Description:
I reported the same issue before with MySQL 5.0.45 community, and it seems as if it has not only snuck into the 5.1.22 RC, but it is worse, because my work around that worked in 5.0.45 does NOT work in 5.1.22
LOCK TABLES `asset_ratings` WRITE;
/*!40000 ALTER TABLE `asset_ratings` DISABLE KEYS */;
INSERT INTO `asset_ratings` VALUES (0,'Unknown','Unknown',0,0,0),(1,'General','General (Everyone)',0,1,1),(2,'Teen','Teen (13 and up)',13,2,1),(3,'Mature','Mature (18 and up)',18,3,1),(4,'General','General (Everyone)',0,1,2),(5,'Teen','Teen (13 and up)',13,2,2),(6,'Mature','Mature (18 and up)',18,3,2),(7,'General','General (Everyone)',0,1,3),(8,'Teen','Teen (13 and up)',13,2,3),(9,'Mature','Mature (18 and up)',18,3,3);
/*!40000 ALTER TABLE `asset_ratings` ENABLE KEYS */;
UNLOCK TABLES;
This is the SQL that produces the error.
/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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 */;
The above is the header for my dump file. My old work around was I simply did a set global SQL_MODE='NO_AUTO_VALUE_ON_ZERO' before the restore; and it would restore the database. This is no longer the case. I still get the error.
This is an inconsistent error. It does not happen with a production database snapshot we have, but it does happen with both our dev and staging databases. They are more or less the same schema.
How to repeat:
See above
Suggested fix:
Implement the fix found in 5.0.45 post release to this version for final release. We would love to use 5.1 in production.
