Mysql workbench ошибка 1264 - Решение и исправление самых разных ошибок на TopOshibok.ru

I’ve a table CustomizationSet with the columns:

customization_set_guid (which is a non-nullable guid and also the primary key)
creator_account_guid
and a few others

And a table with existing data Registration with the columns:

registration_id (an int and the primary key)
customization_set_guid (also a guid (so a char(36)) which is nullable, and all entries are currently null)
and a few other columns

When I try and run

ALTER TABLE Registration ADD FOREIGN KEY 
    (
        customization_set_guid
    ) REFERENCES CustomizationSet (
        customization_set_guid
    );

in MySQL Workbench, it gives the error 1050Table ‘.\dbname\registration’ already exists.

If I try to use the UI to add the foreign keys with the Foreign Keys tab of the Alter Table Dialog, and choose CustomizationSet as the referenced table, it doesn’t let me choose customization_set_guid in the list of columns.

I’m really not sure why it won’t let me add this foreign key. I’ve just successfully created foreign keys between tables I just added. The Registration table has existed for awhile…

Источник

I’ve a table CustomizationSet with the columns:

customization_set_guid (which is a non-nullable guid and also the primary key)
creator_account_guid
and a few others

And a table with existing data Registration with the columns:

registration_id (an int and the primary key)
customization_set_guid (also a guid (so a char(36)) which is nullable, and all entries are currently null)
and a few other columns

When I try and run

ALTER TABLE Registration ADD FOREIGN KEY 
    (
        customization_set_guid
    ) REFERENCES CustomizationSet (
        customization_set_guid
    );

in MySQL Workbench, it gives the error 1050Table ‘.\dbname\registration’ already exists.

I’m really not sure why it won’t let me add this foreign key. I’ve just successfully created foreign keys between tables I just added. The Registration table has existed for awhile…

Источник

As I SET cust_fax in a table in MySQL like this:

cust_fax integer(10) NOT NULL,

and then I insert value like this:

INSERT INTO database values ('3172978990');

but then it say

`error 1264` out of value for column

And I want to know where the error is? My set? Or other?

Any answer will be appreciated!

yunzen

32.9k11 gold badges73 silver badges106 bronze badges

asked Jan 11, 2013 at 18:33

The value 3172978990 is greater than 2147483647 – the maximum value for INT – hence the error. MySQL integer types and their ranges are listed here.

Also note that the (10) in INT(10) does not define the «size» of an integer. It specifies the display width of the column. This information is advisory only.

To fix the error, change your datatype to VARCHAR. Phone and Fax numbers should be stored as strings. See this discussion.

answered Jan 11, 2013 at 18:37

Salman ASalman A

262k82 gold badges430 silver badges522 bronze badges

You can also change the data type to bigInt and it will solve your problem, it’s not a good practice to keep integers as strings unless needed.

ALTER TABLE T_PERSON MODIFY mobile_no BIGINT;

Giacomo1968

25.8k11 gold badges71 silver badges103 bronze badges

answered Nov 23, 2015 at 15:11

You are exceeding the length of int datatype. You can use UNSIGNED attribute to support that value.

SIGNED INT can support till 2147483647 and with UNSIGNED INT allows double than this. After this you still want to save data than use CHAR or VARCHAR with length 10

answered Jan 11, 2013 at 18:39

Saharsh ShahSaharsh Shah

28.7k8 gold badges48 silver badges83 bronze badges

tl;dr

Make sure your AUTO_INCREMENT is not out of range. In that case, set a new value for it with:

ALTER TABLE table_name AUTO_INCREMENT=100 -- Change 100 to the desired number

Explanation

AUTO_INCREMENT can contain a number that is bigger than the maximum value allowed by the datatype. This can happen if you filled up a table that you emptied afterward but the AUTO_INCREMENT stayed the same, but there might be different reasons as well. In this case a new entry’s id would be out of range.

Solution

If this is the cause of your problem, you can fix it by setting AUTO_INCREMENT to one bigger than the latest row’s id. So if your latest row’s id is 100 then:

ALTER TABLE table_name AUTO_INCREMENT=101

If you would like to check AUTO_INCREMENT‘s current value, use this command:

SELECT `AUTO_INCREMENT`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND   TABLE_NAME   = 'TableName';

answered Mar 17, 2019 at 0:37

totymedlitotymedli

29.6k22 gold badges132 silver badges165 bronze badges

Work with:

ALTER TABLE `table` CHANGE `cust_fax` `cust_fax` VARCHAR(60) NULL DEFAULT NULL;

answered Mar 5, 2020 at 2:54

Just table id column name delete and new id created with autoincrement then worked successfully.

answered Apr 10, 2022 at 5:34

Источник

Я добавляю эту таблицу:

CREATE TABLE contenttype (
        contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT,
        class VARBINARY(50) NOT NULL,
        packageid INT UNSIGNED NOT NULL,
        canplace ENUM('0','1') NOT NULL DEFAULT '0',
        cansearch ENUM('0','1') NOT NULL DEFAULT '0',
        cantag ENUM('0','1') DEFAULT '0',
        canattach ENUM('0','1') DEFAULT '0',
        isaggregator ENUM('0', '1') NOT NULL DEFAULT '0',
        PRIMARY KEY (contenttypeid),
        UNIQUE KEY packageclass (packageid, class)
);

И я получаю таблицу 1050 «уже существует»

Но таблица НЕ существует. Любые идеи?

EDIT: подробности, потому что все, кажется, не верят мне:)

DESCRIBE contenttype

дает:

1146 — Таблица ‘gunzfact_vbforumdb.contenttype’ не существует

CREATE TABLE gunzfact_vbforumdb.contenttype(
contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT ,
class VARBINARY( 50 ) NOT NULL ,
packageid INT UNSIGNED NOT NULL ,
canplace ENUM( '0', '1' ) NOT NULL DEFAULT '0',
cansearch ENUM( '0', '1' ) NOT NULL DEFAULT '0',
cantag ENUM( '0', '1' ) DEFAULT '0',
canattach ENUM( '0', '1' ) DEFAULT '0',
isaggregator ENUM( '0', '1' ) NOT NULL DEFAULT '0',
PRIMARY KEY ( contenttypeid ) ,

Урожайность:

1050 — Табл. ‘contenttype’ уже существует

Источник

I’m adding this table:

CREATE TABLE contenttype (
        contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT,
        class VARBINARY(50) NOT NULL,
        packageid INT UNSIGNED NOT NULL,
        canplace ENUM('0','1') NOT NULL DEFAULT '0',
        cansearch ENUM('0','1') NOT NULL DEFAULT '0',
        cantag ENUM('0','1') DEFAULT '0',
        canattach ENUM('0','1') DEFAULT '0',
        isaggregator ENUM('0', '1') NOT NULL DEFAULT '0',
        PRIMARY KEY (contenttypeid),
        UNIQUE KEY packageclass (packageid, class)
);

And I get a 1050 «table already exists»

But the table does NOT exist. Any ideas?

EDIT: more details because everyone seems to not believe me

DESCRIBE contenttype

yields:

1146 — Table ‘gunzfact_vbforumdb.contenttype’ doesn’t exist

and

CREATE TABLE gunzfact_vbforumdb.contenttype(
contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT ,
class VARBINARY( 50 ) NOT NULL ,
packageid INT UNSIGNED NOT NULL ,
canplace ENUM( '0', '1' ) NOT NULL DEFAULT '0',
cansearch ENUM( '0', '1' ) NOT NULL DEFAULT '0',
cantag ENUM( '0', '1' ) DEFAULT '0',
canattach ENUM( '0', '1' ) DEFAULT '0',
isaggregator ENUM( '0', '1' ) NOT NULL DEFAULT '0',
PRIMARY KEY ( contenttypeid ) ,

Yields:

1050 — Table ‘contenttype’ already exists

Using the command:

CREATE TABLE IF NOT EXISTS `test`.`t1` (
    `col` VARCHAR(16) NOT NULL
) ENGINE=MEMORY;

Running this twice in the MySQL Query Browser results in:

Table ‘t1’ already exists Error 1050

I would have thought that creating the table «IF NOT EXISTS» would not throw errors. Am I missing something or is this a bug? I am running version 5.1. Thanks.

Adam Wagner

15.3k7 gold badges52 silver badges66 bronze badges

asked Oct 30, 2009 at 16:39

Works fine for me in 5.0.27

I just get a warning (not an error) that the table exists;

answered Oct 30, 2009 at 16:47

EliEli

5,5001 gold badge29 silver badges27 bronze badges

As already stated, it’s a warning not an error, but (if like me) you want things to run without warnings, you can disable that warning, then re-enable it again when you’re done.

SET sql_notes = 0;      -- Temporarily disable the "Table already exists" warning
CREATE TABLE IF NOT EXISTS ...
SET sql_notes = 1;      -- And then re-enable the warning again

answered Aug 8, 2010 at 12:33

gdtgdt

1,81217 silver badges19 bronze badges

You can use the following query to create a table to a particular database in MySql.

create database if not exists `test`;

USE `test`;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

/*Table structure for table `test` */

CREATE TABLE IF NOT EXISTS `tblsample` (

  `id` int(11) NOT NULL auto_increment,   
  `recid` int(11) NOT NULL default '0',       
  `cvfilename` varchar(250)  NOT NULL default '',     
  `cvpagenumber`  int(11) NULL,     
  `cilineno` int(11)  NULL,    
  `batchname`  varchar(100) NOT NULL default '',
  `type` varchar(20) NOT NULL default '',    
  `data` varchar(100) NOT NULL default '',
   PRIMARY KEY  (`id`)

);

bummi

27.1k13 gold badges62 silver badges101 bronze badges

answered Dec 18, 2015 at 7:40

Sachin ParseSachin Parse

1,25111 silver badges12 bronze badges

create database if not exists `test`;

USE `test`;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

/*Table structure for table `test` */

***CREATE TABLE IF NOT EXISTS `tblsample` (
  `id` int(11) NOT NULL auto_increment,   
  `recid` int(11) NOT NULL default '0',       
  `cvfilename` varchar(250)  NOT NULL default '',     
  `cvpagenumber`  int(11) NULL,     
  `cilineno` int(11)  NULL,    
  `batchname`  varchar(100) NOT NULL default '',
  `type` varchar(20) NOT NULL default '',    
  `data` varchar(100) NOT NULL default '',
   PRIMARY KEY  (`id`)
);***

tedder42

23.3k13 gold badges86 silver badges100 bronze badges

answered Nov 16, 2017 at 9:28

BalkishanBalkishan

2813 silver badges4 bronze badges

I have a solution to a problem that may also apply to you. My database was in a state where a DROP TABLE failed because it couldn’t find the table… but a CREATE TABLE also failed because MySQL thought the table existed. (This state could easily mess with your IF NOT EXISTS clause).

I eventually found this solution:

sudo mysqladmin flush-tables

For me, without the sudo, I got the following error:

mysqladmin: refresh failed; error: 'Access denied; you need the RELOAD privilege for this operation'

(Running on OS X 10.6)

answered Nov 23, 2010 at 0:05

Craig WalkerCraig Walker

49.4k53 gold badges151 silver badges211 bronze badges

Create mysql connection with following parameter. «‘raise_on_warnings’: False». It will ignore the warning. e.g.

config = {'user': 'user','password': 'passwd','host': 'localhost','database': 'db',   'raise_on_warnings': False,}
cnx = mysql.connector.connect(**config)

answered Feb 8, 2014 at 10:25

VinayVinay

4331 gold badge5 silver badges11 bronze badges

I had a similar Problem as @CraigWalker on debian: My database was in a state where a DROP TABLE failed because it couldn’t find the table, but a CREATE TABLE also failed because MySQL thought the table still existed. So the broken table still existed somewhere although it wasn’t there when I looked in phpmyadmin.

I created this state by just copying the whole folder that contained a database with some MyISAM and some InnoDB tables

cp -a /var/lib/mysql/sometable /var/lib/mysql/test

(this is not recommended!)

All InnoDB tables where not visible in the new database test in phpmyadmin.

sudo mysqladmin flush-tables didn’t help either.

My solution: I had to delete the new test database with drop database test and copy it with mysqldump instead:

mysqldump somedatabase -u username -p -r export.sql
mysql test -u username -p < export.sql

answered Jun 22, 2017 at 4:13

rubo77rubo77

19.3k30 gold badges132 silver badges225 bronze badges

Well there are lot of answeres already provided and lot are making sense too.

Some mentioned it is just warning and some giving a temp way to disable warnings. All that will work but add risk when number of transactions in your DB is high.

I came across similar situation today and here is the query I came up with…

declare
begin
  execute immediate '
    create table "TBL" ("ID" number not null)';
  exception when others then
    if SQLCODE = -955 then null; else raise; end if;
end;
/

This is simple, if exception come while running query it will be suppressed. and you can use same for SQL or Oracle.

answered Apr 23, 2020 at 11:16

Kunal VohraKunal Vohra

2,6872 gold badges14 silver badges33 bronze badges

If anyone is getting this error after a Phpmyadmin export, using the custom options and adding the «drop tables» statements cleared this right up.

answered Dec 2, 2018 at 17:44

nizz0knizz0k

4411 gold badge7 silver badges20 bronze badges

Выдаёт ошибку — #1050 — Table ‘users’ already exists.

CREATE TABLE users (
    usersId int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
    usersEmail varchar(128) NOT NULL,
    usersUid varchar(128) NOT NULL,
    usersPwd varchar(128) NOT NULL
);

Вопрос задан

04 авг. 2022
1649 просмотров

CREATE TABLE IF NOT EXISTS users (

Потому что нельзя создать таблицу с именем существующей.

DROP TABLE users;
CREATE TABLE users (
    usersId int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
    usersEmail varchar(128) NOT NULL,
    usersUid varchar(128) NOT NULL,
    usersPwd varchar(128) NOT NULL
);

Пригласить эксперта

Показать ещё
Загружается…

04 июн. 2023, в 01:35

1500 руб./за проект

04 июн. 2023, в 01:25

40000 руб./за проект

03 июн. 2023, в 23:42

1500 руб./за проект

Минуточку внимания

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:

CREATE TABLE Customers (
  CustomerId int NOT NULL PRIMARY KEY,
  CustomerName varchar(60) NOT NULL
  );

Result:

ERROR 1050 (42S01): Table 'customers' already exists

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:

CREATE TABLE Customers2 (
  CustomerId int NOT NULL PRIMARY KEY,
  CustomerName varchar(60) NOT NULL
  );

Result:

Query OK, 0 rows affected (0.02 sec)

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:

CREATE TABLE IF NOT EXISTS Customers (
  CustomerId int NOT NULL PRIMARY KEY,
  CustomerName varchar(60) NOT NULL
  );

Result:

Query OK, 0 rows affected, 1 warning (0.00 sec)

In this case, we got a warning. Let’s check the warning:

SHOW WARNINGS;

Result:

+-------+------+----------------------------------+
| Level | Code | Message                          |
+-------+------+----------------------------------+
| Note  | 1050 | Table 'Customers' already exists |
+-------+------+----------------------------------+
1 row in set (0.00 sec)

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.

Я добавляю эту таблицу:

CREATE TABLE contenttype (
        contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT,
        class VARBINARY(50) NOT NULL,
        packageid INT UNSIGNED NOT NULL,
        canplace ENUM('0','1') NOT NULL DEFAULT '0',
        cansearch ENUM('0','1') NOT NULL DEFAULT '0',
        cantag ENUM('0','1') DEFAULT '0',
        canattach ENUM('0','1') DEFAULT '0',
        isaggregator ENUM('0', '1') NOT NULL DEFAULT '0',
        PRIMARY KEY (contenttypeid),
        UNIQUE KEY packageclass (packageid, class)
);

И я получаю таблицу 1050 «уже существует»

Но таблица НЕ существует. Любые идеи?

EDIT: подробности, потому что все, кажется, не верят мне:)

DESCRIBE contenttype

дает:

1146 — Таблица ‘gunzfact_vbforumdb.contenttype’ не существует

CREATE TABLE gunzfact_vbforumdb.contenttype(
contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT ,
class VARBINARY( 50 ) NOT NULL ,
packageid INT UNSIGNED NOT NULL ,
canplace ENUM( '0', '1' ) NOT NULL DEFAULT '0',
cansearch ENUM( '0', '1' ) NOT NULL DEFAULT '0',
cantag ENUM( '0', '1' ) DEFAULT '0',
canattach ENUM( '0', '1' ) DEFAULT '0',
isaggregator ENUM( '0', '1' ) NOT NULL DEFAULT '0',
PRIMARY KEY ( contenttypeid ) ,

Урожайность:

1050 — Табл. ‘contenttype’ уже существует

Источник