[b38] Mysql 0.38.0 - mysql logger

[elevatorguy]

mysql for TDSM
This plugin is mainly for people who know php/mysql until I finish the rollback part of it.

This requires that you have a mysql server.

This adds mysql logging of:

• Current Players Online (triggered by player JOIN / LEAVE )
• Player IP logging (triggered by player JOIN)
• Tile logging (every tile placed / broke)
• Logging of Lava / water placement

Download: Mysql_0_38_0.dll
c# mysql connector MySql.Data.dll (put this in your Plugins/Libs folder)
Source: github

Usage

Reload settings from mysql.properties
> mysql -L

Get the past 5 changes from a selected block
> blockinfo
this toggles the tile history tool, enter the command again to turn it off

Find out when someone was last seen online
> lastseen playername
if the name has a space in it, then surround with quotes
> lastseen "player name with spaces"

Rollback within a timeframe of minutes
> rollback 5
NOTE: Doesn't preserve liquids or decorations yet.

Updates
0.38.0: update to b38 and IPLog / player join logging fix
0.37.0: quick update to b37 **rollback command disabled **
0.36.3: rollback beta / fix for log plants
0.36.2: fix for multiple entries for the same tile / toggle for plants
0.36.1: breaking corruption thorns are logged
0.36.0: updated for TDSM b36's new API / query queue
0.35.5: blockinfo fix
0.35.4: table creation bug fix from 0.35.3
0.35.3: huge bug fix and renamed xy command to blockinfo
0.35.2: startup bugfix and added lastseen command
0.35.1: mysql queries use the same mysql connection
0.35.0: release to public
...
0.32.0: first beta testing

Todo
1) upgrade to the new TDSM plugin API in time for b36
2) mysql efficiency (insert more than one tile per query)
3) lastseen command
4) finish undo and redo commands
5) put up example php scripts that make use of the plugin
6) rename xy command to something that makes sense

Options
By default everything is turned off. You have to change mysql.properties to set everything up first.
mysql-enabled set this to true once you know your connection settings are correct (mysql-server, mysql-database, mysql-user, mysql-userpass)
logtiles enables the logging of all tile changes
logplayers enables logging of players online
logliquid enables logging of lava and water placement/removal
logplants whether to log grass / vines / corruption thorns / flowers / mushrooms

Please let me know if I have overlooked any bugs.

[InZpire]

Looks like a cool plugin. Ill try it out

[rakiru]

The idea behind this is good, but it is horribly inefficient. You insert each block change individually, and create a new connection every time.

[elevatorguy]

@rakiru
Thanks, I knew I needed to change it so it uses the same connection for blocks. But hadn't gotten around to it yet because I was unsure about what would happen if the connection was closed.
I never thought about doing the insert statements differently though.
I would think it would be better to have a queue of changes in memory and then insert into the mysql database every 1 or 2 seconds.

Thanks for the suggestion I just didn't think about it before, I will look into this more for sure.

EDIT:
version 0.35.1: queries now use the same connection instead of opening and closing a new one for each query
but it's still one query per tile for now

**EDIT**
version 0.35.2: bug fix on first run, and added lastseen command (useable by all players)

[Colin]

Hey, I'm probably doing something very noobish wrong, but I figured I'd post the errors on here anyway to see if I can get some help.

1/10/2011 11:16:05 PM Serv> Error Loading Plugin '/home/colin/Plugins/Mysql_0_35_2.dll'. Is it up to Date?
01/10/2011 11:16:05 PM Serv> Plugin Load Exception '/home/colin/Plugins/Mysql_0_35_2.dll' : System.IO.FileNotFoundException: Could not load file or assembly 'MySql.Data, Version=6.4.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies.
File name: 'MySql.Data, Version=6.4.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d'
  at MysqlPlugin.MysqlPlugin.Load () [0x00000] in <filename unknown>:0
  at Terraria_Server.Plugin.PluginManager.LoadPlugin (System.String PluginPath) [0x00000] in <filename unknown>:0

Can you make sense of that and figure out whats wrong? I just set up my mysql server for the plugin with a new username and database, so my first thought would be that the MySQL version is wrong or the plugin doesn't have high enough permission or something. but I honestly have no idea what that exception really says so any help would be appreciated.

[elevatorguy]

@colin
sorry I didn't put the link to Mysql.Data.dll... it's in the first post now, just download that and put it in the Libs folder.


version 0.35.3: HUGE BUG FIX of mysql logging. and I renamed the xy command to blockinfo
I wanted to just use the single mysql connection for everything, but it is NOT threading friendly, so only the tile log uses it's own now, and it double checks that it isn't being used first otherwise just makes a new connection.

[elevatorguy]

I'm trying to get this compatible with the new API in TDSM 36 prerelease, and it looks like the way I store the tilelog needs to be completely redone.

The mysql table structure has to be changed drastically. I'm waiting to see what the next prerelease looks like, but already, I'm sure that your current terraria_tiles table will have to be deleted for the next version of TDSM(I'll do that for you in the plugin).

Also any PHP scripts that make use of terraria_tiles will have to be looked over to see what changes need to be made. (I'm not looking forward to this one )

EDIT: no change required

Your terraria, and terraria_iplog mysql table will not be affected by this.

[rakiru]

Why would a change in the way you get data require you to change the way you store it? You're still just storing who does what, unless I'm missing something here.

[elevatorguy]

The current mysql columns for the table terraria_tiles are:
   timestamp                  player         x         y         action      tile      tiletype   wall   undone
   2011-10-03 00:33:30   Perceus   2832   222   BREAK   52   BLOCK      0         0

timestamp, player, x, and y would be unchanged with the new API
action and tiletype are different with the new API but I can find out what the action int codes are and probably leave these columns unchanged aswell

My main concern is with tile and wall. (ev.Tile.Type and ev.Tile.Wall)
tile was the tile id if it was a block, and wall had the wall id if it was a wall (very critical info for my future rollback commands)
I still have to do more testing to see how different args.Type is from ev.Tile.Type and ev.Tile.Wall, and I don't even know what args.Style is...

Either way I will do whatever it takes to keep the current table layout because its less work than redesigning my website's op panel. But, if it's just not possible then so be it. I just wanted to warn people that it's a possibility.

EDIT: I ended up just ignoring the args.Style element. the table columns are unaffected by the new API (if I need args.Style then I can add it to the table easily later)

[inisfield]

Code: [Select]

TDSM Build #35
Plugins:
custom chat B#31
spawnguard B#31
mysql logger B#35  (Mysql_0_35_3.dll + MySql.Data.dll)

I am having trouble with mysql, i am getting this error:

Code: [Select]

10/09/2011 14:45:36 Serv> Loading Plugins...
10/09/2011 14:45:36 Serv> Saved file "/root/tdsm/Plugins/mysql/mysql.properties".
10/09/2011 14:45:36 Serv> [mysql] checkTable terraria: Found:False
10/09/2011 14:45:36 Serv> Error Loading Plugin '/root/tdsm/Plugins/Mysql_0_35_3.dll'. Is it up to Date?
10/09/2011 14:45:36 Serv> Plugin Load Exception '/root/tdsm/Plugins/Mysql_0_35_3.dll' : System.NullReferenceException: Object reference not set to an instance of an object
  at MysqlPlugin.MysqlPlugin.checkTable (System.String tablename) [0x00000] in <filename unknown>:0
  at MysqlPlugin.MysqlPlugin.Load () [0x00000] in <filename unknown>:0
  at Terraria_Server.Plugin.PluginManager.LoadPlugin (System.String PluginPath) [0x00000] in <filename unknown>:0
[SPAWNGUARDPE] Protecting 'Empty':(0,0) to (0,0)
[SPAWNGUARDPE] Protecting 'Empty':(0,0) to (0,0)
[SPAWNGUARDPE] Protecting 'Empty':(0,0) to (0,0)
[CUSTOMCHAT] Ready and Waiting!
[SPAWNGUARDPE] Ready and Waiting!
10/09/2011 14:45:36 Serv> [WARNING] Plugin Build Incorrect: CustomChat
10/09/2011 14:45:36 Serv> [WARNING] Plugin Build Incorrect: SpawnGuardPE
10/09/2011 14:45:36 Serv> Plugins Loaded: 2
10/09/2011 14:45:36 Serv> Initializing slot manager for 8+1 players.
You can now insert Commands.
here is the mysql.properties file:

Code: [Select]

mysql-enabled=True
mysql-server=localhost
mysql-database=terraria
mysql-user=root
mysql-userpass=******
logtiles=True
logplayers=True
logliquid=True
getxy=True
and this is phpmyadmin to show i have the database:
http://i.imgur.com/1jEC9.png

can you tell me what im doing wrong, if you need anymore info just ask, thanks.

[rakiru]

Not really an answer, but I just thought I should point out that you should create another user instead of using root.

[elevatorguy]

version 0.35.4: sorry I didn't notice the table creation bug, it was working fine in 0.35.2 but I messed it up in 0.35.3.
I changed the first post with a new version, it should work for you now.

also rakiru is right, you NEVER want to use your root user for things like this.
The reason is that if the password is compromised then not only are all your databases compromised, but that person has the password to your server's operating system, and can do anything they want.

first do this:

Code: [Select]

CREATE USER 'terrariauser'@'localhost' IDENTIFIED BY 'yournewpassword';
then do one of these:

Code: [Select]

GRANT ALL ON terraria.* TO 'terrariauser'@'localhost'; give terrariauser access to only terraria

Code: [Select]

GRANT ALL ON *.* TO 'terrariauser'@'localhost'; give terrariauser access to all your databases

after these two commands you can use this user to login and sleep better knowing that your server is just that much more secure

[D1G1T4L3CH0]

Ok so I finally decided to try this after deciding I have enough time to learn a bit about mysql and get it installed. I used the example you gave for creating a user also.

This is what I did to get it running:

Code: [Select]

su -c 'apt-get install mysql-server'
mysql -u root -p
CREATE USER 'terrariauser'@'localhost' IDENTIFIED BY 'yournewpassword';
GRANT ALL ON terraria.* TO 'terrariauser'@'localhost';
quit
I then put the plugin in it's place and ran the tdsm server. I got this:

Code: [Select]

10/9/2011 9:51:58 PM Serv> Saved file "/home/debuser/tdsm/Plugins/mysql/mysql.properties".
10/9/2011 9:51:58 PM Serv> [mysql] connection not enabled. Please edit mysql.properties
Missing method .ctor in assembly data-0xb09dbd28, type MySql.Data.MySqlClient.MySqlConnection

Unhandled Exception: System.IO.FileNotFoundException: Could not load file or assembly 'MySql.Data, Version=6.4.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies.
File name: 'MySql.Data, Version=6.4.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d'
  at Terraria_Server.Plugin.PluginManager.EnablePlugins () [0x00000] in <filename unknown>:0
  at Terraria_Server.Plugin.PluginManager.LoadPlugins () [0x00000] in <filename unknown>:0
  at Terraria_Server.Plugin.PluginManager.LoadAllPlugins () [0x00000] in <filename unknown>:0
  at Terraria_Server.Netplay.ServerLoop () [0x00000] in <filename unknown>:0
  at Terraria_Server.Netplay.ServerLoopLoop () [0x00000] in <filename unknown>:0
  at Terraria_Server.ProgramThread+<>c__DisplayClass2.<.ctor>b__0 (System.Object arg) [0x00000] in <filename unknown>:0
  at System.Threading.Thread.StartUnsafe () [0x0002b] in /home/debuser/mono-2.10.5/mcs/class/corlib/System.Threading/Thread.cs:682
[ERROR] FATAL UNHANDLED EXCEPTION: System.IO.FileNotFoundException: Could not load file or assembly 'MySql.Data, Version=6.4.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies.
File name: 'MySql.Data, Version=6.4.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d'
  at Terraria_Server.Plugin.PluginManager.EnablePlugins () [0x00000] in <filename unknown>:0
  at Terraria_Server.Plugin.PluginManager.LoadPlugins () [0x00000] in <filename unknown>:0
  at Terraria_Server.Plugin.PluginManager.LoadAllPlugins () [0x00000] in <filename unknown>:0
  at Terraria_Server.Netplay.ServerLoop () [0x00000] in <filename unknown>:0
  at Terraria_Server.Netplay.ServerLoopLoop () [0x00000] in <filename unknown>:0
  at Terraria_Server.ProgramThread+<>c__DisplayClass2.<.ctor>b__0 (System.Object arg) [0x00000] in <filename unknown>:0
  at System.Threading.Thread.StartUnsafe () [0x0002b] in /home/debuser/mono-2.10.5/mcs/class/corlib/System.Threading/Thread.cs:682
I know the missing method part says there is something wrong other than the properties file, and the plugin should have loaded anyway, I didn't think crashing if the properties file isn't setup is normal. But I chose to try and setup the properties file anyway hoping something good will happen heh.

The properties file:

Code: [Select]

mysql-enabled=True
mysql-server=localhost
mysql-database=terraria
mysql-user=terrariauser
mysql-userpass=********
logtiles=True
logplayers=True
logliquid=True
getxy=True


I ran the server again and got pretty much the same thing:

Code: [Select]

10/9/2011 9:55:29 PM Serv> Saved file "/home/debuser/tdsm/Plugins/mysql/mysql.properties".
Missing method .ctor in assembly data-0x8956df0, type MySql.Data.MySqlClient.MySqlConnection
10/9/2011 9:55:29 PM Serv> Error Loading Plugin '/home/debuser/tdsm/Plugins/Mysql_0_35_4.dll'. Is it up to Date?
10/9/2011 9:55:29 PM Serv> Plugin Load Exception '/home/debuser/tdsm/Plugins/Mysql_0_35_4.dll' : System.IO.FileNotFoundException: Could not load file or assembly 'MySql.Data, Version=6.4.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies.
File name: 'MySql.Data, Version=6.4.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d'
  at MysqlPlugin.MysqlPlugin.Load () [0x00000] in <filename unknown>:0
  at Terraria_Server.Plugin.PluginManager.LoadPlugin (System.String PluginPath) [0x00000] in <filename unknown>:0
10/9/2011 9:55:29 PM Serv> Plugins Loaded: 0
With me being completely new to mysql, I'm sure there is something I did wrong here. I also created a database named "terraria" as set in the properties file later as troubleshooting because I really didn't know if the plugin makes the database or if it expects it to be created already.

This was all done on a virtual machine with Debian 6 text mode. I made a brand new install and compiled mono based on the guide on the wiki. Mono 2.10.5

[DeathCradle]

Are you sure the SQL dll is loaded?

Could not load file or assembly 'MySql.Data

Have you tried adding the DLL to the Libs folder within Plugins?

[D1G1T4L3CH0]

Oops. LOL.  I completely missed that.
Yep it seems to have loaded now, so let the testing begin...

Thanks.