Unique email index problem

Discussions about the Enuuk Platform in general - both the GPL and Commercial versions
Post Reply
bamse
Posts: 220
Joined: Mon Feb 06, 2012 12:05 pm
Contact:

Unique email index problem

Post by bamse » Sun Mar 10, 2013 11:54 am

Somehow I managed to mess up my database.

After I delete a user from the database (either through the backend or via phpmyadmin), and try to re-register another user with the same email address as the one that was just deleted, I get the "E-mail address already used" message, i.e. the save triggers a 23000 error (error catch code is in class/Action/User.php->. If I pick an email address that was not used before I can register.

Despite of the error message, the user is written into the database, but no email (with confirmation link) is send to the user.

Repeating this step, i.e. registering a second user with the same email address, the behaviour is as expected: error message ("Email address already used" and second user is not written to database).

Any debugging suggestions would be appreciated.

RWAP
Site Admin
Posts: 748
Joined: Fri Jan 08, 2010 2:23 am
Location: Stoke-on-Trent
Contact:

Re: Unique email index problem

Post by RWAP » Sun Mar 10, 2013 8:11 pm

Hmm - I have just tried this on my test platform, and there was no problem registering another user with the original email address once the first one was deleted.

One issue you may have is that the 23000 error can be generated by more than just the email address not meeting the unique constraint.

Your best bet would be to get the SQL code which is being added to the table by user->saveToDB, and try running that same SQL call in phpmyadmin to see what the actual error is. If your site is live, then use an echo code which is dependent on the user name being used, so that only you can see the SQL code and data being passed.

bamse
Posts: 220
Joined: Mon Feb 06, 2012 12:05 pm
Contact:

Re: Unique email index problem

Post by bamse » Mon Mar 11, 2013 12:26 am

Playing around a bit, it seems to be due to the user id rather than the email as I can re-register using the same email address and a different user name (user id). So it looks as if user id is stored in some table (which has a unique key on it) even after the user is deleted. Still I looked through all the tables and did not spot the user names.

Not sure how to use echo codes yet. Is it possible to just log errors somewhere? I looked at my mysql logs, but they did not tell anything as far as I can see.

One other thought. Could this be related to caching?

bamse
Posts: 220
Joined: Mon Feb 06, 2012 12:05 pm
Contact:

Re: Unique email index problem

Post by bamse » Mon Mar 11, 2013 8:14 am

I ran a diff on the database structure with the default database as reference and it seems that I am missing some foreign key constraints. Not sure how that happened.

bamse
Posts: 220
Joined: Mon Feb 06, 2012 12:05 pm
Contact:

Re: Unique email index problem

Post by bamse » Mon Mar 11, 2013 8:33 pm

Figured out the problem. All those old user names had been lurking in the users_userFields table since those entries did not get deleted when deleting a user due to the missing constraints. Fixed it manually and added the missing key constraints.

I still don't know how those constraints could disappear. Also it seems that I had key constraints on the first half of the database's tables (alphabetically ordered) and did not have any key constraints on the second half of the tables.

Thanks again for pushing me in the right direction.

RWAP
Site Admin
Posts: 748
Joined: Fri Jan 08, 2010 2:23 am
Location: Stoke-on-Trent
Contact:

Re: Unique email index problem

Post by RWAP » Mon Mar 11, 2013 8:51 pm

Seems odd that some of the key constraints were missing - presumably that was the reason for your other issue with stores not being deleted.

The only thing I can think of is that when the database was initially created on your server, the SQL call timed out part way.

The other option may be some corruption in your InnoDB tables - you may need to click on each table within phpmyadmin, and then use tools to Optimise the tables / repair them - see:

http://www.affilorama.com/support/our-p ... phpmyadmin

RWAP
Site Admin
Posts: 748
Joined: Fri Jan 08, 2010 2:23 am
Location: Stoke-on-Trent
Contact:

Re: Unique email index problem

Post by RWAP » Mon Mar 11, 2013 8:56 pm

bamse wrote:Not sure how to use echo codes yet. Is it possible to just log errors somewhere? I looked at my mysql logs, but they did not tell anything as far as I can see.
I meant to just add a command such as

if ($this->id=='example') {
echo $sql;
foreach ($params as $key=>$val) echo $key.'-->'.$val;
}

Into class\User.php inside the SaveToDB() function, so you could see what data was being passed.

The other option if you are experiencing problems is to look at class\DB.php and add some more code to store the SQL calls - have a look at what code is used when the debug=time parameter is passed for example (details not provided to prevent misuse).

Post Reply

Who is online

Users browsing this forum: Yahoo [Bot] and 1 guest