Ошибка 1336 mysql

Recently upgraded to mysql 5.6.30,
throwing out mysql auto incrment error

1366 — Incorrect integer value: » for column ‘s

Community's user avatar

asked Jul 28, 2016 at 8:52

Manas's user avatar

Probably your new Mysql installation runs on strict mode while your previous one did not.And you probably have an empty value in some column which is defined as integer so you get this error.

Go at your my.cnf/my.ini file and look for this line

 sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Change it to blank:

  sql-mode=""

This would disable the strict mode and the error would be converted to warning

BUT

the best aprroach would to be to change the empty field value to a valid integer value.

answered Jul 28, 2016 at 9:36

George Pant's user avatar

George PantGeorge Pant

2,0791 gold badge9 silver badges14 bronze badges

Источник

I solved this by referring to https://stackoverflow.com/a/11835246/8751454

My function definition:

CREATE DEFINER=`root`@`localhost` FUNCTION `SPLIT_STR`(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
) RETURNS varchar(255) CHARSET utf8mb4
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '')

My stored procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `ABC`(fullstr varchar(100))
BEGIN
      DECLARE a INT Default 0 ;
      DECLARE str VARCHAR(255);
      drop temporary table if exists pmo.temp;
      simple_loop: LOOP
         SET a=a+1;
         SET str=SPLIT_STR(fullstr,",",a);
         IF str='' THEN
            LEAVE simple_loop;
         END IF;
 
         create temporary table temp( val char(255) );

         insert into pmo.temp(val) values (str);
         insert into pmo.meet(val)
         select val 
         from pmo.temp;
         drop temporary table if exists pmo.temp;
   END LOOP simple_loop;
   END

My after insert trigger on Table A

CREATE DEFINER=`root`@`localhost` TRIGGER `templates_AFTER_INSERT` AFTER INSERT 
ON `templates` FOR EACH ROW BEGIN
    declare multi_column_data varchar(100);
    set @multi_column_data = replace(new.fieldvalue,' , ',',');
    call ABC(@multi_column_data);

END

Источник

И посмотрите, как в реализации сохранена ошибка процедуры:

mysql> DELIMITER //
mysql> CREATE PROCEDURE ps(IN table_name VARCHAR(200))
-> BEGIN
-> SET @newname = table_name;
-> SET @s = CONCAT('create table ',@newname,'(resourceid varchar(200),',@newname,' varchar(200),time date)');
-> PREPARE stmt FROM @s;
-> EXECUTE stmt;
-> DEALLOCATE PREPARE stmt;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE zs_resourcegroup(group_name VARCHAR(20));
-> ;
-> //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER //
mysql> CREATE TRIGGER ts AFTER INSERT ON zs_resourcegroup
-> FOR EACH ROW
-> BEGIN
-> CALL ps(new.group_name);
-> END
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> insert into zs_resourcegroup select 'dba';
-> //
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or triggermysql>
mysql>
mysql> call ps("cui");
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
-> //
+------------------+
| Tables_in_test |
+------------------+
| cui |
| t |
| zs_resourcegroup |
+------------------+
3 rows in set (0.00 sec)
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or triggermysql>
mysql>
mysql> call ps("cui");
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
-> //
+------------------+
| Tables_in_test |
+------------------+
| cui |
| t |
| zs_resourcegroup |
+------------------+
3 rows in set (0.00 sec)

Я проверил его в Интернете, я обнаружил, что триггер не поддерживает динамический SQL, а также сохраненную процедуру или поддерживаемую.

Источник

And see the execution of the stored procedure error:

mysql> DELIMITER //
mysql> CREATE PROCEDURE ps(IN table_name VARCHAR(200))
-> BEGIN
-> SET @newname = table_name;
-> SET @s = CONCAT('create table ',@newname,'(resourceid varchar(200),',@newname,' varchar(200),time date)');
-> PREPARE stmt FROM @s;
-> EXECUTE stmt;
-> DEALLOCATE PREPARE stmt;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE zs_resourcegroup(group_name VARCHAR(20));
-> ;
-> //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER //
mysql> CREATE TRIGGER ts AFTER INSERT ON zs_resourcegroup
-> FOR EACH ROW
-> BEGIN
-> CALL ps(new.group_name);
-> END
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> insert into zs_resourcegroup select 'dba';
-> //
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or triggermysql>
mysql>
mysql> call ps("cui");
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
-> //
+------------------+
| Tables_in_test |
+------------------+
| cui |
| t |
| zs_resourcegroup |
+------------------+
3 rows in set (0.00 sec)
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or triggermysql>
mysql>
mysql> call ps("cui");
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
-> //
+------------------+
| Tables_in_test |
+------------------+
| cui |
| t |
| zs_resourcegroup |
+------------------+
3 rows in set (0.00 sec)

Checked the Internet and found that the dynamic sql is not supported in the trigger. The execution of the stored procedure alone can still be supported.

Источник

  • Error: 1300 SQLSTATE: HY000
    (ER_INVALID_CHARACTER_STRING)

    Message: Invalid %s character string: ‘%s’

  • Error: 1301 SQLSTATE: HY000
    (ER_WARN_ALLOWED_PACKET_OVERFLOWED)

    Message: Result of %s() was larger than max_allowed_packet (%ld) —
    truncated

  • Error: 1302 SQLSTATE: HY000
    (ER_CONFLICTING_DECLARATIONS)

    Message: Conflicting declarations: ‘%s%s’ and ‘%s%s’

  • Error: 1303 SQLSTATE: 2F003
    (ER_SP_NO_RECURSIVE_CREATE)

    Message: Can’t create a %s from within another stored routine

  • Error: 1304 SQLSTATE: 42000
    (ER_SP_ALREADY_EXISTS)

    Message: %s %s already exists

  • Error: 1305 SQLSTATE: 42000
    (ER_SP_DOES_NOT_EXIST)

    Message: %s %s does not exist

  • Error: 1306 SQLSTATE: HY000
    (ER_SP_DROP_FAILED)

    Message: Failed to DROP %s %s

  • Error: 1307 SQLSTATE: HY000
    (ER_SP_STORE_FAILED)

    Message: Failed to CREATE %s %s

  • Error: 1308 SQLSTATE: 42000
    (ER_SP_LILABEL_MISMATCH)

    Message: %s with no matching label: %s

  • Error: 1309 SQLSTATE: 42000
    (ER_SP_LABEL_REDEFINE)

    Message: Redefining label %s

  • Error: 1310 SQLSTATE: 42000
    (ER_SP_LABEL_MISMATCH)

    Message: End-label %s without match

  • Error: 1311 SQLSTATE: 01000
    (ER_SP_UNINIT_VAR)

    Message: Referring to uninitialized variable %s

  • Error: 1312 SQLSTATE: 0A000
    (ER_SP_BADSELECT)

    Message: PROCEDURE %s can’t return a result set in the given
    context

  • Error: 1313 SQLSTATE: 42000
    (ER_SP_BADRETURN)

    Message: RETURN is only allowed in a FUNCTION

  • Error: 1314 SQLSTATE: 0A000
    (ER_SP_BADSTATEMENT)

    Message: %s is not allowed in stored procedures

  • Error: 1315 SQLSTATE: 42000
    (ER_UPDATE_LOG_DEPRECATED_IGNORED)

    Message: The update log is deprecated and replaced by the binary
    log; SET SQL_LOG_UPDATE has been ignored. This option will be
    removed in MySQL 5.6.

  • Error: 1316 SQLSTATE: 42000
    (ER_UPDATE_LOG_DEPRECATED_TRANSLATED)

    Message: The update log is deprecated and replaced by the binary
    log; SET SQL_LOG_UPDATE has been translated to SET SQL_LOG_BIN.
    This option will be removed in MySQL 5.6.

  • Error: 1317 SQLSTATE: 70100
    (ER_QUERY_INTERRUPTED)

    Message: Query execution was interrupted

  • Error: 1318 SQLSTATE: 42000
    (ER_SP_WRONG_NO_OF_ARGS)

    Message: Incorrect number of arguments for %s %s; expected %u, got
    %u

  • Error: 1319 SQLSTATE: 42000
    (ER_SP_COND_MISMATCH)

    Message: Undefined CONDITION: %s

  • Error: 1320 SQLSTATE: 42000
    (ER_SP_NORETURN)

    Message: No RETURN found in FUNCTION %s

  • Error: 1321 SQLSTATE: 2F005
    (ER_SP_NORETURNEND)

    Message: FUNCTION %s ended without RETURN

  • Error: 1322 SQLSTATE: 42000
    (ER_SP_BAD_CURSOR_QUERY)

    Message: Cursor statement must be a SELECT

  • Error: 1323 SQLSTATE: 42000
    (ER_SP_BAD_CURSOR_SELECT)

    Message: Cursor SELECT must not have INTO

  • Error: 1324 SQLSTATE: 42000
    (ER_SP_CURSOR_MISMATCH)

    Message: Undefined CURSOR: %s

  • Error: 1325 SQLSTATE: 24000
    (ER_SP_CURSOR_ALREADY_OPEN)

    Message: Cursor is already open

  • Error: 1326 SQLSTATE: 24000
    (ER_SP_CURSOR_NOT_OPEN)

    Message: Cursor is not open

  • Error: 1327 SQLSTATE: 42000
    (ER_SP_UNDECLARED_VAR)

    Message: Undeclared variable: %s

  • Error: 1328 SQLSTATE: HY000
    (ER_SP_WRONG_NO_OF_FETCH_ARGS)

    Message: Incorrect number of FETCH variables

  • Error: 1329 SQLSTATE: 02000
    (ER_SP_FETCH_NO_DATA)

    Message: No data — zero rows fetched, selected, or processed

  • Error: 1330 SQLSTATE: 42000
    (ER_SP_DUP_PARAM)

    Message: Duplicate parameter: %s

  • Error: 1331 SQLSTATE: 42000
    (ER_SP_DUP_VAR)

    Message: Duplicate variable: %s

  • Error: 1332 SQLSTATE: 42000
    (ER_SP_DUP_COND)

    Message: Duplicate condition: %s

  • Error: 1333 SQLSTATE: 42000
    (ER_SP_DUP_CURS)

    Message: Duplicate cursor: %s

  • Error: 1334 SQLSTATE: HY000
    (ER_SP_CANT_ALTER)

    Message: Failed to ALTER %s %s

  • Error: 1335 SQLSTATE: 0A000
    (ER_SP_SUBSELECT_NYI)

    Message: Subquery value not supported

  • Error: 1336 SQLSTATE: 0A000
    (ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG)

    Message: %s is not allowed in stored function or trigger

  • Error: 1337 SQLSTATE: 42000
    (ER_SP_VARCOND_AFTER_CURSHNDLR)

    Message: Variable or condition declaration after cursor or handler
    declaration

  • Error: 1338 SQLSTATE: 42000
    (ER_SP_CURSOR_AFTER_HANDLER)

    Message: Cursor declaration after handler declaration

  • Error: 1339 SQLSTATE: 20000
    (ER_SP_CASE_NOT_FOUND)

    Message: Case not found for CASE statement

  • Error: 1340 SQLSTATE: HY000
    (ER_FPARSER_TOO_BIG_FILE)

    Message: Configuration file ‘%s’ is too big

  • Error: 1341 SQLSTATE: HY000
    (ER_FPARSER_BAD_HEADER)

    Message: Malformed file type header in file ‘%s’

  • Error: 1342 SQLSTATE: HY000
    (ER_FPARSER_EOF_IN_COMMENT)

    Message: Unexpected end of file while parsing comment ‘%s’

  • Error: 1343 SQLSTATE: HY000
    (ER_FPARSER_ERROR_IN_PARAMETER)

    Message: Error while parsing parameter ‘%s’ (line: ‘%s’)

  • Error: 1344 SQLSTATE: HY000
    (ER_FPARSER_EOF_IN_UNKNOWN_PARAMETER)

    Message: Unexpected end of file while skipping unknown parameter
    ‘%s’

  • Error: 1345 SQLSTATE: HY000
    (ER_VIEW_NO_EXPLAIN)

    Message: EXPLAIN/SHOW can not be issued; lacking privileges for
    underlying table

  • Error: 1346 SQLSTATE: HY000
    (ER_FRM_UNKNOWN_TYPE)

    Message: File ‘%s’ has unknown type ‘%s’ in its header

  • Error: 1347 SQLSTATE: HY000
    (ER_WRONG_OBJECT)

    Message: ‘%s.%s’ is not %s

  • Error: 1348 SQLSTATE: HY000
    (ER_NONUPDATEABLE_COLUMN)

    Message: Column ‘%s’ is not updatable

  • Error: 1349 SQLSTATE: HY000
    (ER_VIEW_SELECT_DERIVED)

    Message: View’s SELECT contains a subquery in the FROM clause

    • Источник

    Понравилась статья? Поделить с друзьями:

    Не пропустите эти материалы по теме:

  • Яндекс еда ошибка привязки карты
  • Ошибка 1336 1340 ситроен с4
  • Ошибка 1336 1339 ситроен
  • Ошибка 1336 1338 ситроен
  • Ошибка 1336 1338 пежо 308

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии