Error exporting subnets/ip addresses

[nutt318]

Describe the bug
Using the Import/Export feature and exporting the dataset of IP Addresses and Subnets returns blank files. When opening they appear blank but the file size appears to have data. Opening a file Excel detects a problem with the file and says editing may harm your computer. I edit the file and then Excel crashes.

phpIPAM version
1.3(current) 1.5(attempting to migrate to)

Scenario
I have a brand new install of phpipam 1.5 and was attempting to import the files but was receiving an error that the columns needed to contain headers. Looking at the export files from my current phpipam 1.3 they seem to be blank but the file size says otherwise..

I did run the 'Prepare XLS dump' and that did work with a tabbed sheet with all of the contents.

Unless there is an easier way to migrate data from 1.3 to 1.5 thats what I'm attempting to do.

[GaryAllan]

Hi @nutt318
Create a copy of your v1.3 database and attach the copy to a phpIPAM v1.5 instance (mysqldump and restore to a new empty database). When you log into v1.5 it will run a database upgrade process on the copy.

If anything goes wrong log an issue here and when fixed start again with a fresh copy of the original 1.3 database.

Once the upgrade works you can perform the upgrade on your production instance with appropriate backups.

[GaryAllan]

The nightly Docker images may help if you want to quickly stand something up for testing.
https://hub.docker.com/r/phpipam/phpipam-www , Docker External MySQL Server example.

Replace image: phpipam/phpipam-www:latest with image: phpipam/phpipam-www:nightly

[nutt318]

Thanks @GaryAllan ! I'll give that a try and report back.

[nutt318]

@GaryAllan So I took a backup of the database and attempted to restore it, got the below error message, any ideas? Going to the administration link just takes me right back to the restore button.

[GaryAllan]

@nutt318
Can you run the SQL queries below on the original (v1.3) database and post the output here?
The circuitProviders table was added in version 1.3.1, your schema may be somewhere between 1.3.0 and 1.3.1

SELECT version FROM settings;
SHOW TABLES;

[nutt318]

@GaryAllan Here is a screenshot of the queries requested. Thanks!

[GaryAllan]

Hi.
The circuitProviders doesn't exist in the original database. It looks like an upgrade has already been attempted.
Try deleting and re-creating the cloned database.

Both database copies should show 33 rows when you run SHOW TABLES; and print 1.3 on SELECT version FROM settings; Then log into the 1.5 web gui and run the upgrade process.

[nutt318]

I guess I'm a little confused, so delete the database from my 1.3 install and then restore it on 1.3? The current row count is 33, should it be 34 with the 'circuitProviders' table? Or should I just create the 'circuitProviders' table and attempt a restore on 1.3?

[GaryAllan]

You should have two databases. The original production database at version 1.3 and a copy/clone that we can use to iron out the upgrade process. (it looks like the copy has been modified already by a failed upgrade)

Delete the copy/clone and re-create it from the original 1.3 production database.
This can be verified by checking both databases contain 33 tables and report version 1.3.

[nutt318]

Unfortunately I do not have a backup of the original 1.3 production database. Can I download a copy somewhere? All I've got is the current running 1.3 database backup.

Should I create a new vm and install 1.3, then attempt to restore my current 1.3 backup onto it?

[GaryAllan]

Hi
1.3= your production database running the v1.3 SQL schema.

Make a clone of this database and use a v1.5 phpIPAM install to upgrade the cloned copy to the v1.5 SQL schema.

If it goes wrong you can start again as the original is untouched.

[nutt318]

I already did that here #3160 (comment) I took a backup of my 1.3 database, then restored it into my 1.5 install and attempted the upgrade and got that error.

[GaryAllan]

I took a backup of my 1.3 database, then restored it into my 1.5 install and attempted the upgrade and got that error.

The error message suggests you restored a v1.3 schema mysqldump over an existing already populated v1.5 schema database.

You need to restore the production mysqldump into a new empty database. Both databases will then be identical and both databases will show 33 tables when you run SHOW TABLES;

The v1.5 php app pointing at the 2nd cloned databse will then run the upgrade steps.

[nutt318]

Thanks for being patient with me @GaryAllan

So yes, I was doing that, sorry for the confusing. So on my new 1.5 install I created a new DB called 'phpipam2', then changed the config.php to look at this new db. I then got this screenshot as to migrate the database.

I copied my .sql dump over to the db directory and renamed it to MIGRATE.sql as in this screenshot.

After clicking the upgrade I got the following error about foreign keys, any ideas?

[GaryAllan]

Hi,

That looks like a bug. I'll need extra info to investigate.

What version of MySQL/MariaDB are you using?

How did you install the v1.5 files (git or docker)?

[nutt318]

My new v1.5 is running (Server version: 10.3.17-MariaDB MariaDB Server) and I installed it from git. Thanks for the help!

[GaryAllan]

Hello @nutt318
Assuming your production database is called phpipam. [change as appropriate] Can you run....

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='phpipam';

I think the problem is some of the tables are using the MyISAM storage engine which doesn't support foreign keys.

[GaryAllan]

If you find any MyISAM tables, the fix is to change the ENGINE to InnoDB and start the process again from the beginning.

$ cd db
$ grep -i MyISAM MIGRATE.sql
$ sed -i s/ENGINE=MyISAM/ENGINE=InnoDB/i MIGRATE.sql

[nutt318]

Closer? I got this now.

[GaryAllan]

Can you post the below from your production database?

SELECT id,type,description FROM `usersAuthMethod`;

[GaryAllan]

Edit your MIGRATE.sql file and find CREATE TABLE usersAuthMethod

Edit this section to be exactly as below and save.

CREATE TABLE `usersAuthMethod` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `type` ENUM('local','http','AD','LDAP','NetIQ','Radius','SAML2') NOT NULL DEFAULT 'local',
  `params` text DEFAULT NULL,
  `protected` ENUM('Yes','No') NOT NULL DEFAULT 'Yes',
  `description` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

If you restart the upgrade process (with an empty database) it should now complete.

I think the issue is the 1.2.0 upgrade process creates type=SET('local','AD','LDAP','NetIQ','Radius','http') and the expected DB schema is type=SET('local','http','AD','LDAP','NetIQ','Radius','SAML2'). The SET order matters as they are stored as numeric values.

[nutt318]

Here is the output of the 'usersAuthMethod' not sure if you need it now because your last comment of changing that in my MIGRATE.sql fixed it!

When I hit the login page it did act like a normal login but had to do the script to reset the admin password. Once I did that it then prompted to upgrade the database, I did the automatic and it worked great, I'm up at running

Thank you so much @GaryAllan

[nutt318]

The only thing now thats weird is my ldap auth isnt working, I've got php-ldap installed so i'm guessing its not related so i'll do some looking.

[GaryAllan]

Hello,
The password hash type changed at some point in the past so the admin password reset makes sense.

You may need to update the git submodules for LDAP, run git submodule update --init --recursive from the folder containing config.php

The usersAuthMethod table is corrupted. The row with id=2 should have type 'http' (Doesn't matter if you don't use HTTP auth)

UPDATE usersAuthMethod SET type=http WHERE id=2;

[nutt318]

Just found out I still had selinux enabled, I disabled that an it fixed the issue.

Thanks! @GaryAllan