Foreign constraint error on submitting ask page

Question

Recently it started to show foreign constraint errors when user submits ask page.  For some user it never happens, for others it happens occasionally, and for one user it happens every single time.

For some users it helps if user is deleted, created again and for some time error does not show up but eventually it happens again.

Error in log is this:

Question2Answer MySQL query error 1452: Cannot add or update a child row: a foreign key constraint fails (`database`.`qap_userlimits`, CONSTRAINT `qap_userlimits_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `qap_users` (`userid`) ON DELETE CASCADE) - Query: INSERT INTO qap_userlimits (userid, action, period, count) VALUES ('54', 'U', 450872, 1) ON DUPLICATE KEY UPDATE count=IF(period=450872, count+1, 1), period=450872, referer: https://mydomain.com/ask

 

Comments

Yes. That's correct

---

SOLVED!

Indeed, problem was in constraint. After I replaced qap_users with qa_users it fixed the issue.

qa_users is the table both sites use for storing user data. qap_users is obsolete. It contained some old data so that explains why som users had problems and others did not.

---

I've checked other tables and found out that anywhere constraint is set onto qap_users instead of qa_users.

It seems it is not enough just to set QA_MYSQL_USERS_PREFIX in config to share users among several Q2A sites.

---

> Q2A version is 1.8.0. I stated that in question itself.

I asked for the "Q2A database version". You haven't provided that piece of information.

Aside from that, you missed a very important piece of information you've just added to the question:
> For some users it helps if user is deleted, created again and for some time error does not show up but eventually it happens again.

Note you can't "create again" a user. It is a new one. With different ID.

Answer 1

The analysis you made is correct and it's clear the constraint is referencing the wrong table. One option is to recreate the constraint in each table of the new site. Now, bear in mind that if you delete a user from a site, it will cascade to all other sites.

Regarding the "it is not enough just to set QA_MYSQL_USERS_PREFIX in config to share users", that is correct: you need to take actions in the database, such as renaming the tables, for that feature to work. However, that applies to QA_MYSQL_TABLE_PREFIX as well: you need to take actions in the database, such as renaming the tables, for that feature to work. So it is up to you to keep in sync the table names and the prefixes.

The best way, IMO, to mange these scenarios is:

Site 1:

• QA_MYSQL_TABLE_PREFIX = qa_site1_
• QA_MYSQL_USERS_PREFIX = qa_users_

...

Site N:

• QA_MYSQL_TABLE_PREFIX = qa_siteN_
• QA_MYSQL_USERS_PREFIX = qa_users_

Note the users table is not related to any site. Based on the naming on your tables and references it seems your structure is actually:

Site 1:

• QA_MYSQL_TABLE_PREFIX = qa_site1_
• QA_MYSQL_USERS_PREFIX = qa_site1_users_

...

Site N:

• QA_MYSQL_TABLE_PREFIX = qa_siteN_
• QA_MYSQL_USERS_PREFIX = qa_site1_users_

I can almost confirm this by your comment about the table having old data. There is also another thing that confirms this which is when you rename a table, constraints are renamed as well. Your constraints were not renamed.

So in the likely case you are in the second scenario (the one with the qa_site1_users_ table), if you already have the Site 1 live then, before setting up Site N, you will have to:

1. Rename the users table in Site 1 to use a generic name (this will rename all constraints as well)
2. Setup the right QA_MYSQL_USERS_PREFIX in Site N
3. Install Q2A in Site N

Comments

useful answer from the above users