MariaDB – Setting AUTO_INCREMENT Using SELECT

mariadb

I am migrating a MariaDB database from my personal machine to a test
server. The database schema has changed so I need to recreate the
tables. The source database is also a mix of data that I want to keep
and a lot of test junk that I weeded out of the dump text file
manually. Since I dropped a lot of data, I want to update the AUTO_INCREMENT
value back to the new maximum.

This works:

MariaDB [imok]> alter table accounts AUTO_INCREMENT 114;
Query OK, 11 rows affected (0.04 sec)
Records: 11  Duplicates: 0  Warnings: 0

But when I try to create a generic statement using a select, MariaDB
complains:

MariaDB [imok]> alter table accounts AUTO_INCREMENT (select max(AcctID)+1 from accounts);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select max(AcctID)+1 from accounts)' at line 1

I tried using a variable, with the same result:

MariaDB [imok]> set @autoinc=(select max(AcctID)+1 from accounts);
Query OK, 0 rows affected (0.00 sec)

MariaDB [imok]> select @autoinc;
+----------+
| @autoinc |
+----------+
|      114 |
+----------+
1 row in set (0.00 sec)

MariaDB [imok]> alter table accounts AUTO_INCREMENT @autoinc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@autoinc' at line 1

QUESTION: How do I set the AUTO_INCREMENT value to something other than
a literal value in MariaDB?

NOTE: I am using MariaDB not MySQL.

NOTE: The '=' in the ALTER statement is optional, MariaDB gives the same complaint
with or without it; setting AUTO_INCREMENT using a literal value 113 works
without or without the '='.

Best Answer

I think best would be to remove the value of auto_increment from import script/dump - let it start at 1, then insert all the rows with fixed ID and let InnoDB do the work:

"If you insert a row that explicitly specifies the column value, and the value is bigger than the current counter value, the counter is set to the specified column value." - from manual

If this is not possible, then you can use a variable in the ALTER. You might do that using prepared statement:

SET @query = CONCAT('ALTER TABLE accounts AUTO_INCREMENT = ', @autoinc, ';');
PREPARE stmt FROM @query;
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;

Related Solutions

Mysql – ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB

Are you sure you still have an error with the following script ?

change :

CREATE TABLE IF NOT EXISTS `mydb`.`Order_has_Product` (
  `Order_id` INT{+ NOT+} NULL,
  `Product_product_id` INT{+ NOT+} NULL,

(it work on my MySQL 5.7, I don't have any mariadb to try now)

-- MySQL Script generated by MySQL Workbench
-- Thu 12 Oct 2017 01:11:28 PM EDT
-- Model: New Model    Version: 1.0    
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
-- -----------------------------------------------------
-- Schema customer
-- -----------------------------------------------------
-- This schema was created for a stub table

-- -----------------------------------------------------
-- Schema customer
--
-- This schema was created for a stub table
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `customer` ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`Product`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Product` (
  `product_id` INT NOT NULL AUTO_INCREMENT,
  `Product_name` VARCHAR(45) NULL,
  `color` VARCHAR(45) NULL,
  `cost` VARCHAR(45) NULL,
  `price` INT NULL,
  `catagory` VARCHAR(45) NULL,
  `description` VARCHAR(45) NULL,
  `stock` VARCHAR(3) NULL,
  PRIMARY KEY (`product_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`customer` (
  `name` VARCHAR(45) NULL,
  `address` VARCHAR(45) NULL,
  `city` VARCHAR(45) NULL,
  `country` VARCHAR(45) NULL,
  `customer_id` VARCHAR(45) NOT NULL,
  `state` VARCHAR(2) NULL,
  `zip` INT NULL,
  `credit_card` BIGINT(17) NULL,
  PRIMARY KEY (`customer_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Order`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Order` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `price` INT NULL,
  `date` DATE NULL,
  `shipping_date` DATE NULL,
  `completed` VARCHAR(3) NULL,
  `urgent_order` VARCHAR(3) NULL,
  `vip` VARCHAR(3) NULL,
  `quantity` INT NULL,
  `product_id` INT NULL,
  `repeat_order` VARCHAR(3) NULL,
  `customer_customer_id` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Order_customer1_idx` (`customer_customer_id` ASC),
  CONSTRAINT `fk_Order_customer1`
    FOREIGN KEY (`customer_customer_id`)
    REFERENCES `mydb`.`customer` (`customer_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`review`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`review` (
  `review_id` INT NOT NULL AUTO_INCREMENT,
  `rating` INT NULL,
  `review` VARCHAR(45) NULL,
  `order_id` INT NULL,
  `date` DATE NULL,
  `Product_product_id` INT NULL,
  PRIMARY KEY (`review_id`),
  INDEX `fk_review_Product1_idx` (`Product_product_id` ASC),
  CONSTRAINT `fk_review_Product1`
    FOREIGN KEY (`Product_product_id`)
    REFERENCES `mydb`.`Product` (`product_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Order_has_Product`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Order_has_Product` (
  `Order_id` INT NOT NULL,
  `Product_product_id` INT NOT NULL,
  PRIMARY KEY (`Order_id`, `Product_product_id`),
  INDEX `fk_Order_has_Product_Product1_idx` (`Product_product_id` ASC),
  INDEX `fk_Order_has_Product_Order1_idx` (`Order_id` ASC),
  CONSTRAINT `fk_Order_has_Product_Order1`
    FOREIGN KEY (`Order_id`)
    REFERENCES `mydb`.`Order` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Order_has_Product_Product1`
    FOREIGN KEY (`Product_product_id`)
    REFERENCES `mydb`.`Product` (`product_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

USE `customer` ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Mysql – Can MariaDB or MySQL cast as bigint, getting an error: … right syntax to use near ‘bigint)’

Silently? Let's try:

CREATE TABLE g
   AS SELECT CAST('2147483699' AS int);

MariaDB 10.1.32, default sql_mode (non-strict)

Query OK, 1 row affected, 1 warning (0.19 sec) Records: 1 Duplicates: 0 Warnings: 1

And the warning is:

| Warning | 1264 | Out of range value for column 'CAST('2147483699' AS int)' at row 1 |

Yes, selecting from the table is interesting, but not surprising given the warning.

(I don't have a MariaDB 10.2 instance at the moment, but testing on dbfiddle.uk indicates it too fails, though again not silently.)

MariaDB 10.3.6, default sql_mode (strict)

No warnings.

select * from g;

Gives:

+---------------------------+
| CAST('2147483699' AS int) |
+---------------------------+
|                2147483699 |
+---------------------------+
1 row in set (0.000 sec)

And the column is indeed created as bigint(10).

MySQL 8.0, both the default strict and non-strict sql_mode

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int)' at line 2

Computer says no.

Conclusion

Can MariaDB or MySQL cast as bigint? As we have seen, yes, MariaDB 10.3 can (though it's not GA yet ... but surely any day now it will be). However, it can only cast as bigint implicitly. You can't explicitly cast as bigint, just like you can't cast as varchar (but see MDEV-11283), text, float, tinyint etcetera. There are only a limited number of datatypes you can cast to. This is not well documented, but CAST appears to support only the same datatypes as listed for the CONVERT function.