phpipam
phpipam copied to clipboard
phpipam
Database upgrade fails - DB Check unsuccessfull
Hi,
I am trying to upgrade from phpipam 1.45.r25 to 1.5 (r39). The automatic upgrade fails because "Base table of view already exists".
This is what I am doing:
I attempt automatic upgrade:
Database needs to be upgraded to version 1.5.r39, it seems you are using phpipam version 1.45.r25!
SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'vaults' already exists
Failed query:
CREATE TABLE `vaults` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL DEFAULT '',
`type` enum('passwords','certificates') NOT NULL DEFAULT 'passwords',
`description` text,
`test` char(128) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Failed to upgrade database!
Not executed queries:
CREATE TABLE `vaults` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL DEFAULT '',
`type` enum('passwords','certificates') NOT NULL DEFAULT 'passwords',
`description` text,
`test` char(128) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `vaultItems` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`vaultId` int(11) unsigned NOT NULL,
`type` enum('password','certificate') NOT NULL DEFAULT 'password',
`type_certificate` enum('public','pkcs12','certificate','website') NOT NULL DEFAULT 'public',
`values` text,
PRIMARY KEY (`id`),
KEY `vaultId` (`vaultId`),
CONSTRAINT `vaultItems_ibfk_1` FOREIGN KEY (`vaultId`) REFERENCES `vaults` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Database version bump
UPDATE `settings` set `dbversion` = '36';
ALTER TABLE `customers` CHANGE `lat` `lat` varchar(31) DEFAULT NULL;
ALTER TABLE `customers` CHANGE `long` `long` varchar(31) DEFAULT NULL;
ALTER TABLE `locations` CHANGE `lat` `lat` varchar(31) DEFAULT NULL;
ALTER TABLE `locations` CHANGE `long` `long` varchar(31) DEFAULT NULL;
CREATE TABLE `nominatim` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`url` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `nominatim` (`id`, `url`) VALUES (1, 'https://nominatim.openstreetmap.org/search');
CREATE TABLE `nominatim_cache` (
`sha256` binary(32) NOT NULL,
`date` timestamp NOT NULL DEFAULT current_timestamp(),
`query` text NOT NULL,
`lat_lng` text NOT NULL,
PRIMARY KEY (`sha256`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Database version bump
UPDATE `settings` set `dbversion` = '37';
ALTER TABLE `logs` CHANGE `command` `command` text DEFAULT NULL;
ALTER TABLE `logs` CHANGE `details` `details` text DEFAULT NULL;
ALTER TABLE `changelog` CHANGE `caction` `caction` ENUM('add','edit','delete','truncate','resize','perm_change') NOT NULL DEFAULT 'edit';
ALTER TABLE `changelog` CHANGE `cresult` `cresult` ENUM('error','success') NOT NULL DEFAULT 'success';
ALTER TABLE `changelog` CHANGE `cdiff` `cdiff` text DEFAULT NULL;
ALTER TABLE `users` CHANGE `module_permissions` `module_permissions` varchar(255) DEFAULT '{"vlan":"1","l2dom":"1","vrf":"1","pdns":"1","circuits":"1","racks":"1","nat":"1","pstn":"1","customers":"1","locations":"1","devices":"1","routing":"1","vaults":"1"}';
ALTER TABLE `firewallZoneSubnet` ADD PRIMARY KEY (`zoneId`,`subnetId`);
ALTER TABLE `circuitsLogicalMapping` ADD PRIMARY KEY (`logicalCircuit_id`, `circuit_id`);
-- Database version bump
UPDATE `settings` set `dbversion` = '38';
INSERT INTO `widgets` (`wtitle`, `wdescription`, `wfile`, `wparams`, `whref`, `wsize`, `wadminonly`, `wactive`) VALUES ('MAC lookup', 'Shows MAC address vendor', 'mac-lookup', NULL, 'yes', '6', 'no', 'yes');
-- Database version bump
UPDATE `settings` set `dbversion` = '39';
When I log in with my root mysql user and try to execute the non executed statements, all of them, except two succeed.
MariaDB [(none)]> use phpipam;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [phpipam]> -- Database version bump
MariaDB [phpipam]> UPDATE `settings` set `dbversion` = '36';
ALTER TABLE `customers` CHANGE `lat` `lat` varchar(31) DEFAULT NULL;
ALTER TABLE `customers` CHANGE `long` `long` varchar(31) DEFAULT NULL;
ALTER TABLE `locations` CHANGE `lat` `lat` varchar(31) DEFAULT NULL;
ALTER TABLE `locations` CHANGE `long` `long` varchar(31) DEFAULT NULL;
Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [phpipam]> ALTER TABLE `customers` CHANGE `lat` `lat` varchar(31) DEFAULT NULL;
CREATE TABLE `nominatim` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`url` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `nominatim` (`id`, `url`) VALUES (1, 'https://nominatim.openstreetmap.org/search');
CREATE TABLE `nominatim_cache` (
`sha256` binary(32) NOT NULL,
`date` timestamp NOT NULL DEFAULT current_timestamp(),
Query OK, 0 rows affected (0.002 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [phpipam]> ALTER TABLE `customers` CHANGE `long` `long` varchar(31) DEFAULT NULL;
`query` text NOT NULL,
`lat_lng` text NOT NULL,
PRIMARY KEY (`sha256`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.002 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [phpipam]> ALTER TABLE `locations` CHANGE `lat` `lat` varchar(31) DEFAULT NULL;
Query OK, 0 rows affected (0.002 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [phpipam]> ALTER TABLE `locations` CHANGE `long` `long` varchar(31) DEFAULT NULL;
Query OK, 0 rows affected (0.002 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [phpipam]> CREATE TABLE `nominatim` (
-> `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `url` varchar(255) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.038 sec)
MariaDB [phpipam]> INSERT INTO `nominatim` (`id`, `url`) VALUES (1, 'https://nominatim.openstreetmap.org/search');
Query OK, 1 row affected (0.002 sec)
MariaDB [phpipam]> CREATE TABLE `nominatim_cache` (
-> `sha256` binary(32) NOT NULL,
-> `date` timestamp NOT NULL DEFAULT current_timestamp(),
-> `query` text NOT NULL,
-> `lat_lng` text NOT NULL,
-> PRIMARY KEY (`sha256`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.026 sec)
MariaDB [phpipam]> -- Database version bump
MariaDB [phpipam]> UPDATE `settings` set `dbversion` = '37';
ALTER TABLE `logs` CHANGE `command` `command` text DEFAULT NULL;
ALTER TABLE `logs` CHANGE `details` `details` text DEFAULT NULL;
ALTER TABLE `changelog` CHANGE `caction` `caction` ENUM('add','edit','delete','truncate','resize','perm_change') NOT NULL DEFAULT 'edit';
ALTER TABLE `changelog` CHANGE `cresult` `cresult` ENUM('error','success') NOT NULL DEFAULT 'success';
ALTER TABLE `changelog` CHANGE `cdiff` `cdiff` text DEFAULT NULL;
ALTER TABLE `users` CHANGE `module_permissions` `module_permissions` varchar(255) DEFAULT '{"vlan":"1","l2dom":"1","vrf":"1","pdns":"1","circuits":"1","racks":"1","nat":"1","pstn":"1","customers":"1","locations":"1","devices":"1","routing":"1","vaults":"1"}';
ALTER TABLE `firewallZoneSubnet` ADD PRIMARY KEY (`zoneId`,`subnetId`);
ALTER TABLE `circuitsLogicalMapping` ADD PRIMARY KEY (`logicalCircuit_id`, `circuit_id`);
Query OK, 1 row affected (0.005 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [phpipam]> ALTER TABLE `logs` CHANGE `command` `command` text DEFAULT NULL;
-- Database version bump
UPDATE `settings` set `dbversion` = '38';
INSERT INTO `widgets` (`wtitle`, `wdescription`, `wfile`, `wparams`, `whref`, `wsize`, `wadminonly`, `wactive`) VALUES ('MAC lookup', 'Shows MAC address vendor', 'mac-lookup', NULL, 'yes', '6', 'no', 'yes');
-- Database version bump
Query OK, 2176 rows affected (0.422 sec) 'Copy to tmp table' 49.9% of stage done
Records: 2176 Duplicates: 0 Warnings: 0
MariaDB [phpipam]> ALTER TABLE `logs` CHANGE `details` `details` text DEFAULT NULL;
Query OK, 0 rows affected (0.001 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [phpipam]> ALTER TABLE `changelog` CHANGE `caction` `caction` ENUM('add','edit','delete','truncate','resize','perm_change') NOT NULL DEFAULT 'edit';
Query OK, 326 rows affected (0.116 sec)
Records: 326 Duplicates: 0 Warnings: 0
MariaDB [phpipam]> ALTER TABLE `changelog` CHANGE `cresult` `cresult` ENUM('error','success') NOT NULL DEFAULT 'success';
Query OK, 326 rows affected (0.060 sec)
Records: 326 Duplicates: 0 Warnings: 0
MariaDB [phpipam]> ALTER TABLE `changelog` CHANGE `cdiff` `cdiff` text DEFAULT NULL;
Query OK, 326 rows affected (0.064 sec)
Records: 326 Duplicates: 0 Warnings: 0
MariaDB [phpipam]> ALTER TABLE `users` CHANGE `module_permissions` `module_permissions` varchar(255) DEFAULT '{"vlan":"1","l2dom":"1","vrf":"1","pdns":"1","circuits":"1","racks":"1","nat":"1","pstn":"1","customers":"1","locations":"1","devices":"1","routing":"1","vaults":"1"}';
Query OK, 0 rows affected (0.001 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [phpipam]> ALTER TABLE `firewallZoneSubnet` ADD PRIMARY KEY (`zoneId`,`subnetId`);
Query OK, 0 rows affected (0.017 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [phpipam]> ALTER TABLE `circuitsLogicalMapping` ADD PRIMARY KEY (`logicalCircuit_id`, `circuit_id`);
Query OK, 0 rows affected (0.017 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [phpipam]>
MariaDB [phpipam]>
MariaDB [phpipam]> -- Database version bump
MariaDB [phpipam]> UPDATE `settings` set `dbversion` = '38';
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [phpipam]> INSERT INTO `widgets` (`wtitle`, `wdescription`, `wfile`, `wparams`, `whref`, `wsize`, `wadminonly`, `wactive`) VALUES ('MAC lookup', 'Shows MAC address vendor', 'mac-lookup', NULL, 'yes', '6', 'no', 'yes');
Query OK, 1 row affected (0.001 sec)
MariaDB [phpipam]>
MariaDB [phpipam]>
MariaDB [phpipam]> -- Database version bump
MariaDB [phpipam]> UPDATE `settings` set `dbversion` = '39';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
These are the two that fail:
MariaDB [phpipam]> CREATE TABLE `vaults` (
-> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
-> `name` varchar(64) NOT NULL DEFAULT '',
-> `type` enum('passwords','certificates') NOT NULL DEFAULT 'passwords',
-> `description` text,
-> `test` char(128) NOT NULL DEFAULT '',
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1050 (42S01): Table 'vaults' already exists
MariaDB [phpipam]>
MariaDB [phpipam]> CREATE TABLE `vaultItems` (
-> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
-> `vaultId` int(11) unsigned NOT NULL,
-> `type` enum('password','certificate') NOT NULL DEFAULT 'password',
-> `type_certificate` enum('public','pkcs12','certificate','website') NOT NULL DEFAULT 'public',
-> `values` text,
-> PRIMARY KEY (`id`),
-> KEY `vaultId` (`vaultId`),
-> CONSTRAINT `vaultItems_ibfk_1` FOREIGN KEY (`vaultId`) REFERENCES `vaults` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1050 (42S01): Table 'vaultItems' already exists
When I am done, the DB version is still upgraded to v. 39 which is also detected by the webpage:
Database needs to be upgraded to version 1.5.r39, it seems you are using phpipam version 1.45.r39!
I would assume, if a table is already present, it should not error but simply increment the DB Version and be done with it.
MariaDB [phpipam]> select dbversion from settings;
+-----------+
| dbversion |
+-----------+
| 39 |
+-----------+
1 row in set (0.001 sec)
I have reverted for now - I'd be grateful for any pointers on how to solve this issue.
So, I can't explain how the "vault" and "vaultItems" got there, but it could likely be because you cloned the "master" branch of the git repository, rather than the 1.5 or the 1.6 branch.
But I can tell you how the upgrade logic works. Essentially, every time a page is loaded, the code looks at two values in the database. You found the first, which is dbversion inside settings. The second is version inside settings. So, you could do select version,dbversion from settings; to see them both. The code then compares them with the expected values. If there is a mismatch, it runs the upgrade script. The upgrade script will make all the database changes and finally mark the new version(s) on the database. It looks like you crossed a major version boundary, yet the upgrade script only marked one of the two versions. That is likely because it wasn't ready when you cloned the "master" branch.
It's been a year-and-a-half since you opened this issue, so I don't know what you've done since then. But I would recommend pulling the "1.6" branch of code (either by cloning or by using the release package (which is a tar file). It's pretty likely that it will upgrade correctly, unless you still have those "vault" tables. If you're not using vaults, then I would just drop the two tables before upgrading so that it can recreate them.
