[Solved] phpMyAdmin MySQL database migration error

Hi,

I’m trying to migrate my first MODx site.

I installed MODx with the same username locally and on the remote server.

When trying to import the MySQL database with phpMyAdmin, I get this error :



where prf is the table prefix for my MODx site and TheUser is the user name.

I think that the problem comes from the fact that the user already exists on the remote site. What should I do ?

Another problem is that I don’t know if my site was partially imported.

Thanks.

Well if the table is for active users, just make sure that no one is visiting your MODx site (including yourself)..
In other words, log out of the manager interface, optionally close and reopen your browser, and try again.

In case this table is an internal one you may not need to insert data into it.. from its name it looks to be a dynamically updated one.

Yes, you don’t need to restore this table, it looks as though you took the mysql dump with ’TheUser’ logged in on localhost(127.0.0.1)

Thanks for the quick answers. You were right, I was visiting the site while using phpMyAdmin.

Now, I tried again, and still get this error :

but without more information.

I observed that the remote database now contains some (all?) imported tables.

But how to know if some records were imported twice and if the import process could be completed ?

After the import, I logged in the manager, but no document had been attached to the tree.

Thanks for further help...

The import probably failed at the point you hit your error, drop all the tables in your remote database, make sure your logged out and do the import again.

If that fails go to your original site, make sure you are logged out(I would also clear the manager/event logs here unless you really need this info) and do the export again, drop the remote tables again and try the import once more.

In my SQL dumps the active user table is the first dumped, so I would expect if this failed not much else would have been imported into your remote database.

Thanks.

drop all the tables in your remote database

Sorry for my ignorance: does this mean deleting all the tables in the remote database or just emptying them from their records and keep the structure?
In the second case, I don’t know how to do it with phpMyAdmin (some SQL request maybe).

Is there no risk to have problems with the MODx manager ?
I thought that MODx’s manager configuration was also stored in tables with the same prefix as my site.
Isn’t it ?

I still cannot migrate my MODx site after many hours to try. I’m really lost. :’(

Maybe the problem comes from conflicts between tables resulting from the MODx installation on the remote server and the tables imported from the local server. I can assume that it is necessary to preserve some tables from the remote installation. But then, how to handle the fact that the tables in the migrated database can have the same names ?

Another possible explanation is that the remote database (remDat) name and database usernames (datUser) are prefixed by the hosting account username (hostUser) and an underscore, so there are different from my local MODx site.
remote database : hostUser_remDat
rem. datab. user : hostUser_datUser

Here what I did :

1) Used the MySQL wizard to create the remote database and some user : hostUser_remDat , hostUser_datUser

2) Installed MODx remotely, using above data for the connection to the database.
For MODx admin name, I used datUser (without any prefix).
(I use the same admin name and password locally.)

3) Tried to import my database with phpMyAdmin :
got error duplicate entry for "datUser" in table remDat_active_users

4) Deleted user hostUser_datUser with the MySQL manager.
Created another user hostUser_anotherDatUser, so remote and imported users do not conflict.
(because of the error at step 3)

5) Dropped all remote tables (but not dropped the database),
resulting in an empty database.
(because of the error at step 3)

6) Tried again to import my database with phpMyAdmin

7) Tables seems imported (not sure if completely).
The duplicate entry error for "datUser" displayed again,
although there is only one user with the datUser name
in the imported database and no more datUser in the remote database.

Results :

1) The remote site displays :

« MODx Parse Error »
MODx encountered the following error while attempting to parse the requested resource:
« PHP Parse Error »

2) The manager displays :

Failed to create the database connection!
. Please run the MODx install utility

After, I created user hostUser_datUser for MySQL again but this did not solve anything.

I wonder if all these problems can be due to the fact that the local and the remote databases do not have the same name as the remote one is prefixed with the hosting account name.

Any help would be much appreciated, especially step by step instructions letting know in which order you install the remote MODx and migrate the database. Thanks a lot.

I’m also very interested in knowing if the plugins and snippets installed during MODx setup must be exactly the same on the remote site and on the local site. (I’m interested in a minimal MODx on the remote site since I did not use all extensions.)

The main thing you did wrong was to install MODx before moving the DB data. You should have done it the other way around.

What I’d suggest now, is to drop all the tables in your new DB (In PhpMyAdmin, select all tables and use the "with all" drop-down near the bottom, pick "drop"). This should leave the DB competely empty.

Then follow the steps here exactly (starting around step 4): http://modxcms.com/moving-sites-to-new-servers.html

To help you understand it and provide an overview of the process or migration, here’s how it works.

You dump the current DB contents, usually using Export in PhpMyAdmin.
You copy the files and edit the file manager/includes/config.inc.php on the new site to match the configuration there.
You may also have to edit .htaccess if it exists.
You Import the DB you dumped from the old DB into the new DB, usually using PhpMyAdmin Import.

At this point, MODx is installed and will often work. It’s a perfect copy of the old site -- same admin username and password, same users, same content.
Sometimes, you’ll have to run an Upgrade install of MODx in the new site to straighten things out (yoursite.com/install).

As for the content you don’t want on the new site, there are at least two ways to go. One is to forget the above process and do a fresh install at the new site. Then you’d have to selectively import or cut-and-paste all your content, users, etc. It’s kind of chancy.

What I’d recommend instead is to do the migration process just as described in the link above. Then selectively delete the stuff you don’t want from the new site. When you delete something you don’t want, you can find its files and delete them too.

For example, if you delete the Ditto snippet, you’ll find its files in /assets/snippets/ditto.

Hope this helps.

The main thing you did wrong was to install MODx before moving the DB data. You should have done it the other way around.

Thanks a lot Bob. While you were writing your answer, I also realized that it was probably good idea to start by moving the DB data.
So, what I did :
1) totally removed database
2) created again the database using the "Create database" wizard from cPanel, i.e. an empty database.
3) Tried to import the database using phpMyAdmin but got an error :

Erreur

requête SQL:

--
-- Contenu de la table `alti_active_users`
--
INSERT INTO `alti_active_users` ( `internalKey` , `username` , `lasthit` , `id` , `action` , `ip` )
VALUES ( 1, ’Julien’, 1229013344, NULL , ’8’, ’127.0.0.1’ ) ;


MySQL a répondu:
#1062 - Duplicate entry ’1’ for key 1

I cannot understand why it detects a duplicate entry...
Ever heard about this error ?

Tables seems to be imported, but because of above error, I’m not sure if the job was completed or only partially done.

I’ll try going with the installation but solving above error might be necessary.

Well, after importing the database, I could access again to MODx’s manager.
I could see the document tree which was correctly imported.

When logging to the manager, there is this error :

"Error while inserting event log into database."

I remember that I had dropped the event logs table before exporting the database, so I could explain this error.

The path and url to ressources were previously set and seem still OK.
(see step 7 from this procedure : http://modxcms.com/moving-sites-to-new-servers.html )

Unfortunately, MODx’s default demo page still displays when accessing the site.
I’ll try an upgrade install as suggested at step 9 from the procedure.