Update to 0.8.8 is not working

[gandalf]

Looking at http://dev.mysql.com/doc/refman/5.0/en/alter-table.html it SHOULD be supported in 5.0. This is, what I was looking at when coding this bit.
It was present even in earlier releases: http://dev.mysql.com/doc/refman/4.1/en/alter-table.html
But perhaps I missed sth.
R.

Strange but it definately doesn't like that command in my version of 5.0.

Did you find a mysql bug report for that? Well, 5.0 is out of service, from their point of viewing ...
R.

[MikeMcr]

Did you find a mysql bug report for that? Well, 5.0 is out of service, from their point of viewing ...
R.

Well I'm just using the MySQL version as provided by Netgear (NAS products) on Debian Etch that they use. I'll have to investigate if MySQL can be upgraded without breaking anything.

[gandalf]

Well, in most cases, you may simply omit the "USING BTREE" option without harm.
R.

[gandalf]

I just ran

Code: Select all

ALTER TABLE `poller_output` ADD PRIMARY KEY (`local_data_id`, `rrd_name`, `time`) USING BTREE

on our production MySQL 5.0.77 without issues (it's a RHEL 5 system)
So I need exact parameters to reproduce the effect. Please specify
- platform
- exact mysql version
- result of running the above command from mysql cli; complete error message, if any
R.

[MikeMcr]

I just ran

Code: Select all

ALTER TABLE `poller_output` ADD PRIMARY KEY (`local_data_id`, `rrd_name`, `time`) USING BTREE

on our production MySQL 5.0.77 without issues (it's a RHEL 5 system)
So I need exact parameters to reproduce the effect. Please specify
- platform
- exact mysql version
- result of running the above command from mysql cli; complete error message, if any
R.

Code: Select all

ReadyNAS:~# mysql cacti
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1008
Server version: 5.0.32-Debian_7etch5 Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> ALTER TABLE `poller_output` ADD PRIMARY KEY (`local_data_id`, `rrd_name`, `time`) USING BTREE;
ERROR 1064 (42000): 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 'USING BTREE' at line 1

Hopefully the above also contains all the version info?

[gandalf]

Yep, and you've hit it.
Please see http://bugs.mysql.com/bug.php?id=25162, which states

The problem was in incompatible syntax for key definition in CREATE
TABLE.

5.0 supports only the following syntax for key definition (see "CREATE
TABLE syntax" in the manual):

{INDEX|KEY} [index_name] [index_type] (index_col_name,...)

While 5.1 parser supports the above syntax, the "preferred" syntax was
changed to:

{INDEX|KEY} [index_name] (index_col_name,...) [index_type]

This was fixed in 5.0.60. So you still have it!
Please verify by running

Code: Select all

ALTER TABLE `poller_output` ADD PRIMARY KEY USING BTREE (`local_data_id`, `rrd_name`, `time`)

In case this gives us what I expect now, I will have to make up my mind how to worka round this nasty mysql bug ...
R.

[MikeMcr]

Please verify by running

Code: Select all

ALTER TABLE `poller_output` ADD PRIMARY KEY USING BTREE (`local_data_id`, `rrd_name`, `time`)

In case this gives us what I expect now, I will have to make up my mind how to worka round this nasty mysql bug ...
R.

I get:

Code: Select all

ERROR 1068 (42000): Multiple primary key defined

Which probably means the command will now work but the above message is because I already setup the primary keys as detailed earlier in the thread by removing "USING BTREE" from the command.

[MikeMcr]

This was fixed in 5.0.60. So you still have it!

It looks like the Debian etch packages don't go as high as that version - even the backports package.

[gandalf]

Code: Select all

ALTER TABLE `poller_output` ADD PRIMARY KEY (`local_data_id`, `rrd_name`, `time`) /*!50060 USING BTREE */

makes use of MySQL conditional comments.
It will use default index type for older versions (which should be quite fine) and force the index type of BTREE for MySQL >= 5.00.60.
So you should run this very command and post your findings
R.

[MikeMcr]

Code: Select all

ALTER TABLE `poller_output` ADD PRIMARY KEY (`local_data_id`, `rrd_name`, `time`) /*!50060 USING BTREE */

makes use of MySQL conditional comments.
It will use default index type for older versions (which should be quite fine) and force the index type of BTREE for MySQL >= 5.00.60.
So you should run this very command and post your findings
R.

Code: Select all

ERROR 1068 (42000): Multiple primary key defined

So, it accepted the command. Nice idea.

[gandalf]

Fine. I will work out a patch for both the upgrade procedure and the cacti.sql file.
Thx for verifying my assumptions
R.

[gandalf]

Filed as a bug at http://bugs.cacti.net/view.php?id=2211
R.