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)"
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.
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`;