Upgrading MySQL-Database 1.4.5 > 1.6 exit with error

[rrr]

When I upgraded the MySQL-Database via the patch.sql shema it exits with error:

 
ERROR 1061 (42000) at line 144: Duplicate key name 'httptest_httptest_1'

It's because the key 'httptest_httptest_1' still exists, but for a other field!
Since this existing index isn't needed in the 1.6-Version (see createshema for v1.6) it can be deleted.

[BlouPhil]

Got same problem over here...

Do you know what table I need to edit in order to delete it?

Thanks

[xs-]

are you upgrading from 1.5.x or 1.4.x?

[BlouPhil]

From 1.4.5

[xs-]

First off, i hope you made database dumps!

Well, i'd do the following:
- open patch.sql
- search for the line containing: httptest_httptest_1 and look which table it tries to set it

Now either do
- enter the zabbix database
- remove the index httptest_httptest_1 from the tablename you just looked up
- process the remainder of the patch.sql script

or
- Restore backup
- enter zabbix database
- remove index httptest_httptest_1 from tablename you just looked up
- Apply patch.sql





(repeat when other errors occur)

[Alexei]

This is strange, because there is no index with this name in 1.4.x database structure.

[rrr]

You hast have to insert this line before the line with the new creation of the INDEX:

 
DROP INDEX httptest_httptest_1 ON httptest;

[xs-]

One more thing to keep in mind (which could also be the cause of 'similar' issues)

If for any reason patch.sql gives you an error, you can't simply 'do it again' (the patch.sql does not do an atomic commit).
You will either need to figure out on which line it failed and continue from there, or restore a backup and try again.

[BlouPhil]

Thanks for you help!

I am currently dropping the DB and then I'll restore it, it's pretty big (2.2GB)

I've added :
DROP INDEX httptest_httptest_1 ON httptest;

just before the line :
CREATE INDEX httptest_httptest_1 on httptest (applicationid);

I'll post my results here once it's done

[gedp]

had the same problem (upgrading from 1.4.1). Dropping the index and running the patch starting from the CREATE line was ok.

[BlouPhil]

It worked!

My DB is now patched, I'll be now upgrading the frontend and the binaries from source

Thanks a lot everyone for your fast help