How to create a Stored Procedure, View & Routine using CLI & Workbench

When trying to create a View, Routine or Procedure for your database from within PHPMyAdmin, you may encounter an error similar to the following:

#1142 - TRIGGER command denied to user '_sso_XXXXX'@'1.2.3.4' for table 'my_table' 

This error relates to how you sign in to PHPMyAdmin and the permissions that are granted to that user. 

If you sign in to the database through My20i using single sign-on (or SSO for short), the system creates a temporary user to access and navigate through your database.

This is because we do not store passwords in plain text. As such, the UI does not display the details for signing in as the actual MySQL user.

The temporary user it creates, _sso_XXXX, does not have the necessary permissions to create Views, Routines, or Procedures and is, therefore, unsuitable for setting these up. 

In addition, security risks make it impossible to log in directly to phpMyAdmin using a username and password.

Alternative Methods

The two methods to get around this are setting up Remote MySQL Access and connecting to the hosting package via SSH to use MySQL CLI.

Stored Procedures

Workbench

In the Navigator, go to your database > Stored Procedures

Right-click and select 'Create Stored Procedure'.

Define the procedure; in this example, the procedure is called 'CalculateTotalSales'.

Enter the code:

BEGIN
    SELECT SUM(amount)
    INTO total_sales
    FROM sales
    WHERE product_id = product_id
      AND sale_date BETWEEN start_date AND end_date;
END

Click apply and then finish. If you encounter any errors, check your SQL query.

You can test the procedure to ensure it has been added correctly simply by opening a new query tab within the UI and then action the following code by hitting the lightning icon.

CALL CalculateTotalSales(101, '2025-01-01', '2025-01-09', @total_sales);
SELECT @total_sales;

CLI

To begin with, a Procedure will need to be created within a .SQL file. In this example, an essential procedure to calculate the total number of sales has been made. PhpMyAdmin may help when populating your SQL file, but you won't be able to execute this via phpMyAdmin.

DELIMITER
CREATE PROCEDURE CalculateTotalSales(
    IN product_id INT,
    IN start_date DATE,
    IN end_date DATE,
    OUT total_sales DECIMAL(10, 2)
)
BEGIN
    SELECT SUM(s.amount)
    INTO total_sales
    FROM sales s
    WHERE s.product_id = product_id
      AND s.sale_date BETWEEN start_date AND end_date;
END $$

DELIMITER ;

Next, load the .SQL file into your database via MySQL CLI. First, upload the newly created .SQL file to your hosting package's root using FTP or the File Manager. You will then need to connect to your hosting package via SSH and load the .SQL file.

mysql -u [username] -p [database] -h [hostname] < procedure.sql

You will be prompted for a password once the procedure has been created. To confirm the procedure has been created correctly,

MariaDB [wordpress-35303439ccfa]> SHOW PROCEDURE STATUS WHERE db = 'wordpress-35303439ccfa';


+------------------------+---------------------+-----------+--------------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db                     | Name                | Type      | Definer                  | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------------------------+---------------------+-----------+--------------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| wordpress-35303439ccfa | CalculateTotalSales | PROCEDURE | wordpress-35303439ccfa@% | 2025-01-23 17:36:53 | 2025-01-23 17:36:53 | DEFINER       |         | utf8mb3              | utf8mb3_general_ci   | latin1_swedish_ci  |
+------------------------+---------------------+-----------+--------------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.004 sec)

View

Workbench

In the Navigator, select the database in the top left, right-click Views, and click 'Create View'.

Once the View has been created, populate the query box with the code for the query. Once done, click 'apply'.

A popup window will ask you to review the script. Click 'apply' and then 'finish' to complete the configuration of the View.

To verify that the View has been created and configured correctly, go to the Navigator, select the database in the top left, right-click Views and the View should be presented to you. In addition, you use the query box to run a SQL command to select the View.

CLI

To create a View, generate a new SQL file and populate it with the relevant queries. Using phpMyAdmin may help when populating your SQL file, but you won't be able to execute this via phpMyAdmin. This is the example that will be loaded via MySQL CLI.

CREATE OR REPLACE VIEW product_sales_summary AS
SELECT 
    product_id,
    sale_date,
    SUM(amount) AS total_sales
FROM 
    sales
GROUP BY 
    product_id, sale_date
ORDER BY 
    product_id, sale_date;


SELECT * FROM product_sales_summary;

You will need to load the SQL file to the database. In order to do this, upload the SQL file to the web space via the File Manager or by using FTP. Once uploaded to the root of the web space, connect to the hosting package using SSH and then run the following MySQL import command.

mysql -u [username] -p [database] -h [hostname] < view.sql

You can query your View by using the following query within MySQL CLI:

SELECT * FROM total_sales_by_product;
+------------+-------------+
| product_id | total_sales |
+------------+-------------+
|        101 |      420.50 |
|        102 |      250.00 |
|        103 |      450.00 |
+------------+-------------+

Routine
 

Workbench

In the Navigator, go to your database > Stored Procedures

Right-click and select 'Create Stored Procedure'.

Define the procedure; in this example, the procedure is called 'GetProductSales'.

Enter the code, in this example we will be using the following:

BEGIN
    DECLARE total_sales DECIMAL(10, 2);

    SELECT SUM(amount) 
    INTO total_sales 
    FROM sales 
    WHERE product_id = product_id 
      AND sale_date BETWEEN start_date AND end_date;

    SELECT 
        product_id AS ProductID,
        start_date AS StartDate,
        end_date AS EndDate,
        total_sales AS TotalSales;
END

One done, apply and then save the changes made.

To verify this has been done successfully, you can click the drop-down arrow in the top left corner for the Stored Procedures. The newly created routine should be present. Alternatively, action the following SQL query and change the DB name where appropriate.

SHOW PROCEDURE STATUS WHERE Db = 'salesandsold-35303837922f';

CLI

Similarly to the procedures, an SQL file must be generated to contain the routine. This will be uploaded to the database using MySQL CLI. The routine example is as follows:

DELIMITER $$

CREATE PROCEDURE GetProductSales(
    IN product_id INT,
    IN start_date DATE,
    IN end_date DATE
)
BEGIN
    DECLARE total_sales DECIMAL(10, 2);

    -- Calculate the total sales for the product in the given date range
    CALL CalculateTotalSales(product_id, start_date, end_date, total_sales);

    -- Output the result
    SELECT 
        product_id AS ProductID,
        start_date AS StartDate,
        end_date AS EndDate,
        total_sales AS TotalSales
    FROM dual;
END $$

DELIMITER ;

Once the SQL file has been uploaded to the root of your hosting package's web space, please connect to the hosting package via SSH and import the SQL file to the MySQL database.

mysql -u [username] -p [database] -h [hostname] < routine.sql

You can then call the routine via the following query:

CALL GetProductSales(101, '2025-01-01', '2025-01-09');
+-----------+------------+------------+------------+
| ProductID | StartDate  | EndDate    | TotalSales |
+-----------+------------+------------+------------+
|       101 | 2025-01-01 | 2025-01-09 |     420.50 |
+-----------+------------+------------+------------+
1 row in set (0.001 sec)
Query OK, 1 row affected (0.001 sec)