#275 Cannot restore MySQL DB

[Steven Gale]

OWL 0.72 running on Linux (redhat 7.3) with MySQL
database (version 3.23.58).

The file generated my mysqldump (through the admin
interface or via the command line) cannot be restored.
Trying to restore it fails with the error:

ERROR 1062 at line 170: Duplicate entry '1' for key 1

where line 170 in my file reads:

INSERT INTO groups VALUES (0,'Administrators'),
(1,'Anonymous'),(2,'File Admin'),(3,'Authors');

The reason is that auto_incement in mysql always starts
with 1 so the first record inserted into the GROUPS table
gets id=1 and the second one fails with a duplicate id.

It looks like you worked hard to get an id of 0 in the first
place -- look at the code in DOCS/sql/mysql-tables.sql:

INSERT INTO groups VALUES (0, 'Administrators');
UPDATE groups SET id = 0 WHERE name
= 'Administrators';

[Steve]

Logged In: YES
user_id=55225

Yes I have know about this problem for quite some time, and
it would require changing quite a bit of the original Owl
Code to get this to work with groupid 1.

In the mean time you have to manually change the sql for
restore.

Change the insert for the admin group to 999. and then issue
and update to change group 999 to 0 after the insert is done.

B0zz

[Steven Gale]

Logged In: YES
user_id=631228

Automatic restore is very important -- so here is my work
around. I added the following line to my restore script (the
dump is stored in the file dbdump.sql):
perl -pi -e 's/(INSERT INTO owl_groups VALUES \()\d*(.*)/
${1}999$2\nUPDATE owl_\
groups SET id = 0 WHERE name = "Administrators";/'
dbdump.sql

[Doug_hipo]

Logged In: YES
user_id=1028126

you can change the groupid from anonymous to other id like
1111111 and do import , so you must update admin to id 0 and
anonymous to id 1 ..

it's working to me

[Steve]

Logged In: YES
user_id=55225

Here is another Woraround submitted by leikai:

backup.sh:

#/usr/bin/sh
cd /owl
mysqldump --opt intranet > /owl/backup.sql
sed "/INSERT INTO groups VALUES/ s/(0,'Administrators'),//"
> backup.sql > backup1.sql
sed "/INSERT INTO groups VALUES/ iINSERT INTO groups VALUES
(0,'Administrators');update groups SET id = 0 WHERE name =
'Administrators';"
> backup1.sql > backup2.sql
tar zcf owl.`date +%F-%H-%M`.tar.gz backup2.sql
echo "OWL system is backuping,please wait........"
cp -arf /var/www/html/intranet/Documents /owl
tar zcf data.`date +%F-%H-%M`.tar.gz Documents
> /dev/null 2>&1
echo "backup is done!"
echo ""
#end

restore.sh:
#/usr/bin/sh
mysqladmin drop intranet -f > /dev/null 2>&1
mysqladmin create intranet
echo "System is restoring,please wait........"
mysql intranet < /owl/backup2.sql
cp -arf /owl/Documents /var/www/html/intranet
echo "restore is done!"
echo ""
#end

[leikai]

Logged In: YES
user_id=1123310

I can fix it by the followed script:
>
> backup.sh:
>
> #/usr/bin/sh
> cd /owl
> mysqldump --opt intranet > /owl/backup.sql
> sed "/INSERT INTO groups VALUES/ s/(0,'Administrators'),//"
> backup.sql > backup1.sql
> sed "/INSERT INTO groups VALUES/ iINSERT INTO groups
VALUES
> (0,'Administrators');update groups SET id = 0 WHERE name =
> 'Administrators';"
> backup1.sql > backup2.sql
> tar zcf owl.`date +%F-%H-%M`.tar.gz backup2.sql
> echo "OWL system is backuping,please wait........"
> cp -arf /var/www/html/intranet/Documents /owl
> tar zcf data.`date +%F-%H-%M`.tar.gz Documents
>> /dev/null 2>&1
> echo "backup is done!"
> echo ""
> #end
>
> restore.sh:
> #/usr/bin/sh
> mysqladmin drop intranet -f > /dev/null 2>&1
> mysqladmin create intranet
> echo "System is restoring,please wait........"
> mysql intranet < /owl/backup2.sql
> cp -arf /owl/Documents /var/www/html/intranet
> echo "restore is done!"
> echo ""
> #end

Thanks b0zz

[Herb]

Logged In: YES
user_id=571573

Here is a workaround that is easy for the person managing
the database:

Using phpMyAdmin or some other means, in the groups
table, change the id of the Anonymous group to a number
other than 1 which is not being used by another group.

If the database is backed up and then restored, the
Administrator group would be set to 1, but there would be no
other record with an id of 1 to create a conflict.

The system works fine with an administrator group with an id
set to 1 as long as the existing admins have a groupid of 0 in
the users table (this doesn't change).

In other words, existing admins will work OK. To create new
admins, you would have to go in with phpMyAdmin and give
the admin group in the groups table an id of 0.

[Palumbo Daniele]

Logged In: YES
user_id=222909

the patch.
made for 0.80, dunno with other owl.

[Palumbo Daniele]

Logged In: YES
user_id=222909

sorry, the link :)
http://www.retaggio.net/owl_Administrator_autoincrement_mysql.patch

[Saratoga Sam]

Logged In: YES
user_id=1113134
Originator: NO

I ran into this same issue with OWL 0.93 after trying to restore an OWL installation after a server crash. I was unable to figure out what the problem was. Luckily, my host provider had a sharp technical support person who figured out.