Another solution to the SQL foreign key problem

I get this error too, but my maildb users table doesn’t have a `user_id` INT(10) UNSIGNED field–it has a `id` VARCHAR(255) field instead for its primary key. I tried a few modified versions of this table but nothing quite seemed to work. Any suggestions for how I modify the mysql.sql query to create a table that will work? Thanks in advance!

Hi,

I modified the mysql.sql script as follows (both changing user_id to type VARCHAR(128) and changing the referenced key in the users table):


CREATE TABLE `auth_tokens` (
`token` varchar(128) NOT NULL,
`expires` datetime NOT NULL,
`user_id` varchar(128) NOT NULL,
`user_name` varchar(128) NOT NULL,
`user_pass` varchar(128) NOT NULL,
KEY `user_id_fk_auth_tokens` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `auth_tokens`
ADD CONSTRAINT `auth_tokens_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;


and the error I get is

ERROR 1005 (HY000): Can’t create table ‘maildb.#sql-25a_1a7cc’ (errno: 150)

All of my tables except auth_tokens are driven by MyISAM. Perhaps it’s just my lack of in-depth knowledge of MySQL, but I’m concerned that changing the engine on the tables in a working system will cause something to break elsewhere. Is the only solution to change the engine for my other tables?

Thanks for your response!

You’re right, Manuel, that’s the obvious thing to try! I assumed there was a reason why it needed to be InnoDB since it was explicitly specified. Indeed, when I try leaving out the ENGINE argument, the table is created (and I can see that it is MyISAM) and altered without a problem.

However, even after copying the config.inc.php.dist to config.inc.php, turning if_pl_use_auth_tokens on, and restarting apache, the persistent login doesn’t work: when logging out, logging back in again, closing and re-opening my browser, I get a message “Invalid request! No data was saved” and am dumped back to the login screen. All this time, the auth_tokens table remained empty. It worked, of course, with the UserData cookies.

So does this mean that InnoDB is the required engine, or is it perhaps something else? I’ll do more investigation later, unless you have any ideas.

Still no logs in /var/log/apache/error.log or the Roundcube’s log folder?

I will try it on my own at the weekend to find a general approach for this case.

Ah okay..
The plugin uses the database configuration from your “config/db.inc.php”, which probably points to the “roundcube” database, right?

I’m glad to hear that i finally works for you.
I will update the project page with the suggested sentence. Every bit of information helps.