search

pBlueG / SA-MP-MySQL

MySQL plugin for San Andreas Multiplayer
BSD 3-Clause "New" or "Revised" License
198 stars 79 forks source link

mysql_query_file: Comments causing file query to not run correctly #275

Open mikecnr opened 2 years ago

mikecnr commented 2 years ago

I have a bit of a bizzare issue... I am trying to run an .sql file query, it apparently executes ok, but nothing happens.

This is the query (it's actually really stripped down from what it was - I stripped out as much as I could to try and narrow down the issue:

--
-- Database: `mcnr_mns`
--

-- --------------------------------------------------------

--
-- Table structure for table `helpful_hints`
--

CREATE TABLE `helpful_hints` (
  `ID` int(11) NOT NULL,
  `Text` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

This is an exported.sql file from phpMyAdmin.

When I remove the comments, the query executes properly.

The mysql.log file states the query executed successfully but the table is not created. There's no errors it simply doesn't create the table.

Literally, removing the comments at the top makes it work fine. But removing just individual lines (I've methodically tried removing one line at a time to narrow it down) doesn't seem to do it, it appears to be some combination. I'm looking at the -- ----------------------- line suspiciously...

The query, in this exact form, executes perfectly fine in phpMyAdmin. It only seems to be with mysql_query_file.

Any other things I can try in an attempt to narrow this down further? I assume it's just a bug with how comments are parsed by the plugin.

EDIT: @maddinat0r Related to #166? I'm on R41-4

EDIT 2:

[22/09/2022 13:35:20] [DEBUG] mysql_query_file(1, "MNS_SCRIPTFILES/Queries/runonce/helpful_hints.sql", 0) (files/scripts/commands/console_commands.pwn:4925 -> files/scripts/commands/console_commands.pwn:4924)
[22/09/2022 13:35:20] [DEBUG] mysql_query_file: parsed 1 queries for file 'scriptfiles/MNS_SCRIPTFILES/Queries/runonce/helpful_hints.sql' (files/scripts/commands/console_commands.pwn:4925 -> files/scripts/commands/console_commands.pwn:4924)
[22/09/2022 13:35:20] [DEBUG] mysql_query_file: executing query '--  --  --  -- CREATE TABLE `helpful_hints` (   `ID` int(11) NOT NULL,   `Text` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;' (files/scripts/commands/console_commands.pwn:4925 -> files/scripts/commands/console_commands.pwn:4924)
[22/09/2022 13:35:20] [DEBUG] CHandle::Execute(this=0x2c13f560, type=3, query=0x49390658)
[22/09/2022 13:35:20] [DEBUG] CConnection::Execute(query=0x49390658, this=0x3b9c60d8, connection=0x36548d10)
[22/09/2022 13:35:20] [DEBUG] CQuery::Execute(this=0x49390658, connection=0x36548d10)
[22/09/2022 13:35:20] [INFO] query "--  --  --  -- CREATE TABLE `helpful_hints` (   `ID` int(11) NOT NULL,   `Text` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;" successfully executed within 0.109 milliseconds
[22/09/2022 13:35:20] [DEBUG] CResultSet::Create(connection=0x36548d10, query_str='--  --  --  -- CREATE TABLE `helpful_hints` (   `ID` int(11) NOT NULL,   `Text` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;')
[22/09/2022 13:35:20] [DEBUG] created new resultset '0x2d1b61c8'
[22/09/2022 13:35:20] [DEBUG] fetched MySQL result '0x0'
[22/09/2022 13:35:20] [DEBUG] CHandle::Execute - return value: true
[22/09/2022 13:35:20] [DEBUG] mysql_query_file: return value: '0' (files/scripts/commands/console_commands.pwn:4925 -> files/scripts/commands/console_commands.pwn:4924)

EDIT 3: Ok so appending spaces onto the blank comment lines (the ones in which the entire line is literally just '--') solves the issue. Can this please be fixed, as any query that is spat out by phpMyAdmin will be formatted with those types of comments... Simply need to skip over those lines. Relatively easy fix, no?

EDIT 4: https://github.com/pBlueG/SA-MP-MySQL/blob/master/src/natives.cpp#L766 Something needs to be done here regarding this, but I don't have experience enough to know exactly what. I understand the part about appending a space, and that does happen, but appending a space after a comment then immediately having a query after it makes no sense; surely that's just going to have mySQL treat it like the query is a comment??