Error 150 Mysql 5.1
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL: Can't create table (errno: 150) up vote 121 down vote favorite 18 I am trying to import a .sql file and its failing on creating tables. Here's the query that fails: CREATE TABLE `data` ( `id` int(10) unsigned NOT NULL, `name` varchar(100) NOT NULL, `value` varchar(15) NOT NULL, UNIQUE KEY `id` (`id`,`name`), CONSTRAINT `data_ibfk_1` FOREIGN KEY (`id`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; I exported the .sql from the the same database, I dropped all the tables and now im trying to import it, why is it failing? MySQL: Can't create table './dbname/data.frm' (errno: 150) sql mysql phpmyadmin mysql-error-1005 share|improve this question edited Oct 31 '10 at 0:37 OMG Ponies 198k36356415 asked Oct 31 '10 at 0:24 gtilx 75421018 1 For essentially all the causes of this error, here is an exhaustive resource for what causes errno 150 (and errno 121/other foreign key errors) in MySQL. –John Smith Sep 29 '12 at 0:47 15 I've found that the columns must be identical (even the unsigned flag must match). –Justin Skiles Dec 19 '12 at 19:54 3 @JohnSmith ... where? –Charles Wood Jul 29 '13 at 17:07 3 I suggest reading this blog post that lists 10 possible causes: verysimple.com/2006/10/22/
SQL Insertion Learn how web applications with access to your database (e.g. MySQL) may be used to poison, dump, or delete information in your database. MySQL Foreign Keys Learn all about MySQL foreign keys in this white paper. MySQL Foreign Key Errors and Errno: 150 Learn how to avoid MySQL foreign key errors, including the notorious Errno:150 in this white paper. MySQL/SQL Data Validation (with PHP) Learn the importance of data validation in web applications when information is accepted from third parties, or even from internal users. MySQL Foreign Key Errors: errno 150, errno 121, and others Diagnosing Errors SHOW ENGINE INNODB STATUS is Your New Best Friend: Click for solutionIf you get one of the really helpful errors (sarcasm) like http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150 the errno 150 or errno 121, then by simply typing in SHOW ENGINE INNODB STATUS, there is a section called "LATEST FOREIGN KEY ERROR". Under that it will give you a very helpful error message, which typically will tell you right away what is the matter. What's the catch?You need SUPER privileges to run it, so if you don't have that, you'll just have to test out the following scenarios. Use Eliacom's MySQL GUI tool to catch http://www.eliacom.com/mysql-gui-wp-errno-150.php most errors: Click for solutionYou can either download the MySQL GUI to install on your own server, or you can use our free online demo of the MySQL GUI to do it. You can check out our video on how to create foreign keys and indexes using Eliacom's MySQL GUI tool.. MySQL errno 150 ERROR 1005 (HY000): Can't create table 'table' (errno: 150) ERROR 1025 (HY000): Error on rename of 'table' to 'newtable' (errno: 150) Causes and Solutions for errno 150 Data Types Don't Match: Click for solutionThe types of the columns have to be the same (usually). This is one of the most common reasons for errno 150. For instance, if the type of the child column is VARCHAR(50), the type of the parent column should be exactly VARCHAR(50) (since they're supposed to hold the same data). For numeric types, if one is UNSIGNED, then both have to be UNSIGNED. They should match exactly!. I have run into circumstances where it has let me create a foreign key where the child column was a VARCHAR(50) and the parent column was a VARCHAR(200). Interestingly, if I tried to do the opposite for the same tables, reference a child column that was a VARCHAR(200) to a parent column that was a VARCHAR(50), it threw the errno 150 error. This all might depend on the version of MySQL you are usi
Connectors More MySQL.com Downloads Developer Zone Section Menu: Documentation Home MySQL 5.7 Reference Manual Preface and Legal Notices General Information Installing and Upgrading MySQL Using MySQL as a Document Store Tutorial MySQL Programs http://dev.mysql.com/doc/en/innodb-foreign-key-constraints.html MySQL Server Administration Security Backup and Recovery Optimization Language Structure Globalization Data Types http://dev.mysql.com/doc/en/error-messages-server.html Functions and Operators SQL Statement Syntax The InnoDB Storage Engine Introduction to InnoDB Benefits of Using InnoDB Tables Best Practices for InnoDB Tables Checking InnoDB Availability Testing and Benchmarking with InnoDB Turning Off InnoDB InnoDB and the ACID Model InnoDB Multi-Versioning InnoDB Architecture Buffer Pool Change Buffer Adaptive Hash Index Redo error 150 Log Buffer System Tablespace InnoDB Data Dictionary Doublewrite Buffer Undo Log File-Per-Table Tablespaces General Tablespaces Undo Tablespace Temporary Tablespace InnoDB Temporary Table Undo Logs Redo Log Group Commit for Redo Log Flushing InnoDB Locking and Transaction Model InnoDB Locking InnoDB Transaction Model Transaction Isolation Levels autocommit, Commit, and Rollback Consistent Nonlocking Reads Locking Reads Locks Set by Different SQL Statements in InnoDB Phantom Rows error 150 mysql Deadlocks in InnoDB An InnoDB Deadlock Example Deadlock Detection and Rollback How to Minimize and Handle Deadlocks InnoDB Configuration InnoDB Startup Configuration Configuring InnoDB for Read-Only Operation InnoDB Buffer Pool Configuration The InnoDB Buffer Pool Configuring InnoDB Buffer Pool Size Configuring Multiple Buffer Pool Instances Making the Buffer Pool Scan Resistant Configuring InnoDB Buffer Pool Prefetching (Read-Ahead) Configuring InnoDB Buffer Pool Flushing Fine-tuning InnoDB Buffer Pool Flushing Saving and Restoring the Buffer Pool State Monitoring the Buffer Pool Using the InnoDB Standard Monitor Configuring the Memory Allocator for InnoDB Configuring InnoDB Change Buffering Configuring the Change Buffer Maximum Size Configuring Thread Concurrency for InnoDB Configuring the Number of Background InnoDB I/O Threads Configuring the InnoDB Master Thread I/O Rate Configuring Spin Lock Polling Configuring InnoDB Purge Scheduling Configuring Optimizer Statistics for InnoDB Configuring Persistent Optimizer Statistics Parameters Configuring Non-Persistent Optimizer Statistics Parameters Estimating ANALYZE TABLE Complexity for InnoDB Tables Configuring the Merge Threshold for Index Pages InnoDB Tablespaces Resizing the InnoDB System Tablespace Changing the Number or Size of InnoDB Redo Log Files Using Raw Disk Partitions for the System Tablespace InnoDB File-Per-Table Tablespaces Enabling and Disabling File-Per-Table Tablespaces Creatin
Connectors More MySQL.com Downloads Developer Zone Section Menu: Documentation Home MySQL 5.7 Reference Manual Preface and Legal Notices General Information Installing and Upgrading MySQL Using MySQL as a Document Store Tutorial MySQL Programs MySQL Server Administration Security Backup and Recovery Optimization Language Structure Globalization Data Types Functions and Operators SQL Statement Syntax The InnoDB Storage Engine Alternative Storage Engines High Availability and Scalability Replication MySQL Cluster NDB 7.5 Partitioning Stored Programs and Views INFORMATION_SCHEMA Tables MySQL Performance Schema MySQL sys Schema Connectors and APIs Extending MySQL MySQL Enterprise Edition MySQL Workbench MySQL 5.7 Frequently Asked Questions Errors, Error Codes, and Common Problems Sources of Error Information Types of Error Values Server Error Codes and Messages Client Error Codes and Messages Problems and Common Errors How to Determine What Is Causing a Problem Common Errors When Using MySQL Programs Access denied Can't connect to [local] MySQL server Lost connection to MySQL server Client does not support authentication protocol Password Fails When Entered Interactively Host 'host_name' is blocked Too many connections Out of memory MySQL server has gone away Packet Too Large Communication Errors and Aborted Connections The table is full Can't create/write to file Commands out of sync Ignoring user Table 'tbl_name' doesn't exist Can't initialize character set File Not Found and Similar Errors Table-Corruption Issues Administration-Related Issues Problems with File Permissions How to Reset the Root Password What to Do If MySQL Keeps Crashing How MySQL Handles a Full Disk Where MySQL Stores Temporary Files How to Protect or Change the MySQL Unix Socket File Time Zone Problems Query-Related Issues Case Sensitivity in String Searches Problems Using DATE Columns Problems with NULL Values Problems with Column Aliases Rollback Failure for Nontransactional Tables Deleting Rows from Related Tables Solving Problems with No Matching Rows Problems with Floating-Point Values Optimizer-Related Issues Table Definition-Related Issues Problems with ALTER TABLE TEMPORARY Table Problems Known Issues in MySQL Restrictions and Limits Indexes MySQL Glossary Related Documentation MySQL 5.7 Release Notes Download this Manual PDF (US Ltr) - 35.6Mb