cannot use database "zabbix": its "users" table is empty

[uek1967]

Hi fellows,

after I got zabbix-server 4 running om debian10 I tried to implement it on centos7 today following this tutorial: https://www.digitalocean.com/communi...rs-on-centos-7

Everything went fine until I did:
zcat /usr/share/doc/zabbix-server-mysql-4.2.5/create.sql.gz | mysql -uzabbix -p zabbix When I tried to start zabbix-server the logfile showed me: cannot use database "zabbix": its "users" table is empty so I renewed the zabbix Database and ran the zcat command again. Now I got: : ERROR 1452 (23000) at line 2198: Cannot add or update a child row :a foreign key constraint fails (`zabbix`.`dchecks`, CONOREIGN KEY (`druleid`)STRAINT `c_dchecks_1` F REFERENCES `drules` (`druleid`) ON DELETE CASCADE) so I tried to solve it by: ALTER TABLE `zabbix`.`dchecks` -> ADD CONSTRAINT `c_dchecks_1` FOREIGN KEY (`druleid`) REFERENCES `zabbix`.`drules` (`druleid`) ON DELETE CASCADE ON UPDATE RESTRICT; what failed with: Can't create table `zabbix`.`dchecks` (errno: 121 "Duplicate key on write or update") If anybody has a proposal to solve it please let me know. Many thanks in advance Uli

[brunohl]

Yeah, I guess that something went wrong on your first import. You should drop zabbix database, create it again and populate it with the DB script.

[uek1967]

Same Error again

Dear Brunohl,

I followed your advise like this:
drop database zabbix;
create database zabbix character set utf8 collate utf8_bin;
quit;

zcat /usr/share/doc/zabbix-server-mysql-4.2.5/create.sql.gz | mysql -uzabbix -p zabbix



and finally I got: ERROR 1452 (23000) at line 2198: Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`dchecks`, CONSTRAINT `c_dchecks_1` FOREIGN KEY (`druleid`) REFERENCES `drules` (`druleid`) ON DELETE CASCADE)

so I tried

ALTER TABLE dservices ADD CONSTRAINT c_dservices_2 FOREIGN KEY (dcheckid) REFERENCES dchecks (dcheckid) ON DELETE CASCADE;
ERROR 1046 (3D000): No database selected

use zabbix;

ALTER TABLE dservices ADD CONSTRAINT c_dservices_2 FOREIGN KEY (dcheckid) REFERENCES dchecks (dcheckid) ON DELETE CASCADE;
ERROR 1005 (HY000): Can't create table `zabbix`.`dservices` (errno: 121 "Duplicate key on write or update")

Do you maybee have another solution to fix it ?

I f so please let me know

[brunohl]

Hmm, I guess that maybe your sql has some error, you should try and get a new one. Maybe reinstall zabbix from scratch also could be a good idea.

[Atsushi]

Are you using a new version of MariaDB?

ZBX-16465 : Not possible to insert create.sql.gz on MariaDB 10.3.17

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

[uek1967]

Dear Atsushi,
I am using Server version: 10.4.7-MariaDB MariaDB Server

And I think I found the error , but not the solution , yet.

zcat /usr/share/doc/zabbix-server-mysql-4.4.0/create.sql.gz | mysql -uzabbix -p zabbix
Enter password:
ERROR 1118 (42000) at line 1282: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

Do you maybe have an idea how to fix that?

Many thanks in advance,

Uli

[uek1967]

The first solution ended with a Syntax Error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'varchar(64) DEFAULT '' NOT NULL,
`site_country` var' at line 55

The second proposal does not work.