Database update system
 

I know it checks for sql updates everytime you boot your server up but I wanted to know if there is a way to force run it on bootup bypassing the check. Incase I want to redownload bot scripts because I wipe them out ect. I know I can manually source the script im just curious because its a convenient tool.

Just run eqemu_update.pl manually.

That's the current method for installing/updating bots (option 4.)

Ah thanks, that saves me some time.
Regarding bots I get this error when I try and run the SQL in required


mysql> source 2015_09_30_bots.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.16 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

ERROR 1136 (21S01): Column count doesn't match value count at row 1
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

ERROR 1146 (42S02): Table 'peq.bot_data' doesn't exist
ERROR 1146 (42S02): Table 'peq.bot_groups' doesn't exist
ERROR 1146 (42S02): Table 'peq.bot_data' doesn't exist
ERROR 1146 (42S02): Table 'peq.bot_guild_members' doesn't exist
mysql>

I know it's stating the obvious that the table doesn't exist but I just dropped the bots out of the DB and sourced them back in.

You really should use eqemu_update.pl to update bots since there is now a 'bots versioning' system in place.

Otherwise, the `bots_version` field doesn't get updated.


Any way to find the query that is causing this: "ERROR 1136 (21S01): Column count doesn't match value count at row 1"

I've seen a few people have issues with it and would like try and track down the issue.

I, personally, haven't had a problem with the error points that people stalling at..but, that doesn't mean there isn't an issue with it.

so should i drop the bots database amd use the updater to source them in?

You can do that..but, you will lose any bots data that you have.

If it's an unused one, that would be best :)


If not, I'll post what you can remark out and run the script with (still need to setup my commit privileges on this new windows install.)

The Plot thickens, this is the readout I get when I try and update the bots with the updater and no bots in db at all.

================================================== ==========
#::: EQEmu Update Utility Menu: (eqemu_update.pl)
================================================== ==========
1) [Backup Database] :: (Saves to Backups folder)
2) [Backup Database Compressed] :: (Saves to Backups folder)
3) [EQEmu DB Schema] :: Check and stage pending REQUIRED Database updates
4) [EQEmu DB Bots Schema] Run pending REQUIRED updates... (1)
5) [OPCodes] :: Download latest opcodes for each EQ Client
6) [Maps] :: Download latest map and water files
7) [Plugins (Perl)] :: Download latest Perl plugins
8) [Quests (Perl/LUA)] :: Download latest PEQ quests and stage updates
9) [LUA Modules] :: Download latest LUA Modules (Required for Lua)
10) [DB Data : Alternate Advancement] :: Download Latest AA's from PEQ (This ov
erwrites existing data)
20) [Update the updater] Force update this script (Redownload)
0) Exit

Enter numbered option and press enter...

> 4
Running Update: 9000 - 2015_09_30_bots.sql
ERROR 1136 (21S01) at line 675: Column count doesn't match value count at row 1
ERROR 1146 (42S02) at line 735: Table 'peq.bot_data' doesn't exist
ERROR 1146 (42S02) at line 755: Table 'peq.bot_groups' doesn't exist
ERROR 1146 (42S02) at line 768: Table 'peq.bot_data' doesn't exist
ERROR 1146 (42S02) at line 779: Table 'peq.bot_guild_members' doesn't exist
Missing DB Update 9000 '2015_09_30_bots.sql'
[URL] :: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/bo
ts/required/2015_09_30_bots.sql
[Saved] :: db_update/2015_09_30_bots.sql

I tried sourcing in the load_bots.sql in the deprecated folder same error. So im going to assume the update won't add the bots in only update what is already available in the DB. So maybe im adding the bots in with the wrong sql?

Yeah, it's failing inside of a conditional check that is suppose to keep that from happening...

Seems like the field MUST be present, even if the conditional check never proves true, when a reference is made to it inside of a function...


Here's a link to the thread for the bots db versioning implementation: http://www.eqemulator.org/forums/showthread.php?t=40091

This is the if statement that you will need to remark out to get it to work: https://github.com/EQEmu/Server/blob..._bots.sql#L471 (all 7 lines)


Double-check your `peq`.`db_version` table to see if `bots_version` is set to '9000' - if not, go ahead and set it.

Then manually run the '2015_09_30_bots.sql' script after remarking out those lines.


If that doesn't do it, post back.

bots version was already set to 9000 (too bad its not "over 9000"....im a geek) Redacted the 7 lines still get the same issue. I noticed the 4 table's its refering too don't exist in the db. Ive tried sourcing in the bots manually from the decap folder and doing it through the updater just fyi. Also if bots arenn't in the DB At all the updater doesn't recognize that and only wants to install the 1 required sql update

I set base version number to 9000 for that exact joke when I created the system ;)

LOL im glad I got the joke, I thought it was pretty funny =)

Im not the best with code but the way it reads atleast is that its trying to modify a table that doesn't exist because the load_bots.sql didn't add in the required tables. Just my 2 cents

The function is failing..which is causing the tables to not be created.
The last 4 lines are the views being added..which reference tables that don't exist

I honestly don't know what the issue is because I ran this many, many times on both a clean db and a pre-conversion bot one.

(I know what's causing it..but, I don't know why the error is occurring... Maybe NULL is greater than '0'?)

Generally speak NULL means nothing while 0 represents a number. They both mean 0 but 0 is still a number where NULL isn't. if that makes any kind of sense lol.
I guess what im saying is NULL is voiding something out so it actually means less than 0

Just an observation.
The 2015_09_30_bots.sql drops any and all bot view tables and creates them.
The load_bots.sql drops the view tables and creates them.
Observation : One creates in all lower case names, the other in mixed case.
So I assume if one used both sql's, could that cause headaches?
(depending on the order they were used in)
I also noticed a difference in the table naming, for example;
load_bots creates table botgroup
The update creates table bot_groups but, so far, I can't see anything where
the update drops a botgroup table (if exist).
I've never used the load_bot.sql My db has never been replaced, just
updated over the last few years

You shouldn't be using load_bots.sql any longer.

The eqemu_update.pl script now has a bots versioning system built in and all required bots sqls (minus the drop_bots.sql) will be applied through the update script.


Any other script use will apply incorrect database schema and will not function with the newest server code.


'2015_09_30_bots.sql' is both an install and conversion script.

The newer bots table more closely reflect the naming schema used in the 'player profile' conversion and is an attempt to help standardize bots to the rest of the
system.


There is more incoming as soon as I get the bugs worked out of my fresh install..and worked out of the changes themselves :)

I tried just using the update script but i get the same errors and it wont source the bots in.

Here is the log for the Update.pl after I dropped all traces of bots from my db.

Perl Version is 5.12.3
================================================== ==========
EQEmu: Automatic Upgrade Check
================================================== ==========
(Windows) MySQL is in system path
Path = C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin/mysql
================================================== ==========
Binary Revision / Local: (9089 / 9089)

================================================== ==========
#::: EQEmu Update Utility Menu: (eqemu_update.pl)
================================================== ==========
1) [Backup Database] :: (Saves to Backups folder)
2) [Backup Database Compressed] :: (Saves to Backups folder)
3) [EQEmu DB Schema] :: Check and stage pending REQUIRED Database updates
4) [EQEmu DB Bots Schema] Check for Bot REQUIRED database updates... (Must have
bots enabled)
5) [OPCodes] :: Download latest opcodes for each EQ Client
6) [Maps] :: Download latest map and water files
7) [Plugins (Perl)] :: Download latest Perl plugins
8) [Quests (Perl/LUA)] :: Download latest PEQ quests and stage updates
9) [LUA Modules] :: Download latest LUA Modules (Required for Lua)
10) [DB Data : Alternate Advancement] :: Download Latest AA's from PEQ (This ov
erwrites existing data)
20) [Update the updater] Force update this script (Redownload)
0) Exit

Enter numbered option and press enter...

> 4
Retrieving latest bots database manifest...
[URL] :: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/bo
ts/bots_db_update_manifest.txt
[Saved] :: db_update/db_update_manifest.txt
Reading manifest...

Missing DB Update 9000 '2015_09_30_bots.sql'
[URL] :: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/bo
ts/required/2015_09_30_bots.sql
[Saved] :: db_update/2015_09_30_bots.sql

================================================== ==========
#::: EQEmu Update Utility Menu: (eqemu_update.pl)
================================================== ==========
1) [Backup Database] :: (Saves to Backups folder)
2) [Backup Database Compressed] :: (Saves to Backups folder)
3) [EQEmu DB Schema] :: Check and stage pending REQUIRED Database updates
4) [EQEmu DB Bots Schema] Run pending REQUIRED updates... (1)
5) [OPCodes] :: Download latest opcodes for each EQ Client
6) [Maps] :: Download latest map and water files
7) [Plugins (Perl)] :: Download latest Perl plugins
8) [Quests (Perl/LUA)] :: Download latest PEQ quests and stage updates
9) [LUA Modules] :: Download latest LUA Modules (Required for Lua)
10) [DB Data : Alternate Advancement] :: Download Latest AA's from PEQ (This ov
erwrites existing data)
20) [Update the updater] Force update this script (Redownload)
0) Exit

Enter numbered option and press enter...

> 4
Running Update: 9000 - 2015_09_30_bots.sql
ERROR 1136 (21S01) at line 675: Column count doesn't match value count at row 1
ERROR 1146 (42S02) at line 735: Table 'peq.bot_data' doesn't exist
ERROR 1146 (42S02) at line 755: Table 'peq.bot_groups' doesn't exist
ERROR 1146 (42S02) at line 768: Table 'peq.bot_data' doesn't exist
ERROR 1146 (42S02) at line 779: Table 'peq.bot_guild_members' doesn't exist
Missing DB Update 9000 '2015_09_30_bots.sql'
[URL] :: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/bo
ts/required/2015_09_30_bots.sql
[Saved] :: db_update/2015_09_30_bots.sql

================================================== ==========
#::: EQEmu Update Utility Menu: (eqemu_update.pl)
================================================== ==========
1) [Backup Database] :: (Saves to Backups folder)
2) [Backup Database Compressed] :: (Saves to Backups folder)
3) [EQEmu DB Schema] :: Check and stage pending REQUIRED Database updates
4) [EQEmu DB Bots Schema] Run pending REQUIRED updates... (1)
5) [OPCodes] :: Download latest opcodes for each EQ Client
6) [Maps] :: Download latest map and water files
7) [Plugins (Perl)] :: Download latest Perl plugins
8) [Quests (Perl/LUA)] :: Download latest PEQ quests and stage updates
9) [LUA Modules] :: Download latest LUA Modules (Required for Lua)
10) [DB Data : Alternate Advancement] :: Download Latest AA's from PEQ (This ov
erwrites existing data)
20) [Update the updater] Force update this script (Redownload)
0) Exit

Enter numbered option and press enter...

>

I haven't used that myself in a long time. Usually the only thing I've done in the past,
before this update script come along, when I had to re-source my db back in, I had
to source botadventuring.sql so the bots would zone along with owner. But I didn't
get any errors when running update script.

Is this of any use ?

http://www.eqemulator.org/forums/sho...d.php?p=244897

Referring to this post here:

I already removed the 7 lines and tried it but no go

Nightrider84, what version of MySQL/MariaDB are you using? (x.x.x, 32/64 bit, win/linux )


EDIT: Ok..I back read and found all but the 'build' number for your installation (5.1.x)

Yeah im using the same stuff ive used for awhile now. I would have updated my VS to a newer version instead of 11 but I need SP1 on my windows rig that uses the server and its being an asshole and locks up and boot loops everytime I try to update it so I said fuck it lol. This seems to be the only update ive had any issues with it sticking so Im sure mysql is fine.

If you're using windows 7, there's a issue with the updater.

(You can watch the updater with Resource Monitor and see MB's tick away from in-use to free..then it will 'hang' for an hour or two..but, it will eventually update.)


You can try installing this kb: https://support.microsoft.com/en-us/kb/3102810

But, note that language pack installations require re-installing it.


EDIT: And watch out for the Windows 10 'pre-positioner' KB..I clicked on every update to see what they did...

It wasn't the updater that was the issue as far as I can tell. Once it installed the update it went into a boot loop trying to enter recovery mode and restarting because it couldnt find a problem only to reboot back into recovery. Tried safe mode and everything else so I just gave up on it for the time being.

Oh, wow..I did miss the boot part...

What a pita!

yeah still haven't figured out the bot issue unfortunatly.

Still can't figure out why the bot sql won't load correctly any thoughts?

Did you try the updated one?


EDIT: I switched the criteria from count to null check.

Totally didn't lol. Do I need to drop my current bots database or can I just "upgrade" them

If you're testing on a back-up, just upgrade them. (You can try it on both, if you like..that will provide feedback on whether this change works or not :))

If you previously ran it on a live database and fixed it manually, do not mess that one up :)


I still don't know what is causing the issue..which makes trouble-shooting it even harder...

The '9000' script is supposed to install/update..depending on the current state of your database.

when i use the updater it just closes right after i select 4. also tried manual, no go on upgrading. I'll try removing bots from db and sourcing in.

Edit: Just tried DB without bots loaded using Updater (closes when I select 4) and using manual sourcing but I get the same error with the DB not existing for those 4 lines.

'DB not existing' is an odd message... I'll have to check DATABASE()'s usage..but, it should work.

That would explain the failures, though (if it's not reporting the current database correctly)

It's acting like it fails to create the 4 tables and just fails the rest of the script because of those problems.

And I may have worded the db not existing wrong I just glanced at it but it looked like the same ones as these

ERROR 1136 (21S01) at line 675: Column count doesn't match value count at row 1
ERROR 1146 (42S02) at line 735: Table 'peq.bot_data' doesn't exist
ERROR 1146 (42S02) at line 755: Table 'peq.bot_groups' doesn't exist
ERROR 1146 (42S02) at line 768: Table 'peq.bot_data' doesn't exist
ERROR 1146 (42S02) at line 779: Table 'peq.bot_guild_members' doesn't exist

So as an added note this was done manually because the updater did not work at all with the new sql that was posted. The script seems to work but once it hits that snag it fails to create the remaining tables and is incomplete.

Edit #500,231 So I just looked at this perticular topic has almost 350 views so I can't be the only one with this problem lol. Sorry uleat don't mean to make you do more work than you already have which is a shit ton by this point lol.

Edit #500,232 I may just wipe the whole database and just download a fresh one and try that

The four 700-series errors are due to the table references used in the views - tables that were failed to be created due to a failure within the 'LoadBotsSchema()' procedure.

Those you can ignore because it's understood why the errors are being triggered.


The line 675 error is actually the line in the script that calls the procedure.

I'm pretty certain I know why it's failing..but, not the cause of it.

There are code checks in place to look for existing column names..then, if found, an import is performed.

That's to address different incarnations of bot schema.


For some reason, either the code block criteria is triggering an error, or a false positive is returned and the import is triggering on a non-existent column name.
(Just not sure which atm since I can't replicate the condition.)


EDIT: I'm still looking into alternative code block criteria. In the mean time, I need to write-up how to apply that manually from update failure to end.

I understood about half of what you said lol. But I get the jist of it. Yeah I don't know why the update is causing the automatic updater to just crash thats whats confusing me. you know besides the sql not want to do its job heh.