Raised This Month: $7 Target: $400
 1% 

Shero w/ MySQL "error"?


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
gui1porpis
Member
Join Date: Jan 2009
Location: Brodowski, SP, Brazil
Old 06-06-2017 , 10:57   Shero w/ MySQL "error"?
Reply With Quote #1

Hello, I'm having a comeback in the superhero debug and is talking about the tables, the problem is that I'm a bit of a layman on these table things, I wanted to know briefly what I have to do for the error to stop returning?

qconsole.log:
Quote:
L 06/05/2017 - 18:027: [superheromod.amxx] DEBUG: Error Making Tables: [1426] 'Too-big precision 14 specified for 'LAST_PLAY_DATE'. Maximum is 6.' - 'CREATE TABLE IF NOT EXISTS `sh_savexp` ( `SH_KEY` varchar(32) binary NOT NULL default '', `PLAYER_NAME` varchar(32) binary NOT NULL default '', `LAST_PLAY_DATE` timestamp(14) NOT NULL, `XP` int(10) NOT NULL default '0', `HUDHELP`
Note: I use MySQL because of Shero Stats.
__________________
gui1porpis is offline
Send a message via MSN to gui1porpis Send a message via Skype™ to gui1porpis
notusian
Junior Member
Join Date: Dec 2004
Location: CA
Old 06-11-2017 , 18:59   Re: Shero w/ MySQL "error"?
Reply With Quote #2

I'm not sure where this query is written but switch timestamp(14)

LAST_PLAY_DATE` timestamp(14) NOT NULL
to
LAST_PLAY_DATE` timestamp NOT NULL
notusian is offline
gui1porpis
Member
Join Date: Jan 2009
Location: Brodowski, SP, Brazil
Old 07-07-2018 , 23:49   Re: Shero w/ MySQL "error"?
Reply With Quote #3

Quote:
Originally Posted by notusian View Post
I'm not sure where this query is written but switch timestamp(14)

LAST_PLAY_DATE` timestamp(14) NOT NULL
to
LAST_PLAY_DATE` timestamp NOT NULL
Sorry for the delay, is that I was with my server unavailable for a few months, I came back now, and I went after this error and changed in .INC, but it is returning this error now:
Quote:
0069 L 07/08/2018 - 006:42: [superheromod.amxx] DEBUG: Error Making Tables: [1064] 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to us' - 'CREATE TABLE IF NOT EXISTS `sh_savexp` ( `SH_KEY` varchar(32) binary NOT NULL default '', `PLAYER_NAME` varchar(32) binary NOT NULL default '', `LAST_PLAY_DATE` timestamL 07/08/2018 - 006:42: [superheromod.amxx] DEBUG: Couldn't Clean Powers: [1146] 'Table 'site13658764927.sh_saveskills' doesn't exist' - 'DELETE FROM `sh_saveskills` USING `sh_saveskills`, `sh_savexp` WHERE sh_savexp.`SH_KEY` = sh_saveskills.`SH_KEY` AND sh_savexp.`LAST_PLAY_DATE` < (SYSDATE() - INTERVAL '14' DAY) AND NOT (sh_savexp.`HUDHELP` & '4')'
__________________
gui1porpis is offline
Send a message via MSN to gui1porpis Send a message via Skype™ to gui1porpis
notusian
Junior Member
Join Date: Dec 2004
Location: CA
Old 11-15-2018 , 09:05   Re: Shero w/ MySQL "error"?
Reply With Quote #4

looks like youre missing the table from your site db. You will need to make the sh_saveskills table. I would guess you missed part of whatever setup was necessary. http://www.mysqltutorial.org/mysql-create-table/ fyi. Look for whatever step runs the create table statement and run it.
notusian is offline
Krillin
Senior Member
Join Date: Jul 2004
Old 04-14-2021 , 04:09   Re: Shero w/ MySQL "error"?
Reply With Quote #5

Quote:
Originally Posted by gui1porpis View Post
Hello, I'm having a comeback in the superhero debug and is talking about the tables, the problem is that I'm a bit of a layman on these table things, I wanted to know briefly what I have to do for the error to stop returning?

qconsole.log:


Note: I use MySQL because of Shero Stats.
I know this post is old, but so isn't SHMOD. This error message goes deeper than this. The problem is caused by an invalid TYPE, in this case, improper syntax as ENGINE is not specified.

We had this issue recently when we upgraded the host of our database storage facilities to MariaDB over the winter. Database's used to run off of 'types' but now they run off of 'ENGINE' instead. A change that was made by MySQL some years ago, I found out form another project I run back in 2014! (Yeah, that long ago).

The mod creates the tables (two of them) but the tables are stored without an ENGINE type specified. So if you rebuild you database, sure it will recreate the tables, but once you disconnect from the database (a restart or upgrade), MySQL / MariaDB reports the tables are missing even though it IS there. The sad part is, the tables are going to have to be dropped, I have not found a way to recover the information stored within the database tables as they are no longer accessible. Sorry, but you cannot just change the ENGINE to a table once it is no longer seen.

Here is the fix;
Open amxmodx/scripting/includes/superheromysql.inc
At the top you will see the following
Code:
CREATE TABLE `sh_savexp` (
	`SH_KEY` varchar(32) binary NOT NULL default '',
	`PLAYER_NAME` varchar(32) binary NOT NULL default '',
	`LAST_PLAY_DATE` timestamp(14) NOT NULL,
	`XP` int(10) NOT NULL default '0',
	`HUDHELP` tinyint(3) unsigned NOT NULL default '1',
	`SKILL_COUNT` tinyint(3) unsigned NOT NULL default '0',
	PRIMARY KEY  (`SH_KEY`)
) TYPE=MyISAM COMMENT='SUPERHERO XP Saving Table';

CREATE TABLE `sh_saveskills` (
	`SH_KEY` varchar(32) binary NOT NULL default '',
	`SKILL_NUMBER` tinyint(3) unsigned NOT NULL default '0',
	`HERO_NAME` varchar(25) NOT NULL default '',
	PRIMARY KEY  (`SH_KEY`,`SKILL_NUMBER`)
) TYPE=MyISAM COMMENT='SUPERHERO Skill Saving Table';
You see where it says "TYPE", this is no longer valid. It is needs to read "ENGINE" this is if you create the tables manually. The code you have to dig further into the file.

Scroll down to line #273
Code:
		copy(sql, charsmax(sql), "CREATE TABLE IF NOT EXISTS `sh_savexp` ( `SH_KEY` varchar(32) binary NOT NULL default '', `PLAYER_NAME` varchar(32) binary NOT NULL default '', `LAST_PLAY_DATE` timestamp(14) NOT NULL, `XP` int(10) NOT NULL default '0', `HUDHELP` tinyint(3) unsigned NOT NULL default '1', `SKILL_COUNT` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY  (`SH_KEY`) ) TYPE=MyISAM COMMENT='SUPERHERO XP Saving Table'")
Replace "TYPE=MyISAM" with "ENGINE=MyISAM"

Now a little bit further scroll down to line #285
Code:
		copy(sql, charsmax(sql), "CREATE TABLE IF NOT EXISTS `sh_saveskills` ( `SH_KEY` varchar(32) binary NOT NULL default '', `SKILL_NUMBER` tinyint(3) unsigned NOT NULL default '0', `HERO_NAME` varchar(25) NOT NULL default '', PRIMARY KEY  (`SH_KEY`,`SKILL_NUMBER`) ) TYPE=MyISAM COMMENT='SUPERHERO Skill Saving Table'")
Again, replace "TYPE=MyISAM" with "ENGINE=MyISAM" so you should end up with:

Code:
		copy(sql, charsmax(sql), "CREATE TABLE IF NOT EXISTS `sh_savexp` ( `SH_KEY` varchar(32) binary NOT NULL default '', `PLAYER_NAME` varchar(32) binary NOT NULL default '', `LAST_PLAY_DATE` timestamp NOT NULL, `XP` int(10) NOT NULL default '0', `HUDHELP` tinyint(3) unsigned NOT NULL default '1', `SKILL_COUNT` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY  (`SH_KEY`) ) ENGINE=MyISAM COMMENT='SUPERHERO XP Saving Table'")
and

Code:
		copy(sql, charsmax(sql), "CREATE TABLE IF NOT EXISTS `sh_saveskills` ( `SH_KEY` varchar(32) binary NOT NULL default '', `SKILL_NUMBER` tinyint(3) unsigned NOT NULL default '0', `HERO_NAME` varchar(25) NOT NULL default '', PRIMARY KEY  (`SH_KEY`,`SKILL_NUMBER`) ) ENGINE=MyISAM COMMENT='SUPERHERO Skill Saving Table'")
Once this change is made now no matter what happens to your server or your database, the ENGINE will keep you database files accessible weather you reboot, rebuild, upgrade or migrate your database host.

This drove me crazy for months so I decided to tackle the source and this is what was found. I lost my players due to this glitch because they would come back to find their work gone not saved or retrieved. I did not know this had been happening as no one told me until I saw the errors in the log files. Logs kept reporting that the tables did not exists even though they were there. The data was just not accessible due to a malformed creation from using "TYPE" instead of "ENGINE". Once you fix this, recompile and stage the new compiled version into your plugins folder. Restart the gameserver, and all will be good to go.

I am a bit ticked off I did not see this in 2014 and it took me all this time to find the problem myself.

Goodluck!

Krillin
__________________
Krillin's World Server(s) Operator


Last edited by Krillin; 04-14-2021 at 04:16.
Krillin is offline
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 06:03.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode