4

Trying to create simple stored procedure in phpmyadmin through 'Routines' tab as

BEGIN SELECT * FROM tbl_user; END

throws errors as Column count of mysql.proc is wrong,expected 21 found 20, please use mysql_upgrade to fix this error

What I already tried

1 - tried couple of way to use mysql_upgrade , but as I am using xampp I was not able to find a way to upgrade mysql as mysql is replaced by maridb

2 - tried upgrading phpmyadmin to latest by following this

3 - Uninstalled xampp and installed latest version '7.3.8'

4 - Formatted the system and installed latest ubuntu '19.04' (lol that was't needed though)

5 - tried to make changes in mysql.proc, added new column as 'aggregate' , which I found here now which throws error as can not load from mysql.proc, the table is probably corrupted

Now I am left with only one option which is uninstall xampp and install mysql workbench.

I am using xampp as most of my projects are php based, How can I use mysql_upgrade Or can fix this issue Any help would really appreciate.

dEL
  • 482
  • 1
  • 6
  • 23
  • What happens when you replace `*` with explicitly named columns? – Bohemian Aug 24 '19 at 22:33
  • It throws the same error as `column count of mysql.proc is wrong,expected 21 found 20, please use mysql_upgrade to fix this error` – dEL Aug 24 '19 at 22:36
  • I tells you a row number whoch ever is truncated, that wound change by installing anything. check the row in the error message. – nbk Aug 24 '19 at 22:40
  • @nbk `mysql.proc` has every row as per [this](https://mariadb.com/kb/en/library/mysqlproc-table/) except `aggregate`, which I tried to add in mysql.proc which then throws error as `can not load from mysql.proc, the table is probably corrupted` – dEL Aug 24 '19 at 22:45
  • try this one please https://stackoverflow.com/a/31140127/5193536 – nbk Aug 24 '19 at 23:05
  • @nbk thanks for commenting, I tried to repair the table as given in the link, however the error stays as it is, still throwing `column count of mysql.proc is wrong,expected 21 found 20, please use mysql_upgrade to fix this error` – dEL Aug 24 '19 at 23:25
  • What happens when you replace * with just one named column? Does the error still mention 20 columns? – Bohemian Aug 24 '19 at 23:30
  • yes sir, the error still remains as it is – dEL Aug 24 '19 at 23:39

1 Answers1

11

Thanks for commenting @nbk
your link gave me a way to fix this issue
Any one who is facing same issue here is the answer you can access mysql in the terminal using

  1. /opt/lampp/bin/mysql -u root -p
  2. after that enter the mysql user password if it worked well then type exit
  3. and now type sudo /opt/lampp/bin/mysql_upgrade -u root -p , enter your password and tadam , here is your mysql upgraded

really appreciate your help @Bohemian, @nbk, Now I am able to create the Stored Procedure without facing any issue

Javapocalypse
  • 2,223
  • 17
  • 23
dEL
  • 482
  • 1
  • 6
  • 23
  • Using absolute path at 1st step would make it `/opt/lampp/bin/mysql -u root -p` (notice the `/` at the beginning). Default password for `xampp` is empty, so you can press enter without typing any password when prompted. Also, for the 3rd step to work, you'll have to `cd` into `/opt/lampp/bin` first, or you can do `sudo /opt/lampp/bin/mysql_upgrade -u root -p`. – Qumber Dec 03 '20 at 06:14