Errors creating tables

Come here for help or to post comments on Sphider
Post Reply
usabilitest
Posts: 9
Joined: Thu Aug 31, 2023 9:17 pm

Errors creating tables

Post by usabilitest »

I've attempted to run /admin/install.php file but got the following errors:
Error: Can't create table `links` (errno: 121 "Duplicate key on write or update")
Error: Can't create table `link_keyword0` (errno: 150 "Foreign key constraint is incorrectly formed")
Error: Can't create table `link_keyword1` (errno: 150 "Foreign key constraint is incorrectly formed")
Error: Can't create table `link_keyword2` (errno: 150 "Foreign key constraint is incorrectly formed")
Error: Can't create table `link_keyword3` (errno: 150 "Foreign key constraint is incorrectly formed")
Error: Can't create table `link_keyword4` (errno: 150 "Foreign key constraint is incorrectly formed")
Error: Can't create table `link_keyword5` (errno: 150 "Foreign key constraint is incorrectly formed")
Error: Can't create table `link_keyword6` (errno: 150 "Foreign key constraint is incorrectly formed")
Error: Can't create table `link_keyword7` (errno: 150 "Foreign key constraint is incorrectly formed")
Error: Can't create table `hlink_keyword8` (errno: 150 "Foreign key constraint is incorrectly formed")
Error: Can't create table `link_keyword9` (errno: 150 "Foreign key constraint is incorrectly formed")
Error: Can't create table `link_keyworda` (errno: 150 "Foreign key constraint is incorrectly formed")
Error: Can't create table `link_keywordb` (errno: 150 "Foreign key constraint is incorrectly formed")
Error: Can't create table `link_keywordc` (errno: 150 "Foreign key constraint is incorrectly formed")
Error: Can't create table `link_keywordd` (errno: 150 "Foreign key constraint is incorrectly formed")
Error: Can't create table `link_keyworde` (errno: 150 "Foreign key constraint is incorrectly formed")
Error: Can't create table `link_keywordf` (errno: 150 "Foreign key constraint is incorrectly formed")
Error: Can't create table `site_category` (errno: 121 "Duplicate key on write or update")
Error: Can't create table `pending` (errno: 121 "Duplicate key on write or update")
Error: Can't create table `images` (errno: 121 "Duplicate key on write or update")
Error: Can't create table `hist_db`.`_search_rss_links` (errno: 121 "Duplicate key on write or update")
Creating tables failed. Consult the above error messages.
What am I missing? It did create sites table.
User avatar
captquirk
Site Admin
Posts: 306
Joined: Sun Apr 09, 2017 8:49 pm
Location: Arizona, USA
Contact:

Re: Errors creating tables

Post by captquirk »

Did you edit the install script?
I ask because I see a couple things that I would not expect.
Error: Can't create table `hlink_keyword8
Error: Can't create table `hist_db`.`_search_rss_links`
I don't know what may have been changed. While I have no problem with users modifying Sphider (I encourage it! That's what I did with the original Sphider!)
One thing to check is that all tables are InnoDB (or in newer versions of MySql, NDB).
https://dev.mysql.com/doc/refman/8.0/en ... -keys.html is a good place to learn more about foreign key restraints.

My suggestion, if you are adding any tables, is to follow the same pattern used for each table in install.php. Maybe copy a table creation section of code, paste it, then modify it as to name, column name/type, then mold the foreign key using the your needs for that table.
usabilitest
Posts: 9
Joined: Thu Aug 31, 2023 9:17 pm

Re: Errors creating tables

Post by usabilitest »

Yes, I'm sorry I was trying all sorts of things but my results are pretty consistent. The install script installed the following tables before it starts throwing errors:
  • categories
    domains
    keywords
    query_log
    rss_sites
    settings
    sites
usabilitest
Posts: 9
Joined: Thu Aug 31, 2023 9:17 pm

Re: Errors creating tables

Post by usabilitest »

I think I've figured out what the problem is. In CONSTRAINTS areas some tables have a prefix and some don't:

Code: Select all

CONSTRAINT links_ibfk_1 FOREIGN KEY (site_id) REFERENCES ".$mysql_table_prefix."sites (site_id) ON DELETE CASCADE ON UPDATE CASCADE
User avatar
captquirk
Site Admin
Posts: 306
Joined: Sun Apr 09, 2017 8:49 pm
Location: Arizona, USA
Contact:

Re: Errors creating tables

Post by captquirk »

If you are referring to "$mysql_table_prefix", it depends. IF $mysql_table_prefix is defined, then all tables MUST use it.
On the other hand, if $mysql_table_prefix is not defined, it is NULL and can be omitted.

There are rules for defining CONSTRAINTS. You will find those in the MySQL help page dealing with CONSTRAINTS. They do need unique names.
Post Reply