FUDforum: Bug Reports » SQL Error: Unique key violated for forum_read and forum

Home » FUDforum Development » Bug Reports » SQL Error: Unique key violated for forum_read and forum_notify

Show: Today's Messages :: Polls :: Message Navigator

SQL Error: Unique key violated for forum_read and forum_notify [message #27842]

Wed, 28 September 2005 10:53

awadallah

Messages: 11
Registered: September 2005
Location: Palo Alto, CA

Karma: 0

Junior Member

Its a very weird error that only happens when a users trys to post to same topic more than once, sometimes I get the following error for thread_notify:

(/egypt_chat/index.php:200
/egypt_chat/theme/default/post.php:1081
/egypt_chat/theme/default/post.php:3861
/egypt_chat/index.php:1171
) 2000: Unknown MySQL error
Query: INSERT INTO fud26_thread_notify (user_id, thread_id) VALUES (2, 12)
_GET: array ( 't' => 'post', )
_POST: array ( 'msg_subject' => 'Re: ��', 'msg_icon' ...
Server Version: 4.1.12

and some other times I get the following error for thread_read:

(/egypt_chat/index.php:200
/egypt_chat/index.php:1046
/egypt_chat/theme/default/msg.php:1050
/egypt_chat/index.php:1171
) 2000: Unknown MySQL error
Query: INSERT INTO fud26_forum_read (forum_id, user_id, last_view) VALUES (10, 2, 1127896274)
_GET: array ( 't' => 'msg', 'th' => '12', 'rid' => '2', 'S' ..
Server Version: 4.1.12

or:

(/egypt_chat/index.php:200
/egypt_chat/index.php:1046
/egypt_chat/theme/default/post.php:3867
/egypt_chat/index.php:1171
) 2000: Unknown MySQL error
Query: INSERT INTO fud26_forum_read (forum_id, user_id, last_view) VALUES (10, 2, 1127899088)
_GET: array ( 't' => 'post', )
_POST: array ( 'spell_chk_subject_3' ...
Server Version: 4.1.12

its clear the the forum_id, user_id will be violated in both of these cases (since they are repeat visits) which leads mysql server to return an error.

what is the solution ? I had this problem with 2.6, I upgraded to 2.7.2 and still have this problem. Everything else seems to be working fine, user registration, posting a brand new topic, etc.

thanks,

-- amr

Report message to a moderator

Re: SQL Error: Unique key violated for forum_read and forum_notify [message #27846 is a reply to message #27842]

Wed, 28 September 2005 13:57

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

Enable MySQL 4.1 optimization setting in the admin control panel.

The problem seems to stem from the fact that the forum is unable to retrieve error messages from MySQL.

FUDforum Core Developer

Report message to a moderator

Re: SQL Error: Unique key violated for forum_read and forum_notify [message #27860 is a reply to message #27846]

Wed, 28 September 2005 20:39

awadallah

Messages: 11
Registered: September 2005
Location: Palo Alto, CA

Karma: 0

Junior Member

MySQL optimization is enabled in fudforum.

The error is a unique key violation error (despitate fact that MySQL error message just says unknown error).

does your code depend on the error code returned ? In that case I would have to contact my hosting provider to figure out why the heck the mysql server is replying back with Unknown error.

But I did try the sql statement directly, and when I change one of the userid or forumid it works fine, so it is just about a unique key violation. I am referring to this statement:

INSERT INTO fud26_thread_notify (user_id, thread_id) VALUES (2, 12)

OR

INSERT INTO fud26_forum_read (forum_id, user_id, last_view) VALUES (10, 2, 1127899088)

Thanks,

-- amr

Report message to a moderator

Re: SQL Error: Unique key violated for forum_read and forum_notify [message #27865 is a reply to message #27860]

Wed, 28 September 2005 22:45

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

Fudforum relies on an error code being returned, it is not in your case hence the problem. If you have MySQL 4.1 optimization setting turned on this query shouldn't even happen. Instead the query should have a suffix with ON DUPLICATE KEY UPDATE ...

FUDforum Core Developer

Report message to a moderator

Re: SQL Error: Unique key violated for forum_read and forum_notify [message #27867 is a reply to message #27842]

Wed, 28 September 2005 23:13

awadallah

Messages: 11
Registered: September 2005
Location: Palo Alto, CA

Karma: 0

Junior Member

interesting, I double checked in the Admin Control Panel, and it says:

Use MySQL 4.1 Performance Options: YES
If enabled the forum will use various query optimization tricks available through MySQL 4.1.2+ functionality.

Is that the setting you referring too ?

Also this is a paste of the GLOBALS.php file in case that helps:

<?php
/**
* copyright : (C) 2001-2004 Advanced Internet Designs Inc.
* email : forum(at)prohost(dot)org
* $Id: GLOBALS.php,v 1.44 2004/11/24 19:53:33 hackie Exp $
*
* This program is free software; you can redistribute it and/or modify it
* under the terms of the GNU General Public License as published by the
* Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
**/

$INCLUDE = "//FUDforum/include/";
$WWW_ROOT = "http://egypt-chat.asfory.com/";
$WWW_ROOT_DISK = "//egypt_chat/";
$DATA_DIR = "//FUDforum/";
$ERROR_PATH = "//FUDforum/errors/";
$MSG_STORE_DIR = "//FUDforum/messages/";
$TMP = "//FUDforum/tmp/";
$FILE_STORE = "//FUDforum/files/";
$FORUM_SETTINGS_PATH = "//FUDforum/cache/";

$FUD_OPT_1 = 1731638323;
$FUD_OPT_2 = 692990317;
$FUD_OPT_3 = 5216;

$CUSTOM_AVATAR_MAX_SIZE = 10000; /* bytes */
$CUSTOM_AVATAR_MAX_DIM = "64x64"; /* width x height (pixels) */

$COOKIE_PATH = "/egypt_chat/";
$COOKIE_DOMAIN = ".asfory.com";
$COOKIE_NAME = "fud_session_1111565409";
$COOKIE_TIMEOUT = 604800; /* seconds */
$SESSION_TIMEOUT = 1800; /* seconds */

$DBHOST = "mysql";
$DBHOST_USER = "HIDDEN";
$DBHOST_PASSWORD = "HIDDEN";
$DBHOST_DBNAME = "fudforum";
$DBHOST_TBL_PREFIX = "fud26_"; /* do not modify this */

$FUD_SMTP_SERVER = "";
$FUD_SMTP_TIMEOUT = 10; /* seconds */
$FUD_SMTP_LOGIN = "";
$FUD_SMTP_PASS = "";

$ADMIN_EMAIL = "egyptchat(at)asfory(dot)com";

$PRIVATE_ATTACHMENTS = 0; /* int */
$PRIVATE_ATTACH_SIZE = 1000000; /* bytes */
$MAX_PMSG_FLDR_SIZE = 100000; /* bytes */

$FORUM_IMG_CNT_SIG = 2; /* int */
$FORUM_SIG_ML = 256; /* int */

$UNCONF_USER_EXPIRY = 30; /* days */
$MOVED_THR_PTR_EXPIRY = 5; /* days */

$MAX_SMILIES_SHOWN = 15; /* int */
$DISABLED_REASON = "Temporarily offline; please come back soon!";
$POSTS_PER_PAGE = 40;
$THREADS_PER_PAGE = 40;
$WORD_WRAP = 0;
$NOTIFY_FROM = "egyptchat(at)asfory(dot)com"; /* email */
$ANON_NICK = "Anonymous User";
$FLOOD_CHECK_TIME = 60; /* seconds */
$SERVER_TZ = "America/Los_Angeles"; /* timezone code from tz.inc */
$SEARCH_CACHE_EXPIRY = 172800; /* seconds */
$MEMBERS_PER_PAGE = 40;
$POLLS_PER_PAGE = 40;
$THREAD_MSG_PAGER = 5;
$GENERAL_PAGER_COUNT = 15;
$EDIT_TIME_LIMIT = 60;
$LOGEDIN_TIMEOUT = 15; /* minutes */
$MAX_IMAGE_COUNT = 10;
$STATS_CACHE_AGE = 600; /* seconds */
$FORUM_TITLE = "Egypt Chat by Asfory";
$MAX_LOGIN_SHOW = 25;
$MAX_LOCATION_SHOW = 40;
$SHOW_N_MODS = 2;

$TREE_THREADS_MAX_DEPTH = 15;
$TREE_THREADS_MAX_SUBJ_LEN = 75;

$REG_TIME_LIMIT = 60; /* seconds */
$POST_ICONS_PER_ROW = 9; /* int */
$MAX_LOGGEDIN_USERS = 50; /* int */
$PHP_COMPRESSION_LEVEL = 9; /* int 1-9 */
$MNAV_MAX_DATE = 90; /* days */
$MNAV_MAX_LEN = 256; /* characters */

$RDF_MAX_N_RESULTS = 100; /* int */
$RDF_AUTH_ID = 0; /* 0 - treat as anon user, >0 treat like specific forum user */

$PDF_PAGE = "letter"; /* string */
$PDF_WMARGIN = 15; /* int */
$PDF_HMARGIN = 15; /* int */
$PDF_MAX_CPU = 60; /* seconds */

/* DO NOT EDIT FILE BEYOND THIS POINT UNLESS YOU KNOW WHAT YOU ARE DOING */

require($INCLUDE.'core.inc');
?>

Report message to a moderator

Re: SQL Error: Unique key violated for forum_read and forum_notify [message #27868 is a reply to message #27842]

Wed, 28 September 2005 23:40

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

Ah one of those optimizations is only in 2.7.3RC1.

FUDforum Core Developer

Report message to a moderator

Re: SQL Error: Unique key violated for forum_read and forum_notify [message #27869 is a reply to message #27842]

Thu, 29 September 2005 00:15

awadallah

Messages: 11
Registered: September 2005
Location: Palo Alto, CA

Karma: 0

Junior Member

should I upgrade to 2.7.3RC1 and see if that works .. is it stable enough ?

thanks for prompt replys!

-- amr

Report message to a moderator

Re: SQL Error: Unique key violated for forum_read and forum_notify [message #27870 is a reply to message #27869]

Thu, 29 September 2005 00:22

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

It'll fix the problem with INSERTs into the read table failing, but you'll still have problems with thread_notify inserts. You need to figure out why mysql_error() PHP function fails to return the error code.

FUDforum Core Developer

Report message to a moderator

Re: SQL Error: Unique key violated for forum_read and forum_notify [message #27871 is a reply to message #27842]

Thu, 29 September 2005 00:27

awadallah

Messages: 11
Registered: September 2005
Location: Palo Alto, CA

Karma: 0

Junior Member

until my hosting provider figures out why mysql is messed up, how can I turn off notifications ?

-- amr

Report message to a moderator

Re: SQL Error: Unique key violated for forum_read and forum_notify [message #27872 is a reply to message #27842]

Thu, 29 September 2005 00:48

awadallah

Messages: 11
Registered: September 2005
Location: Palo Alto, CA

Karma: 0

Junior Member

ok, for now, I just changed the db_li function to be as listed below, not clean, but gets the job done till hosting provider figures out why mysql keeps returning 2000 for dupe key.

essentially just added "|| mysql_errno(fud_sql_lnk) == 2000" to the 2nd if condition.

function db_li($q, &$ef, $li=0)
{
$r = mysql_query($q, fud_sql_lnk);
if ($r) {
return ($li ? mysql_insert_id(fud_sql_lnk) : $r);
}

/* duplicate key */
if (mysql_errno(fud_sql_lnk) == 1062 || mysql_errno(fud_sql_lnk) == 2000 ) {
$ef = ltrim(strrchr(mysql_error(fud_sql_lnk), ' '));
return null;
} else {
die(fud_sql_error_handler($q, mysql_error(fud_sql_lnk), mysql_errno(fud_sql_lnk), get_version()));
}
}

Report message to a moderator

Re: SQL Error: Unique key violated for forum_read and forum_notify [message #27873 is a reply to message #27871]

Thu, 29 September 2005 00:48

Ilia

Messages: 13241
Registered: January 2002

Karma: 0

Senior Member
Administrator
Core Developer

You cannot short of commenting out the code inside thread_notify.inc.t and rebuilding the theme.

FUDforum Core Developer

Report message to a moderator

Previous Topic:	V2.7.0RC1 - thread not displayed in forum list or navigator
Next Topic:	locking/unlocking files crashed the server...

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 00:06:34 GMT 2024

Total time taken to generate the page: 0.02577 seconds