how to enable REST API for a certain plugin

…investigation is being forwarded to Cpanel people. They reply so far:
something is failing in MySQL due to the stored routine this user has:

MariaDB [information_schema]> select *  from routines \G
*************************** 1. row ***************************
           SPECIFIC_NAME: avs_fn_realmedialibrary_childs
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: agenda_website
            ROUTINE_NAME: avs_fn_realmedialibrary_childs
            ROUTINE_TYPE: FUNCTION
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 1000
  CHARACTER_OCTET_LENGTH: 1000
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: latin1
          COLLATION_NAME: latin1_swedish_ci
          DTD_IDENTIFIER: varchar(1000)
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN
            DECLARE sTemp VARCHAR(1000);
            DECLARE sTempChd VARCHAR(1000);
            SET sTemp = '$';
            SET sTempChd = CAST(rootId AS CHAR);
      SET SESSION group_concat_max_len = 100000;
            WHILE sTempChd IS NOT NULL DO
          SET sTemp = CONCAT(sTemp,',',sTempChd);
          IF  IS NULL OR  = false THEN
              SELECT GROUP_CONCAT(id) INTO sTempChd FROM avs_realmedialibrary WHERE FIND_IN_SET(parent,sTempChd) > 0;
          ELSE
              SELECT GROUP_CONCAT(id) INTO sTempChd FROM avs_realmedialibrary_tmp WHERE FIND_IN_SET(parent,sTempChd) > 0;
          END IF;
            END WHILE;
            RETURN sTemp;
        END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: INVOKER
                 CREATED: 2020-12-16 00:50:59
            LAST_ALTERED: 2020-12-16 00:50:59
                SQL_MODE: ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT:
                 DEFINER: agenda@localhost
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_unicode_ci
      DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.001 sec)

can you please give me some feedback about deleting that routine. I still have all my site/database compromised by it. Even the staff at cpanel have no clue what can happen if I delete it.

Hello Mathias! The problem is that I have a log error triggering after I export my database and click oh “home”. I have had my host to do a big investigation with the cPanel people who did a mysqldump on the database and it didn’t give errors, but also didn’t complete. They said that the presence of that leftover routine could explain why the mysqldump command was not getting everything at the command line (yet they couldn’t tell me what might happen if routines are removed).

You say that I can delete it, and I have now done that. I shall test a few more times and will eventually close this ticket.
I will wait some more time before trying the plugin again… I need to recover a somehow corrupted database. I don’t swear the plugin was the cause but everything points to it 🙂

• This reply was modified 3 years, 3 months ago by anonymized-14293447.

Just to recap, for your records: the issue was that, after installing the plugin, each time I exported the database via PhpMyAdmin I got a log error as I clicked on “home” icon. I shared the log that pointed to a routine (see report above).
Then:

1) One report seemed to be expected behavior when the connection gets ‘terminated’ in some fashion:
cpsrvd b0c8148a8157: Cpanel::Exception::NetSSLeay/(XID jvuh9a) Net::SSLeay::ssl_write_all(SCALAR(0x38afe18)) produced an operating system error (EPIPE, Broken pipe) and 1 [asis,OpenSSL] [numerate,_6,error,errors]: ARRAY(0x3939188)
at /usr/local/cpanel/Cpanel/Server/Connection/SSL.pm line 64.
Cpanel::Server::Connection::SSL::write_buffer(Cpanel::Server::Connection::SSL=HASH(0x3867350), SCALAR(0x38afe18)) called at /usr/local/cpanel/Cpanel/Server/Response.pm line 217
but the log error persisted

2) did a mysqldump on the original database and it didn’t give errors, but also didn’t complete…

[ root@213.175.200.209 [94039093] ~/cptechs]# mysqldump mydatabase > mydatabase.sql
[ root@213.175.200.209 [94039093] ~/cptechs]# ll mydatabase.sql
total 47M
-rw-r--r--   1 root root  47M Dec 24 14:30 mydatabse.sql

The solution was to find a backup of the database that isn’t corrupted in some way. Unfortunately I didn’t have that databse (previous to installing the plugin) anymore

3) I dropped the tables relative to your plugin manually, but the routine seemed to be the issue. Then I asked them if they knew anything about deleting routine. Last reply was the one I mentioned above: “We cannot provide support for custom MySQL routines or how they work or what might happen if they are removed. That’s something you will need to discuss with your developer. However, I will say that this could explain why the mysqldump command was not getting everything at the command line. It would not know anything about routines. In order to do that an additional flag would need to be added. So it is possible that the issue you were experiencing is related to the routine which would also explain why we could not duplicate this on a test server. Your developer will need to run further tests on that.”

4) I removed the routine and the problem is finally gone.

Hope it helps in case anyone else would have the same problem. Cheers