View Issue Details

IDProjectCategoryView StatusLast Update
0001176bareos-core[All Projects] Generalpublic2020-02-11 17:18
ReporterfrankAssigned Tofrank 
PriorityurgentSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Product Version19.2.5 
Fixed in Version 
Summary0001176: Upgrade 18.2.5 to 19.2.5 failed while upgrading MySQL database
DescriptionUpgrade failed, the database upgrade script put out an error.

ERROR 1064 (42000) at line 6: 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 'IF NOT EXISTS jobtdate_idx ON Job (JobTDate)' at line 1
Failed to upgrade database schema from version 2171 to 2192
Steps To Reproduceroot@backup:/usr/lib/bareos/scripts# ./update_bareos_tables
Updating mysql tables
Upgrading database schema from version 2171 to 2192
ERROR 1064 (42000) at line 6: 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 'IF NOT EXISTS jobtdate_idx ON Job (JobTDate)' at line 1
Failed to upgrade database schema from version 2171 to 2192
root@backup:/usr/lib/bareos/scripts#
TagsNo tags attached.
bareos-master: impactyes
bareos-master: actionfixed
bareos-19.2: impactyes
bareos-19.2: actionfixed
bareos-18.2: impactno
bareos-18.2: action
bareos-17.2: impactno
bareos-17.2: action
bareos-16.2: impactno
bareos-16.2: action
bareos-15.2: impact
bareos-15.2: action
bareos-14.2: impact
bareos-14.2: action
bareos-13.2: impact
bareos-13.2: action
bareos-12.4: impact
bareos-12.4: action

Relationships

related to 0001177 closedarogge Release Bareos 19.2.6 
related to 0001183 closedfrank Error: Connection refused, director seems to be down or blocking our request. 

Activities

teka74

teka74

2020-02-08 00:43

reporter   ~0003754

hellevil9@gmail.com gave me the correct hint, thanks a lot!



edited /usr/lib/bareos/scripts/ddl/updates/mysql.2171_2191.sql

changed line 6

old: CREATE INDEX IF NOT EXISTS jobdate_idx ON Job (JobTDate);

new: CREATE INDEX jobdate_idx ON Job (JobTDate);


now my database upgrade runs without any errors


thanks to hellevil9 !! Thanks thanks thanks


Thomas
frank

frank

2020-02-10 10:44

manager   ~0003758

Some of our customers may already have that specific named index in place, so "CREATE INDEX jobtdate_idx ON Job (JobTDate);" can end up in a duplicate key name error.

"ALTER TABLE Job ADD INDEX (JobTDate);" should be a better alternative for the update script, compatible to MySQL and MariaDB, unfortunately you may end up with multiple indexes and a manual clean up is required.

MariaDB [test]> CREATE INDEX jobtdate_idx ON Job (JobTDate);
ERROR 1061 (42000): Duplicate key name 'jobtdate_idx'
MariaDB [test]> ALTER TABLE Job ADD INDEX (JobTDate);
Query OK, 0 rows affected, 1 warning (0.028 sec)
Records: 0 Duplicates: 0 Warnings: 1

MariaDB [test]> SHOW INDEXES FROM Job;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Job | 0 | PRIMARY | 1 | JobId | A | 0 | NULL | NULL | | BTREE | | |
| Job | 1 | Name | 1 | Name | A | 0 | 128 | NULL | | BTREE | | |
| Job | 1 | JobTDate | 1 | JobTDate | A | 0 | NULL | NULL | YES | BTREE | | |
| Job | 1 | jobtdate_idx | 1 | JobTDate | A | 0 | NULL | NULL | YES | BTREE | | |
| Job | 1 | JobTDate_5 | 1 | JobTDate | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.001 sec)

MariaDB [test]> DROP INDEX JobTDate_5 ON Job;
Query OK, 0 rows affected (0.021 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test]> SHOW INDEXES FROM Job;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Job | 0 | PRIMARY | 1 | JobId | A | 0 | NULL | NULL | | BTREE | | |
| Job | 1 | Name | 1 | Name | A | 0 | 128 | NULL | | BTREE | | |
| Job | 1 | JobTDate | 1 | JobTDate | A | 0 | NULL | NULL | YES | BTREE | | |
| Job | 1 | jobtdate_idx | 1 | JobTDate | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.001 sec)

[...]
dimmko

dimmko

2020-02-10 13:00

reporter   ~0003761

Thanks! It is Work!
frank

frank

2020-02-10 18:22

manager   ~0003767

Fix committed to bareos master branch with changesetid 12820.
frank

frank

2020-02-11 12:22

manager   ~0003778

Fix committed to bareos bareos-19.2 branch with changesetid 12840.
arogge

arogge

2020-02-11 17:18

developer   ~0003782

Fixed in Bareos 19.2.6

Related Changesets

bareos: master fcf73700

2020-02-06 12:29:43

frank

Ported: N/A

Details Diff
cats: Fix upgrading MySQL database from version 2171 to 2192

"CREATE INDEX IF NOT EXISTS ..." does not work on MySQL.

Instead "ALTER TABLE Job ADD INDEX (JobTDate);" is compatible to
MariaDB and MySQL. This also works if an index is already in place.

Unfortunately we could end up having multiple indexes on
JobTDate this way, so manually cleaning up could be required.

Perform also a key distribution analysis on the PathVisibility
table after index drop.

Fixes 0001176: Upgrade 18.2.5 to 19.2.5 failed ...
Affected Issues
0001176
mod - core/src/cats/ddl/updates/mysql.2171_2192.sql Diff File

bareos: bareos-19.2 79efe166

2020-02-06 12:29:43

frank

Ported: N/A

Details Diff
cats: Fix upgrading MySQL database from version 2171 to 2192

"CREATE INDEX IF NOT EXISTS ..." does not work on MySQL.

Instead "ALTER TABLE Job ADD INDEX (JobTDate);" is compatible to
MariaDB and MySQL. This also works if an index is already in place.

Unfortunately we could end up having multiple indexes on
JobTDate this way, so manually cleaning up could be required.

Perform also a key distribution analysis on the PathVisibility
table after index drop.

Fixes 0001176: Upgrade 18.2.5 to 19.2.5 failed ...
Affected Issues
0001176
mod - core/src/cats/ddl/updates/mysql.2171_2192.sql Diff File

Issue History

Date Modified Username Field Change
2020-02-06 12:18 frank New Issue
2020-02-06 12:29 frank Assigned To => frank
2020-02-06 12:29 frank Status new => assigned
2020-02-06 13:34 arogge Relationship added related to 0001177
2020-02-08 00:43 teka74 Note Added: 0003754
2020-02-10 10:44 frank Note Added: 0003758
2020-02-10 13:00 dimmko Note Added: 0003761
2020-02-10 13:40 frank Relationship added related to 0001183
2020-02-10 13:42 frank Status assigned => confirmed
2020-02-10 18:22 frank Changeset attached => bareos master fcf73700
2020-02-10 18:22 frank Note Added: 0003767
2020-02-10 18:22 frank Status confirmed => resolved
2020-02-10 18:22 frank Resolution open => fixed
2020-02-11 12:22 frank Changeset attached => bareos bareos-19.2 79efe166
2020-02-11 12:22 frank Note Added: 0003778
2020-02-11 17:18 arogge Status resolved => closed
2020-02-11 17:18 arogge bareos-master: impact => yes
2020-02-11 17:18 arogge bareos-master: action => fixed
2020-02-11 17:18 arogge bareos-19.2: impact => yes
2020-02-11 17:18 arogge bareos-19.2: action => fixed
2020-02-11 17:18 arogge bareos-18.2: impact => no
2020-02-11 17:18 arogge bareos-17.2: impact => no
2020-02-11 17:18 arogge bareos-16.2: impact => no
2020-02-11 17:18 arogge Note Added: 0003782