Page 1 of 1
Update ZoneIP SQL File completes unsuccessfully
Posted: Wed Sep 03, 2014 11:01 pm
by xelloss
Using Navicat, attempting to apply one of the latest sqls results in a delimiter error. Specifically:
Code: Select all
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELIMITER' at line 1
[Err] CREATE PROCEDURE `update_zoneip`(IN v_ip VARCHAR(15))
BEGIN
UPDATE zone_settings
SET zoneip = (
SELECT z.a + (z.b * 256) + z.c * power(256, 2) + z.d * power(256, 3)
FROM (
SELECT substring_index(substring_index(y.ip, '.', 1), '.', -1) a,
substring_index(substring_index(y.ip, '.', 2), '.', -1) b,
substring_index(substring_index(y.ip, '.', 3), '.', -1) c,
substring_index(substring_index(y.ip, '.', 4), '.', -1) d
FROM (
SELECT v_ip AS ip
) y
) z
);
END ;
DELIMITER ;
[Msg] Finished - Unsuccessfully
--------------------------------------------------
Utilising PMA instead, I was able to successfully upload the file. This creates the procedure on the server, and is viewable in Navicat under the Functions tab / tree.
However, in Navicat it doesn't function; when you try to run it you get another error:
Code: Select all
Procedure execution failed
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.55.24)' at line 1
Again, utilising PMA I can use the procedure to determine IP address conversion.
Is this particular feature / file not compatible with Navicat?
Re: Update ZoneIP SQL File completes unsuccessfully
Posted: Thu Sep 04, 2014 11:02 am
by whasf
Only thing I can think of is that we use MYSQL not MARIADB as the backend, maybe the syntax is different?
Re: Update ZoneIP SQL File completes unsuccessfully
Posted: Thu Sep 04, 2014 11:11 am
by xelloss
MariaDB is a drop-in replacement for MySQL... so it shouldn't be. It does work just fine in PMA ( I made no modifications to the syntax when I ran the code on PMA and it worked without issue, set the IP to a few different items and verified the table updates ), I'm guessing it may just be a fault of Navicat. Since PMA still works for updating it isn't a dealbreaker, just annoying.
Re: Update ZoneIP SQL File completes unsuccessfully
Posted: Thu Sep 04, 2014 11:43 am
by atom0s
Manually executed this via MySQL Workbench and it worked just fine. So it seems MariaDB has some issue with it.
An example of calculating the IP address needed for DSP I wrote awhile ago was:
Code: Select all
SET @ip = '66.215.132.194'; -- edit the address listed here!
SELECT (o4 << 24) | (o3 << 16) | (o2 << 8) | o1 AS ip_address
FROM (SELECT SUBSTRING_INDEX(@ip, '.', 1) AS o1,
SUBSTRING_INDEX(SUBSTRING_INDEX(@ip , '.', 2 ), '.', -1) AS o2,
SUBSTRING_INDEX(SUBSTRING_INDEX(@ip , '.', -2 ), '.', 1) AS o3,
SUBSTRING_INDEX(@ip, '.', -1) AS o4) AS t1;
If you want to alter the function and see if it works with that instead.
Re: Update ZoneIP SQL File completes unsuccessfully
Posted: Thu Sep 04, 2014 2:37 pm
by whasf
Or use
Code: Select all
update zone_settings set zoneip=(select inet_aton('reversedIP'));
Say your WAN IP is 44.257.12.23:
Code: Select all
update zone_settings set zoneip=(select inet_aton('23.12.257.44'));
Re: Update ZoneIP SQL File completes unsuccessfully
Posted: Thu Sep 04, 2014 2:50 pm
by xelloss
atom0s wrote:Manually executed this via MySQL Workbench and it worked just fine. So it seems MariaDB has some issue with it.
If the issue was MariaDB, then PMA wouldn't work either. PMA = phpMyAdmin, if that wasn't clear. It is most likely an issue with Navicat, as other sql files in the project that *do* work with Navicat *do not* work with any other SQL programs ( ie HeidiSQL ) without first clearing out the comments or changing them to ANSI/ISO instead of SQL-99 standards. For this file, it doesn't seem to matter.
atom0s wrote:An example of calculating the IP address needed for DSP I wrote awhile ago was:
Code: Select all
SET @ip = '66.215.132.194'; -- edit the address listed here!
SELECT (o4 << 24) | (o3 << 16) | (o2 << 8) | o1 AS ip_address
FROM (SELECT SUBSTRING_INDEX(@ip, '.', 1) AS o1,
SUBSTRING_INDEX(SUBSTRING_INDEX(@ip , '.', 2 ), '.', -1) AS o2,
SUBSTRING_INDEX(SUBSTRING_INDEX(@ip , '.', -2 ), '.', 1) AS o3,
SUBSTRING_INDEX(@ip, '.', -1) AS o4) AS t1;
I'm aware of this code, it was what I had been using previously to calculate ZoneIPs. This works without issue from Navicat or PMA.
My concern was that a new SQL file was added to the project that Navi refused to use. PMA is capable of doing everything except query copies ( for some reason PMA refuses to save my SQL Bookmarks even though it is properly setup ), so I can just use it if need be instead of Navi for updates and keep Navi to run my custom update scripts until I can figure out why Bookmarks aren't saving.
Re: Update ZoneIP SQL File completes unsuccessfully
Posted: Fri Sep 05, 2014 9:47 pm
by dacrybabysuck
To be honest, the delimiter syntax may not even be needed if you aren't using the mysql client directly. This totally could be a failure of the client. Since there is a semicolon in the proc the command line client requires the delimiter to be changed so it doesn't prematurely kill the script. My apologies as I'm used to oracle and postgres which don't care because they use more sane logic when interpreting queries through their clients. Navicat and other gui type sql admin tools usually have better handling of the pitfalls of command line clients like auto-casting of types to attempt to not fail.
Re: Update ZoneIP SQL File completes unsuccessfully
Posted: Sat Sep 06, 2014 11:27 pm
by Cyprus
I am having the same issue with the same SQL file in MySQL & am still in the learning process of setting this up so I am completely lost as to what I need to do. I noticed that this file was not there on Monday when I first downloaded the files but didn't have time to finish everything until now. Is this even needed at this point? If so, how do I go about fixing this problem. Here is what comes up when I try to execute the file:
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1
[Err] DELIMITER ;
[Msg] Finished - Unsuccessfully
Re: Update ZoneIP SQL File completes unsuccessfully
Posted: Sat Sep 06, 2014 11:50 pm
by kjLotus
Cyprus wrote:I am having the same issue with the same SQL file in MySQL & am still in the learning process of setting this up so I am completely lost as to what I need to do. I noticed that this file was not there on Monday when I first downloaded the files but didn't have time to finish everything until now. Is this even needed at this point? If so, how do I go about fixing this problem. Here is what comes up when I try to execute the file:
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1
[Err] DELIMITER ;
[Msg] Finished - Unsuccessfully
it's not actually needed for anything, it's just to make setting the zoneip easier - i'll try to remember to ask dacrybabysuck about it
Re: Update ZoneIP SQL File completes unsuccessfully
Posted: Sat Sep 06, 2014 11:55 pm
by Cyprus
Ok good...I added it manually in MySQL & it still came back with an error so I am just going to remove it for the time being until there is an update to fix it. Thanks for the response!