Pass SELECT MAX(`Id`) FROM Table to setval()

Question

I want to pass (SELECT MAX(Id) FROM Table to mariadb's setval() function I tried with:

SELECT setval(`MySequence`, (SELECT MAX(`Id`) FROM `Table`));

but it doesn't work, I also tried:

SET @max_value = (SELECT MAX(`Id`) FROM `Table`);
SELECT setval(`MySequence`, @max_value);

how am I supposed to do this?

EDIT I made a mistake posting the question. I was using SET on the second code and is not working

EDIT As I said on the comments I'm trying to do this just once, executing from an Entity Framework Core migration. What I ended doing is executing the SELECT MAX(Id) FROM Table and recovering that value from the migration code to interpolate it later on the $"SELECT setval('sequence', {value}, true)"

Caution! -- If another thread sneaks in, it can get the `Max(Id)` that you think you are getting. Don't do sequences this way! See `AUTO_INCREMENT` — Rick James, Jul 20 '19 at 19:14
Thanks, it’s a migration. It’s going to be executed once, after a large upgrade on the table is going to be acting. — Pablo Recalde, Jul 20 '19 at 19:21

score 2 · Answer 1 · answered Jul 20 '19 at 13:02

2

In a select, use := to assign variables:

SELECT @max_value := MAX(`Id`) FROM `Table`;
SELECT setval(`MySequence`, @max_value);

You might want to add 1 to the value.

I think you can do:

SELECT setval(`MySequence`, MAX(Id))
FROM `Table`;

answered Jul 20 '19 at 13:02

Gordon Linoff

1,242,037
58
646
786

I’m going to try the second option and let you know. I was using SET but I posted it wrong – Pablo Recalde Jul 20 '19 at 19:20
Also, I append true as third argument to setval() so it knows it has to give the next value. – Pablo Recalde Jul 20 '19 at 19:27
I tested it, and it doesn't work either :( 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 'MAX(Id)) FROM `table`' – Pablo Recalde Jul 20 '19 at 21:15
@PabloRecalde . . . It works in db<>fiddle: https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=f87eeba580d767e9e5aba6fdc71e811f. – Gordon Linoff Jul 20 '19 at 21:19
I'm afraid it doesn't https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=68f4b8bf5990e58e75ef24504fa901e0 – Pablo Recalde Jul 20 '19 at 21:22
I mean I tested it first on my database and got the same error. I'm starting to think that this is not possible with the actual sequence operators implementation, I've tried doing `ALTER` and even `CREATE` a new sequence and it does not seem to like inner queries, nor variables. – Pablo Recalde Jul 20 '19 at 21:30
@PabloRecalde . . . That error is an artifact because `@max_value` is not defined in the different code parts of the window. That is why my db<>fiddle does work and yours does not. – Gordon Linoff Jul 20 '19 at 21:34
It is in fact defined: https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=f5bd09f4854bc5f94f43624a7a8b3ce9 – Pablo Recalde Jul 20 '19 at 21:36

score 2 · Accepted Answer · answered Dec 02 '21 at 15:38

2

I found working workaround using prepared statement:

SET @max_value = (SELECT MAX(`Id`) FROM `Table`);
EXECUTE IMMEDIATE CONCAT('SELECT SETVAL(`MySequence`, ', @max_value, ')');

answered Dec 02 '21 at 15:38

Milan Majer

594
6
8

I'm no longer working on this project, the question is from 2y ago but, this does work indeed. https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=f15b42ce025cb78a3411c9a4213a32a8 I'm marking your answer as accepted. Thank you. – Pablo Recalde Dec 03 '21 at 08:16
Bonus: EXECUTE IMMEDIATE also works for recreating the SEQUENCE if anyone wants to change the start value. EXECUTE IMMEDIATE CONCAT('CREATE SEQUENCE `my_seq` start with ', @max_value, ' minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 0 nocycle'); Thanks – GabrielOshiro May 18 '22 at 16:25

score 1 · Answer 3 · answered Jul 20 '19 at 13:02

1

In a stand-alone statement (not a query), SET is generally used to assign value to a user-defined variable. Try the following instead:

SET @max_value = (SELECT MAX(`Id`) FROM `Table`);
SELECT setval(`MySequence`, @max_value);

answered Jul 20 '19 at 13:02

Madhur Bhaiya

28,155
10
49
57

Yeah I was in hurry posting, and misspelled SET. It does not work either. – Pablo Recalde Jul 20 '19 at 19:19
@PabloRecalde it seems that setval() function works only with integer literals, and not expressions/variables. Refer: https://mariadb.com/kb/en/library/setval/ – Madhur Bhaiya Jul 21 '19 at 06:59
I've been there and its not explicit about whether or not you can use them. – Pablo Recalde Jul 21 '19 at 11:01
not even the CRATE or ALTER Sequence documentation says that you can or can not use inner queries/variables but it seems that in fact you can't. – Pablo Recalde Jul 21 '19 at 12:16

Pablo Recalde · Answer 4 · 2019-07-21T13:24:47.777

It seems that is not possible to do this as of MariaDB 10.3.16

I've raised a bug on https://jira.mariadb.org/browse/MDEV-20111 for devs to consider adding this feature or upgrading the documentation to make explicit that it can't be done.

I worked arround this by selecting the value using the Mysqlconnector in c# code

private long ReadMaxIdFromTable()
{

    MySqlConnection connection = null;
    try
    {
        var environment = Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT");



        var builder = new ConfigurationBuilder();
        var builderenv = builder.AddJsonFile("config/appsettings.json", optional: false, reloadOnChange: false)
                         .AddJsonFile($"config/appsettings.{environment}.json", false, false).AddEnvironmentVariables();

        IConfigurationRoot configuration = builderenv.Build();
        var connectionString = configuration.GetConnectionString("ConnectionStringName");

        connection = new MySqlConnection(connectionString);
        connection.Open();
        var cmd = connection.CreateCommand() as MySqlCommand;
        cmd.CommandText = @"SELECT MAX(`Id`) FROM `Table`";
        var result = (long)cmd.ExecuteScalar();
        return result;
    }
    catch (Exception)
    {
        throw;
    }
    finally
    {
        if (connection != null && connection.State != System.Data.ConnectionState.Closed)
        {
            connection.Close();
        }
    }
}

Is not as clean as I would like it but, it gets the job done.

Later I use SETVAL interpolating the value in the sql string again from c# code.

 var currentSeq = ReadMaxIdFromTable();
 migrationBuilder.Sql($"SELECT SETVAL(`MySequence`, {currentSeq}, true);");

Also, beware that all the Up() code it's executed BEFORE anything gets to the database, this means that SELECT MAX(Id) FROM Table; has to result in the value we're looking for before the migration starts manipulating the database.

Pass SELECT MAX(`Id`) FROM Table to setval()

4 Answers4