Bug 15168

Hi Ole. There is a known issue when upgrading from an earlier release than 10.2.1 to this one or later and using slurm. The fix involves a manual alteration to the slurm database and is easy to perform but does require that you shutdown slurmctld and slurmdbd, make some table changes and then restart those daemons. Take a look at this comment that goes into detail and provides the fix: bug 13562 comment 21.

Let me know if you have more questions about this procedure.

Hi Chad,

Thanks for the answer!  I'm really hoping that the next version of Slurm will have your fixes for MariaDB >= 10.2.1 included as part of the normal upgrade procedure!  I'm not at all a database expert, so I appreciate it if things get done correctly without the risk of me making bad mistakes :-)

(In reply to Chad Vizino from comment #1)
> Hi Ole. There is a known issue when upgrading from an earlier release than
> 10.2.1 to this one or later and using slurm. The fix involves a manual
> alteration to the slurm database and is easy to perform but does require
> that you shutdown slurmctld and slurmdbd, make some table changes and then
> restart those daemons. Take a look at this comment that goes into detail and
> provides the fix: bug 13562 comment 21.
> 
> Let me know if you have more questions about this procedure.

I've read the bug 13562 comment 21 and have some follow-up questions about the database fixes:

1. Can you confirm that this issue is independent of the Slurm version?

2. I don't see any need for shutting down slurmctld while the database fixes are being applied.  The slurmdbd must be shut down, of course, and this means that slurmctld can't communicate with slurmdbd and the database.  IMHO, slurmctld should be able to work happily while work is being done on the database, and resume without problems when slurmdbd is restarted.  Can you please confirm this assumption?

3. I would like to test and document the procedure in bug 13562 comment 21 including every little detail. I'm assuming that the MariaDB 5.5 database will be copied from an old server (EL7, say) to a new server (EL8, say) which has MariaDB 10.3.  I wonder if it's possible to do an in-place upgrade of MariaDB on an old EL7 server, and if this changes anything or introduces additional risks?

Could you please verify that I've understood the database fixing procedure correctly and fully?  This is what I think:

a. Old server: Stop slurmdbd, make a database dump and copy it to the new server.

b. New server: Make sure slurmdbd is stopped. Configure MariaDB 10.3 for Slurm in the usual way[1].  Restore the database dump from the old server[2].

c. Copy the "ALTER TABLE" commands from bug 13562 comment 21 to a file.  Edit the file to replace "sherlock" by my cluster's table name ("niflheim" in our case, see the attachment with our tables).
   
d. Login to the database: mysql -p -u slurm slurm_acct_db

e. Verify the slurm tables:
   show tables;
   and ensure that it looks like the "sherlock" tables.

f. Apply all "ALTER TABLE" commands from the edited file.

g. Verify that the tables have indeed been corrected, for example:
   desc <clustername>_assoc_table;

   Question: How does a corrected table look?

h. Quit; the mysql command.

i. Start slurmdbd from the command line:
   slurmdbd -D -vvvv
   and wait for the output:
   slurmdbd: debug2: Everything rolled up
   Then ctrl-C and restart the slurmdbd service as usual.

j. Reconfigure slurm.conf to set AccountingStorageHost=<new_slurmdbd_server>

k. Restart slurmctld.

I hope this captures everything?  I can test items b-i on a test server.

Thanks,
Ole

References:
[1] https://wiki.fysik.dtu.dk/niflheim/Slurm_database#set-up-mariadb-database
[2] https://wiki.fysik.dtu.dk/niflheim/Slurm_database#restore-of-a-database-backup

Created attachment 27261 [details]
list of database tables

(In reply to Ole.H.Nielsen@fysik.dtu.dk from comment #2)
> 1. Can you confirm that this issue is independent of the Slurm version?
Yes--it is independent of supported slurm versions. The issue is due to a change in MariaDB.

> 2. I don't see any need for shutting down slurmctld while the database fixes
> are being applied.  The slurmdbd must be shut down, of course, and this
> means that slurmctld can't communicate with slurmdbd and the database. 
> IMHO, slurmctld should be able to work happily while work is being done on
> the database, and resume without problems when slurmdbd is restarted.  Can
> you please confirm this assumption?
Yes of course. I believe I only listed that in bug 13562 to be safe at the time but it is not necessary.

> 3. I would like to test and document the procedure in bug 13562 comment 21
> including every little detail. I'm assuming that the MariaDB 5.5 database
> will be copied from an old server (EL7, say) to a new server (EL8, say)
> which has MariaDB 10.3.  I wonder if it's possible to do an in-place upgrade
> of MariaDB on an old EL7 server, and if this changes anything or introduces
> additional risks?
Yes, this is possible but does not change anything--the table change is still needed and requires the same procedure.

> Could you please verify that I've understood the database fixing procedure
> correctly and fully?  This is what I think:
> ...
> I hope this captures everything?  I can test items b-i on a test server.
Since you are willing to test and since you would like to document the procedure, I'd actually suggest a different, easier method that I've used successfully with limited testing. This leverages a check and corrective action of slurmdbd at startup, specifically in _mysql_make_table_current(): If a slurm table doesn't exist in the table "table_defs_table" then an alter will be run on the Slurm table to make it "current" (use the stored table defs in the slurm code). The main advantage of this procedure is that it let's slurm do most of the detail work (backing up the slurm db is recommended to be safe).

* Shutdown slurmdbd
* Drop the table_defs_table. Example: mysql -D <slurm_acct_db> -e "drop table table_defs_table"
  (where <slurm_acct_db> is the StorageLoc value from "sacctmgr show configuration|grep -i storageloc" or from slurmdbd.conf)
* If you would like to see evidence of the action slurmdbd took when starting then set DebugLevel=debug4 in slurmdbd.conf and note the previous value
* Start slurmdbd
* If DebugLevel=debug4 was set, you will see lines logged in slurmdbd.log similar to this one:
  ... debug4: Table "cluster_assoc_table" doesn't exist, adding
  Set DebugLevel back to its previous value and restart slurmdbd (to reduce logging volume)

In this code snippet from _mysql_make_table_current() you can see the log message and the corrective action. Here query holds the string "alter table ..." which will actually do the table correction:

>        if (run_update == 2)
>                debug4("Table %s doesn't exist, adding", table_name);
>        else 
>                debug("Table %s has changed.  Updating...", table_name);
>        if (mysql_db_query(mysql_conn, query)) {
>                xfree(query);
>                return SLURM_ERROR;
>        }
Note that if table_defs_table does not exist at slurmdbd startup, it is first created in mysql_db_create_table() and then reconstructed in the snippet below (again from _mysql_make_table_current() and follows just after the above one)--it appears this table acts as a sort of cache of table definitions so that they don't have to be rerun at every slurmdbd startup--so there is no problem with dropping it and losing anything.

>        query2 = xstrdup_printf("insert into %s (creation_time, "
>                                "mod_time, table_name, definition) "
>                                "values (%ld, %ld, '%s', '%s') "
>                                "on duplicate key update "
>                                "definition='%s', mod_time=%ld;",
>                                table_defs_table, now, now,
>                                table_name, quoted,
>                                quoted, now);
>        xfree(quoted);
>        if (mysql_db_query(mysql_conn, query2)) {
>                xfree(query2);
>                return SLURM_ERROR;
>        }
Let me know how this works out for you on your test system.

(In reply to Chad Vizino from comment #4)
> Since you are willing to test and since you would like to document the
> procedure, I'd actually suggest a different, easier method that I've used
> successfully with limited testing. This leverages a check and corrective
> action of slurmdbd at startup, specifically in _mysql_make_table_current():
> If a slurm table doesn't exist in the table "table_defs_table" then an alter
> will be run on the Slurm table to make it "current" (use the stored table
> defs in the slurm code). The main advantage of this procedure is that it
> let's slurm do most of the detail work (backing up the slurm db is
> recommended to be safe).

Thanks, this procedure is definitely a lot simpler to perform than what's in https://bugs.schedmd.com/show_bug.cgi?id=13562#c21 !

I've taken an AlmaLinux 8.6 (EL8) compute node and configured it as a test database server as described in my Slurm Wiki page https://wiki.fysik.dtu.dk/Niflheim_system/Slurm_installation/#make-a-dry-run-database-upgrade.  I restored our production Slurm database from MariaDB 5.5.  The testing node has MariaDB 10.3:
$ rpm -q mariadb
mariadb-10.3.35-1.module_el8.6.0+3265+230ed96b.x86_64

Then I followed your procedure (more or less):

> * Shutdown slurmdbd
> * Drop the table_defs_table. Example: mysql -D <slurm_acct_db> -e "drop
> table table_defs_table"
>   (where <slurm_acct_db> is the StorageLoc value from "sacctmgr show
> configuration|grep -i storageloc" or from slurmdbd.conf)

$ mysql -p -u slurm slurm_acct_db
Enter password:
drop table table_defs_table;
show tables;
quit;

> * If you would like to see evidence of the action slurmdbd took when
> starting then set DebugLevel=debug4 in slurmdbd.conf and note the previous
> value
> * Start slurmdbd
> * If DebugLevel=debug4 was set, you will see lines logged in slurmdbd.log
> similar to this one:
>   ... debug4: Table "cluster_assoc_table" doesn't exist, adding

Lo and behold, the tables get recreated:

[2022-10-18T13:57:54.705] debug4: Table "niflheim_assoc_table" doesn't exist, adding
[2022-10-18T13:57:54.706] debug4: Table "niflheim_assoc_usage_day_table" doesn't exist, adding
[2022-10-18T13:57:54.707] debug4: Table "niflheim_assoc_usage_hour_table" doesn't exist, adding
[2022-10-18T13:57:54.708] debug4: Table "niflheim_assoc_usage_month_table" doesn't exist, adding
[2022-10-18T13:57:54.709] debug4: Table "niflheim_usage_day_table" doesn't exist, adding
[2022-10-18T13:57:54.710] debug4: Table "niflheim_usage_hour_table" doesn't exist, adding
[2022-10-18T13:57:54.711] debug4: Table "niflheim_usage_month_table" doesn't exist, adding
[2022-10-18T13:57:54.712] debug4: Table "niflheim_event_table" doesn't exist, adding
[2022-10-18T13:57:54.714] debug4: Table "niflheim_job_table" doesn't exist, adding
[2022-10-18T13:57:54.715] debug4: Table "niflheim_last_ran_table" doesn't exist, adding
[2022-10-18T13:57:54.716] debug4: Table "niflheim_resv_table" doesn't exist, adding
[2022-10-18T13:57:54.717] debug4: Table "niflheim_step_table" doesn't exist, adding
[2022-10-18T13:57:54.718] debug4: Table "niflheim_suspend_table" doesn't exist, adding
[2022-10-18T13:57:54.719] debug4: Table "niflheim_wckey_table" doesn't exist, adding
[2022-10-18T13:57:54.720] debug4: Table "niflheim_wckey_usage_day_table" doesn't exist, adding
[2022-10-18T13:57:54.721] debug4: Table "niflheim_wckey_usage_hour_table" doesn't exist, adding
[2022-10-18T13:57:54.722] debug4: Table "niflheim_wckey_usage_month_table" doesn't exist, adding
[2022-10-18T13:57:54.722] debug4: accounting_storage/as_mysql: as_mysql_convert_tables_post_create: as_mysql_convert_tables_post_create: No conversion needed, Horray!
[2022-10-18T13:57:54.723] debug4: Table acct_coord_table doesn't exist, adding
[2022-10-18T13:57:54.724] debug4: Table acct_table doesn't exist, adding
[2022-10-18T13:57:54.725] debug4: Table res_table doesn't exist, adding
[2022-10-18T13:57:54.726] debug4: Table clus_res_table doesn't exist, adding
[2022-10-18T13:57:54.727] debug4: Table qos_table doesn't exist, adding
[2022-10-18T13:57:54.728] debug4: Table user_table doesn't exist, adding
[2022-10-18T13:57:54.729] debug4: Table federation_table doesn't exist, adding

I've attached the full slurmdbd.log file FYI.

The mysql command "show tables;" shows that the table_defs_table exists.

I've documented the procedure in my Slurm Wiki page https://wiki.fysik.dtu.dk/Niflheim_system/Slurm_database/#slurm-database-modifications-required-for-mariadb-10-2-1-and-above

Question:  Are there any additional tests to be done, or can we close this case now?  Can I send a message to the slurm-users list so that other sites will be alerted to this MariaDB upgrade issue?

Thanks a lot,
Ole

Created attachment 27316 [details]
slurmdbd.log file

Hi Ole, Chad brought this question to my attention.

> Question:  Are there any additional tests to be done, or can we close this case 
> now?  Can I send a message to the slurm-users list so that other sites will be 
> alerted to this MariaDB upgrade issue?

I do not have a strong opinion one way or another about this procedure, though mucking with the database by hand has always been discouraged by SchedMD, so I would rather this not be broadcasted unnecessarily. We can not prevent you from mentioning this to the slurm-user, but it would not cause any significate issues with us here.

(In reply to Ole.H.Nielsen@fysik.dtu.dk from comment #5)
> Question:  Are there any additional tests to be done, or can we close this
> case now?  Can I send a message to the slurm-users list so that other sites
> will be alerted to this MariaDB upgrade issue?
Hi Ole. Jason addressed the second question so I'll go ahead and close this--looks like you are in good shape with the database and no further tests are needed.