Inserts via 998 method in hiveext.dll not working

[Swiss-Sarge]

Hi man,

just wanted to let you know, eventually i am dumb and stupid, but at least for me if i use the insert statement with a 998 hive statement, no records get written to the db. The logfiles show success, but a quick check reveals nothing in the table.

The tables that are affected imo: buidings and instance_buildings.

Select and update statements work perfectly with 999/998.

example log:

2013-02-12 22:25:15 HiveExt: [Debug] Original params: |CHILD:998: INSERT INTO building (class_name) VALUES ('Land_Wall_Gate_Ind2B_R'):[]:| 2013-02-12 22:25:15 HiveExt: [Information] Method: 998 Params: INSERT INTO building (class_name) VALUES ('Land_Wall_Gate_Ind2B_R'):[]: 2013-02-12 22:25:15 HiveExt: [Information] Result: true

thx for looking into it, i am starting to lose my nerves heer with the random insert behaviour :-)

Sarge

[deathlyrage]

Having the exact same issues, I hope this is resolved soon.

[thevisad]

Sorry guys, my power supply in my machine blew out today. I just got my machine back up and running. I am looking into this issue now.

[thevisad]

What happens when you run it via 999?

[dzoeteman]

@thevisad: Yeah it's 999 not 998. I've been using that method over a month now in bliss, so I think it should be similiar in Reality. INSERT INTO i've never tried though, but I don't expect it to fail. The only thing that could be the issue is that your SQL or arma script is wrong.

[Swiss-Sarge]

Hi mate,

Getting closer to the problem ...

Tried to make the whole test function bulletproof.

You can see in the below code that i try 5 times to insert an object classname into the building table (my workaround for response times / whatever issue, problem here is that sometimes after an insert, the follow up query to retrieve the objectID seems to be coming too fast for mysql ­ so the initial insert hasn¹t finished, and the followup select fails. This i mitigated with my way of trying up to 5 times to insert / retrieve.

So the building table with that logic works.

If i try to insert that ObjectId into the instance_building table, the query fails. What ³SEEMS² to happen is that when i try an insert into the instance_buildings table, mysql tries to insert the classname of the object into the buildings table as a new record wherever it might get it from ­ which fails, due to it already being there, and by this the whole insert statement to instance_buildings fails.

I am more than puzzled ...

The insert into instance_buildings fails regardless if the object i want to insert has an entry in building or not.

Is the hive dll messing up the passed keys ?

I attached the code and the corresponding rpt and hive log snippets, maybe you can see sth that causes this.

What the code aims to do (in case you want to test ingame) - is add an menu item ³save object² to your action menu (eh not included, addaction not included, i hooked it into R3F), and passes the object to the server, where it gets processed by the attached script.

C

[dzoeteman]

Try writing it in your favorite MySQL program (HeidiSQL or whatever you use), see if it pops up with any errors. Make sure your script code is actually correct. I don't believe the HiveExt is at fault here.

[Swiss-Sarge]

it is ... i tried the inserts via squirrel, works like a charm.

It seems that if i run an insert via 998 and had a previous insert to another 998 the hive messes those 2 requests up.

error message in the hiveext.log:

2013-02-14 03:00:48 HiveExt: [Debug] Original params: |CHILD:998: INSERT INTO instance_building (building_id,instance_id,worldspace) VALUES (576,1,'[240,[6908.88,2439.78,-0.00281382]]'):[]:|
2013-02-14 03:00:48 HiveExt: [Information] Method: 998 Params: INSERT INTO instance_building (building_id,instance_id,worldspace) VALUES (576,1,'[240,[6908.88,2439.78,-0.00281382]]'):[]:
2013-02-14 03:00:48 HiveExt: [Information] Result: true
2013-02-14 03:00:48 Database: [Error] Error 1062 (Duplicate entry 'Land_Wall_Gate_Ind2A_R' for key 'uq1_building') in MySQLStmtExecute SQL: 'INSERT INTO building (class_name) VALUES ('Land_Wall_Gate_Ind2A_R') VALUES()'

code snippet, warning wall of text incoming:

/**
 * Saves an object to the hive
 * 
 * 
 * Copyright (C) 2013 SARGE
 * 
 * This program is free software under the terms of the GNU General Public License version 3.
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

private ["_objectid","_key","_oId","_classname","_pos","_worldspace","_val","_count"];

_object = _this;

_classname = typeOf _object;

_pos = getPosATL _object;
_worldspace = [round(direction _object), _pos];

_objectid = _object getvariable "ObjectID";

_tbl_bld_id = 0;
_ib_id = 0;
_val = 0;
_key = "";
_status = "";

If (isNil "_objectid") then { // check if object has no ID  = not coming from the DB

    // check if classname of object exists in table buildings

    // define query
    _key = format ["CHILD:999: select id from building WHERE class_name= '%1' AND id > ?:[0]:", _classname];    
    _result = _key call server_hiveReadWrite;

    _status = _result select 0;

    if (_status == "CustomStreamStart") then {
        _val = _result select 1;
        diag_log format["SARGE DEBUG: Value of - _val : %1",_val];
    };

    if (_val == 0) then { // need to add object to buildings table

        diag_log "Sarge debug: No entries in table building found, starting to insert";

        _save_success = false;
        _count = 0;

        while {!_save_success && _count < 5} do {

            if(_count == 0) then {
                _key = format ["CHILD:998: INSERT INTO building (class_name) VALUES ('%1'):[]:", _classname];  
                _key call server_hiveWrite;

                diag_log "Sarge debug: new classname should have been written to building table, sleeping 1 second";
                sleep 1;
            };

            _key = format ["CHILD:999: select id from building WHERE class_name= '%1' AND id > ?:[0]:", _classname];
            _result1 = _key call server_hiveReadWrite;

            _status = _result1 select 0;
            _resultsfound = _result1 select 1;

            diag_log format["SARGE DEBUG: Results found = %1",_resultsfound];

            if (_resultsfound == 1) then {
                _save_success = true;
                _result1 = _key call server_hiveReadWrite;
                _tbl_bld_id = _result1 select 0;
                diag_log format["SARGE DEBUG: ID of the inserted buiding: %1",_tbl_bld_id];        

            } else {
                _count = _count + 1;
                diag_log format["SARGE DEBUG: %1. retrieving try failed, trying again.",_count];                
            };

        };

    } else {

        diag_log "Sarge DEBUG: assumes that the building is found in the building table";

        _result = _key call server_hiveReadWrite;
        _tbl_bld_id = _result select 0;

        diag_log format["SARGE DEBUG: ID of the found buiding: %1",_tbl_bld_id];            
    };

    if (typename _tbl_bld_id == typename 0) then {

        _save_success = false;
        _count = 0;

        while {!_save_success && _count < 5} do {

            // insert object into instance_building

            _key = format ["CHILD:998: INSERT INTO instance_building (building_id,instance_id,worldspace) VALUES (%1,1,'%2'):[]:", _tbl_bld_id,_worldspace];  
            _key call server_hiveWrite;

            diag_log format["SARGE DEBUG: Instance_Building entry should have been written! %1",""];
            sleep 1;

            _key = format ["CHILD:999: select id from instance_building WHERE worldspace = '%1' AND ?:[1]:", _worldspace];
            _result2 = _key call server_hiveReadWrite;
            _resultsfound = _result2 select 1;

            diag_log format["SARGE DEBUG: Results from instance_building found = %1",_resultsfound];

            if (_resultsfound == 1) then {
                _save_success = true;
                _result2 = _key call server_hiveReadWrite;
                _tbl_ins_id = _result2 select 0;
                diag_log format["SARGE DEBUG: ID of the inserted instance buiding: %1",_tbl_ins_id];
                _object setVariable ["ObjectID",_tbl_ins_id,true];

            } else {
                _count = _count + 1;
                diag_log format["SARGE DEBUG: instance_buidling - %1. try failed, trying again.",_count];                
            };
        };

    };

} else {   // else (object already in instance_buildings

    // generate query to update coordinates

    _key = format ["CHILD:998:update `instance_building` set `worldspace` = '%1' where `id` = ?:[%2]:", _worldspace, _objectid];

    diag_log _key; 

    // execute query

    _key call server_hiveWrite;

};    

// debug

_key = format["ID des Objektes: %1 - Classname: %2 - Worldspace: %3",_objectid,_classname,_worldspace];
diag_log _key;

[p-schneider]

You should only add it once to the database. If the table building already has an entry for 'Land_Wall_Gate_Ind2A_R' you must not insert another one (which is prevented by the unique setting in the DB). Replace 'CHILD:998: INSERT INTO building (class_name) VALUES' with 'CHILD:998: INSERT IGNORE INTO building (class_name) VALUES'. Always make sure you completely understood the database design before running custom SQL commands with 998/999. You can test your commands with tools like HeidiSQL. In this case you would have gotten the same error message: Error 1062 (Duplicate entry... AFAIK this issue can be closed, because this is not a bug in Reality or the hive. Use the forums at opendayz.net for support.

[Swiss-Sarge]

Sry mate i disagree.

if you check my code, you will see that the classname gets inserted only once into the building table.

In the insert into instance_buildings, i try to insert up to 5 times, and this is intended ... the logic checks if the record is there, if not, it tries again.

It fails every single time. even IF my script logic would fail, a FIRST entry should have been written to the instance_buidling table, which didnt happen.

so please do not close this issue, if you need help understanding what is happening, i am happy to support.

[p-schneider]

The error your log shows in this line Database: [Error] Error 1062 (Duplicate entry 'Land_Wall_Gate_Ind2A_R' for key 'uq1_building') in MySQLStmtExecute SQL: 'INSERT INTO building (class_name) VALUES ('Land_Wall_Gate_Ind2A_R') VALUES()' tells you that there already is a building called Land_Wall_Gate_Ind2A_R in the table building. This is caused by the execution of the line _key = format ["CHILD:998: INSERT INTO building (class_name) VALUES... which is executed because _save_success = false; and _count = 0; You didn't show us the content of the .rpt file. So we don't see the other debug messages related to that.

Edit: if your insert into building fails, the script could abort and would not insert into instance_building.

[Swiss-Sarge]

Well, if you read the script, you can see that it never tries to insert into instance_building if the insert into building didnt happen.

The issue is a different one mate. The Duplicate error occurs AFTER i try to insert into instance_buidling. The insert into the building table has long been happening and isnt called a second time.

RPT log:

---snippet ----

-- first test, classname is NOT in buildings table

 --SARGE comment: checking if building is in building table

 3:00:47 "READ/WRITE: "["CustomStreamStart",0]""
 3:00:47 "SARGE DEBUG: Value of - _val : 0"
 3:00:47 "Sarge debug: No entries in table building found, starting to insert"
 3:00:47 "WRITE: "true""
 3:00:47 "Sarge debug: new classname should have been written to building table, sleeping 1 second"
 3:00:48 "READ/WRITE: "["CustomStreamStart",1]""
 3:00:48 "SARGE DEBUG: Results found = 1"
 3:00:48 "READ/WRITE: "[576]""
 3:00:48 "SARGE DEBUG: ID of the inserted buiding: 576"

 --SARGE comment: all fine, was inserted in buildings and the ID was retrieved

 -- SARGE comment: starting to try to insert into instance_building

 3:00:48 "WRITE: "true""
 3:00:48 "SARGE DEBUG: Instance_Building entry should have been written! "
 3:00:49 "READ/WRITE: "["CustomStreamStart",0]""
 3:00:49 "SARGE DEBUG: Results from instance_building found = 0"
 3:00:49 "SARGE DEBUG: instance_buidling - 1. try failed, trying again."

 -- Sarge comment - 1. try failed, although hivewrite returned true

 -- Sarge comment: trying 4 more times, all failing

 3:00:49 "WRITE: "true""
 3:00:49 "SARGE DEBUG: Instance_Building entry should have been written! "
 3:00:50 "READ/WRITE: "["CustomStreamStart",0]""
 3:00:50 "SARGE DEBUG: Results from instance_building found = 0"
 3:00:50 "SARGE DEBUG: instance_buidling - 2. try failed, trying again."
 3:00:50 "WRITE: "true""
 3:00:50 "SARGE DEBUG: Instance_Building entry should have been written! "
 3:00:51 "READ/WRITE: "["CustomStreamStart",0]""
 3:00:51 "SARGE DEBUG: Results from instance_building found = 0"
 3:00:51 "SARGE DEBUG: instance_buidling - 3. try failed, trying again."
 3:00:51 "WRITE: "true""
 3:00:51 "SARGE DEBUG: Instance_Building entry should have been written! "
 3:00:52 "READ/WRITE: "["CustomStreamStart",0]""
 3:00:52 "SARGE DEBUG: Results from instance_building found = 0"
 3:00:52 "SARGE DEBUG: instance_buidling - 4. try failed, trying again."
 3:00:52 "WRITE: "true""
 3:00:52 "SARGE DEBUG: Instance_Building entry should have been written! "
 3:00:53 "READ/WRITE: "["CustomStreamStart",0]""
 3:00:53 "SARGE DEBUG: Results from instance_building found = 0"
 3:00:53 "SARGE DEBUG: instance_buidling - 5. try failed, trying again."

 -- Sarge comment: no insert into instance_building happened

 3:00:53 "ID des Objektes: <null> - Classname: Land_Wall_Gate_Ind2A_R - Worldspace: [240,[6908.88,2439.78,-0.00281382]]"

 3:05:02 "PLAYER: DISCONNECT: Sarge (XXXXXXXX) Object: B 1-1-B:1 (Sarge) REMOTE, Character ID: 7"
 3:08:24 "PLAYER: LOGIN STARTING: Sarge [XXXXXXXX]"

 -- SARGE comment: relogin, and trying to save the object again, this time it already is in the buildings table

 3:08:42 "READ/WRITE: "["CustomStreamStart",1]""
 3:08:42 "SARGE DEBUG: Value of - _val : 1"
 3:08:42 "Sarge DEBUG: assumes that the building is found in the building table"
 3:08:42 "READ/WRITE: "[576]""
 3:08:42 "SARGE DEBUG: ID of the found buiding: 576"

 -- SARGE comment: id succesfully retrieved from the buidlings table, trying to insert to instance_table

 3:08:42 "WRITE: "true""
 3:08:42 "SARGE DEBUG: Instance_Building entry should have been written! "
 3:08:43 "READ/WRITE: "["CustomStreamStart",0]""
 3:08:43 "SARGE DEBUG: Results from instance_building found = 0"
 3:08:43 "SARGE DEBUG: instance_buidling - 1. try failed, trying again."

 3:08:43 "WRITE: "true""
 3:08:43 "SARGE DEBUG: Instance_Building entry should have been written! "
 3:08:44 "READ/WRITE: "["CustomStreamStart",0]""
 3:08:44 "SARGE DEBUG: Results from instance_building found = 0"
 3:08:44 "SARGE DEBUG: instance_buidling - 2. try failed, trying again."

 3:08:44 "WRITE: "true""
 3:08:44 "SARGE DEBUG: Instance_Building entry should have been written! "
 3:08:45 "READ/WRITE: "["CustomStreamStart",0]""
 3:08:45 "SARGE DEBUG: Results from instance_building found = 0"
 3:08:45 "SARGE DEBUG: instance_buidling - 3. try failed, trying again."

 3:08:45 "WRITE: "true""
 3:08:45 "SARGE DEBUG: Instance_Building entry should have been written! "
 3:08:46 "READ/WRITE: "["CustomStreamStart",0]""
 3:08:46 "SARGE DEBUG: Results from instance_building found = 0"
 3:08:46 "SARGE DEBUG: instance_buidling - 4. try failed, trying again."

 3:08:46 "WRITE: "true""
 3:08:46 "SARGE DEBUG: Instance_Building entry should have been written! "
 3:08:47 "READ/WRITE: "["CustomStreamStart",0]""
 3:08:47 "SARGE DEBUG: Results from instance_building found = 0"
 3:08:47 "SARGE DEBUG: instance_buidling - 5. try failed, trying again."

 -- SARGE comment: object was again NOT written into the instance_building table

[p-schneider]

_key = format ["CHILD:999: select id from building WHERE class_name= '%1' AND id > ?:[0]:", _classname];
            _result1 = _key call server_hiveReadWrite;

            _status = _result1 select 0;
            _resultsfound = _result1 select 1;

            diag_log format["SARGE DEBUG: Results found = %1",_resultsfound];

            if (_resultsfound == 1) then {
                _save_success = true;
                _result1 = _key call server_hiveReadWrite;
                _tbl_bld_id = _result1 select 0;
                diag_log format["SARGE DEBUG: ID of the inserted buiding: %1",_tbl_bld_id];        

here you are executing the same query twice in case the _result1 select 1 was 1. AFAIK _result1 select 1 gives you the value from the colum id you are looking for and _result1 select 0 gives you some status (in your case _tbl_bld_id should be equal to _status) Add _result1 to your diag_log to see what is in it and check your database if the newly inserted id is correct.

But I'm sure thevisad does not want us to write about problems you have with your custom sqf code on an issue on his github. There are better places to discuss problems like this.

[Swiss-Sarge]

Sry and you are wrong again my friend .-)

read up on what the hive returns and in which way. the first is checking the stream status, the following does actually retrieve the requested values.

Thevisad - are you reading this? I assume you can understand that this is not about custom sqf, but an issue that is reproduceable and seems to affect more people than just me.

[deathlyrage]

^^ Yes you are correct, ever since we switched from bliss with the new dll 998 broke.

[dzoeteman]

Then use 999. Works the same way. That one should work fine

[Swiss-Sarge]

confirmed, 999 works without issues. I recommend to leave this bug open though, 998 is clearly broken.

[thevisad]

Closing issue, please post in the forums at http://opendayz.net/index.php?forums/support.100/ for any further support.

This cannot be fixed in the current hive due to inherent issues.