ArmA2 Persistent Database Scripts - WIP

[PeeKay 1]

Thanks for your time! I have it working now somehow.... I re-imported the sql file and some how it works this time.

[[kh]jman 49]

PersistentDB updated

- Arma2NETMySQL.dll compiled against Arma2Net 2.2

- Arma2NETMySQL.dll added sqlite support

- Arma2NETMySQL.dll The socket connection would stay open once started. This caused problems (aka crashing). Now, it will close down the connection after 30 minutes of inactivity.

- Arma2NETMySQL.dll logs save to AppData/Local/Arma2NETMySQL

- Arma2NETMySQL.dll Databases.txt now loads from AppData/Local/Arma2NETMySQL

- Arma2NETMySQL.dll New Databases.txt format. Added type, mysql and sqlite

Dev-heaven repository

Direct downloads:

Arma2NETMySQLPlugin.7z

PersistentDB

PDB in MSO:

Dev-heaven repository branch

[BenBen 1]

Hello,

I'm trying to use persistentDB but i have a issue that doesn't append every time : the Number values was return has "Any" instead of the real value :

Logs from arma2oaserver :

Exemple with missionId (tmid) on vehicule saving :

0:43:45 "SERVER MSG: SQL output: [tobj=SkodaGreen,tpos=[7007.31|7724.17|9.15527e-005],tdir=[-0.919542|-0.392992|0],tup=[0|0|1],tdam=0,tfue=1,tlkd=false,twcar=[["SCAR_L_STD_Mk4CQT"]|[1]],teng=false,twmag=[["30Rnd_556x45_Stanag"]|[1]],tmid=any,tintid=1]"

Exemple on player saving :

0:43:36 "SERVER MSG: SQL output:tsc=[b]any[/b],tpos=6806.97|6470.06|0.00177002,tdam=0,tdhe=[b]any[/b],tdbo=[b]any[/b],tdha=[b]any[/b],tdle=[b]any[/b],tdir=50.2899,tsta=Stand,tsid=[b]any[/b],tveh=[b]any[/b],tsea=,ttyp=USMC_Soldier_Officer,trat=0,tvd=[b]any[/b],ttd=[b]any[/b],tran=[b]any[/b],tfir=[b]any[/b],tek=[b]any[/b],tck=[b]any[/b],tfk=[b]any[/b],tsui=[b]any[/b],tlif=ALIVE,tdea=[b]any[/b],ttp=[b]any[/b],tlc=[b]any[/b],tld="04/09/2012 22:43:36",tpid=44461254,tna=BenBen,tmid=[b]any[/b]"

Logs from Arma2NETMySQL folder :

Info: 04:40:40 - Received - Database: arma Procedure: RemoveObjects Parameters: tmid=[b]any[/b]
Info: 04:40:40 - Parsing parameters...
Warning: 04:40:40 - MySQL error. MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect integer value: '[b]any[/b]' for column 'tmid' at row 1
  at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
  at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)
  at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId)
  at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
  at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
  at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
  at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
  at Arma2NETMySQLPlugin.MySQL.RunOnDatabase(MySqlCommand command, Int32 maxResultSize)

If some have an idea, I don't want to modify files without know if it a stupid thing from me.

I forgot, I add persistentDB to the Dynamic Zombie Sandbox mission, I don't think it is related but in case.

Thanks for help.

[BollaZee 1]

Hi there, im new to hosting etc

we are running a charnarus life server, we would like to integrate this persistent Database to save the players money on him, money in his bank, the items and licenses,his location and vehicles,

is there anyone that can assist us in this please? what must we change to enable this? or will this mod automatically save all those stats?

Thank you / Kind Regards

Edited September 12, 2012 by BollaZee

[[kh]jman 49]

@BollaZee. Whilst what you require is totally possible with the aid of the PDB core files you would need to extend the code specifically for your CL mission rather like we are doing with MSO. I'm afraid I cannot help you with this due to my other obligations. If you have the time why not learn sqf and and try to extend it yourself.

[AAD10 Pete 10]

The Version from 7.9.12 brings some important changes.

Thanks and keep up the amazing work!

[JoeDamage 10]

Hi,

Perhaps someone can help me here. I am running Arma2NET 2.2, with Arma2NETMySQLPlugin.dll (compiled against Arma2NET 2.2). I have created a mission, using MSO4.4. I have enabled only the persistantDB module, and when I start it, I get the following message in the Arma2NETMySQLPlugin log:

Info: 09:50:36 - Arma2NETMySQL Plugin Started.

Info: 09:50:36 - Version number: 0.1.0.0

Info: 09:50:36 - Compiled against Arma2NET Version: 2.2.0.0

Info: 09:50:36 - Loading databases...

Info: 09:50:36 - Type: mysql Database: arma IPAddress: localhost Port: 3306 Username: arma Password: NotShownForSecurityReasons

Info: 09:50:36 - Received - Database: arma Procedure: GetMissionByName Parameters: tna=CO16_DesperateMeasures

Info: 09:50:36 - Parsing parameters...

Info: 09:50:36 - Received - Database: arma Procedure: NewMission Parameters: tna=CO16_DesperateMeasures,ttd=1,tsc=1,tgsc=1,tlog=1,twea=1,tace=1,tlv=0,tobj=0,tloc=0

Info: 09:50:36 - Parsing parameters...

Info: 09:50:36 - Received - Database: arma Procedure: GetMissionByName Parameters: tna=CO16_DesperateMeasures

Info: 09:50:36 - Parsing parameters...

Info: 09:50:48 - Received - Database: arma Procedure: GetPlayer Parameters: tmid=<null>,tpid=14831046

Info: 09:50:48 - Parsing parameters...

Warning: 09:50:48 - MySQL error. MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect integer value: '<null>' for column 'tmid' at row 1

It is clear to me that the connection to the database is being made successfully. The mission information is even correctly entered in the missions table, however, when the mission id (I'm assuming 'tmid' is the mission id) is returned, it is consistently null. I am using the database schema as supplied by MSO4.4, although I've also tried the one that comes with the standalone persistantDB package. I doubt that the problem lies with the schema. I saw that there was a discussion earlier in this thread about a similar problem, where the issue was related to the specifics of the array structure returned by the MySQL plugin, and the recommendation was to use Arma2NET 1.11.1, which I have also tried, without success.

The plot thickens, when I try testing using the mission supplied with the persistantDB package (Co30_ACE_KH_PDB_Batfields_2_13.Chernarus.pbo). Everytime I load the mission, I get the various messages saying that the mission entry has been created etc. Then the mission starts. When I look in the logs for the Arma2NETMySQLPlugin I see the following:

Info: 10:44:45 - Arma2NETMySQL Plugin Started.

Info: 10:44:45 - Version number: 0.1.0.0

Info: 10:44:45 - Compiled against Arma2NET Version: 2.2.0.0

Info: 10:44:45 - Loading databases...

Info: 10:44:45 - Type: mysql Database: arma IPAddress: localhost Port: 3306 Username: arma Password: NotShownForSecurityReasons

Info: 10:44:46 - Received - Database: arma Procedure: GetMissionByName Parameters: tna=Co30 ACE KH PDB Battlefields

Info: 10:44:46 - Parsing parameters...

Info: 10:44:46 - Received - Database: arma Procedure: NewMission Parameters: tna=Co30 ACE KH PDB Battlefields,ttd=1,tsc=1,tgsc=1,tlog=1,twea=1,tace=1,tlv=0,tobj=0,tloc=0,tobc=0

Info: 10:44:46 - Parsing parameters...

Warning: 10:44:46 - MySQL error. MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect number of arguments for PROCEDURE arma.NewMission; expected 10, got 11

I realise the second issue is probably unrelated to the first.

Does anyone have any ideas?

[[kh]jman 49]

Incorrect number of arguments for PROCEDURE arma.NewMission; expected 10, got 11

They are related.

In the first your stored procedure is not returning all 11 parameters. It is not returning tobc therefore the parse fails resulting in tmid = null:

Info: 09:50:36 - Received - Database: arma Procedure: NewMission Parameters: tna=CO16_DesperateMeasures,ttd=1,tsc=1,tgsc=1,tlog =1,twea=1,tace=1,tlv=0,tobj=0,tloc=0

In the second because your stored procedure is only expecting 10 parameters, the newmission write is failing:

Info: 10:44:46 - Received - Database: arma Procedure: NewMission Parameters: tna=Co30 ACE KH PDB Battlefields,ttd=1,tsc=1,tgsc=1,tlog=1,twea=1,tace =1,tlv=0,tobj=0,tloc=0,tobc=0

You're mixing different database stored procedures with different base PDB code.

The current NewMission stored procedure has 11 parameters not 10

INSERT INTO missions (na,td,sc,gsc,log,wea,ace,lv,obj,loc,obc) values (tna,ttd,tsc,tgsc,tlog,twea,tace,tlv,tobj,tloc,tobc);

Delete all of the stored procedures in your DB and re-insert the correct ones

For info the NewMission procedure call is in serverLoadMission.sqf

	
// Set new mission details
_procedureName = "NewMission"; 
_parameters = format[ [tna=%1,ttd=%2,tsc=%3,tgsc=%4,tlog=%5,twea=%6,tace=%7,tlv=%8,tobj=%9,tloc=%10,tobc=%11]",pdb_fullmissionName,mpdb_date_enabled,mpdb_persistentScores_enabled,mpdb_globalScores_enabled,mpdb_log_enabled,mpdb_weapons_enabled,mpdb_ace_enabled,mpdb_landvehicles_enabled,mpdb_objects_enabled,mpdb_locations_enabled,mpdb_objects_contents_enabled];                
_response = [_procedureName,_parameters] call persistent_fnc_callDatabase;

I will be updating the base PDB code, DB and stored procedures again once MSO 4.5 has been released. We are still working on it so there is no point in us updating the base PDB until then.

Edited September 24, 2012 by [KH]Jman

[iOGC_Aenigma 1]

After spending hours getting Arma2 communicating properly with the SQL database I now have an issue where it seems no information is actually being written to the DB.

The mission information has been stored correctly but anything such as player and vehicle locations are not.

The closest thing I can see to an error in the Arma2NETMySQL log file is the following:

Info: 13:16:07 - Received - Database: arma Procedure: UpdateDate Parameters: tda=2011|3|8|1|44,tmid=<null>
Info: 13:16:07 - Parsing parameters...
Warning: 13:16:07 - MySQL error. MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect integer value: '<null>' for column 'tmid' at row 1
  at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
  at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)
  at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId)
  at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
  at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
  at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
  at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
  at Arma2NETMySQLPlugin.MySQL.RunOnDatabase(MySqlCommand command, Int32 maxResultSize)

Is there something I may well have missed in setting the DB up. This has been my first venture into setting up a SQL database and the first time I have tried to configure Arma2NET so it could well be something simple I have missed.

Also is there any reason why I have had to copy files from the @Arma2NET folder and place duplicates of them in \Expansions\beta? Although I would of expected the mod to load from the mod folder it does seem to work correctly with this work around I have had to implement.

It is worth noting that our server does run the latest beta patch is this known to cause conflict in anyway with the Persistent DB stuff?

Cheers for the hard work anyway guys,

Aenigma.

[[kh]jman 49]

There is no conflict with beta's infact I test PDB with beta's. Neither do you have to copy files from the @Arma2NET folder to expansion\beta

Your data is not saving since tmid (mission id) is null

Please post both the server rpt, client rpt and the Arma2NETMySQL log. Suggest you use pastebin.

[iOGC_Aenigma 1]

I didnt think I would have to copy the files to that folder but my Arma2NET was giving me errors asking for the files to be located there. In duplicating them to that folder it fixed the issue. I'm not sure why that was though.

Here is the server rpt file: http://pastebin.com/6xyW0G1T

Here is the client rpt file: http://pastebin.com/ZmnNz4ah

and here is the Arma2NETMySQL log: http://pastebin.com/iDKcJ2TX

I cant post the complete Arma2NETMySQL log as it is too big for Pastebin. As you can see I have been using the latest RC for the MSO mission with the update schema. I have tried the same with the Convoy Hell mission and seem to get the same error.

I have noticed one instance of the Chernarus 4.4 MSO logged in the database but nothing else was written.

Thank you for your quick reply and as I said in my initial post I would not be surprised if this is caused by user error on my part.

Thanks again,

Aenigma.

[[kh]jman 49]

Yes I see exactly the problem. The new scripts in the MSO 4.5RC require that you have the ServerPlugin addin installed in Arma2NET. This addin grabs the server's netbios name, ip address and location. Because you don't have this addin installed the 'NewMission' mysql procedure call is breaking when it tries to call this addin as part of the data call and returing a data string that is too long and thus preventing the new mission row data from inserting correctly resulting in a null id value for the mission id in the returning GetMissionByName call.

ref: http://pastebin.com/iDKcJ2TX

NewMission call (save)

Info: 17:16:40 - Received - Database: arma Procedure: NewMission Parameters: tna=CO32_MSO 4.5 RC2,ttd=1,tsc=1,tgsc=1,tlog=1,twea=1,tace=0,tlv=1,tobj=1,tloc=1,tobc=1,tmar=1,ttas=1,taar=1,tmda="10/1/2012 4:16:39 PM",tmap=chernarus,tsvr=throw "Arma2Net.Managed.FunctionNotFoundException",taddr=throw "Arma2Net.Managed.FunctionNotFoundException",tsloc=throw "Arma2Net.Managed.FunctionNotFoundException"
Info: 17:16:40 - Parsing parameters...
Warning: 17:16:40 - MySQL error. MySql.Data.MySqlClient.MySqlException (0x80004005): Data too long for column 'taddr' at row 1

GetMissionByName call (retrieve)

Info: 17:16:40 - Received - Database: arma Procedure: GetMissionByName Parameters: tna=CO32_MSO 4.5 RC2
Info: 17:16:40 - Parsing parameters...
Info: 17:16:40 - Received - Database: arma Procedure: CountTaskIDsByMission Parameters: tmid=<null>

Please install the ServerPlugin addin, clear (truncate) all the existing data in your mysql arma db and try again.

Please download from the following link, you will find the required addins contained

https://dev-heaven.net/attachments/18978/@Arma2NET.7z

Also I might add that you do not need the -arma2netdev switch in your server's startup line, it is legacy and no longer used/implemented.

Edited October 1, 2012 by [KH]Jman

[iOGC_Aenigma 1]

Thank you again for your reply. I have installed the version of Arma2Net you have given me the link for. In the Arma2NET log file I now get these errors that I was talking about previously:

10/02/2012 20:54:56 Error Caught exception of type System.IO.FileNotFoundException
System.IO.FileNotFoundException: C:\Program Files (x86)\Steam\steamapps\common\arma 2 operation arrowhead\Expansion\beta\Arma2Net.Managed.dll
  at System.Diagnostics.FileVersionInfo.GetVersionInfo(String fileName)
  at Arma2Net.AddInProxy.Utils.get_FileVersion()
  at Arma2NETMySQLPlugin.Startup.StartupConnection()
  at Arma2NETMySQLPlugin.Arma2NETMySQLPlugin.Invoke(String args, Int32 maxResultSize)
  at Arma2Net.Managed.AddInManager.DomainInvokeAddIn()
  at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
  at Arma2Net.Managed.AddInManager.InvokeAddIn(String addInName, String arguments, Int32 maxResultSize)
  at Arma2Net.Managed.Bridge.InvokeBuiltInOrAddIn(String function, Int32 maxResultSize)
  at Arma2Net.Managed.Bridge.InvokeFunction(String function, Int32 maxResultSize)
10/02/2012 20:54:56 Error function: Arma2NETMySQL ['arma','UpdatePlayer','tsc=any,tpos=4694.76|2591.53|0.00143909,tdam=0,tdhe=any,tdbo=any,tdha=any,tdle=any,tdir=148.695,tsta=Stand,tsid=any,tveh=any,tsea=,ttyp=BAF_Soldier_TL_MTP,trat=0,tvd=any,ttd=any,tran=any,tfir=any,tek=any,tck=any,tfk=any,tsui=any,tlif=ALIVE,tdea=any,ttp=any,tlc=any,tld="10/2/2012 7:54:56 PM",tpid=2413766,tna=Aenigma,tmid=any']
10/02/2012 20:54:56 Error maxResultSize: 4095
10/02/2012 20:55:02 Error Caught exception of type System.IO.FileNotFoundException
System.IO.FileNotFoundException: C:\Program Files (x86)\Steam\steamapps\common\arma 2 operation arrowhead\Expansion\beta\Arma2Net.Managed.dll
  at System.Diagnostics.FileVersionInfo.GetVersionInfo(String fileName)
  at Arma2Net.AddInProxy.Utils.get_FileVersion()
  at Arma2NETMySQLPlugin.Startup.StartupConnection()
  at Arma2NETMySQLPlugin.Arma2NETMySQLPlugin.Invoke(String args, Int32 maxResultSize)
  at Arma2Net.Managed.AddInManager.DomainInvokeAddIn()
  at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
  at Arma2Net.Managed.AddInManager.InvokeAddIn(String addInName, String arguments, Int32 maxResultSize)
  at Arma2Net.Managed.Bridge.InvokeBuiltInOrAddIn(String function, Int32 maxResultSize)
  at Arma2Net.Managed.Bridge.InvokeFunction(String function, Int32 maxResultSize)
10/02/2012 20:55:02 Error function: Arma2NETMySQL ['arma','RemoveLandVehicles','[tmid=any]']
10/02/2012 20:55:02 Error maxResultSize: 4095

These errors occurred before until I copied all said files to the beta directory. Is there a reason why my server would be calling for the files there?

[firefly2442 6]

If you install Arma 2 beta version 97299 or greater, that will alleviate the length problem. This beta increases the limit on passing information to 16 KB. As for the other errors, what's your launcher look like?

[[kh]jman 49]

system.IO.FileNotFoundException: C:\Program Files (x86)\Steam\steamapps\common\arma 2 operation arrowhead\Expansion\beta\Arma2Net.Managed.dll

Your Arma2NET installation is incorrect.

I would suggest that you post your logs in the Arma2NET thread and ask Scott for advise regarding installing on a Steam installation.

Edited October 3, 2012 by [KH]Jman

[iOGC_Aenigma 1]

Thanks for your help guys. I now have the PersistentDB working but I have had to copy the contents of the Arma2NET folder into the Expansions\beta\ folder. I have asked Scott what he thinks about this issue but for me its no big problem now that everything is working. Cheers again guys and keep up the brilliant work.

Aenigma.

[zgrillo2004 1]

I know I sound noobish but I need some help on installing this addon. since I dont keep my PC running and it annoys my friends, having to redo the warfare missions over and over and it annoys me too. I look at the installation guide, and its like readin it in japanese to me. I have no idea on how to run database, which is my weakspot. can anyone help me this running?

Thanks

[Hatchet_AS 201]

Google is your friend. Could potentially be your enemy also. (I have no idea.)

The process can differ depending on which type of database you are using.

[zgrillo2004 1]

Already got it running. but seriously. Google never help recruits here. I would suggest you put more effort on it nexttime

[firefly2442 6]

What particular area of the readme/installation guide did you have trouble with?

[norsk2277 96]

i get a error! "include file x\cba\addons\main\script_mod.hppnot found" i am running arma 2 OA takistan life server

Edited October 24, 2012 by norsk2277

[[kh]jman 49]

That error has absolutely nothing to do with PDB. I suggest that you check your CBA installation. CBA is only required on the server side for PDB.

If you are running ACE you'll need CBA client side or ACE will not work. I do not support takistan life, I suggest that you contact the maker.

[norsk2277 96]

Jman;2244336']That error has absolutely nothing to do with PDB. I suggest that you check your CBA installation. CBA is only required on the server side for PDB.

If you are running ACE you'll need CBA client side or ACE will not work. I do not support takistan life' date=' I suggest that you contact the maker.[/quote']

well i dont understand the intallation guide! can you make a video tut of it? where should the CBA be installed? do i need to install the CBA file from theyr website for ACE??

i running the example mission file just to test and i got the same error!

[norsk2277 96]

Here is my error log! it seems like it cant find the databases.txt? it is in the arma 2 operation overhead main directory

Arma2NETMySQL.log

Info: 21:09:51 - Arma2NETMySQL Plugin Started.

Info: 21:09:51 - Version number: 0.1.0.0

Info: 21:09:51 - Compiled against Arma2NET Version: 2.2.0.0

Info: 21:09:51 - Loading databases...

Info: 21:09:51 - Type: mysql Database: arma IPAddress: 127.0.0.1 Port: 3306 Username: norsk2277 Password: NotShownForSecurityReasons

Info: 21:09:53 - Unable to open connection to MySQL database, trying again in 10 seconds.MySql.Data.MySqlClient.MySqlException (0x80004005): Access denied for user 'norsk2277'@'localhost' (using password: YES)

at MySql.Data.MySqlClient.MySqlStream.ReadPacket()

at MySql.Data.MySqlClient.NativeDriver.AuthenticateNew(Boolean reset)

at MySql.Data.MySqlClient.NativeDriver.Authenticate(Boolean reset)

at MySql.Data.MySqlClient.NativeDriver.Open()

at MySql.Data.MySqlClient.Driver.Open()

at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)

at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection()

at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver()

at MySql.Data.MySqlClient.MySqlPool.GetConnection()

at MySql.Data.MySqlClient.MySqlConnection.Open()

at Arma2NETMySQLPlugin.MySQL.OpenConnection(String connectionString)

Arma2NET.log

10/24/2012 21:13:54 Info Arma2NET 2.2.0.0 running on CLR 4.0.30319.269

10/24/2012 21:13:54 Info Loaded into process Arma2NetExplorer

10/24/2012 21:13:54 Info Loading addins

10/24/2012 21:13:54 Info Domain Name Version Publisher Description

10/24/2012 21:13:54 Info Arma2NETMySQLPlugin Arma2NETMySQL 0.1.0.0 firefly2442 Runs MySQL procedure commands.

10/24/2012 21:13:54 Info Arma2NETMySQLPlugin Arma2NETMySQLCommand0.1.0.0 firefly2442 Runs raw MySQL/SQLite commands

10/24/2012 21:13:54 Info BaseFunctionsPlugin CompareVersion 2.0.0.0 Scott_NZ Compares two version strings and returns an integer indicating how they compare.

10/24/2012 21:13:54 Info BaseFunctionsPlugin ClrVersion 2.0.0.0 Scott_NZ Retrieves the version of the Common Language Runtime.

10/24/2012 21:13:54 Info BaseFunctionsPlugin Version 2.0.0.0 Scott_NZ Returns the version of Arma2NET.

10/24/2012 21:13:54 Info CommandLinePlugin CommandLine 2.0.0.0 Scott_NZ Returns the command line used to start the application.

10/24/2012 21:13:54 Info DateTimePlugin DateTime 2.0.0.0 Scott_NZ Returns the current date and time.

10/24/2012 21:13:54 Info Arma2NET initialized in 00:00:00.3087868

Edited October 24, 2012 by norsk2277

[norsk2277 96]

and i wondering what type database i should set up? from the mysql program or use xammp and phpmyadmin?