1

Here is procedure counting all of the tables and storing result in temporary table:

DELIMITER $$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `COUNT_ALL`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE TNAME CHAR(255);
DECLARE table_names CURSOR for 
    SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN table_names;   
DROP TABLE IF EXISTS TCOUNTS;
CREATE TEMPORARY TABLE TCOUNTS 
  (
    TABLE_NAME CHAR(255),
    RECORD_COUNT INT
  ) ENGINE = MEMORY; 
WHILE done = 0 DO
  FETCH NEXT FROM table_names INTO TNAME;
   IF done = 0 THEN
    SET @SQL_TXT = CONCAT("INSERT INTO TCOUNTS(SELECT '" , TNAME  , "' AS TABLE_NAME, COUNT(*) AS RECORD_COUNT FROM ", TNAME, ")");
    PREPARE stmt_name FROM @SQL_TXT;
    EXECUTE stmt_name;
    DEALLOCATE PREPARE stmt_name;  
  END IF;
END WHILE;
CLOSE table_names;
SELECT * FROM TCOUNTS;
SELECT SUM(RECORD_COUNT) AS TOTAL_DATABASE_RECORD_CT FROM TCOUNTS;
END
$$

It works fine in mysql> terminal, so I can use CALL COUNT_ALL statement and after a while I have printed tables with results.

Can anyone help how to store this particular procedure in PhpMyAdmin? I'm struggling with documentation, and I see no clear explanation, just a few posts describing simple procedure creating, but not such complex solution...

PhpMyAdmin do not want to grant access to this procedure, it generates cPanel session token in INVOKER/DEFINER field and after logout and login again procedure is missing. It doesn't work with root, database_root, database_user, cpanel_user, i've tried every account. It works with temporary cPanel session such like cpses_adyCcgNvgZ@localhost inserted by phpMyAdmin in DEFINER field only. Otherwise i have a info: MySQL said: #1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Server is on Centos7 system with WHM and CPanel, I have all root privileges.

So the question is how to store this procedure PERMANENTLY in phpMyAdmin and get rid of this message?

===========

Finally resolved, maybe it will be helpful for others.

Problem is in WHM. When you log in WHM and then from WHM you log in cPanel account and enter phpMyAdmin, you are recognized as session_user@localhost and then you can only create and execute procedures in your session. Procedures and functions are stored but are not accessible with different session token.

To avoid such behavior you should log in cPanel directly, so then phpMyAdmin recognizes you correctly, and every procedure in your database(s) has INVOKER / DEFINER field set to you_cpanel_login@localhost. So every procedure/function is fully accessible.

Hope it help someone.

Vilq
  • 567
  • 6
  • 11
  • phpMyAdmin is just an IDE for your database (MySQL) stored procedures are stored in your database. – Jay Blanchard Mar 13 '17 at 15:51
  • First do a `use database;` where database is the database you stored your proceedure. then do `call COUNT_ALL;` – RiggsFolly Mar 13 '17 at 15:56
  • Thank you for answer. Of course everything is working in MySQL shell. But how to store PERMANENTLY this procedure in PhpMyAdmin? PhpMyAdmin force me to add security INVOKER or DEFINER. After inserting procedure it generates a cPanel session token sth like: cpses_adyCcgNvgZ@localhost and the procedure works fine. But after logout and login again to cPanel account whole procedure is missing and i cannot edit it. PhpMyAdmin don't want to grant access for: - cPanel user, -database user and even -root; of course all account have full database access. That's the problem. – Vilq Mar 14 '17 at 10:35

0 Answers0