Stored Proc that used to work won't work

[dbrewerton]

Hello everyone. I have a really strange issue here that I thought maybe someone could give me some insight on. I have a set of stored procedures that to my knowledge worked just fine before. To my knowledge, nothing has changed. Just yesterday I decided to test a source of data that is in the same existing format but shorter. MySQL came back with an error that says:

#1136 - Column count doesn't match value count at row 1

So, like any good experimenter, I decided to feed some of the older formatted data back in and whammo, I get the same error. Now I start scratching my head going "huh?" I then reinstall my stored procedures from backup, does the same stupid thing. So now I turn to you experts hoping someone, anyone can shed some light on why this error is happening since the SP I'm running hasn't changed.

1st procedure: parse_raw

###################
delimiter ;

DROP PROCEDURE IF EXISTS `parse_raw`;
DELIMITER $$
CREATE PROCEDURE `parse_raw`()
BEGIN

DECLARE x_id BIGINT;
DECLARE xtext text;
DECLARE useless1 text;
DECLARE useless2 text;
DECLARE x_device_string varchar(30);
DECLARE x_device_id BIGINT;
DECLARE slice_text text;
DECLARE time_text text;
DECLARE new_slice_id BIGINT;
DECLARE msmnt_tag VARCHAR(30);
DECLARE value_text text;
DECLARE x_msmnt_type_id BIGINT;

#Loop for all rows in table
main_loop: LOOP

	#Get a row, if we can't we're done
	SET x_id = -1;
	SELECT ewise_raw_data_in.raw_id, ewise_raw_data_in.raw_data_xml FROM ewise_raw_data_in LIMIT 1 INTO x_id, xtext;
	IF (x_id = -1) THEN
		LEAVE main_loop;
	END IF;


	CALL get_node_content(xtext, 'energywise', xtext, useless1, useless2);

	CALL get_node_content(xtext, 'device', x_device_string, useless1, xtext);

	set x_device_id = 0;
	SELECT ewise_device.device_id FROM ewise_device WHERE ewise_device.device_string = x_device_string INTO x_device_id;

	IF (x_device_id = 0) THEN
	INSERT INTO ewise_device VALUES (default, NULL, x_device_string, NULL, NULL, NULL, NULL);
	SELECT LAST_INSERT_ID() INTO x_device_id;
	END IF;

	WHILE length(xtext) > 0 DO
	CALL get_node_content(xtext, 'slice', slice_text, useless1, xtext);

	IF length(slice_text) >0 THEN
		CALL get_node_content(slice_text, 'time', time_text, useless1, slice_text);
		INSERT INTO ewise_slice VALUES (default, x_device_id, time_text);
		SELECT LAST_INSERT_ID() INTO new_slice_id;

		WHILE length(slice_text) > 0 DO
			set x_msmnt_type_id = 0;
			CALL get_first_node_name(slice_text, msmnt_tag);
			CALL get_node_content(slice_text, msmnt_tag, value_text, useless1, slice_text);
			IF length(msmnt_tag) >0 THEN
				SELECT ewise_msmnt_type.msmnt_type_id FROM ewise_msmnt_type WHERE ewise_msmnt_type.msmnt_type_code=LEFT(msmnt_tag,8) INTO x_msmnt_type_id;
				IF (x_msmnt_type_id = 0) THEN
					INSERT INTO ewise_msmnt_type VALUES (default, LEFT(msmnt_tag, 8), NULL, NULL);
					SELECT LAST_INSERT_ID() INTO x_msmnt_type_id;
				END IF;
				INSERT INTO ewise_slice_msmnt VALUES (default, new_slice_id, x_msmnt_type_id, value_text); 
			END IF;
		END WHILE;

	END IF;

	END WHILE;

	#Delete the data row
	DELETE FROM ewise_raw_data_in WHERE ewise_raw_data_in.raw_id=x_id;

END LOOP main_loop;
END
$$

2nd procedure - get node content:

###################
delimiter ;

DROP PROCEDURE IF EXISTS `get_node_content`;
DELIMITER $$
CREATE PROCEDURE `get_node_content`(IN in_xml text, IN in_node_tag VARCHAR(30), OUT node_contents text, OUT prev_string text, OUT post_string text )
BEGIN

DECLARE node_start int;
DECLARE node_content_start int;
DECLARE node_end int;
DECLARE node_tag_start varchar(30);
DECLARE node_tag_end varchar(30);

set node_tag_start = concat('<',in_node_tag,'>');
set node_tag_end = concat('</',in_node_tag,'>');


set node_start = locate(node_tag_start, in_xml) ;
set node_end = locate(node_tag_end, in_xml);
set node_content_start = node_start + length(node_tag_start);

set node_contents = if(node_start=0,'', if(node_end <= node_content_start, '', substring(in_xml, node_content_start, node_end - node_content_start)));
set prev_string = if(node_contents = '','',substring(in_xml, 1, node_start-1));
set post_string = if(node_contents = '','',substring(in_xml, node_end + length(node_tag_end), length(in_xml))); 

END
$$

###################

3rd sp: GET FIRST NODE NAME

###################
delimiter ;

DROP PROCEDURE IF EXISTS `get_first_node_name`;
DELIMITER $$
CREATE PROCEDURE `get_first_node_name`(IN in_xml TEXT, OUT out_node_name VARCHAR(30))
BEGIN

DECLARE node_start int;
DECLARE node_end int;

set node_start = locate('<', in_xml);
set node_end = locate('>', in_xml);

set out_node_name = if(node_start=0,'', if(node_end <=node_start, '', substring(in_xml, node_start + 1, node_end - node_start - 1)));

END
$$

###################

I've included my entire stored procedure file for reference if I forgot any of the SPs in the body of this message. Many thanks all :)

[dbrewerton]

I found the exact block of code that is causing the issue. It seems MySQL for windows doesn't like me using default for the value. Can anyone give me advice on what to use instead? Mind you, if the device is not found, the x_device_id gets set to 0. In turn, if the value is zero, the code is supposed to add it to the device table but it doesn't.

set x_device_id = 0; 
SELECT ewise_device.device_id FROM ewise_device WHERE ewise_device.device_string = x_device_string INTO x_device_id; 

select x_id, x_device_string, x_device_id; 

IF (x_device_id = 0) THEN 
[B][I]INSERT INTO ewise_device VALUES (default, NULL, x_device_string, NULL, NULL, NULL, NULL); [/I][/B]
SELECT LAST_INSERT_ID() INTO x_device_id; 
END IF;

[Atli]

Hey.

Has the structure of the ewise_device table changed since the procedure was last know to be working? - The error you showed at the top of your post is typically shown when you try a INSERT statement without specifying which columns to use, but then failing to provide a value for all the columns in the table.

This, by the way, is why it is recommended to ALWAYS provide a list of columns to use, even if you plan on using all columns in the table. It allows the table structure to be altered without having to go through and modifying all the INSERT queries.

[dbrewerton]

Generally, I always use specific columns during insert statements. This SP was not my creation, I inherited it :) Now, the previous system that this ran on was Linux/apache. The new system is Windows with IIS. I'm thinking of re-writing this part of the SP to take the last number of IDs and add 1 to it if the device is not found in the table. Any suggestions on how to re-write this proc better?

[dbrewerton]

Let me ask a question. I'm thinking of rewriting that one part of the procedure. From this:

	set x_device_id = 0;
	SELECT ewise_device.device_id FROM ewise_device WHERE ewise_device.device_string = x_device_string INTO x_device_id;

	select x_id, x_device_string, x_device_id;

	IF (x_device_id = 0) THEN
	INSERT INTO ewise_device VALUES (default, NULL, x_device_string, NULL, NULL, NULL, NULL);
	SELECT LAST_INSERT_ID() INTO x_device_id;
	END IF;

to something more like this:

	set x_device_id = 0;
	SELECT ewise_device.device_id FROM ewise_device WHERE ewise_device.device_string = x_device_string INTO x_device_id;

	select x_id, x_device_string, x_device_id;

	IF (x_device_id = 0) THEN
	INSERT INTO ewise_device (device_string) VALUES (x_device_string);
	SELECT MAX(device_id) INTO x_device_id;
	END IF;

Would that resolve my problem?

[dbrewerton]

Never mind, I tested it and that worked.