Issues going through db setup on ClearDB
Closed this issue · 9 comments
Hello,
Using the latest 1.5 release, I get the following error after launching off from /setup:
Error Number: 1452
Cannot add or update a child row: a foreign key constraint fails (unmarkdb.labels, CONSTRAINT FK_label_smart_label_id FOREIGN KEY (smart_label_id) REFERENCES labels (label_id) ON DELETE CASCADE ON UPDATE CASCADE)
INSERT INTO labels (smart_label_id, domain, smart_key, created_on) VALUES ('2', 'php.net', '5f810408ddbbd6d349b4be4766f41a37', '2014-04-03 14:59:01')
Filename: D:\home\site\wwwroot\system\database\DB_driver.php
Line Number: 330
Please forgive my database inexperience here.. Any idea what is causing the error? I'm using a MySQL db from ClearDB. I have made no changes to the unmark configs other than providing the URL, user, pass, and DB in databases.php.
Welcome,
Can you provide a little bit more details on how you ended up seeing that error? Did it happen while running /install action, or was it after you've created an account and tried to add a mark?
Also, was it a clean 1.5 version install, or did you have older version and run an upgrade?
Sure thing. it was while running /install action on a clean install of 1.5.
Here's the relevant portion of the log if it helps:
DEBUG - 2014-04-03 14:58:57 --> Loader Class Initialized
DEBUG - 2014-04-03 14:58:57 --> Plain_Loader Class Initialized
DEBUG - 2014-04-03 14:58:57 --> Config file loaded: application/config/all/app.php
DEBUG - 2014-04-03 14:58:57 --> Helper loaded: data_helper
DEBUG - 2014-04-03 14:58:57 --> Helper loaded: hash_helper
DEBUG - 2014-04-03 14:58:57 --> Helper loaded: validation_helper
DEBUG - 2014-04-03 14:58:57 --> Helper loaded: view_helper
DEBUG - 2014-04-03 14:58:57 --> Database Driver Class Initialized
DEBUG - 2014-04-03 14:58:57 --> Controller Class Initialized
DEBUG - 2014-04-03 14:58:57 --> Migrations class initialized
DEBUG - 2014-04-03 14:58:57 --> Language file loaded: language/english/migration_lang.php
DEBUG - 2014-04-03 14:58:57 --> Database Forge Class Initialized
DEBUG - 2014-04-03 14:58:57 --> Database Forge Class Initialized
DEBUG - 2014-04-03 14:58:57 --> Database Utility Class Initialized
DEBUG - 2014-04-03 14:58:57 --> Helper loaded: file_helper
INFO - 2014-04-03 14:58:57 --> Created backup file D:\home\site\wwwroot\application\cache\db_backups\db_0_1396562337.bak.gz
DEBUG - 2014-04-03 14:58:57 --> Migrating up from version 0 to version 1
DEBUG - 2014-04-03 14:58:58 --> Migrating up from version 1 to version 2
DEBUG - 2014-04-03 14:58:58 --> Migrating up from version 2 to version 3
DEBUG - 2014-04-03 14:58:59 --> Migrating up from version 3 to version 4
DEBUG - 2014-04-03 14:58:59 --> Migrating up from version 4 to version 5
DEBUG - 2014-04-03 14:59:00 --> Migrating up from version 5 to version 6
DEBUG - 2014-04-03 14:59:00 --> Migrating up from version 6 to version 7
DEBUG - 2014-04-03 14:59:01 --> DB Transaction Failure
ERROR - 2014-04-03 14:59:01 --> Query error: Cannot add or update a child row: a foreign key constraint fails (`unmarkdb`.`labels`, CONSTRAINT `FK_label_smart_label_id` FOREIGN KEY (`smart_label_id`) REFERENCES `labels` (`label_id`) ON DELETE CASCADE ON UPDATE CASCADE)
DEBUG - 2014-04-03 14:59:01 --> Language file loaded: language/english/db_lang.php
@ianbannerman This week @kip9 is on vacation. Have you asked ClearDB for any support with this?
I have not, no.. I don't have a particular relationship with them, just using one of their free MySQL dbs via their relationship with Azure. www.cleardb.com/store/azure
Free ClearDB MySQL + Free Azure Website has been a nice way to try new projects.
@ianbannerman - is it possible you've made another attempt on the same DB prior to current install?
I think I found an issue which may occur when you're trying to set up unmark on a DB with not clean state (like Auto increment field with non zero values).
Can you please run following mysql statements manually and then make another attempt:
drop table if exists labels;
drop table if exists marks;
drop table if exists migrations;
drop table if exists plain_sessions;
drop table if exists tags;
drop table if exists tokens;
drop table if exists user_marks_to_tags;
drop table if exists users;
drop table if exists user_settings;
drop table if exists users_to_marks;
WARN: This will wipe those tables, so if you have any data in these, it will be lost. Please do that only if you're trying to make a fresh install.
All right, I ran through and dropped all existing tables:
mysql> drop table if exists labels;
Query OK, 0 rows affected (0.27 sec)
mysql> drop table if exists marks;
Query OK, 0 rows affected (0.20 sec)
mysql> drop table if exists migrations;
Query OK, 0 rows affected (0.11 sec)
mysql> drop table if exists plain_sessions;
Query OK, 0 rows affected (0.09 sec)
mysql> drop table if exists tags;
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> drop table if exists tokens;
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> drop table if exists user_marks_to_tags;
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql> drop table if exists users;
Query OK, 0 rows affected (0.13 sec)
mysql> drop table if exists user_settings;
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> drop table if exists users_to_marks;
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql> show tables;
+--------------------+
| Tables_in_unmarkdb |
+--------------------+
| users_marks |
| users_smartlabels |
+--------------------+
2 rows in set (0.07 sec)
mysql> drop table users_marks;
Query OK, 0 rows affected (0.09 sec)
mysql> drop table users_smartlabels;
Query OK, 0 rows affected (0.13 sec)
mysql> show tables;
Empty set (0.07 sec)
mysql>
And as expected /setup prompted me with the Unmark: Setup page. Clicking install I again got this:
A DATABASE ERROR OCCURRED
Error Number: 1452
Cannot add or update a child row: a foreign key constraint fails (`unmarkdb`.`labels`, CONSTRAINT `FK_label_smart_label_id` FOREIGN KEY (`smart_label_id`) REFERENCES `labels` (`label_id`) ON DELETE CASCADE ON UPDATE CASCADE)
INSERT INTO `labels` (`smart_label_id`, `domain`, `smart_key`, `created_on`) VALUES ('2', 'php.net', '5f810408ddbbd6d349b4be4766f41a37', '2014-04-14 07:52:56')
Filename: D:\home\site\wwwroot\system\database\DB_driver.php
Line Number: 330
Show tables now returns:
mysql> show tables;
+--------------------+
| Tables_in_unmarkdb |
+--------------------+
| labels |
| marks |
| migrations |
| plain_sessions |
| users |
| users_marks |
| users_smartlabels |
+--------------------+
7 rows in set (0.07 sec)
mysql>
Any ideas? I can share credentials to the cleardb if that would help. (They're easy to delete & spin up again, no charges for it). Thank you for the help thus far, sorry for the trouble! :)
@ianbannerman Sorry that we could not be more helpful here.
I'm closing this ticket purely because we don't "support" ClearDB. But if anyone ever figures this out please reopen and submit patch.