1
/*****************************************************************************\
2
* as_mysql_convert.c - functions dealing with converting from tables in
4
*****************************************************************************
6
* Copyright (C) 2004-2007 The Regents of the University of California.
7
* Copyright (C) 2008-2010 Lawrence Livermore National Security.
8
* Produced at Lawrence Livermore National Laboratory (cf, DISCLAIMER).
9
* Written by Danny Auble <da@llnl.gov>
11
* This file is part of SLURM, a resource management program.
12
* For details, see <https://computing.llnl.gov/linux/slurm/>.
13
* Please also read the included file: DISCLAIMER.
15
* SLURM is free software; you can redistribute it and/or modify it under
16
* the terms of the GNU General Public License as published by the Free
17
* Software Foundation; either version 2 of the License, or (at your option)
20
* In addition, as a special exception, the copyright holders give permission
21
* to link the code of portions of this program with the OpenSSL library under
22
* certain conditions as described in each individual source file, and
23
* distribute linked combinations including the two. You must obey the GNU
24
* General Public License in all respects for all of the code used other than
25
* OpenSSL. If you modify file(s) with this exception, you may extend this
26
* exception to your version of the file(s), but you are not obligated to do
27
* so. If you do not wish to do so, delete this exception statement from your
28
* version. If you delete this exception statement from all source files in
29
* the program, then also delete it here.
31
* SLURM is distributed in the hope that it will be useful, but WITHOUT ANY
32
* WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
33
* FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
36
* You should have received a copy of the GNU General Public License along
37
* with SLURM; if not, write to the Free Software Foundation, Inc.,
38
* 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
39
\*****************************************************************************/
41
#include "as_mysql_convert.h"
43
static bool assocs=0, events=0, jobs=0, last_ran=0, resvs=0, steps=0,
44
suspends=0, usage=0, wckeys=0;
46
static int converted = 0;
47
static pthread_mutex_t converted_lock = PTHREAD_MUTEX_INITIALIZER;
48
static pthread_cond_t converted_cond;
50
static void *_convert_cluster_tables(void *arg)
52
char *cluster_name = (char *)arg;
54
int rc = SLURM_SUCCESS;
56
MYSQL_RES *result = NULL;
58
mysql_conn_t mysql_conn;
60
memset(&mysql_conn, 0, sizeof(mysql_conn_t));
61
mysql_conn.rollback = 1;
62
slurm_mutex_init(&mysql_conn.lock);
63
/* each thread needs it's own connection */
64
rc = check_connection(&mysql_conn);
65
if (rc != SLURM_SUCCESS)
70
info("Converting old association tables for %s, "
71
"this may take some time, please do not restart.",
74
"insert into \"%s_%s\" (creation_time, "
75
"mod_time, deleted, id_assoc, user, "
76
"acct, partition, parent_acct, lft, "
77
"rgt, shares, max_jobs, max_submit_jobs, "
78
"max_cpus_pj, max_nodes_pj, max_wall_pj, "
79
"max_cpu_mins_pj, grp_jobs, "
80
"grp_submit_jobs, grp_cpus, grp_nodes, "
81
"grp_wall, grp_cpu_mins, qos, delta_qos) "
82
"select creation_time, mod_time, deleted, "
83
"id, user, acct, partition, "
84
"parent_acct, lft, rgt, fairshare, "
85
"max_jobs, max_submit_jobs, "
86
"max_cpus_per_job, max_nodes_per_job, "
87
"max_wall_duration_per_job, "
88
"max_cpu_mins_per_job, grp_jobs, "
89
"grp_submit_jobs, grp_cpus, grp_nodes, "
90
"grp_wall, grp_cpu_mins, qos, delta_qos "
91
"from %s where cluster='%s' "
92
"on duplicate key update "
93
"deleted=VALUES(deleted);",
94
cluster_name, assoc_table,
95
"assoc_table_old", cluster_name);
96
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
97
rc = mysql_db_query(&mysql_conn, query);
99
if (rc != SLURM_SUCCESS) {
100
error("Couldn't update assoc table correctly");
104
query = xstrdup_printf("select lft from \"%s_%s\" "
105
"where acct='root' and user=''",
106
cluster_name, assoc_table);
107
if (!(result = mysql_db_query_ret(&mysql_conn, query, 0))) {
114
if (!(row = mysql_fetch_row(result))) {
115
mysql_free_result(result);
116
error("Couldn't find root association "
117
"for cluster %s", cluster_name);
121
diff = slurm_atoul(row[0]) - 1;
122
mysql_free_result(result);
124
error("lft was %d that can't happen!", diff+1);
129
/* This will set the lft and rgts back as if
130
these were the first cluster added to the
133
query = xstrdup_printf("update \"%s_%s\" set "
134
"lft=(lft-%d), rgt=(rgt-%d)",
135
cluster_name, assoc_table,
137
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
138
rc = mysql_db_query(&mysql_conn, query);
140
if (rc != SLURM_SUCCESS) {
141
error("Couldn't update assoc table correctly");
146
"insert into \"%s_%s\" "
148
"mod_time, deleted, id_assoc, "
149
"time_start, alloc_cpu_secs) "
150
"select t1.creation_time, t1.mod_time, "
151
"t1.deleted, id, period_start, "
153
"from %s as t1, \"%s_%s\" as t2 where "
154
"(t1.id=t2.id_assoc) "
155
"on duplicate key update "
156
"deleted=VALUES(deleted), "
157
"time_start=VALUES(time_start);",
158
cluster_name, assoc_day_table,
159
"assoc_day_usage_table_old",
160
cluster_name, assoc_table);
162
"insert into \"%s_%s\" "
164
"mod_time, deleted, id_assoc, "
165
"time_start, alloc_cpu_secs) "
166
"select t1.creation_time, t1.mod_time, "
167
"t1.deleted, id, period_start, "
169
"from %s as t1, \"%s_%s\" as t2 where "
170
"(t1.id=t2.id_assoc) "
171
"on duplicate key update "
172
"deleted=VALUES(deleted), "
173
"time_start=VALUES(time_start);",
174
cluster_name, assoc_hour_table,
175
"assoc_hour_usage_table_old",
176
cluster_name, assoc_table);
178
"insert into \"%s_%s\" "
180
"mod_time, deleted, id_assoc, "
181
"time_start, alloc_cpu_secs) "
182
"select t1.creation_time, t1.mod_time, "
183
"t1.deleted, id, period_start, "
185
"from %s as t1, \"%s_%s\" as t2 where "
186
"(t1.id=t2.id_assoc) "
187
"on duplicate key update "
188
"deleted=VALUES(deleted), "
189
"time_start=VALUES(time_start);",
190
cluster_name, assoc_month_table,
191
"assoc_month_usage_table_old",
192
cluster_name, assoc_table);
193
debug4("(%s:%d) query\n%s",
194
THIS_FILE, __LINE__, query);
195
rc = mysql_db_query(&mysql_conn, query);
197
if (rc != SLURM_SUCCESS) {
198
error("Couldn't update assoc usage "
205
info("Converting old event table for %s, "
206
"this may take some time, please do not restart.",
208
query = xstrdup_printf(
209
"insert into \"%s_%s\" (node_name, cpu_count, "
210
"state, time_start, time_end, reason, "
211
"reason_uid, cluster_nodes) "
212
"select node_name, cpu_count, state, "
213
"period_start, period_end, reason, "
214
"reason_uid, cluster_nodes from %s where "
215
"cluster='%s' on duplicate key update "
216
"time_start=VALUES(time_start), "
217
"time_end=VALUES(time_end);",
218
cluster_name, event_table,
219
"cluster_event_table_old", cluster_name);
220
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
221
rc = mysql_db_query(&mysql_conn, query);
223
if (rc != SLURM_SUCCESS) {
224
error("Couldn't update event table correctly");
230
info("Converting old job table for %s, "
231
"this may take some time, please do not restart.",
233
query = xstrdup_printf(
234
"insert into \"%s_%s\" (job_db_inx, "
236
"cpus_req, cpus_alloc, exit_code, job_name, "
237
"id_assoc, id_block, id_job, id_qos, id_resv, "
238
"id_wckey, id_user, id_group, kill_requid, "
239
"nodelist, nodes_alloc, node_inx, "
240
"partition, priority, state, timelimit, "
241
"time_submit, time_eligible, time_start, "
242
"time_end, time_suspended, track_steps, wckey) "
243
"select id, deleted, account, req_cpus, "
244
"alloc_cpus, comp_code, name, associd, "
245
"blockid, jobid, qos, resvid, wckeyid, uid, gid, "
246
"kill_requid, nodelist, alloc_nodes, "
247
"node_inx, partition, priority, state, "
248
"timelimit, submit, eligible, start, end, "
249
"suspended, track_steps, wckey from %s where "
250
"cluster='%s' on duplicate key update "
251
"deleted=VALUES(deleted), "
252
"time_start=VALUES(time_start), "
253
"time_end=VALUES(time_end);",
254
cluster_name, job_table,
255
"job_table_old", cluster_name);
256
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
257
rc = mysql_db_query(&mysql_conn, query);
259
if (rc != SLURM_SUCCESS) {
260
error("Couldn't update job table correctly");
266
info("Converting old last ran table for %s, "
267
"this may take some time, please do not restart.",
269
query = xstrdup_printf(
270
"insert into \"%s_%s\" (hourly_rollup, daily_rollup, "
272
"select hourly_rollup, daily_rollup, "
273
"monthly_rollup from %s on duplicate key update "
274
"hourly_rollup=VALUES(hourly_rollup), "
275
"daily_rollup=VALUES(daily_rollup), "
276
"monthly_rollup=VALUES(monthly_rollup);",
277
cluster_name, last_ran_table,
278
"last_ran_table_old");
279
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
280
rc = mysql_db_query(&mysql_conn, query);
282
if (rc != SLURM_SUCCESS) {
283
error("Couldn't update last ran table correctly");
289
info("Converting old reservation table for %s, "
290
"this may take some time, please do not restart.",
292
query = xstrdup_printf(
293
"insert into \"%s_%s\" (id_resv, "
294
"deleted, assoclist, "
295
"cpus, flags, nodelist, node_inx, "
296
"resv_name, time_start, time_end) "
297
"select id, deleted, assoclist, cpus, "
298
"flags, nodelist, node_inx, name, start, end "
299
"from %s where cluster='%s' "
300
"on duplicate key update "
301
"deleted=VALUES(deleted), "
302
"time_start=VALUES(time_start), "
303
"time_end=VALUES(time_end);",
304
cluster_name, resv_table,
305
"resv_table_old", cluster_name);
306
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
307
rc = mysql_db_query(&mysql_conn, query);
309
if (rc != SLURM_SUCCESS) {
310
error("Couldn't update reserve "
317
info("Converting old step table for %s, "
318
"this may take some time, please do not restart.",
320
query = xstrdup_printf(
321
"insert into \"%s_%s\" (job_db_inx, "
322
"deleted, cpus_alloc, "
323
"exit_code, id_step, kill_requid, nodelist, "
324
"nodes_alloc, node_inx, state, step_name, "
325
"task_cnt, task_dist, time_start, time_end, "
326
"time_suspended, user_sec, user_usec, "
327
"sys_sec, sys_usec, max_pages, "
328
"max_pages_task, max_pages_node, ave_pages, "
329
"max_rss, max_rss_task, max_rss_node, "
330
"ave_rss, max_vsize, max_vsize_task, "
331
"max_vsize_node, ave_vsize, min_cpu, "
332
"min_cpu_task, min_cpu_node, ave_cpu) "
333
"select id, t1.deleted, cpus, "
334
"comp_code, stepid, t1.kill_requid, t1.nodelist, "
335
"nodes, t1.node_inx, t1.state, name, tasks, "
336
"task_dist, start, end, suspended, user_sec, "
337
"user_usec, sys_sec, sys_usec, max_pages, "
338
"max_pages_task, max_pages_node, ave_pages, "
339
"max_rss, max_rss_task, max_rss_node, "
340
"ave_rss, max_vsize, max_vsize_task, "
341
"max_vsize_node, ave_vsize, min_cpu, "
342
"min_cpu_task, min_cpu_node, ave_cpu "
343
"from %s as t1, \"%s_%s\" as t2 where "
344
"(t2.job_db_inx=t1.id) on duplicate key update "
345
"deleted=VALUES(deleted), "
346
"time_start=VALUES(time_start), "
347
"time_end=VALUES(time_end);",
348
cluster_name, step_table,
349
"step_table_old", cluster_name, job_table);
350
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
351
rc = mysql_db_query(&mysql_conn, query);
353
if (rc != SLURM_SUCCESS) {
354
error("Couldn't update step table correctly");
360
info("Converting old suspend table for %s, "
361
"this may take some time, please do not restart.",
363
query = xstrdup_printf(
364
"insert into \"%s_%s\" (job_db_inx, id_assoc, "
365
"time_start, time_end) "
366
"select id, associd, start, end "
367
"from %s as t1, \"%s_%s\" as t2 where "
368
"(t2.job_db_inx=t1.id) on duplicate key update "
369
"time_start=VALUES(time_start), "
370
"time_end=VALUES(time_end);",
371
cluster_name, suspend_table,
372
"suspend_table_old", cluster_name, job_table);
373
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
374
rc = mysql_db_query(&mysql_conn, query);
376
if (rc != SLURM_SUCCESS) {
377
error("Couldn't update suspend "
384
info("Converting old usage tables for %s, "
385
"this may take some time, please do not restart.",
387
query = xstrdup_printf(
388
"insert into \"%s_%s\" "
389
"(creation_time, mod_time, "
390
"deleted, time_start, cpu_count, "
391
"alloc_cpu_secs, down_cpu_secs, "
392
"pdown_cpu_secs, idle_cpu_secs, "
393
"resv_cpu_secs, over_cpu_secs) "
394
"select creation_time, mod_time, deleted, "
395
"period_start, cpu_count, alloc_cpu_secs, "
396
"down_cpu_secs, pdown_cpu_secs, "
397
"idle_cpu_secs, resv_cpu_secs, over_cpu_secs "
398
"from %s where cluster='%s' "
399
"on duplicate key update "
400
"deleted=VALUES(deleted), "
401
"time_start=VALUES(time_start);",
402
cluster_name, cluster_day_table,
403
"cluster_day_usage_table_old", cluster_name);
405
"insert into \"%s_%s\" (creation_time, "
406
"mod_time, deleted, time_start, cpu_count, "
407
"alloc_cpu_secs, down_cpu_secs, "
408
"pdown_cpu_secs, idle_cpu_secs, "
409
"resv_cpu_secs, over_cpu_secs) "
410
"select creation_time, mod_time, deleted, "
411
"period_start, cpu_count, alloc_cpu_secs, "
412
"down_cpu_secs, pdown_cpu_secs, "
413
"idle_cpu_secs, resv_cpu_secs, "
415
"from %s where cluster='%s' "
416
"on duplicate key update "
417
"deleted=VALUES(deleted), "
418
"time_start=VALUES(time_start);",
419
cluster_name, cluster_hour_table,
420
"cluster_hour_usage_table_old", cluster_name);
422
"insert into \"%s_%s\" (creation_time, "
423
"mod_time, deleted, time_start, cpu_count, "
424
"alloc_cpu_secs, down_cpu_secs, "
425
"pdown_cpu_secs, idle_cpu_secs, "
426
"resv_cpu_secs, over_cpu_secs) "
427
"select creation_time, mod_time, deleted, "
428
"period_start, cpu_count, alloc_cpu_secs, "
429
"down_cpu_secs, pdown_cpu_secs, "
430
"idle_cpu_secs, resv_cpu_secs, "
432
"from %s where cluster='%s' "
433
"on duplicate key update "
434
"deleted=VALUES(deleted), "
435
"time_start=VALUES(time_start);",
436
cluster_name, cluster_month_table,
437
"cluster_month_usage_table_old", cluster_name);
438
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
439
rc = mysql_db_query(&mysql_conn, query);
441
if (rc != SLURM_SUCCESS) {
442
error("Couldn't update cluster usage "
449
info("Converting old wckey tables for %s, "
450
"this may take some time, please do not restart.",
453
"insert into \"%s_%s\" (creation_time, "
454
"mod_time, deleted, id_wckey, wckey_name, "
456
"select creation_time, mod_time, deleted, "
458
"from %s where cluster='%s' "
459
"on duplicate key update "
460
"deleted=VALUES(deleted);",
461
cluster_name, wckey_table,
462
"wckey_table_old", cluster_name);
463
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
464
rc = mysql_db_query(&mysql_conn, query);
466
if (rc != SLURM_SUCCESS) {
467
error("Couldn't update wckey table correctly");
472
"insert into \"%s_%s\" "
474
"mod_time, deleted, id_wckey, "
475
"time_start, alloc_cpu_secs, "
476
"resv_cpu_secs, over_cpu_secs) "
477
"select t1.creation_time, t1.mod_time, "
478
"t1.deleted, id, period_start, "
479
"alloc_cpu_secs, resv_cpu_secs, "
481
"from %s as t1, \"%s_%s\" as t2 where "
482
"(t1.id=t2.id_wckey) "
483
"on duplicate key update "
484
"deleted=VALUES(deleted), "
485
"time_start=VALUES(time_start);",
486
cluster_name, wckey_day_table,
487
"wckey_day_usage_table_old",
488
cluster_name, wckey_table);
490
"insert into \"%s_%s\" "
492
"mod_time, deleted, id_wckey, "
493
"time_start, alloc_cpu_secs, "
494
"resv_cpu_secs, over_cpu_secs) "
495
"select t1.creation_time, t1.mod_time, "
496
"t1.deleted, id, period_start, "
497
"alloc_cpu_secs, resv_cpu_secs, "
499
"from %s as t1, \"%s_%s\" as t2 where "
500
"(t1.id=t2.id_wckey) "
501
"on duplicate key update "
502
"deleted=VALUES(deleted), "
503
"time_start=VALUES(time_start);",
504
cluster_name, wckey_hour_table,
505
"wckey_hour_usage_table_old",
506
cluster_name, wckey_table);
508
"insert into \"%s_%s\" "
510
"mod_time, deleted, id_wckey, "
511
"time_start, alloc_cpu_secs, "
512
"resv_cpu_secs, over_cpu_secs) "
513
"select t1.creation_time, t1.mod_time, "
514
"t1.deleted, id, period_start, "
515
"alloc_cpu_secs, resv_cpu_secs, "
517
"from %s as t1, \"%s_%s\" as t2 where "
518
"(t1.id=t2.id_wckey) "
519
"on duplicate key update "
520
"deleted=VALUES(deleted), "
521
"time_start=VALUES(time_start);",
522
cluster_name, wckey_month_table,
523
"wckey_month_usage_table_old",
524
cluster_name, wckey_table);
525
debug4("(%s:%d) query\n%s",
526
THIS_FILE, __LINE__, query);
527
rc = mysql_db_query(&mysql_conn, query);
529
if (rc != SLURM_SUCCESS) {
530
error("Couldn't update wckey usage "
536
/* now setup the defaults */
537
if (assocs || wckeys)
538
as_mysql_convert_user_defs(&mysql_conn, cluster_name);
542
if (rc == SLURM_SUCCESS) {
543
if (mysql_db_commit(&mysql_conn)) {
544
error("Commit failed, didn't update cluster %s",
548
verbose("Cluster %s updated", cluster_name);
550
verbose("Cluster %s update failed", cluster_name);
551
if (mysql_db_rollback(&mysql_conn))
552
error("rollback failed");
555
mysql_db_close_db_connection(&mysql_conn);
556
slurm_mutex_destroy(&mysql_conn.lock);
558
slurm_mutex_lock(&converted_lock);
560
pthread_cond_signal(&converted_cond);
561
slurm_mutex_unlock(&converted_lock);
566
extern int as_mysql_convert_tables(mysql_conn_t *mysql_conn)
568
storage_field_t assoc_table_fields_2_1[] = {
569
{ "creation_time", "int unsigned not null" },
570
{ "mod_time", "int unsigned default 0 not null" },
571
{ "deleted", "tinyint default 0" },
572
{ "id", "int not null auto_increment" },
573
{ "user", "tinytext not null default ''" },
574
{ "acct", "tinytext not null" },
575
{ "cluster", "tinytext not null" },
576
{ "partition", "tinytext not null default ''" },
577
{ "parent_acct", "tinytext not null default ''" },
578
{ "lft", "int not null" },
579
{ "rgt", "int not null" },
580
{ "fairshare", "int default 1 not null" },
581
{ "max_jobs", "int default NULL" },
582
{ "max_submit_jobs", "int default NULL" },
583
{ "max_cpus_per_job", "int default NULL" },
584
{ "max_nodes_per_job", "int default NULL" },
585
{ "max_wall_duration_per_job", "int default NULL" },
586
{ "max_cpu_mins_per_job", "bigint default NULL" },
587
{ "grp_jobs", "int default NULL" },
588
{ "grp_submit_jobs", "int default NULL" },
589
{ "grp_cpus", "int default NULL" },
590
{ "grp_nodes", "int default NULL" },
591
{ "grp_wall", "int default NULL" },
592
{ "grp_cpu_mins", "bigint default NULL" },
593
{ "qos", "blob not null default ''" },
594
{ "delta_qos", "blob not null default ''" },
598
storage_field_t assoc_usage_table_fields_2_1[] = {
599
{ "creation_time", "int unsigned not null" },
600
{ "mod_time", "int unsigned default 0 not null" },
601
{ "deleted", "tinyint default 0" },
602
{ "id", "int not null" },
603
{ "period_start", "int unsigned not null" },
604
{ "alloc_cpu_secs", "bigint default 0" },
608
storage_field_t cluster_usage_table_fields_2_1[] = {
609
{ "creation_time", "int unsigned not null" },
610
{ "mod_time", "int unsigned default 0 not null" },
611
{ "deleted", "tinyint default 0" },
612
{ "cluster", "tinytext not null" },
613
{ "period_start", "int unsigned not null" },
614
{ "cpu_count", "int default 0" },
615
{ "alloc_cpu_secs", "bigint default 0" },
616
{ "down_cpu_secs", "bigint default 0" },
617
{ "pdown_cpu_secs", "bigint default 0" },
618
{ "idle_cpu_secs", "bigint default 0" },
619
{ "resv_cpu_secs", "bigint default 0" },
620
{ "over_cpu_secs", "bigint default 0" },
624
storage_field_t event_table_fields_2_1[] = {
625
{ "node_name", "tinytext default '' not null" },
626
{ "cluster", "tinytext not null" },
627
{ "cpu_count", "int not null" },
628
{ "state", "smallint unsigned default 0 not null" },
629
{ "period_start", "int unsigned not null" },
630
{ "period_end", "int unsigned default 0 not null" },
631
{ "reason", "tinytext not null" },
632
{ "reason_uid", "int unsigned default 0xfffffffe not null" },
633
{ "cluster_nodes", "text not null default ''" },
637
storage_field_t job_table_fields_2_1[] = {
638
{ "id", "int not null auto_increment" },
639
{ "deleted", "tinyint default 0" },
640
{ "jobid", "int unsigned not null" },
641
{ "associd", "int unsigned not null" },
642
{ "wckey", "tinytext not null default ''" },
643
{ "wckeyid", "int unsigned not null" },
644
{ "uid", "int unsigned not null" },
645
{ "gid", "int unsigned not null" },
646
{ "cluster", "tinytext not null" },
647
{ "partition", "tinytext not null" },
648
{ "blockid", "tinytext" },
649
{ "account", "tinytext" },
650
{ "eligible", "int unsigned default 0 not null" },
651
{ "submit", "int unsigned default 0 not null" },
652
{ "start", "int unsigned default 0 not null" },
653
{ "end", "int unsigned default 0 not null" },
654
{ "suspended", "int unsigned default 0 not null" },
655
{ "timelimit", "int unsigned default 0 not null" },
656
{ "name", "tinytext not null" },
657
{ "track_steps", "tinyint not null" },
658
{ "state", "smallint unsigned not null" },
659
{ "comp_code", "int default 0 not null" },
660
{ "priority", "int not null" },
661
{ "req_cpus", "int unsigned not null" },
662
{ "alloc_cpus", "int unsigned not null" },
663
{ "alloc_nodes", "int unsigned not null" },
664
{ "nodelist", "text" },
665
{ "node_inx", "text" },
666
{ "kill_requid", "int default -1 not null" },
667
{ "qos", "smallint default 0" },
668
{ "resvid", "int unsigned not null" },
672
storage_field_t last_ran_table_fields_2_1[] = {
673
{ "hourly_rollup", "int unsigned default 0 not null" },
674
{ "daily_rollup", "int unsigned default 0 not null" },
675
{ "monthly_rollup", "int unsigned default 0 not null" },
679
storage_field_t resv_table_fields_2_1[] = {
680
{ "id", "int unsigned default 0 not null" },
681
{ "name", "text not null" },
682
{ "cluster", "text not null" },
683
{ "deleted", "tinyint default 0" },
684
{ "cpus", "int unsigned not null" },
685
{ "assoclist", "text not null default ''" },
686
{ "nodelist", "text not null default ''" },
687
{ "node_inx", "text not null default ''" },
688
{ "start", "int unsigned default 0 not null"},
689
{ "end", "int unsigned default 0 not null" },
690
{ "flags", "smallint unsigned default 0 not null" },
694
storage_field_t step_table_fields_2_1[] = {
695
{ "id", "int not null" },
696
{ "deleted", "tinyint default 0" },
697
{ "stepid", "smallint not null" },
698
{ "start", "int unsigned default 0 not null" },
699
{ "end", "int unsigned default 0 not null" },
700
{ "suspended", "int unsigned default 0 not null" },
701
{ "name", "text not null" },
702
{ "nodelist", "text not null" },
703
{ "node_inx", "text" },
704
{ "state", "smallint unsigned not null" },
705
{ "kill_requid", "int default -1 not null" },
706
{ "comp_code", "int default 0 not null" },
707
{ "nodes", "int unsigned not null" },
708
{ "cpus", "int unsigned not null" },
709
{ "tasks", "int unsigned not null" },
710
{ "task_dist", "smallint default 0" },
711
{ "user_sec", "int unsigned default 0 not null" },
712
{ "user_usec", "int unsigned default 0 not null" },
713
{ "sys_sec", "int unsigned default 0 not null" },
714
{ "sys_usec", "int unsigned default 0 not null" },
715
{ "max_vsize", "bigint unsigned default 0 not null" },
716
{ "max_vsize_task", "smallint unsigned default 0 not null" },
717
{ "max_vsize_node", "int unsigned default 0 not null" },
718
{ "ave_vsize", "double unsigned default 0.0 not null" },
719
{ "max_rss", "bigint unsigned default 0 not null" },
720
{ "max_rss_task", "smallint unsigned default 0 not null" },
721
{ "max_rss_node", "int unsigned default 0 not null" },
722
{ "ave_rss", "double unsigned default 0.0 not null" },
723
{ "max_pages", "int unsigned default 0 not null" },
724
{ "max_pages_task", "smallint unsigned default 0 not null" },
725
{ "max_pages_node", "int unsigned default 0 not null" },
726
{ "ave_pages", "double unsigned default 0.0 not null" },
727
{ "min_cpu", "int unsigned default 0 not null" },
728
{ "min_cpu_task", "smallint unsigned default 0 not null" },
729
{ "min_cpu_node", "int unsigned default 0 not null" },
730
{ "ave_cpu", "double unsigned default 0.0 not null" },
734
storage_field_t suspend_table_fields_2_1[] = {
735
{ "id", "int not null" },
736
{ "associd", "int not null" },
737
{ "start", "int unsigned default 0 not null" },
738
{ "end", "int unsigned default 0 not null" },
742
storage_field_t wckey_table_fields_2_1[] = {
743
{ "creation_time", "int unsigned not null" },
744
{ "mod_time", "int unsigned default 0 not null" },
745
{ "deleted", "tinyint default 0" },
746
{ "id", "int not null auto_increment" },
747
{ "name", "tinytext not null default ''" },
748
{ "cluster", "tinytext not null" },
749
{ "user", "tinytext not null" },
753
storage_field_t wckey_usage_table_fields_2_1[] = {
754
{ "creation_time", "int unsigned not null" },
755
{ "mod_time", "int unsigned default 0 not null" },
756
{ "deleted", "tinyint default 0" },
757
{ "id", "int not null" },
758
{ "period_start", "int unsigned not null" },
759
{ "alloc_cpu_secs", "bigint default 0" },
760
{ "resv_cpu_secs", "bigint default 0" },
761
{ "over_cpu_secs", "bigint default 0" },
765
ListIterator itr = NULL;
767
MYSQL_RES *result = NULL;
770
char *drop_query = NULL;
771
int rc = SLURM_ERROR;
773
/* now do associations */
774
query = xstrdup_printf("show tables like '%s';", assoc_table);
776
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
777
if (!(result = mysql_db_query_ret(mysql_conn, query, 0))) {
783
if ((row = mysql_fetch_row(result))) {
784
/* make it up to date */
785
if (mysql_db_create_table(mysql_conn, assoc_table,
786
assoc_table_fields_2_1,
787
", primary key (id), "
788
" unique index (user(20), acct(20), "
789
"cluster(20), partition(20)))")
792
if (mysql_db_create_table(mysql_conn, "assoc_day_usage_table",
793
assoc_usage_table_fields_2_1,
794
", primary key (id, period_start))")
798
if (mysql_db_create_table(mysql_conn, "assoc_hour_usage_table",
799
assoc_usage_table_fields_2_1,
800
", primary key (id, period_start))")
804
if (mysql_db_create_table(mysql_conn, "assoc_month_usage_table",
805
assoc_usage_table_fields_2_1,
806
", primary key (id, period_start))")
812
mysql_free_result(result);
816
query = xstrdup_printf("show tables like 'cluster_event_table';");
817
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
818
if (!(result = mysql_db_query_ret(mysql_conn, query, 0))) {
824
if ((row = mysql_fetch_row(result))) {
825
/* make it up to date */
826
if (mysql_db_create_table(mysql_conn, "cluster_event_table",
827
event_table_fields_2_1,
828
", primary key (node_name(20), "
829
"cluster(20), period_start))")
834
mysql_free_result(result);
838
query = xstrdup_printf("show tables like '%s';", job_table);
840
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
841
if (!(result = mysql_db_query_ret(mysql_conn, query, 0))) {
847
if ((row = mysql_fetch_row(result))) {
848
/* make it up to date */
849
if (mysql_db_create_table(mysql_conn, job_table,
850
job_table_fields_2_1,
851
", primary key (id), "
852
"unique index (jobid, "
858
mysql_free_result(result);
861
/* now do last_ran_table */
862
query = xstrdup_printf("show tables like '%s';", last_ran_table);
864
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
865
if (!(result = mysql_db_query_ret(mysql_conn, query, 0))) {
871
if ((row = mysql_fetch_row(result))) {
872
/* make it up to date */
873
if (mysql_db_create_table(mysql_conn, last_ran_table,
874
last_ran_table_fields_2_1,
879
mysql_free_result(result);
882
/* now do reservations */
883
query = xstrdup_printf("show tables like '%s';", resv_table);
885
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
886
if (!(result = mysql_db_query_ret(mysql_conn, query, 0))) {
892
if ((row = mysql_fetch_row(result))) {
893
/* make it up to date */
894
if (mysql_db_create_table(mysql_conn, resv_table,
895
resv_table_fields_2_1,
896
", primary key (id, start, "
902
mysql_free_result(result);
906
query = xstrdup_printf("show tables like '%s';", step_table);
908
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
909
if (!(result = mysql_db_query_ret(mysql_conn, query, 0))) {
915
if ((row = mysql_fetch_row(result))) {
916
/* make it up to date */
917
if (mysql_db_create_table(mysql_conn, step_table,
918
step_table_fields_2_1,
919
", primary key (id, stepid))")
924
mysql_free_result(result);
927
/* now do suspends */
928
query = xstrdup_printf("show tables like '%s';", suspend_table);
930
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
931
if (!(result = mysql_db_query_ret(mysql_conn, query, 0))) {
937
if ((row = mysql_fetch_row(result))) {
938
/* make it up to date */
939
if (mysql_db_create_table(mysql_conn, suspend_table,
940
suspend_table_fields_2_1,
946
mysql_free_result(result);
950
query = xstrdup("show tables like 'cluster_hour_usage_table';");
952
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
953
if (!(result = mysql_db_query_ret(mysql_conn, query, 0))) {
959
if ((row = mysql_fetch_row(result))) {
960
/* make it up to date */
961
if (mysql_db_create_table(mysql_conn, "cluster_day_usage_table",
962
cluster_usage_table_fields_2_1,
963
", primary key (cluster(20), "
968
if (mysql_db_create_table(mysql_conn,
969
"cluster_hour_usage_table",
970
cluster_usage_table_fields_2_1,
971
", primary key (cluster(20), "
976
if (mysql_db_create_table(mysql_conn,
977
"cluster_month_usage_table",
978
cluster_usage_table_fields_2_1,
979
", primary key (cluster(20), "
986
mysql_free_result(result);
990
query = xstrdup_printf("show tables like '%s';", wckey_table);
992
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
993
if (!(result = mysql_db_query_ret(mysql_conn, query, 0))) {
999
if ((row = mysql_fetch_row(result))) {
1000
/* make it up to date */
1001
if (mysql_db_create_table(mysql_conn, wckey_table,
1002
wckey_table_fields_2_1,
1003
", primary key (id), "
1004
" unique index (name(20), user(20), "
1008
if (mysql_db_create_table(mysql_conn, "wckey_day_usage_table",
1009
wckey_usage_table_fields_2_1,
1010
", primary key (id, period_start))")
1014
if (mysql_db_create_table(mysql_conn, "wckey_hour_usage_table",
1015
wckey_usage_table_fields_2_1,
1016
", primary key (id, period_start))")
1020
if (mysql_db_create_table(mysql_conn, "wckey_month_usage_table",
1021
wckey_usage_table_fields_2_1,
1022
", primary key (id, period_start))")
1027
mysql_free_result(result);
1032
if (!assocs && !events && !jobs && !last_ran && !resvs
1033
&& !steps && !suspends && !usage && !wckeys)
1036
/* Rename the old tables first so we don't have to worry about
1037
clusters actually named "cluster" or "wckey".
1041
drop_query = xstrdup("drop table if exists ");
1043
xstrcat(drop_query, ", ");
1045
query = xstrdup("rename table ");
1047
xstrcat(query, ", ");
1048
xstrfmtcat(drop_query, "%s_old, %s_old, %s_old, %s_old",
1050
"assoc_day_usage_table",
1051
"assoc_hour_usage_table",
1052
"assoc_month_usage_table");
1053
xstrfmtcat(query, "%s to %s_old, "
1054
"%s to %s_old, %s to %s_old, "
1056
assoc_table, assoc_table,
1057
"assoc_day_usage_table",
1058
"assoc_day_usage_table",
1059
"assoc_hour_usage_table",
1060
"assoc_hour_usage_table",
1061
"assoc_month_usage_table",
1062
"assoc_month_usage_table");
1067
drop_query = xstrdup("drop table if exists ");
1069
xstrcat(drop_query, ", ");
1071
query = xstrdup("rename table ");
1073
xstrcat(query, ", ");
1074
xstrcat(drop_query, "cluster_event_table_old");
1076
"cluster_event_table to cluster_event_table_old");
1081
drop_query = xstrdup("drop table if exists ");
1083
xstrcat(drop_query, ", ");
1085
query = xstrdup("rename table ");
1087
xstrcat(query, ", ");
1088
xstrfmtcat(drop_query, "%s_old", job_table);
1089
xstrfmtcat(query, "%s to %s_old", job_table, job_table);
1094
drop_query = xstrdup("drop table if exists ");
1096
xstrcat(drop_query, ", ");
1098
query = xstrdup("rename table ");
1100
xstrcat(query, ", ");
1101
xstrfmtcat(drop_query, "%s_old", last_ran_table);
1102
xstrfmtcat(query, "%s to %s_old",
1103
last_ran_table, last_ran_table);
1108
drop_query = xstrdup("drop table if exists ");
1110
xstrcat(drop_query, ", ");
1112
query = xstrdup("rename table ");
1114
xstrcat(query, ", ");
1115
xstrfmtcat(drop_query, "%s_old", resv_table);
1116
xstrfmtcat(query, "%s to %s_old", resv_table, resv_table);
1121
drop_query = xstrdup("drop table if exists ");
1123
xstrcat(drop_query, ", ");
1125
query = xstrdup("rename table ");
1127
xstrcat(query, ", ");
1128
xstrfmtcat(drop_query, "%s_old", step_table);
1129
xstrfmtcat(query, "%s to %s_old", step_table, step_table);
1134
drop_query = xstrdup("drop table if exists ");
1136
xstrcat(drop_query, ", ");
1138
query = xstrdup("rename table ");
1140
xstrcat(query, ", ");
1141
xstrfmtcat(drop_query, "%s_old", suspend_table);
1142
xstrfmtcat(query, "%s to %s_old", suspend_table, suspend_table);
1147
drop_query = xstrdup("drop table if exists ");
1149
xstrcat(drop_query, ", ");
1151
query = xstrdup("rename table ");
1153
xstrcat(query, ", ");
1154
xstrfmtcat(drop_query, "%s_old, %s_old, %s_old",
1155
"cluster_day_usage_table",
1156
"cluster_hour_usage_table",
1157
"cluster_month_usage_table");
1158
xstrfmtcat(query, "%s to %s_old,"
1159
"%s to %s_old, %s to %s_old",
1160
"cluster_day_usage_table",
1161
"cluster_day_usage_table",
1162
"cluster_hour_usage_table",
1163
"cluster_hour_usage_table",
1164
"cluster_month_usage_table",
1165
"cluster_month_usage_table");
1170
drop_query = xstrdup("drop table if exists ");
1172
xstrcat(drop_query, ", ");
1174
query = xstrdup("rename table ");
1176
xstrcat(query, ", ");
1177
xstrfmtcat(drop_query, "%s_old, %s_old, %s_old, %s_old",
1179
"wckey_day_usage_table",
1180
"wckey_hour_usage_table",
1181
"wckey_month_usage_table");
1182
xstrfmtcat(query, "%s to %s_old,"
1183
"%s to %s_old, %s to %s_old,"
1185
wckey_table, wckey_table,
1186
"wckey_day_usage_table",
1187
"wckey_day_usage_table",
1188
"wckey_hour_usage_table",
1189
"wckey_hour_usage_table",
1190
"wckey_month_usage_table",
1191
"wckey_month_usage_table");
1195
xstrfmtcat(drop_query, ";%s;", query);
1197
info("Renaming old tables with _old behind them.");
1198
rc = mysql_db_query(mysql_conn, drop_query);
1200
if (rc != SLURM_SUCCESS) {
1201
error("Couldn't rename tables");
1206
slurm_mutex_init(&converted_lock);
1207
pthread_cond_init(&converted_cond, NULL);
1209
slurm_mutex_lock(&as_mysql_cluster_list_lock);
1210
/* now convert to new form */
1211
itr = list_iterator_create(as_mysql_total_cluster_list);
1212
while ((cluster_name = list_next(itr))) {
1213
pthread_t convert_tid;
1214
pthread_attr_t convert_attr;
1215
slurm_attr_init(&convert_attr);
1216
/* _convert_cluster_tables(cluster_name); */
1217
if (pthread_create(&convert_tid, &convert_attr,
1218
_convert_cluster_tables,
1219
(void *)cluster_name))
1220
fatal("pthread_create: %m");
1221
slurm_attr_destroy(&convert_attr);
1223
slurm_mutex_lock(&converted_lock);
1225
/* This has to be done after the threads because of a locking
1226
issue on this table. */
1227
list_iterator_reset(itr);
1228
while ((cluster_name = list_next(itr))) {
1229
char *txn_ids = NULL;
1231
/* Since there isn't a cluster name in the
1232
assoc usage tables we need to get all the ids from
1233
the assoc_table and query against that. Since
1234
we can't use the new table yet just use the
1235
old one. This is much faster than
1236
joining the 2 tables together
1237
surprisingly. But we tested it so you
1238
wouldn't have to in the future ;).
1240
query = xstrdup_printf("select id from %s "
1241
"where cluster='%s'",
1242
"assoc_table_old", cluster_name);
1243
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
1244
if (!(result = mysql_db_query_ret(
1245
mysql_conn, query, 0))) {
1252
if (mysql_num_rows(result)) {
1253
while ((row = mysql_fetch_row(result))) {
1255
xstrcat(txn_ids, " || ");
1257
xstrcat(txn_ids, "(");
1259
"(name like '%%id=%s %%' "
1260
"|| name like '%%id=%s)' "
1262
row[0], row[0], row[0]);
1264
xstrcat(txn_ids, ")");
1266
mysql_free_result(result);
1270
query = xstrdup_printf(
1271
"update %s set cluster='%s' where "
1272
"(action= %d || action = %d "
1273
"|| action = %d) && %s;",
1274
txn_table, cluster_name,
1275
DBD_ADD_ASSOCS, DBD_MODIFY_ASSOCS,
1276
DBD_REMOVE_ASSOCS, txn_ids);
1278
debug4("(%s:%d) query\n%s",
1279
THIS_FILE, __LINE__, query);
1280
rc = mysql_db_query(mysql_conn, query);
1282
if (rc != SLURM_SUCCESS) {
1283
error("Couldn't update assoc "
1291
/* Since there isn't a cluster name in the
1292
wckey usage tables we need to get all the ids from
1293
the wckey_table query against that. Since
1294
we can't use the new table yet just use the
1295
old one. This is much faster than
1296
joining the 2 tables together
1297
surprisingly. But we tested it so you
1298
wouldn't have to in the future ;).
1300
query = xstrdup_printf("select id from %s where "
1302
"wckey_table_old", cluster_name);
1303
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
1304
if (!(result = mysql_db_query_ret(
1305
mysql_conn, query, 0))) {
1312
if (mysql_num_rows(result)) {
1313
while ((row = mysql_fetch_row(result))) {
1315
xstrcat(txn_ids, " || ");
1317
xstrcat(txn_ids, "(");
1319
"(name like '%%id=%s %%' "
1320
"|| name like '%%id=%s)' "
1322
row[0], row[0], row[0]);
1324
xstrcat(txn_ids, ")");
1326
mysql_free_result(result);
1329
query = xstrdup_printf(
1330
"update %s set cluster='%s' where "
1331
"(action= %d || action = %d "
1332
"|| action = %d) && %s;",
1333
txn_table, cluster_name,
1334
DBD_ADD_WCKEYS, DBD_MODIFY_WCKEYS,
1335
DBD_REMOVE_WCKEYS, txn_ids);
1337
debug4("(%s:%d) query\n%s",
1338
THIS_FILE, __LINE__, query);
1339
rc = mysql_db_query(mysql_conn, query);
1341
if (rc != SLURM_SUCCESS) {
1342
error("Couldn't update wckey "
1349
list_iterator_destroy(itr);
1350
slurm_mutex_unlock(&as_mysql_cluster_list_lock);
1352
/* Now make some sort of destinction as to what an id is. */
1354
xstrfmtcat(query, "update %s set name=replace(name, 'id=', "
1355
"'id_assoc=') where (action = %d || action = %d);",
1356
txn_table, DBD_MODIFY_ASSOCS, DBD_REMOVE_ASSOCS);
1359
"update %s set name=concat('id_assoc=', name) "
1360
"where action = %d;",
1361
txn_table, DBD_ADD_ASSOCS);
1364
xstrfmtcat(query, "update %s set name=replace(name, 'id=', "
1365
"'id_wckey=') where (action = %d || action = %d);",
1366
txn_table, DBD_MODIFY_WCKEYS, DBD_REMOVE_WCKEYS);
1369
"update %s set name=concat('id_wckey=', name) "
1370
"where action = %d;",
1371
txn_table, DBD_ADD_WCKEYS);
1375
rc = mysql_db_query(mysql_conn, query);
1377
if (rc != SLURM_SUCCESS) {
1378
error("Couldn't replace id='s in txn table");
1383
while (converted < list_count(as_mysql_total_cluster_list)) {
1384
pthread_cond_wait(&converted_cond, &converted_lock);
1385
debug2("Got %d converted", converted);
1387
slurm_mutex_unlock(&converted_lock);
1388
debug2("Everything converted");
1389
slurm_mutex_destroy(&converted_lock);
1390
pthread_cond_destroy(&converted_cond);
1394
mysql_free_result(result);
1399
/* as_mysql_cluster_list_lock needs to be locked before calling this. */
1400
extern int as_mysql_convert_user_defs(mysql_conn_t *mysql_conn,
1404
MYSQL_RES *result = NULL;
1406
time_t now = time(NULL);
1407
int i = 0, rc = SLURM_SUCCESS;
1408
char *user_req_inx[] = {
1420
xassert(cluster_name);
1422
/* See if the old table exist first. If already ran here
1423
default_acct and default_wckey won't exist.
1425
query = xstrdup_printf("show columns from %s where "
1426
"Field='default_acct';",
1429
debug4("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
1430
if (!(result = mysql_db_query_ret(mysql_conn, query, 0))) {
1435
i = mysql_num_rows(result);
1436
mysql_free_result(result);
1440
error("It appears the defaults have already been set before. "
1441
"The user_table no longer has the fields.");
1442
return SLURM_SUCCESS;
1445
info("Updating user/assoc tables for cluster %s defaults.",
1448
xstrfmtcat(query, "select %s", user_req_inx[0]);
1449
for (i=1; i<USER_REQ_COUNT; i++) {
1450
xstrfmtcat(query, ", %s", user_req_inx[i]);
1453
xstrfmtcat(query, " from %s", user_table);
1455
debug3("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
1456
if (!(result = mysql_db_query_ret(mysql_conn, query, 0))) {
1462
while ((row = mysql_fetch_row(result))) {
1463
/* now convert to new form */
1465
"update \"%s_%s\" set mod_time=%ld, is_def=1 "
1466
"where user='%s' && acct='%s';"
1467
"insert into \"%s_%s\" (creation_time, mod_time, "
1468
"wckey_name, user, is_def) values (%ld, %ld, '%s', "
1469
"'%s', 1) on duplicate key "
1470
"update deleted=0, is_def=1, mod_time=%ld;",
1471
cluster_name, assoc_table, (long)now,
1472
row[USER_REQ_NAME], row[USER_REQ_DA],
1473
cluster_name, wckey_table, (long)now, (long)now,
1474
row[USER_REQ_DW], row[USER_REQ_NAME], (long)now);
1476
mysql_free_result(result);
1478
debug3("(%s:%d) query\n%s", THIS_FILE, __LINE__, query);
1479
rc = mysql_db_query(mysql_conn, query);
added
removed
src/plugins/accounting_storage/mysql/as_mysql_convert.c
