On 12/11/10 2:32 AM, A.L.E.C wrote:

On Fri, 10 Dec 2010 16:15:54 -0500, John May wrote:

...

Does anyone have any input on this? If I can't get the SQL upgrade lines working, is there at least some way to export/import data to a newer version of the database?

Didn't you read my reply? Please, don't create new threads. Sending more posts will not help you to get help here.

http://lists.roundcube.net/mail-archive/users/2010-12/0000016.html

Sorry, I didn't see your response. Additionally, I thought I just replied to my original post as a ping - didn't realize I had created a new thread.

Regarding your reply:

On 12/11/10 2:32 AM, A.L.E.C wrote:

On 08.12.2010 15:10, John May wrote:

...

I'm trying to upgrade our 0.3.1 installation to 0.4.2.

When I execute the following SQL upgrade lines, I get the errors that follow them:

mysql> ALTER TABLE contacts ADD CONSTRAINT user_id_fk_contacts FOREIGN KEY (user_id) -> REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (pis_roundcube/#sql-36e8_638, CONSTRAINT user_id_fk_contacts FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE)

mysql> ALTER TABLE identities ADD CONSTRAINT user_id_fk_identities FOREIGN KEY (user_id) -> REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (pis_roundcube/#sql-36e8_638, CONSTRAINT user_id_fk_identities FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE)

...

The first 2 ALTER TABLE commands work OK, and I've verified all tables have been changed to InnoDB. Any ideas on how to get this to work?

There are four ALTER TABLE ... DROP FOREIGN KEY ... Did you execute them?

Yes, I ran the first two without an issue. It's these second two I've specified here that aren't working for me.

...

Alternately, is there a good way to import data from an existing older roundcube MySQL database into a fresh, up-to-date one?

What MySQL version? Check SELECT COUNT(*) FROM identities WHERE user_id NOT IN ( SELECT user_id FROM users); SELECT COUNT(*) FROM contacts WHERE user_id NOT IN ( SELECT user_id FROM users);

MySQL 5.0.91

The queries you listed output:

SELECT COUNT(*) FROM identities WHERE user_id NOT IN (SELECT user_id FROM users);

1

SELECT COUNT(*) FROM contacts WHERE user_id NOT IN (SELECT user_id FROM users);

56

Thanks for your help!

- John

On 12/13/10 2:16 AM, A.L.E.C wrote:

On 12.12.2010 01:27, John May wrote:

...

...

SELECT COUNT(*) FROM identities WHERE user_id NOT IN (SELECT user_id FROM users);

1

...

SELECT COUNT(*) FROM contacts WHERE user_id NOT IN (SELECT user_id FROM users);

56

So, you have some records that shouldn't be there. You can delete them with

DELETE FROM identities WHERE user_id NOT IN (SELECT user_id FROM users); DELETE FROM contacts WHERE user_id NOT IN (SELECT user_id FROM users);

Worked now - thanks!

- John