AlliedModders - Shero w/ MySQL "error"?

Quote:

Originally Posted by gui1porpis (Post 2526586)

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