How to apply a lot of DB patches in the trunk version

[zalex_ua]

I wanted to ask, tell me way how to live life more on the trunk version? That is, before when the patches to the database was just a few I could remember having applied and what is not, and manually apply the necessary patches.
But now, after [DEV-471] patches became much more. Tell me please - I have a normal approach is to apply the entire patch with key -f several times in a row?

cat patch.sql | mysql -f zabbix

Can unforeseen situations arise the problem?
I've already done that - no problems noticed, but I'm not sure whether I'm doing right.


p.s. Developers, describe something about - [DEV-471] database integrity - added foreign keys. I wonder what it is and why it is needed.

[zalex_ua]

Well, I stumbled on the problem.
When the trunk was commited with "[ZBXNEXT-457] added support of nanoseconds for history tables", then I applied the patch 'dbpatches\2.0\mysql\patch\items.sql'

ALTER TABLE items MODIFY units VARCHAR(255) DEFAULT '' NOT NULL;
ALTER TABLE items ADD lastns integer NULL;
UPDATE items SET units='Bps' WHERE type=9 AND units='bps';

After commit "[DEV-471] database integrity - added foreign keys",
I can not apply the entire patch 'items.sql' fully and successfully

ALTER TABLE items MODIFY itemid bigint unsigned NOT NULL,
		  MODIFY hostid bigint unsigned NOT NULL,
		  MODIFY lastvalue text NULL,
		  MODIFY prevvalue text NULL,
		  MODIFY units varchar(255) DEFAULT '' NOT NULL,
		  MODIFY templateid bigint unsigned NULL,
		  MODIFY valuemapid bigint unsigned NULL,
		  ADD lastns integer NULL;
UPDATE items SET templateid=NULL WHERE templateid=0;
CREATE TEMPORARY TABLE tmp_items_itemid (itemid bigint unsigned PRIMARY KEY);
INSERT INTO tmp_items_itemid (itemid) (SELECT itemid FROM items);
UPDATE items SET templateid=NULL WHERE NOT templateid IS NULL AND NOT templateid IN (SELECT itemid FROM tmp_items_itemid);
DROP TABLE tmp_items_itemid;
UPDATE items SET valuemapid=NULL WHERE valuemapid=0;
UPDATE items SET valuemapid=NULL WHERE NOT valuemapid IS NULL AND NOT valuemapid IN (SELECT valuemapid from valuemaps);
UPDATE items SET units='Bps' WHERE type=9 AND units='bps';
DELETE FROM items WHERE NOT hostid IN (SELECT hostid FROM hosts);
ALTER TABLE items ADD CONSTRAINT c_items_1 FOREIGN KEY (hostid) REFERENCES hosts (hostid) ON DELETE CASCADE;
ALTER TABLE items ADD CONSTRAINT c_items_2 FOREIGN KEY (templateid) REFERENCES items (itemid) ON DELETE CASCADE;
ALTER TABLE items ADD CONSTRAINT c_items_3 FOREIGN KEY (valuemapid) REFERENCES valuemaps (valuemapid);

because i receive error:

ERROR 1060 (42S21) at line 1: Duplicate column name 'lastns'

If I use the -f key (mysql -f) as I wrote in the first post, I can ignore the offending queries, but for example in the first query of the new patch, I also miss important changes.

...
  MODIFY itemid bigint unsigned NOT NULL,
  MODIFY hostid bigint unsigned NOT NULL,
  MODIFY lastvalue text NULL,
  MODIFY prevvalue text NULL,
  MODIFY units varchar(255) DEFAULT '' NOT NULL,
  MODIFY templateid bigint unsigned NULL,
  MODIFY valuemapid bigint unsigned NULL,
....

And as a result, some part of the web-interface stop working on latest trunk revision.


Prompt correct way in this case, you're sure to know all this?
I can not manually track changes and manually edit the DB schema for every Issue commit? Is very hard.
Perhaps in such situations need to do a patch so that the SQL-query from each Issue made separate query but not combined of all as in my example?
In this case, those who live on the trunk versions can update the database schema without problems simply by applying all the patches with key -f and ignore error for query applied before.
I understand that in terms of performance upgrades for large databases is better to make one query, but you can find a compromise!
You also need beta testers or not?
Please, give tips what to do.

[richlv]

this is a problem for me as well. lately i'm not really sure whether my db is correct or not

making patches applyable in a forceful manner does not seem like a good solution to me - you could easily miss genuine errors.

maybe something like a db patchlevel should be introduced - a field somewhere in the db, which indicates up to which level the patches have been applied, and then in addition to the global patch separate incremental patches could be provided.

while that would be additional work, i guess it's the only way to get any testing on development versions

[zalex_ua]

this is a problem for me as well. lately i'm not really sure whether my db is correct or not

Suddenly, to hear what you something not know
I have studied this problem before your messages, and also after.
As a result, I have come to a result that approximate the path like this: create a temporary database with a fresh schema and compare it with our current database.
Depending on the presence of some keywords (DROP COLUMN, etc) - or apply the patch, which offers a program, or manually resolves all inconsistencies with the individual patches.

On the Internet about this sea of information. Many different solutions.
First started from here this http://forums.mysql.com/read.php?159...314#msg-201314
http://wb.mysql.com/workbench/
So I spent several days trying to make MySQL Workbench (functionality 'Syncronize model'), and once figured out (it was not easy), it turned out that he was not all synchronized, namely:
if you take the source mysql.sql, then bigint takes size 19 rather than 20, but if you sync from another already created database (which is already 20 size for bigint), it still has other problems:
UNIQUE attribute for the index is not updated
the keys for some reason, does not create (automatically adds a key only if you add CONSTRAINT)
Besides clearly see the difference between the database schema is not possible, before performing synchronization to look ready SQL request, but it is not very convenient.

Another important point - you need to be careful, for example in the case of /upgrades/dbpatches/2.0/mysql/patch/graph_theme.sql
The original patch does Modified:

CHANGE noneworktimecolor nonworktimecolor varchar(6) DEFAULT 'CCCCCC' NOT NULL;

a patch created by the program - creating a new column, and then deletes the old one:

ADD COLUMN `nonworktimecolor` varchar(6)  COLLATE utf8_general_ci NOT NULL DEFAULT 'CCCCCC' after `rightpercentilecolor`, 
DROP COLUMN `noneworktimecolor`, COMMENT='';

This is the behavior of three different programs that I tested

an interesting tool that I have not tried

http://schemasync.org/

As result I stopped at: http://www.webyog.com/en/
Here, this function works perfectly. Everything is very clear, and all syncs correctly.

http://www.webyog.com/images/screenshots/schema_sync.png

Of course the utility is not free, but with 30 days it works well, and if you need even more

http://www.datanamic.com/schemadiff/index.html - not bad, but worse than the previous one.

... no more searching and have not tried.


After all this suffering comes to an idea. May add to Zabbix verify functionality of the database schema, whether the current scheme of the one with which the interface should work? I.e. somewhere in your web interface should be based on a database file or mysql.sql or schema.sql and Administration section of the dialogue will check the database schema. I think this is possible.
I remember when I updated from 1.6 to 1.8, I have great difficulty updated database, and as it turned out just now I have not all been updated - missed about a dozen different indexes. Only now do I fix it. It is possible that people like me a lot

added:
Just a reminder - the issue associated with these topic.

https://support.zabbix.com/browse/ZBX-3026