Please help us to make AbanteCart Ideal Open Source Ecommerce Solution for everyone.

Support AbanteCart eCommerce

Author Topic: Cannot get tables list. Please check privileges of mysql database user.  (Read 6311 times)

Offline andrevdb

  • Newbie
  • *
  • Posts: 16
  • Karma: +0/-6
    • View Profile
 Cannot get tables list. Please check privileges of mysql database user.

Offline maxter

  • Full Member
  • ***
  • Posts: 228
  • Karma: +64/-0
    • View Profile
What is this? Spam?

Offline Basara

  • Administrator
  • Hero Member
  • *****
  • Posts: 5774
  • Karma: +274/-2
    • View Profile

Offline hani

  • Full Member
  • ***
  • Posts: 119
  • Karma: +14/-1
    • View Profile
Re: Cannot get tables list. Please check privileges of mysql database user.
« Reply #3 on: December 04, 2020, 10:13:59 AM »
Hello everyone

I moved my website to a new server and everything was fine but when i stated installing some extensions I get the message
Cannot get tables list. Please check privileges of mysql database user.

i checked my database user privileges and all is OK and cannot see where is the problem.

Any advice
Thanks in advance

Offline Basara

  • Administrator
  • Hero Member
  • *****
  • Posts: 5774
  • Karma: +274/-2
    • View Profile
Re: Cannot get tables list. Please check privileges of mysql database user.
« Reply #4 on: December 07, 2020, 01:01:12 AM »

I moved my website to a new server and everything was fine but when i stated installing some extensions I get the message
Cannot get tables list. Please check privileges of mysql database user.

Hello. Please ask your hosting provider to help you.

Offline hani

  • Full Member
  • ***
  • Posts: 119
  • Karma: +14/-1
    • View Profile
Re: Cannot get tables list. Please check privileges of mysql database user.
« Reply #5 on: December 07, 2020, 01:53:08 AM »
Hi
Thank for your reply

I did check with my host provider before i submitted this issue on the forum and from the server side all privileges are allowed.

Offline Basara

  • Administrator
  • Hero Member
  • *****
  • Posts: 5774
  • Karma: +274/-2
    • View Profile
Re: Cannot get tables list. Please check privileges of mysql database user.
« Reply #6 on: December 07, 2020, 06:10:10 AM »
Hello.
What are the extensions you have an issue with?

Offline hani

  • Full Member
  • ***
  • Posts: 119
  • Karma: +14/-1
    • View Profile
Re: Cannot get tables list. Please check privileges of mysql database user.
« Reply #7 on: December 07, 2020, 08:10:59 AM »
Not with any specific extension, I get this message when i want to install an extension see attached image

Offline abolabo

  • core-developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 2046
  • Karma: +318/-13
  • web for all, all for web!
    • View Profile
    • AbanteCart
Re: Cannot get tables list. Please check privileges of mysql database user.
« Reply #8 on: December 07, 2020, 08:55:27 AM »
It's a MySQL issue.
To solve just grant privileges for database user to see list of tables via your CPanel.

You can check manually in the phpMyAdmin.
Just run SHOW TABLES; query.

Or ask your hosting provider techsupport about that.
“No one is useless in this world who lightens the burdens of another.”
― Charles Dickens

Offline hani

  • Full Member
  • ***
  • Posts: 119
  • Karma: +14/-1
    • View Profile
Re: Cannot get tables list. Please check privileges of mysql database user.
« Reply #9 on: December 10, 2020, 12:57:21 AM »
Hello

I contacted my host support and they checked in depth the cause for this message [ Cannot get tables list. Please check privileges of mysql database user.] and their reply is below.

==================================================================================

The reason for the experienced issue appears to be in the core files of Abantecart and more precisely in the following one:

core/lib/backup.php
In this file there is the following code:

 //2. check mysql driver
        $sql = "SELECT TABLE_NAME AS 'table_name',
                                        table_rows AS 'num_rows', (data_length + index_length - data_free) AS 'size'
                                FROM information_schema.TABLES
                                WHERE information_schema.TABLES.table_schema = '".DB_DATABASE."'";
        $result = $this->db->query($sql, true);
        if ($result === false && DB_DRIVER == 'mysql') {
            $this->error[] = 'Probably error will occur. Please change db-driver to "amysqli" in your /system/config.php file.';
        } elseif ($result === false) {
            $this->error[] = 'Cannot get tables list. Please check privileges of mysql database user.';
            var_dump($result);
        }
From what I can see this code is being used to check if the tables and data in your database, however opposite to the expected behavior of the application this SQL query returns false which makes the error "Cannot get tables list. Please check privileges of mysql database user." appear at the top of the extensions installation page.

This issue has nothing to do with the privileges of your MySQL user to the information_schema table as your database use already have full privileges for that table. Instead the issue here is caused by something else and that thing is the error which that SQL query results in:

SQL Error: BIGINT UNSIGNED value is out of range in '((`information_schema`.`TABLES`.`DATA_LENGTH` + `information_schema`.`TABLES`.`INDEX_LENGTH`) - `information_schema`.`TABLES`.`DATA_FREE`)'
Error No: 1690
Now, I am not sure why that error appears but definitely, it has nothing to do with the setup of the web hosting service. You might want to forward this result to the authors of the application. I read quite a lot about this error and I even tried to set the SQL mode to "NO_UNSIGNED_SUBTRACTION", however this error still persist.

By the way, from what I understand the file core/lib/backup.php is used with backup purposes, however, we already provide you with a free complimentary backup service so you should simply disregard this message and proceed further with the installation of the module except of course if that error prevents you to proceed with the installation.

Offline abolabo

  • core-developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 2046
  • Karma: +318/-13
  • web for all, all for web!
    • View Profile
    • AbanteCart
Re: Cannot get tables list. Please check privileges of mysql database user.
« Reply #10 on: December 10, 2020, 04:06:08 AM »
Please post your mysql version
“No one is useless in this world who lightens the burdens of another.”
― Charles Dickens

Offline hani

  • Full Member
  • ***
  • Posts: 119
  • Karma: +14/-1
    • View Profile
Re: Cannot get tables list. Please check privileges of mysql database user.
« Reply #11 on: December 10, 2020, 04:41:38 AM »
Mysql version is 5.7

Offline abolabo

  • core-developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 2046
  • Karma: +318/-13
  • web for all, all for web!
    • View Profile
    • AbanteCart
Re: Cannot get tables list. Please check privileges of mysql database user.
« Reply #12 on: December 10, 2020, 10:23:44 AM »
not sure about this solution..
can you check it?

just open core/lib/backup.php line 619
and replace sql query with this

Code: [Select]
$sql = "SELECT TABLE_NAME AS 'table_name',
table_rows AS 'num_rows', (data_length as signed + index_length  as signed - data_free as signed) AS 'size'
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = '".DB_DATABASE."'";
“No one is useless in this world who lightens the burdens of another.”
― Charles Dickens

Offline hani

  • Full Member
  • ***
  • Posts: 119
  • Karma: +14/-1
    • View Profile
Re: Cannot get tables list. Please check privileges of mysql database user.
« Reply #13 on: December 11, 2020, 01:07:13 AM »
Hi
Thanks for your reply.

here is the code how made it
    //2. check mysql driver
       // $sql = "SELECT TABLE_NAME AS 'table_name',
               //table_rows AS 'num_rows', (data_length + index_length - data_free) AS 'size'
            //FROM information_schema.TABLES
            //WHERE information_schema.TABLES.table_schema = '".DB_DATABASE."'";
      $sql = "SELECT TABLE_NAME AS 'table_name',
      table_rows AS 'num_rows', (data_length as signed + index_length  as signed - data_free as signed) AS 'size'
   FROM information_schema.TABLES
   WHERE information_schema.TABLES.table_schema = '".DB_DATABASE."'";   
        //$result = $this->db->query($sql,true);

Note that i had to last line i had to remove, if kept it does not solve the problem.
Now with this code the error message is gone

Thanks again

 

Powered by SMFPacks Social Login Mod