Fix ERROR 1050 (42S01) “Table … already exists” in MySQL
If you’re getting an error that reads something like “ERROR 1050 (42S01): Table ‘customers’ already exists” when trying to create a table in MySQL, it’s probably because there’s already a table in the database with the same name.
To fix this issue, either change the name of the table you’re trying to create, or check the existing table to see if it’s the one you actually need.
Example of Error
Here’s an example of code that produces the error:
In this case, I’m trying to create a table called Customers , but it already exists in the database.
Solution 1
The most obvious solution is to change the name of the table we’re creating:
Here, I simply renamed the table to Customers2 . In practice, we would probably give it a more appropriate name.
We should also be mindful that if there’s already a table with the same name as the one we’re trying to create, there’s a possibility that our desired table has already been created. In this case we wouldn’t need to recreate it (unless we had good reason). We could either just go ahead and use it, or we could alter it to suit our new requirements.
Solution 2
Another way to deal with this error is to suppress it. We can modify our CREATE TABLE statement to only create the table if it doesn’t already exist:
In this case, we got a warning. Let’s check the warning:
The warning explicitly tells us that the table already exists.
The Table REALLY Doesn’t Exist?
If you believe that the table really doesn’t exist, perhaps there’s something else going on. See this article on Stack Overflow for a discussion on possible solutions.
Mysql 1050 Error "Table already exists" when in fact, it does not
Seriously now, you probably have a broken table. Try:
- DROP TABLE IF EXISTS contenttype
- REPAIR TABLE contenttype
- If you have sufficient permissions, delete the data files (in /mysql/data/db_name)
You may need to flush the table cache. For example:
I got this same error, and REPAIR TABLE (from @NullUserException’s answer) didn’t help.
I eventually found this solution:
For me, without the sudo , I got the following error:
(Running on OS X 10.6)
Same problem occurred with me while creating a view. The view was present earlier then due to some changes it got removed But when I tried to add it again it was showing me «view already exists» error message.
Solution:
You can do one thing manually.
- Go to the MySQL folder where you have installed it
- Go to the data folder inside it.
- Choose your database and go inside it.
- Data base creates «.frm» format files.
- delete the particular table’s file.
- Now create the table again.
It will create the table successfully.
I had this problem on Win7 in Sql Maestro for MySql 12.3. Enormously irritating, a show stopper in fact. Nothing helped, not even dropping and recreating the database. I have this same setup on XP and it works there, so after reading your answers about permissions I realized that it must be Win7 permissions related. So I ran MySql as administrator and even though Sql Maestro was run normally, the error disappeared. So it must have been a permissions issue between Win7 and MySql.
I’ve been fighting with this all day: I have a Perl script that builds a set of tables by first doing a DROP IF EXISTS . on them and then CREATE ing them. The DROP succeeded, but on CREATE I got this error message: table already exists
I finally got to the bottom of it: The new version of MySQL that I’m using has a default engine of InnoDB («show engine \G;») I changed it in the my.cnf file to default to MyISAM, re-started MySQL, and now I no longer get the «table already exists» error.
I am struggling with the same issue. I cannot create a table, even though it does not exist. I tried all the above solutions with no success.
My solution was to delete the files ib_logfil0 , ib_logfile1 , ibdata1 , and auto.cnf from the data folder of MySQL; make sure to stop the MySQL service first before deleting these files.
Then after restarting the service, MySQL recreated these files and I was able to run a backup script were all my CREATE s were stored (a sqldump file).
Encountering the same problem (create InnoDB table) this is what finally worked for me:
I checked on a file basis, permissions, tried to REPAIR and FLUSH but nothing worked.
So if this is an option, move all working tables to another DATABASE, drop the old one (you might have to manually remove any files from the database folder before the drop to work), rename the new one, and you ‘should’ be back on your way. Apparently, whatever gets ‘cached’ using InnoDB is dropped along with the original database.
You won´t believe me! I´ve just removed a comment block from my .sql file and now it works.
The deleted comment block was this:
I´ve left the problematic table alone in the same .sql file. After that I´ve removed comments, the only code was left, and the error disappears.
I also encountered this problem where trying to Create a table said it already exists and Dropping the table said it didn’t exist.
I did «FLUSH TABLES» and it cleared the problem.
First check if you are in the right database USE yourDB and try Select * from contenttype just to see what is it and if it exists really.
I had the same problem at Mac OS X and MySQL 5.1.40. I used eclipse to edit my SQL script and than I tried MySQLWorkbench 5.2.28. Probably it converted newline characters to Mac format. I had no idea about what’s wrong with my script until I commented out the first line in file. After this this script was interpreted by mysql as a one single comment. I used build-in TextEdit Mac application to fix this. After line-breaks was converted to the correct format, the error 1050 gone.
Update for Eclipse users:
To set up default ending for new files created, across the entire workspace:
Window -> Preferences -> General -> Workspace -> New text file line delimiter.
To convert existing files, open file for editing and for the currently edited file, go to the menu:
File -> Convert Line Delimiters To
I had this same case. The problem ended up being permissions on the parent directory.
I had been copying files in and out of mysql during testing.
was not enough, needed to be:
Sorry to resurrect.
I was having huge issues with Error 1050 and 150.
The problem, for me was that I was trying to add a constraint with ON DELETE SET NULL as one of the conditions.
Changing to ON DELETE NO ACTION allowed me to add the required FK constraints.
Unfortunately the MySql error messages are utterly unhelpful so I had to find this solution iteratively and with the help of the answers to the question above.
Your disk also might just be full. (just had that)
I had this same problem and it looks like the Database name was case sensitive. My Database is called:
Whilst my script included
Once I changed the database name to the correct case it all seemed to work. Using MYSQL Workbench on MAC OSX
This problem also occurs if a ‘view’ (imaginary table) exists in database as same name as our new table name.
In my case I found this to be an issue with InnoDB; I never discovered what the actual problem was, but creating as a MyISAM allowed it to build
For me the problem was caused when using a filesystem copy of the mysql database directory instead of mysqldump. I have some very large tables, mostly MyISAM and a few InnoDB cache tables and it is not practical to mysqldump the data. Since we are still running MyISAM, XtraBackup is not an option.
The same symptoms as above happened to me. The table is not there, there are no files in the directory that pertain to the table, yet it cannot be created because MySQL thinks its there. Drop table says it’s not there, create table says it is.
The problem occurred on two machines, both were fixed by copying backups. However, I noticed that in my backup that there was a .MYD and .MYI file, even though I was under the impression that these files are not used for InnoDB. The .MYD and .MYI files had an owner of root, while the .frm was owned by mysql.
If you copy from backup, check the file permissions. Flush tables might work, but I opted to shut down and restart the database.
Table already exists как решить
Fix ERROR 1050 (42S01) “Table … already exists” in MySQL
If you’re getting an error that reads something like “ERROR 1050 (42S01): Table ‘customers’ already exists” when trying to create a table in MySQL, it’s probably because there’s already a table in the database with the same name.
To fix this issue, either change the name of the table you’re trying to create, or check the existing table to see if it’s the one you actually need.
Example of Error
Here’s an example of code that produces the error:
In this case, I’m trying to create a table called Customers , but it already exists in the database.
Solution 1
The most obvious solution is to change the name of the table we’re creating:
Here, I simply renamed the table to Customers2 . In practice, we would probably give it a more appropriate name.
We should also be mindful that if there’s already a table with the same name as the one we’re trying to create, there’s a possibility that our desired table has already been created. In this case we wouldn’t need to recreate it (unless we had good reason). We could either just go ahead and use it, or we could alter it to suit our new requirements.
Solution 2
Another way to deal with this error is to suppress it. We can modify our CREATE TABLE statement to only create the table if it doesn’t already exist:
In this case, we got a warning. Let’s check the warning:
The warning explicitly tells us that the table already exists.
The Table REALLY Doesn’t Exist?
If you believe that the table really doesn’t exist, perhaps there’s something else going on. See this article on Stack Overflow for a discussion on possible solutions.
Table already exists error when trying to import sql file
I am trying to upload a backup sql file through phpMyAdmin.
I create the empty db with the same db name as in my import file in phpMyAdmin then use the import function selected from within this empty db.
I get the following error message.
Inside the import file each CREATE TABLE statement is suffixed by IF NOT EXISTS, so why is this being reported as an error?
I have tried to take out the table reported from the sql file, but then the next table just throws up the error, and the next etc until we have a sql import with no tables left.
I am using Windows XP with MySQL 5.5.37-win32, these versions could be different from the versions used when creating the backup, would this matter?
Thanks in advance.
Edit : Added SQL code.
This is just a sample, the code is created by phpMyAdmin’s export function
Much more sql data but seems pointless to post as it is all formatted the same.
Guide for Mysql2 «Table already exists» error fix
The following guide will teach you how to solve the database Mysql2 table already exists error. It is a serious problem as Redmine crashes in general and could be related to any of our plugins.
If the problem appears at your Redmine, then the error logs (in the production.log file) should look like this:
Note: The name_of_the_table depends on which plugin the error is appearing on.
The error itself means that the migration was broken some time ago. Please follow these steps to fix it:
Mysql 1050 Error "Table already exists" when in fact, it does not
Seriously now, you probably have a broken table. Try:
- DROP TABLE IF EXISTS contenttype
- REPAIR TABLE contenttype
- If you have sufficient permissions, delete the data files (in /mysql/data/db_name)
You may need to flush the table cache. For example:
I got this same error, and REPAIR TABLE (from @NullUserException’s answer) didn’t help.
I eventually found this solution:
For me, without the sudo , I got the following error:
(Running on OS X 10.6)
Same problem occurred with me while creating a view. The view was present earlier then due to some changes it got removed But when I tried to add it again it was showing me «view already exists» error message.
Solution:
You can do one thing manually.
- Go to the MySQL folder where you have installed it
- Go to the data folder inside it.
- Choose your database and go inside it.
- Data base creates «.frm» format files.
- delete the particular table’s file.
- Now create the table again.
It will create the table successfully.
I had this problem on Win7 in Sql Maestro for MySql 12.3. Enormously irritating, a show stopper in fact. Nothing helped, not even dropping and recreating the database. I have this same setup on XP and it works there, so after reading your answers about permissions I realized that it must be Win7 permissions related. So I ran MySql as administrator and even though Sql Maestro was run normally, the error disappeared. So it must have been a permissions issue between Win7 and MySql.
I’ve been fighting with this all day: I have a Perl script that builds a set of tables by first doing a DROP IF EXISTS . on them and then CREATE ing them. The DROP succeeded, but on CREATE I got this error message: table already exists
I finally got to the bottom of it: The new version of MySQL that I’m using has a default engine of InnoDB («show engine \G;») I changed it in the my.cnf file to default to MyISAM, re-started MySQL, and now I no longer get the «table already exists» error.
I am struggling with the same issue. I cannot create a table, even though it does not exist. I tried all the above solutions with no success.
My solution was to delete the files ib_logfil0 , ib_logfile1 , ibdata1 , and auto.cnf from the data folder of MySQL; make sure to stop the MySQL service first before deleting these files.
Then after restarting the service, MySQL recreated these files and I was able to run a backup script were all my CREATE s were stored (a sqldump file).
Encountering the same problem (create InnoDB table) this is what finally worked for me:
I checked on a file basis, permissions, tried to REPAIR and FLUSH but nothing worked.
So if this is an option, move all working tables to another DATABASE, drop the old one (you might have to manually remove any files from the database folder before the drop to work), rename the new one, and you ‘should’ be back on your way. Apparently, whatever gets ‘cached’ using InnoDB is dropped along with the original database.
You won´t believe me! I´ve just removed a comment block from my .sql file and now it works.
The deleted comment block was this:
I´ve left the problematic table alone in the same .sql file. After that I´ve removed comments, the only code was left, and the error disappears.
I also encountered this problem where trying to Create a table said it already exists and Dropping the table said it didn’t exist.
I did «FLUSH TABLES» and it cleared the problem.
First check if you are in the right database USE yourDB and try Select * from contenttype just to see what is it and if it exists really.
I had the same problem at Mac OS X and MySQL 5.1.40. I used eclipse to edit my SQL script and than I tried MySQLWorkbench 5.2.28. Probably it converted newline characters to Mac format. I had no idea about what’s wrong with my script until I commented out the first line in file. After this this script was interpreted by mysql as a one single comment. I used build-in TextEdit Mac application to fix this. After line-breaks was converted to the correct format, the error 1050 gone.
Update for Eclipse users:
To set up default ending for new files created, across the entire workspace:
Window -> Preferences -> General -> Workspace -> New text file line delimiter.
To convert existing files, open file for editing and for the currently edited file, go to the menu:
File -> Convert Line Delimiters To
I had this same case. The problem ended up being permissions on the parent directory.
I had been copying files in and out of mysql during testing.
was not enough, needed to be:
Sorry to resurrect.
I was having huge issues with Error 1050 and 150.
The problem, for me was that I was trying to add a constraint with ON DELETE SET NULL as one of the conditions.
Changing to ON DELETE NO ACTION allowed me to add the required FK constraints.
Unfortunately the MySql error messages are utterly unhelpful so I had to find this solution iteratively and with the help of the answers to the question above.
Your disk also might just be full. (just had that)
I had this same problem and it looks like the Database name was case sensitive. My Database is called:
Whilst my script included
Once I changed the database name to the correct case it all seemed to work. Using MYSQL Workbench on MAC OSX
This problem also occurs if a ‘view’ (imaginary table) exists in database as same name as our new table name.
In my case I found this to be an issue with InnoDB; I never discovered what the actual problem was, but creating as a MyISAM allowed it to build
For me the problem was caused when using a filesystem copy of the mysql database directory instead of mysqldump. I have some very large tables, mostly MyISAM and a few InnoDB cache tables and it is not practical to mysqldump the data. Since we are still running MyISAM, XtraBackup is not an option.
The same symptoms as above happened to me. The table is not there, there are no files in the directory that pertain to the table, yet it cannot be created because MySQL thinks its there. Drop table says it’s not there, create table says it is.
The problem occurred on two machines, both were fixed by copying backups. However, I noticed that in my backup that there was a .MYD and .MYI file, even though I was under the impression that these files are not used for InnoDB. The .MYD and .MYI files had an owner of root, while the .frm was owned by mysql.
If you copy from backup, check the file permissions. Flush tables might work, but I opted to shut down and restart the database.
Table already exists error when trying to import sql file
I am trying to upload a backup sql file through phpMyAdmin.
I create the empty db with the same db name as in my import file in phpMyAdmin then use the import function selected from within this empty db.
I get the following error message.
Inside the import file each CREATE TABLE statement is suffixed by IF NOT EXISTS, so why is this being reported as an error?
I have tried to take out the table reported from the sql file, but then the next table just throws up the error, and the next etc until we have a sql import with no tables left.
I am using Windows XP with MySQL 5.5.37-win32, these versions could be different from the versions used when creating the backup, would this matter?
Thanks in advance.
Edit : Added SQL code.
This is just a sample, the code is created by phpMyAdmin’s export function
Much more sql data but seems pointless to post as it is all formatted the same.
Fix ERROR 1050 (42S01) “Table … already exists” in MySQL
If you’re getting an error that reads something like “ERROR 1050 (42S01): Table ‘customers’ already exists” when trying to create a table in MySQL, it’s probably because there’s already a table in the database with the same name.
To fix this issue, either change the name of the table you’re trying to create, or check the existing table to see if it’s the one you actually need.
Example of Error
Here’s an example of code that produces the error:
In this case, I’m trying to create a table called Customers , but it already exists in the database.
Solution 1
The most obvious solution is to change the name of the table we’re creating:
Here, I simply renamed the table to Customers2 . In practice, we would probably give it a more appropriate name.
We should also be mindful that if there’s already a table with the same name as the one we’re trying to create, there’s a possibility that our desired table has already been created. In this case we wouldn’t need to recreate it (unless we had good reason). We could either just go ahead and use it, or we could alter it to suit our new requirements.
Solution 2
Another way to deal with this error is to suppress it. We can modify our CREATE TABLE statement to only create the table if it doesn’t already exist:
In this case, we got a warning. Let’s check the warning:
The warning explicitly tells us that the table already exists.
The Table REALLY Doesn’t Exist?
If you believe that the table really doesn’t exist, perhaps there’s something else going on. See this article on Stack Overflow for a discussion on possible solutions.
Table already exists как решить
Fix ERROR 1050 (42S01) “Table … already exists” in MySQL
If you’re getting an error that reads something like “ERROR 1050 (42S01): Table ‘customers’ already exists” when trying to create a table in MySQL, it’s probably because there’s already a table in the database with the same name.
To fix this issue, either change the name of the table you’re trying to create, or check the existing table to see if it’s the one you actually need.
Example of Error
Here’s an example of code that produces the error:
In this case, I’m trying to create a table called Customers , but it already exists in the database.
Solution 1
The most obvious solution is to change the name of the table we’re creating:
Here, I simply renamed the table to Customers2 . In practice, we would probably give it a more appropriate name.
We should also be mindful that if there’s already a table with the same name as the one we’re trying to create, there’s a possibility that our desired table has already been created. In this case we wouldn’t need to recreate it (unless we had good reason). We could either just go ahead and use it, or we could alter it to suit our new requirements.
Solution 2
Another way to deal with this error is to suppress it. We can modify our CREATE TABLE statement to only create the table if it doesn’t already exist:
In this case, we got a warning. Let’s check the warning:
The warning explicitly tells us that the table already exists.
The Table REALLY Doesn’t Exist?
If you believe that the table really doesn’t exist, perhaps there’s something else going on. See this article on Stack Overflow for a discussion on possible solutions.
Table already exists error when trying to import sql file
I am trying to upload a backup sql file through phpMyAdmin.
I create the empty db with the same db name as in my import file in phpMyAdmin then use the import function selected from within this empty db.
I get the following error message.
Inside the import file each CREATE TABLE statement is suffixed by IF NOT EXISTS, so why is this being reported as an error?
I have tried to take out the table reported from the sql file, but then the next table just throws up the error, and the next etc until we have a sql import with no tables left.
I am using Windows XP with MySQL 5.5.37-win32, these versions could be different from the versions used when creating the backup, would this matter?
Thanks in advance.
Edit : Added SQL code.
This is just a sample, the code is created by phpMyAdmin’s export function
Much more sql data but seems pointless to post as it is all formatted the same.
Guide for Mysql2 «Table already exists» error fix
The following guide will teach you how to solve the database Mysql2 table already exists error. It is a serious problem as Redmine crashes in general and could be related to any of our plugins.
If the problem appears at your Redmine, then the error logs (in the production.log file) should look like this:
Note: The name_of_the_table depends on which plugin the error is appearing on.
The error itself means that the migration was broken some time ago. Please follow these steps to fix it:
Mysql 1050 Error "Table already exists" when in fact, it does not
Seriously now, you probably have a broken table. Try:
- DROP TABLE IF EXISTS contenttype
- REPAIR TABLE contenttype
- If you have sufficient permissions, delete the data files (in /mysql/data/db_name)
You may need to flush the table cache. For example:
I got this same error, and REPAIR TABLE (from @NullUserException’s answer) didn’t help.
I eventually found this solution:
For me, without the sudo , I got the following error:
(Running on OS X 10.6)
Same problem occurred with me while creating a view. The view was present earlier then due to some changes it got removed But when I tried to add it again it was showing me «view already exists» error message.
Solution:
You can do one thing manually.
- Go to the MySQL folder where you have installed it
- Go to the data folder inside it.
- Choose your database and go inside it.
- Data base creates «.frm» format files.
- delete the particular table’s file.
- Now create the table again.
It will create the table successfully.
I had this problem on Win7 in Sql Maestro for MySql 12.3. Enormously irritating, a show stopper in fact. Nothing helped, not even dropping and recreating the database. I have this same setup on XP and it works there, so after reading your answers about permissions I realized that it must be Win7 permissions related. So I ran MySql as administrator and even though Sql Maestro was run normally, the error disappeared. So it must have been a permissions issue between Win7 and MySql.
I’ve been fighting with this all day: I have a Perl script that builds a set of tables by first doing a DROP IF EXISTS . on them and then CREATE ing them. The DROP succeeded, but on CREATE I got this error message: table already exists
I finally got to the bottom of it: The new version of MySQL that I’m using has a default engine of InnoDB («show engine \G;») I changed it in the my.cnf file to default to MyISAM, re-started MySQL, and now I no longer get the «table already exists» error.
I am struggling with the same issue. I cannot create a table, even though it does not exist. I tried all the above solutions with no success.
My solution was to delete the files ib_logfil0 , ib_logfile1 , ibdata1 , and auto.cnf from the data folder of MySQL; make sure to stop the MySQL service first before deleting these files.
Then after restarting the service, MySQL recreated these files and I was able to run a backup script were all my CREATE s were stored (a sqldump file).
Encountering the same problem (create InnoDB table) this is what finally worked for me:
I checked on a file basis, permissions, tried to REPAIR and FLUSH but nothing worked.
So if this is an option, move all working tables to another DATABASE, drop the old one (you might have to manually remove any files from the database folder before the drop to work), rename the new one, and you ‘should’ be back on your way. Apparently, whatever gets ‘cached’ using InnoDB is dropped along with the original database.
You won´t believe me! I´ve just removed a comment block from my .sql file and now it works.
The deleted comment block was this:
I´ve left the problematic table alone in the same .sql file. After that I´ve removed comments, the only code was left, and the error disappears.
I also encountered this problem where trying to Create a table said it already exists and Dropping the table said it didn’t exist.
I did «FLUSH TABLES» and it cleared the problem.
First check if you are in the right database USE yourDB and try Select * from contenttype just to see what is it and if it exists really.
I had the same problem at Mac OS X and MySQL 5.1.40. I used eclipse to edit my SQL script and than I tried MySQLWorkbench 5.2.28. Probably it converted newline characters to Mac format. I had no idea about what’s wrong with my script until I commented out the first line in file. After this this script was interpreted by mysql as a one single comment. I used build-in TextEdit Mac application to fix this. After line-breaks was converted to the correct format, the error 1050 gone.
Update for Eclipse users:
To set up default ending for new files created, across the entire workspace:
Window -> Preferences -> General -> Workspace -> New text file line delimiter.
To convert existing files, open file for editing and for the currently edited file, go to the menu:
File -> Convert Line Delimiters To
I had this same case. The problem ended up being permissions on the parent directory.
I had been copying files in and out of mysql during testing.
was not enough, needed to be:
Sorry to resurrect.
I was having huge issues with Error 1050 and 150.
The problem, for me was that I was trying to add a constraint with ON DELETE SET NULL as one of the conditions.
Changing to ON DELETE NO ACTION allowed me to add the required FK constraints.
Unfortunately the MySql error messages are utterly unhelpful so I had to find this solution iteratively and with the help of the answers to the question above.
Your disk also might just be full. (just had that)
I had this same problem and it looks like the Database name was case sensitive. My Database is called:
Whilst my script included
Once I changed the database name to the correct case it all seemed to work. Using MYSQL Workbench on MAC OSX
This problem also occurs if a ‘view’ (imaginary table) exists in database as same name as our new table name.
In my case I found this to be an issue with InnoDB; I never discovered what the actual problem was, but creating as a MyISAM allowed it to build
For me the problem was caused when using a filesystem copy of the mysql database directory instead of mysqldump. I have some very large tables, mostly MyISAM and a few InnoDB cache tables and it is not practical to mysqldump the data. Since we are still running MyISAM, XtraBackup is not an option.
The same symptoms as above happened to me. The table is not there, there are no files in the directory that pertain to the table, yet it cannot be created because MySQL thinks its there. Drop table says it’s not there, create table says it is.
The problem occurred on two machines, both were fixed by copying backups. However, I noticed that in my backup that there was a .MYD and .MYI file, even though I was under the impression that these files are not used for InnoDB. The .MYD and .MYI files had an owner of root, while the .frm was owned by mysql.
If you copy from backup, check the file permissions. Flush tables might work, but I opted to shut down and restart the database.
Mysql 1050 Ошибка «Таблица уже существует», хотя на самом деле это не так
Я получил ту же ошибку, и REPAIR TABLE (из ответа @NullUserException) не помогло.
В итоге я нашел это решение:
Для меня, без sudo , я получил следующую ошибку:
(Работает на OS X 10.6)
Вам может понадобиться очистить кеш таблицы. Например:
Встречая ту же проблему (создайте таблицу InnoDB), это то, что окончательно сработало для меня:
Я проверил файлы, разрешения, попытался выполнить REPAIR и FLUSH, но ничего не получилось.
Итак, если это вариант, переместите все рабочие таблицы в другую базу данных, отбросьте старую (вам может потребоваться вручную удалить любые файлы из папки базы данных до того, как она начнет работать), переименуйте новую, должен «вернуться на ваш путь». По-видимому, все, что «кэшируется» с помощью InnoDB, отбрасывается вместе с исходной базой данных.
Я борюсь с этим весь день: у меня есть Perl script, который строит набор таблиц, сначала делая DROP IF EXISTS . на них, а затем CREATE их. DROP преуспел, но на CREATE я получил это сообщение об ошибке: table already exists
Наконец-то я понял: новая версия MySQL, которую я использую, имеет двигатель InnoDB по умолчанию ( «show engine\G;» ). Я изменил его в файле my.cnf по умолчанию на MyISAM, повторно запустил MySQL, и теперь я больше не получаю ошибку «таблица уже существует».
Аналогичная проблема возникла при создании представления. Представление было раньше, но из-за некоторых изменений, которые он удалил. Но когда я попытался добавить его снова, он показывал мне сообщение об ошибке «вид уже существует».
Решение:
Вы можете сделать одно дело вручную.
- Перейдите в папку MySQL, в которой вы ее установили.
- Перейдите в папку данных внутри нее.
- Выберите свою базу данных и зайдите в нее.
- База данных создает файлы формата .frm.
- удалить конкретный файл таблицы.
- Теперь создайте таблицу еще раз.
Он успешно создаст таблицу.
Я также столкнулся с этой проблемой, когда пытаюсь создать таблицу, в которой говорилось, что она уже существует, и сбросив таблицу, сказал, что ее не существует.
Я сделал «FLUSH TABLES», и он очистил проблему.
У меня была эта проблема на Win7 в Sql Maestro для MySql 12.3. Огромное раздражение, шоу-стоп на самом деле. Ничто не помогло, даже не отбросило и не воссоздало базу данных. У меня есть такая же настройка на XP, и она работает там, поэтому, прочитав ваши ответы о разрешениях, я понял, что это должны быть разрешения Win7. Поэтому я запускал MySql как администратор, и хотя Sql Maestro запускался нормально, ошибка исчезла. Таким образом, это может быть проблема с разрешениями между Win7 и MySql.
Я борюсь с той же проблемой. Я не могу создать таблицу, даже если она не существует. Я пробовал все вышеперечисленные решения без успеха.
Моим решением было удалить файлы ib_logfil0 , ib_logfile1 , ibdata1 и auto.cnf из папки данных MySQL; перед удалением этих файлов обязательно запустите службу MySQL.
Затем после перезапуска службы MySQL воссоздал эти файлы, и я смог запустить резервную копию script, все мои файлы CREATE были сохранены (файл sqldump).
У меня был такой же случай. Проблема заключалась в разрешении родительского каталога.
В ходе тестирования я копировал файлы из mysql и из него.
Извините, что воскрес.
У меня была такая же проблема в Mac OS X и MySQL 5.1.40. Я использовал eclipse для редактирования моего SQL script, и я попробовал MySQLWorkbench 5.2.28. Вероятно, он преобразует символы новой строки в формат Mac. Я понятия не имел, что случилось с моим script, пока я не прокомментировал первую строку в файле. После этого этот script был интерпретирован mysql как один комментарий. Для этого я использовал встроенное приложение TextEdit Mac. После того, как разрывы строк были преобразованы в правильный формат, ошибка 1050 исчезла.
Обновление для пользователей Eclipse:
Чтобы настроить окончание по умолчанию для новых файлов, созданных по всей рабочей области:
Окно → Настройки → Общие → Рабочая область → Новая строка текстового файла разделитель.
Чтобы преобразовать существующие файлы, откройте файл для редактирования и для текущего файла, перейдите в меню:
Файл → Преобразование разделителей строк в
Сначала проверьте, находитесь ли вы в правильной базе данных USE yourDB и попробуйте Select * from contenttype просто посмотреть, что это такое, и если оно действительно существует.
В моем случае проблема заключалась в том, что было представление с тем же именем, что и моя таблица, поэтому мне пришлось отбросить представление, чтобы продолжить импорт.
Автоматизированное решение, которое сработало для меня, — это заменить стандартную таблицу drop на этот sed во время дампа, чтобы также удалить любые существующие представления:
Или, если вы предпочитаете печатать в файл для резервного копирования
Или, если вы получили сбрасываемый файл и импортируете его в свой db
Вы получаете идею
Примечание: важно добавить ^ в начало замещающего регулярного выражения, потому что в дампах, которые вы не хотите касаться, есть другие типы команд DROP TABLE IF EXISTS .
У вас есть что-то вроде этого:
Чтобы иметь что-то вроде этого:
У меня возникли огромные проблемы с ошибкой 1050 и 150.
Проблема заключалась в том, что я пытался добавить ограничение с ON DELETE SET NULL в качестве одного из условий.
Переход на ON DELETE NO ACTION позволил мне добавить необходимые ограничения FK.
К сожалению, сообщения об ошибках MySql совершенно бесполезны, поэтому я должен был найти это решение итеративно и с помощью ответов на вопрос выше.
пытался импортировать резервный sql файл, но получал ошибку; 1050 «Таблица уже существует»
Моя настройка была:
- Windows 7
- Mysql 5.5.16
- Изменен движок сервера от InnoDB до MyISAM
- Использование phpMyAdmin Удаленная база данных, которую я пытался импортировать в
- Перезапуск службы mysql
- Пробовал повторный импорт и работал
gosh, у меня была та же проблема с osCommerce install script, пока я не выяснил, что система mysql имеет много баз данных, а запрос create table сам копирует в каждый из них и, таким образом, отбрасывает только рабочую таблицу на активном db didnt help, мне пришлось отказаться от таблицы из всех dbs
Для меня проблема возникла при использовании копии файловой системы базы данных mysql вместо mysqldump. У меня есть очень большие таблицы, в основном MyISAM и несколько таблиц кэша InnoDB, и это не является практичным для mysqldump данных. Поскольку мы все еще запускаем MyISAM, XtraBackup не является вариантом.
Те же симптомы, что и выше, произошли со мной. Таблицы там нет, в каталоге нет файлов, относящихся к таблице, но это невозможно создать, потому что MySQL думает о ней. Капля таблицы говорит, что нет, создание таблицы говорит, что это так.
Проблема возникла на двух машинах, оба были исправлены путем копирования резервных копий. Тем не менее, я заметил, что в моей резервной копии был файл .MYD и .MYI, хотя у меня создалось впечатление, что эти файлы не используются для InnoDB. У файлов .MYD и .MYI был владелец root, а .frm принадлежал mysql.
Если вы копируете из резервной копии, проверьте права доступа к файлу. Таблицы Flush могут работать, но я решил закрыть и перезапустить базу данных.
Мой оператор CREATE был частью промежуточного сценария env.
Я попробовал все, что было упомянуто выше. Я НЕ получил решение. Однако мой путь к искуплению:
Я натыкаюсь на то, что (один из многих в) оператор CREATE прошел, когда я исправил чувствительность к регистру базы данных. Это что-то нажало. Я повторил то же самое для других таблиц.
Однако на сцену появилась новая ошибка. Прямые цитаты для «комментариев» бросали синтаксическую ошибку. Я был потрясен. заменили их, но появилась новая ошибка. Наконец я знал решение.
РЕШЕНИЕ: Дамп, который я использовал, возможно, был из другой версии MySql. Я получил разрешение на подключение к промежуточной MYsql с помощью локальной (установленной на моей машине) рабочей среды mysql. Я не rdp на промежуточном сервере для входа в очередь для работы с mysql workbench. Создал дамп оттуда. Отказался от дампа, и он работал как сладкий.
Table already exists error when trying to import sql file
I am trying to upload a backup sql file through phpMyAdmin.
I create the empty db with the same db name as in my import file in phpMyAdmin then use the import function selected from within this empty db.
I get the following error message.
Inside the import file each CREATE TABLE statement is suffixed by IF NOT EXISTS, so why is this being reported as an error?
I have tried to take out the table reported from the sql file, but then the next table just throws up the error, and the next etc until we have a sql import with no tables left.
I am using Windows XP with MySQL 5.5.37-win32, these versions could be different from the versions used when creating the backup, would this matter?
Thanks in advance.
Edit : Added SQL code.
This is just a sample, the code is created by phpMyAdmin’s export function
Much more sql data but seems pointless to post as it is all formatted the same.