Problems with the count drones query

[darozak]

When I tried creating a query to count the numbers of spacecraft in a companies fleet, it didn't work, failing to return any data to the results buffer. I spent a full afternoon troubleshooting without any success. I'm going to set this problem to the side for now but will likely need to come back to it soon.

[darozak]

I find that I have the same issue when trying to retrieve the resource description using the following script:

SELECT description FROM resources WHERE id=[value_1];

Basically, the game engine doesn't report an error. However, it also doesn't place a value in the results buffer.

[darozak]

The following query also generates the same issue:

SELECT x_coord, y_coord, z_coord FROM objects WHERE object_id=[value_1];

[darozak]

When running the above query, I found the following report in the game engine log:

* 25/11/2020,22:31:30.673 [0] Running VM query [SELECT objects.x_coord, objects.y_coord, objects.z_coord FROM objects WHERE object_id=0;]
* 25/11/2020,22:31:30.673 [0] Allocated [12] bytes (Total memory: [27518])
* 25/11/2020,22:31:30.673 [0] Query [SELECT objects.x_coord, objects.y_coord, objects.z_coord FROM objects WHERE object_id=0;] field[0] type [5]
* 25/11/2020,22:31:30.673 [0] Query [SELECT objects.x_coord, objects.y_coord, objects.z_coord FROM objects WHERE object_id=0;] field[1] type [5]
* 25/11/2020,22:31:30.673 [0] Query [SELECT objects.x_coord, objects.y_coord, objects.z_coord FROM objects WHERE object_id=0;] field[2] type [5]
* 25/11/2020,22:31:30.673 [0] [1] entries found for query [SELECT objects.x_coord, objects.y_coord, objects.z_coord FROM objects WHERE object_id=0;] ([3] fields per row)
* 25/11/2020,22:31:30.673 [0] WARNING: Unexpected field type [5], ignored/not serialized into VM memory
* 25/11/2020,22:31:30.673 [0] WARNING: Unexpected field type [5], ignored/not serialized into VM memory
* 25/11/2020,22:31:30.673 [0] WARNING: Unexpected field type [5], ignored/not serialized into VM memory
* 25/11/2020,22:31:30.674 [0] Deallocated [12] bytes (Total memory in use: [27506])
* 25/11/2020,22:31:30.674 [0] Value [1] pushed into stack

This suggests that the game engine doesn't recognize the field type and is therefore not reporting the values.

[darozak]

When I tried query 520, which I know works, to identify the central body for Earth (0), I got the following report in the game engine log:

* 25/11/2020,22:41:35.869 [0] Query script after tags replacement [SELECT central_body_object_id FROM objects WHERE object_id=3;]
* 25/11/2020,22:41:35.869 [0] Running VM query [SELECT central_body_object_id FROM objects WHERE object_id=3;]
* 25/11/2020,22:41:35.869 [0] Allocated [4] bytes (Total memory: [27654])
* 25/11/2020,22:41:35.869 [0] Query [SELECT central_body_object_id FROM objects WHERE object_id=3;] field[0] type [3]
* 25/11/2020,22:41:35.869 [0] [1] entries found for query [SELECT central_body_object_id FROM objects WHERE object_id=3;] ([1] fields per row)
* 25/11/2020,22:41:35.870 [0] Writing integer [0] into VM address [5498], bytes [00][00]

This appears to have worked because the game engine doesn't have a problem with field type 3.

[darozak]

Here are the hex values for some of the different SQL data types:

Table Column Type	Hex Value	Notes
Protocol::MYSQL_TYPE_DECIMAL	0x00	Implemented by ProtocolBinary::MYSQL_TYPE_DECIMAL
Protocol::MYSQL_TYPE_TINY	0x01	Implemented by ProtocolBinary::MYSQL_TYPE_TINY
Protocol::MYSQL_TYPE_SHORT	0x02	Implemented by ProtocolBinary::MYSQL_TYPE_SHORT
Protocol::MYSQL_TYPE_LONG	0x03	Implemented by ProtocolBinary::MYSQL_TYPE_LONG
Protocol::MYSQL_TYPE_FLOAT	0x04	Implemented by ProtocolBinary::MYSQL_TYPE_FLOAT
Protocol::MYSQL_TYPE_DOUBLE	0x05	Implemented by ProtocolBinary::MYSQL_TYPE_DOUBLE
Protocol::MYSQL_TYPE_NULL	0x06	Implemented by ProtocolBinary::MYSQL_TYPE_NULL
Protocol::MYSQL_TYPE_TIMESTAMP	0x07	Implemented by ProtocolBinary::MYSQL_TYPE_TIMESTAMP
Protocol::MYSQL_TYPE_LONGLONG	0x08	Implemented by ProtocolBinary::MYSQL_TYPE_LONGLONG
Protocol::MYSQL_TYPE_INT24	0x09	Implemented by ProtocolBinary::MYSQL_TYPE_INT24
Protocol::MYSQL_TYPE_DATE	0x0a	Implemented by ProtocolBinary::MYSQL_TYPE_DATE
Protocol::MYSQL_TYPE_TIME	0x0b	Implemented by ProtocolBinary::MYSQL_TYPE_TIME
Protocol::MYSQL_TYPE_DATETIME	0x0c	Implemented by ProtocolBinary::MYSQL_TYPE_DATETIME
Protocol::MYSQL_TYPE_YEAR	0x0d	Implemented by ProtocolBinary::MYSQL_TYPE_YEAR
Protocol::MYSQL_TYPE_NEWDATE [a]	0x0e	see Protocol::MYSQL_TYPE_DATE
Protocol::MYSQL_TYPE_VARCHAR	0x0f	Implemented by ProtocolBinary::MYSQL_TYPE_VARCHAR
Protocol::MYSQL_TYPE_BIT	0x10	Implemented by ProtocolBinary::MYSQL_TYPE_BIT
Protocol::MYSQL_TYPE_TIMESTAMP2 [a]	0x11	see Protocol::MYSQL_TYPE_TIMESTAMP
Protocol::MYSQL_TYPE_DATETIME2 [a]	0x12	see Protocol::MYSQL_TYPE_DATETIME
Protocol::MYSQL_TYPE_TIME2 [a]	0x13	see Protocol::MYSQL_TYPE_TIME
Protocol::MYSQL_TYPE_NEWDECIMAL	0xf6	Implemented by ProtocolBinary::MYSQL_TYPE_NEWDECIMAL
Protocol::MYSQL_TYPE_ENUM	0xf7	Implemented by ProtocolBinary::MYSQL_TYPE_ENUM
Protocol::MYSQL_TYPE_SET	0xf8	Implemented by ProtocolBinary::MYSQL_TYPE_SET
Protocol::MYSQL_TYPE_TINY_BLOB	0xf9	Implemented by ProtocolBinary::MYSQL_TYPE_TINY_BLOB
Protocol::MYSQL_TYPE_MEDIUM_BLOB	0xfa	Implemented by ProtocolBinary::MYSQL_TYPE_MEDIUM_BLOB
Protocol::MYSQL_TYPE_LONG_BLOB	0xfb	Implemented by ProtocolBinary::MYSQL_TYPE_LONG_BLOB
Protocol::MYSQL_TYPE_BLOB	0xfc	Implemented by ProtocolBinary::MYSQL_TYPE_BLOB
Protocol::MYSQL_TYPE_VAR_STRING	0xfd	Implemented by ProtocolBinary::MYSQL_TYPE_VAR_STRING
Protocol::MYSQL_TYPE_STRING	0xfe	Implemented by ProtocolBinary::MYSQL_TYPE_STRING
Protocol::MYSQL_TYPE_GEOMETRY	0xff

So it looks like type 3 is MYSQL_TYPE_LONG and that is acceptable. However, the game engine does not like type 5, which is MYSQL_TYPE_DOUBLE.

Here are the corresponding data types:

Input Variable C Type	buffer_type Value
MYSQL_TYPE_TINY	TINYINT
MYSQL_TYPE_SHORT	SMALLINT
MYSQL_TYPE_LONG	INT
MYSQL_TYPE_LONGLONG	BIGINT
MYSQL_TYPE_FLOAT	FLOAT
MYSQL_TYPE_DOUBLE	DOUBLE
MYSQL_TYPE_TIME	TIME
MYSQL_TYPE_DATE	DATE
MYSQL_TYPE_DATETIME	DATETIME
MYSQL_TYPE_TIMESTAMP	TIMESTAMP
MYSQL_TYPE_STRING	TEXT, CHAR, VARCHAR
MYSQL_TYPE_BLOB	BLOB, BINARY, VARBINARY

[darozak]

Ok. It looks like to resolve the issue where the query returns the x, y , and z coordinates, I need to change the coordinate fields from double to int in the DB. Since these fields currently aren't being populated and the query would be useless, I won't waste my time with this particular issue.

The issue with the other query not returning the resource description is similar. The resource description field is a different field type than the name. So, it is probably generating an error when the game engine tries to read from that field type. I would probably need to switch this over to VARCHAR to work. However, since this isn't an important query to support at this point, I'm not going to fuss with it either.

[darozak]

The original issue, in which a count of the spacecraft failed to deliver results, is likely due to the fact that the MySQL count function returns a BIGINT value. However, the game engine is only configured to collect INT values. The only way I can fix this is by modifying and recompiling db.c so that it responds to both MYSQL_TYPE_LONG and MYSQL_TYPE_LONGLONG. This should be easy enough to do. I just need to use an or statement in the case test.