Update ZoneIP SQL File completes unsuccessfully

Post Reply
xelloss
Posts: 57
Joined: Sat Mar 09, 2013 1:09 am

Update ZoneIP SQL File completes unsuccessfully

Post by xelloss » Wed Sep 03, 2014 11:01 pm

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?

User avatar
whasf
Site Admin
Posts: 1300
Joined: Thu Jul 19, 2012 9:11 pm

Re: Update ZoneIP SQL File completes unsuccessfully

Post by whasf » Thu Sep 04, 2014 11:02 am

Only thing I can think of is that we use MYSQL not MARIADB as the backend, maybe the syntax is different?
-- Whasf

xelloss
Posts: 57
Joined: Sat Mar 09, 2013 1:09 am

Re: Update ZoneIP SQL File completes unsuccessfully

Post by xelloss » Thu Sep 04, 2014 11:11 am

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.

User avatar
atom0s
Developer
Posts: 537
Joined: Thu Oct 25, 2012 9:52 am

Re: Update ZoneIP SQL File completes unsuccessfully

Post by atom0s » Thu Sep 04, 2014 11:43 am

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.

User avatar
whasf
Site Admin
Posts: 1300
Joined: Thu Jul 19, 2012 9:11 pm

Re: Update ZoneIP SQL File completes unsuccessfully

Post by whasf » Thu Sep 04, 2014 2:37 pm

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'));
-- Whasf

xelloss
Posts: 57
Joined: Sat Mar 09, 2013 1:09 am

Re: Update ZoneIP SQL File completes unsuccessfully

Post by xelloss » Thu Sep 04, 2014 2:50 pm

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.

dacrybabysuck
Posts: 5
Joined: Mon Aug 27, 2012 9:54 pm

Re: Update ZoneIP SQL File completes unsuccessfully

Post by dacrybabysuck » Fri Sep 05, 2014 9:47 pm

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.

Cyprus
Posts: 53
Joined: Sat Sep 06, 2014 10:54 pm

Re: Update ZoneIP SQL File completes unsuccessfully

Post by Cyprus » Sat Sep 06, 2014 11:27 pm

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

User avatar
kjLotus
Lead Developer
Posts: 1813
Joined: Sun Jul 22, 2012 2:16 pm

Re: Update ZoneIP SQL File completes unsuccessfully

Post by kjLotus » Sat Sep 06, 2014 11:50 pm

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

Cyprus
Posts: 53
Joined: Sat Sep 06, 2014 10:54 pm

Re: Update ZoneIP SQL File completes unsuccessfully

Post by Cyprus » Sat Sep 06, 2014 11:55 pm

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!

Post Reply