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

That's the kind of error nobody gets unless they have messed with the database structure :)

1. Please, provide a dump of the database structure. I'd say the qap_users and the qap_userlimits tables should be enough. Just the structure, no need for data
2. What is your "Q2A database version" that shows up in admin/stats ?
3. Do you face that issue if you remove all non-core plugins ?

---

As a side note: unless you're the owner of mydomain.com (which I doubt), don't use it (or some other random domain owned by somebody else) for obfuscating your actual domain. RFC 2606 expressly reserves some domains for this very purpose (example.com and example.org, among others), so use those instead.

---

Tables structure:


CREATE TABLE `qa_users` (
  `userid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL,
  `createip` varbinary(16) NOT NULL,
  `email` varchar(80) NOT NULL,
  `handle` varchar(20) NOT NULL,
  `avatarblobid` bigint(20) unsigned DEFAULT NULL,
  `avatarwidth` smallint(5) unsigned DEFAULT NULL,
  `avatarheight` smallint(5) unsigned DEFAULT NULL,
  `passsalt` binary(16) DEFAULT NULL,
  `passcheck` binary(20) DEFAULT NULL,
  `passhash` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `level` tinyint(3) unsigned NOT NULL,
  `loggedin` datetime NOT NULL,
  `loginip` varbinary(16) NOT NULL,
  `written` datetime DEFAULT NULL,
  `writeip` varbinary(16) DEFAULT NULL,
  `emailcode` char(8) CHARACTER SET ascii NOT NULL DEFAULT '',
  `sessioncode` char(8) CHARACTER SET ascii NOT NULL DEFAULT '',
  `sessionsource` varchar(16) CHARACTER SET ascii DEFAULT '',
  `flags` smallint(5) unsigned NOT NULL DEFAULT '0',
  `wallposts` mediumint(9) NOT NULL DEFAULT '0',
  PRIMARY KEY (`userid`),
  KEY `email` (`email`),
  KEY `handle` (`handle`),
  KEY `level` (`level`),
  KEY `created` (`created`,`level`,`flags`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `qap_userlimits` (
  `userid` int(10) unsigned NOT NULL,
  `action` char(1) CHARACTER SET ascii NOT NULL,
  `period` int(10) unsigned NOT NULL,
  `count` smallint(5) unsigned NOT NULL,
  UNIQUE KEY `userid` (`userid`,`action`),
  CONSTRAINT `qap_userlimits_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `qap_users` (`userid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

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


This occurs occasionally and on a site that is in use. I cannot disable plugins, as that would render site unusable. However I do not think any of plugins is related to this:

Adresar v0.1 - Pedja Supurovic
Question2Answer plugin for displaying internal Infosys address book

Cloudflare User IP support v0.1 - InfinityLF (latest unknown)
Get visitors real IP address instead of CloudFlare's

Comment to Answer v0.4 - q2apro.com (...)
Converts a comment to an answer, optionally moves the succeeding comments

Create New Users v0.2 - q2apro.com
Create new users manually from a seperate page for admins

Database Backup v1.0 - Krzysztof Kielce
Database import/export utility - options

Embed v1.7 - NoahY (latest)
Embed Video, Images and MP3 files - options

Event Logger v1.1 - Question2Answer
Stores a record of user activity in the database and/or log files - options

Example Page v1.1 - Question2Answer
Example of a page plugin

Expert Questions v2.2 - NoahY (latest unknown)
Ask expert questions - options

Facebook Login v1.1.5 - Question2Answer
Allows users to log in via Facebook

Featured Questions v1 - NoahY (latest)
Keep featured questions at top of list - options

Google Analytics v1.1 - Ivan (latest)
Inserts Analytics code in the <head> and can skip for admin - options

Hide Users List v0.3 - Pedja Supurovic (get v0.4)
Question2Answer plugin for hiding users list from anonymous visitors and not confirmed users

Image Manager / List Uploads v0.5 - q2apro.com (latest unknown)
Displays the newest image uploads on a separate page

Mouseover Layer v1.0.1 - Question2Answer
Shows question content on mouse over in question lists - options

On-Site-Notifications v1.2 - q2apro.com (get v1.3.0)
Facebook-like / Stackoverflow-like notifications on your question2answer forum that can replace all email-notifications. - options

OpenSearch Support v1.0 - Question2Answer
Allows OpenSearch clients to search Q2A site directly

Permissions2Categories v1.0. - Kirill Fuchs
Allows permissions to be set for categories.

Prevent Simultaneous Edits v0.1 - echteinfachtv (...)
Prevents simultaneous post edits by your users

Print v1.0b - NoahY (...)
Adds print view to questions - options

reCAPTCHA v2.0 - Question2Answer
Provides support for reCAPTCHA captchas

Share v1.2 - NoahY (...)
Adds social sharing buttons to questions - options

Tag Cloud Widget v1.0.1 - Question2Answer
Provides a list of tags with size indicating popularity - options

Tagging Tools v1.8.1 - Scott Vivian (...)
Automatically modify/remove tags in questions - options

Translator v0.3 - Pedja Supurovic (...)
Question2Answer plugin for online translation of Question2Answer language files

Warn On Leave v1.0 - q2apro.com (...)
Warns the user after he entered text in textarea or CKEditor and is leaving the page - options

Widget Anywhere v1.3.1 - Scott Vivian (...)
Add custom HTML on any page(s) in a variety of locations. Useful for Google Adsense, Analytics, adding special instructions for asking questions, and so on. - options

WYSIWYG Editor v1.1.1 - Question2Answer
Wrapper for CKEditor WYSIWYG rich text editor - options

XML Sitemap v1.1.1 - Question2Answer
Generates sitemap.xml file for submission to search engines - options

---

Now I am thinking - I actually have two Q2A sites on the server that share the same users.

It is achieved by using of documented feature as setting QA_MYSQL_USERS_PREFIX in config to be the same on both sites.

For other tables different prefix for each site is used.

Maybe this is problem:
CONSTRAINT `qap_userlimits_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `qap_users`

This cosntarint references qap_users but that tabel is not in use. It should be qa_users?

---

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