[MDEV-17097] Prepared statments error Created: 2018-08-29  Updated: 2018-10-29  Resolved: 2018-10-29

Status: Closed
Project: MariaDB Server
Component/s: Prepared Statements
Affects Version/s: 10.3.9
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Michael Both Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

windows 10

Attachments: File DESKTOP-FNR21V8.err    

 Description    

DELIMITER $$
 
 DROP PROCEDURE IF EXISTS epic_from_exch_epic$$
 
 CREATE PROCEDURE epic_from_exch_epic()
 
 BEGIN
 
 DECLARE x  INT;
 
 DECLARE y  INT;
 
 DECLARE str  VARCHAR(255);
 
 
 
  SET x = 1;
 
  SET y = 20000000;
 
 
 WHILE x  <= y DO
 
 SET  str = CONCAT(str,x,',');
 
 
 
 SET @idlow = 26423400;
 
SET @idhi = @idlow + 2000000;
 
 
 
UPDATE sharescope_ex_ep 
SET epic = substring_index(exch_epic, ':' , -1)
 
WHERE 
epic = ''
 
AND id_sharescope_data between @idlow and @idhi;
 
 
 SET  x = x + 1; 
 
 END WHILE;
 
 
 select * from sharescope_ex_ep 
WHERE id_sharescope_data > (y - 10)
 
order by id_sharescope_data
 
limit 100;
 
 
 
select * from sharescope_ex_ep order by id_sharescope_data DESC limit 10;
 
 
 
 
 END$$
 
DELIMITER ;

RESULT is following error:

SQL Error (1558)
Column count of mysql.proc is wrong. Expected 21, found 20.
Unable to resolve by running mysql_upgrade.exe

 Comments   
Comment by Elena Stepanova [ 2018-08-29 ]

Did you upgrade from some other version?
Please provide the full error log from at least server startup, but preferably including the previous server start/shutdown as well.

Comment by Michael Both [ 2018-08-30 ]

Hi Elena,
I was running MariaDB 10.3 when server crashed and locked me out so I thought if I upgraded to very latest sub-version it might help, which it seems to have done. (I stopped all MYSQL / Apache services first and then restarted after upgrade.)
I had never previously tried to use a routine directly (via HeidiSQL 9.5.0.5291 64Bit) so I cannot say if it was something which previously worked.
I am not fully tech-savvy. Where should I look for the error log files you mentioned? Is it in the event view under windows logs? I don't know what I should be looking for.

Comment by Michael Both [ 2018-08-30 ]

?

Comment by Alice Sherepa [ 2018-08-30 ]

for the location of error log please run 

show variables like 'log_error';

Comment by Michael Both [ 2018-08-31 ]

Thanks Alice.
I found DESKTOP-FNR21V8.err and uploaded it.

I did repeatedly try to run mysql_upgrade.exe from a shell prompt. Perhaps I needed to include switches but any I may have tried clearly had no effect.

Comment by Elena Stepanova [ 2018-08-31 ]

According to the log, you've upgraded from 10.3.2 to 10.3.9, so yes, the error is expected unless mysql_upgrade was run successfully.
Please try to run it with --force option, otherwise it won't do anything on the 2nd and further attempts.

Comment by Michael Both [ 2018-08-31 ]

I stopped Apache and all MySQL services.
I used the command prompt and changed to
C:\>cd\program Files\MariaDB 10.3\bin

then ran:
C:\Program Files\MariaDB 10.3\bin>mysql_upgrade --force

This generated the following error message:

Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 2002 (HY000): Can't connect to MySQL server on 'localhost' (10061)
FATAL ERROR: Upgrade failed

I then restarted Apache and MYSQL again, only to get the following error:
C:\Program Files\MariaDB 10.3\bin>mysql_upgrade --force
Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
FATAL ERROR: Upgrade failed

Next I tried:
C:\Program Files\MariaDB 10.3\bin>mysql_upgrade --force --password
I think that may have done the trick.

Thank you so much for your patience and assistance!

Comment by Elena Stepanova [ 2018-09-30 ]

The first error is naturally expected, since mysql_upgrade has to be run on a running server.
The second error seems to be self-explanatory, you need to provide the password the same way you do when you connect to the server using mysql client.

Generated at Thu Feb 08 08:33:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.