Sql ошибка 1265 - Исправление ошибок и поиск оптимальных решений проблем

This is an article where the main discussion or its written to solve the error problem specified in the title of this article. The article is triggered upon inserting new rows to a table in a database which all the data are extracted fro another table located in another database. For more information, below is the actual output shown as an error message upon inserting new records :

user@hostname:~$ mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2537
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> insert into newdb.table1 from select * from olddb.table1;
ERROR 1265 (01000): Data truncated for column 'column_name' at row 1
mysql>

The above SQL Query command, it is actually an SQL Query command executed in order to insert a new record in a table located in a database from another table located in other database. In order to solve it, one way to solve it is to actually set SQL_MODE to nothing. Below is the actual SQL_MODE value :

mysql> select @@SQL_MODE;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SQL_MODE                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql>

So, in order to set SQL_MODE to nothing, below is the configuration on it :

mysql> SET SQL_MODE='';
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql>

To prove it whether it has already changed, in this context the value of SQL_MODE, below is another query command executed in order to preview the value of SQL_MODE :

mysql> select @@SQL_MODE;
+------------+
| @@SQL_MODE |
+------------+
|            |
+------------+
1 row in set (0,00 sec)

mysql>

After changing the value of SQL_MODE, just re-execute the query as shown below :

mysql> insert into newdb.table1 from select * from olddb.table1;
Query OK, 59 rows affected, 118 warnings (0,05 sec)
Records: 59  Duplicates: 0  Warnings: 118
                                                                                                                                                                                                                                  
mysql> quit                                                                                                                                                                                                                       
Bye                                                                                                                                                                                                                               
user@hostname:~$

Источник

Ads were blocked — no problem. But keep in mind that developing HeidiSQL,
user support and hosting takes time and money. You may want to
send a donation instead.

Hi folks.

I originally had a VARCHAR column set to a width of 6, but now I want to reduce it to 3.

If I make the change in the Table tab of HeidiSQL I get the following error when I click the save button:

SQL error (1265): Data truncated for column ‘membertype’ at row 1

The field «membertype» will only ever consist of a string of three upper case alpha characters.

What can I do about this, to make the change and have it stick?

Donor

Your column «membertype» obviously has one or more values which are longer than 3 chars. That’s why you get this message from the server.

I believe you are running MySQL in strict mode. Turn it off and the above message should not appear, while that surely cuts of longer values in your membertype column.

Sorry, I don’t know what strict mode is.

How do I turn it off and could there be any adverse consequences to doing so?

Donor

Please check this:

SHOW VARIABLES LIKE 'sql_mode'

and post the results here.

Hi anse.

I executed that command line in the Query tab and this is all that came up:

STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Is that what you mean?

Donor

Exactly. STRICT_TRANS_TABLES is what I meant by «strict mode». That forces you to be somehow more disciplined.

In this case you have to run an UPDATE on your data and truncate them manually. Then, change the column definition and the error should not pop up.

Alternatively, remove STRICT_TRANS_TABLES from the global(!) sql_mode variable (Host > Variables) with a doubleclick, reconnect to the server and apply your changes.

Will I possibly be setting myself up for future problems by removing the STRICT_TRANS_TABLES entry?

Donor

This is what the manual says:

n MySQL 5.0.2 and up, you can select stricter treatment of input values by using the STRICT_TRANS_TABLES or STRICT_ALL_TABLES SQL modes:

SET sql_mode = ‘STRICT_TRANS_TABLES’;
SET sql_mode = ‘STRICT_ALL_TABLES’;

STRICT_TRANS_TABLES enables strict mode for transactional storage engines, and also to some extent for nontransactional engines. It works like this:

*

For transactional storage engines, bad data values occurring anywhere in a statement cause the statement to abort and roll back.
*

For nontransactional storage engines, a statement aborts if the error occurs in the first row to be inserted or updated. (When the error occurs in the first row, the statement can be aborted to leave the table unchanged, just as for a transactional table.) Errors in rows after the first do not abort the statement, because the table has already been changed by the first row. Instead, bad data values are adjusted and result in warnings rather than errors. In other words, with STRICT_TRANS_TABLES, a wrong value causes MySQL to roll back all updates done so far, if that can be done without changing the table. But once the table has been changed, further errors result in adjustments and warnings.

For even stricter checking, enable STRICT_ALL_TABLES. This is the same as STRICT_TRANS_TABLES except that for nontransactional storage engines, errors abort the statement even for bad data in rows following the first row. This means that if an error occurs partway through a multiple-row insert or update for a nontransactional table, a partial update results. Earlier rows are inserted or updated, but those from the point of the error on are not. To avoid this for nontransactional tables, either use single-row statements or else use STRICT_TRANS_TABLES if conversion warnings rather than errors are acceptable. To avoid problems in the first place, do not use MySQL to check column content. It is safest (and often faster) to let the application ensure that it passes only legal values to the database.

With either of the strict mode options, you can cause errors to be treated as warnings by using INSERT IGNORE or UPDATE IGNORE rather than INSERT or UPDATE without IGNORE.

OK, thanks for all your help.

Please login to leave a reply, or register at first.

This page uses cookies to show you non-personalized advertising and server usage statistic diagrams.

Источник

#1 27.06.2011 21:53:34

simple: Активист; Зарегистрирован: 25.11.2010; Сообщений: 168

Warning 1265 при апдейте таблицы

Перешел на стандартную консоль mysql, до этого работал в клиенте heidisql. Так вот время от времени в клиенте вылазиет такое сообщение: Query Ok, 0 rows affected, 1 warnings (0.01 sec). Делаю show warnings; вылазиет вот такое…Note 1265 Data truncated for column ‘Quote’ at row 1. Хотя запрос все выполняет верно, хотелось бы знать возможную причину почему появляется это сообщение? В другом клиенте такого рода ошибок вообще не было а вот стандартный что то капризничает

Неактивен

#2 28.06.2011 13:32:42

paulus: Администратор; Зарегистрирован: 22.01.2007; Сообщений: 6740

Re: Warning 1265 при апдейте таблицы

Видимо, таки дело не в клиенте, а в том, что вставляете значение длиннее, чем ширина столбца.

Неактивен

#3 28.06.2011 15:06:17

simple: Активист; Зарегистрирован: 25.11.2010; Сообщений: 168

Re: Warning 1265 при апдейте таблицы

А эта ошибка может привести в дальнейшем к более серьезным ошибкам? Поле quote имеет тип данных decimal(5,2), в него записывается сумма всех цен деленное на кол-во сделок, т.е средняя цена, вот на этом поле и вылазиет эта ошибка 1265, только я не пойму как значение оказывается длинее поле то?

Неактивен

#4 28.06.2011 15:17:37

simple: Активист; Зарегистрирован: 25.11.2010; Сообщений: 168

Re: Warning 1265 при апдейте таблицы

А понял, при делении суммы цен иногда в дробная часть увеличивается на несколько цифр, наприме 90.0524125 отсюда и ошибка, спасибо форуму, буду исправлять этот «баг»

Неактивен

#5 28.06.2011 18:13:48

paulus: Администратор; Зарегистрирован: 22.01.2007; Сообщений: 6740

Re: Warning 1265 при апдейте таблицы

Если я правильно помню обозначения, в DECIMAL(5,2) не влезет 1265 — до точки
должно быть 5-2=3 символа.

Брр, перечитал, 1265 — это не то, что Вы пытаетесь вставить Но все равно, учтите.

Неактивен

Источник