Invalid object name 'sys.Tables'.

[arlequin]

Hello, After installing the plugin's last version I try to access Site Administration / User / Merge user accounts / Merge user accounts and I get the following error (please note I enabled the debug mode for verbosity reasons). Seems like the script is trying to query moodle tables. Our DBAs tell us the query returns 316 rows.

Error reading from database

Debug info: Invalid object name 'sys.Tables'.
SELECT name FROM sys.Tables WHERE name LIKE 'mdl_%' AND type = 'U' ORDER BY name
[array (
)]
Error code: dmlreadexception
Stack trace:

line 443 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 242 of /lib/dml/mssql_native_moodle_database.php: call to moodle_database->query_end()
line 724 of /lib/dml/mssql_native_moodle_database.php: call to mssql_native_moodle_database->query_end()
line 758 of /lib/dml/mssql_native_moodle_database.php: call to mssql_native_moodle_database->get_recordset_sql()
line 326 of /admin/tool/mergeusers/lib/mergeusertool.php: call to mssql_native_moodle_database->get_records_sql()
line 199 of /admin/tool/mergeusers/lib/mergeusertool.php: call to MergeUserTool->init()
line 71 of /admin/tool/mergeusers/index.php: call to MergeUserTool->__construct()

I am running Moodle 2.7.1+ under Apache 2.2.15 and PHP 5.4.30 in CentOS 6.7 x86_64. The RDBMS is located in a Windows box running MS-SQL Server 2012 Standard Ed. service pack 1.

[jpahullo]

Thanks for reporting this issue.

We'll keep an eye on this asap. We are quite busy this time, though.

As you suggest, we are retrieving the list of Moodle database tables. We did this way instead of using the Moodle wrapper (accessing via $DB->get_tables()).

If you could contribute this change by calling directly to $DB->get_tables(), it will solve your problem and it would make a generic solution for any supported database by Moodle.

Thanks,

Jordi

More references: http://www.sourcexref.com/xref/moodle/nav.html?lib/dml/moodle_database.php.source.html#l898

[arlequin]

Jordi, Thanks for the guides.

I've changed line 326 of /admin/tool/mergeusers/lib/mergeusertool.php, where it was:

   $tableNames = $DB->get_records_sql($this->sqlListTables);

...now it's:

   $tableNames = $DB->get_tables();

And now the error is the following:

Debug info: Invalid object name 'INFORMATION_SCHEMA.Columns'.

SELECT DISTINCT column_name
FROM
INFORMATION_SCHEMA.Columns
WHERE
TABLE_NAME = ? AND
(TABLE_SCHEMA = ? OR TABLE_CATALOG=?) AND
COLUMN_NAME IN ('authorid','reviewerid','userid','user_id','id_user','user')
[array (
0 => 'profiling',
1 => 'moodle',
2 => 'moodle',
)]
Error code: dmlreadexception
Stack trace:

    line 443 of /lib/dml/moodle_database.php: dml_read_exception thrown
    line 242 of /lib/dml/mssql_native_moodle_database.php: call to moodle_database->query_end()
    line 724 of /lib/dml/mssql_native_moodle_database.php: call to mssql_native_moodle_database->query_end()
    line 785 of /lib/dml/mssql_native_moodle_database.php: call to mssql_native_moodle_database->get_recordset_sql()
    line 429 of /admin/tool/mergeusers/lib/mergeusertool.php: call to mssql_native_moodle_database->get_fieldset_sql()
    line 354 of /admin/tool/mergeusers/lib/mergeusertool.php: call to MergeUserTool->getCurrentUserFieldNames()
    line 199 of /admin/tool/mergeusers/lib/mergeusertool.php: call to MergeUserTool->init()
    line 71 of /admin/tool/mergeusers/index.php: call to MergeUserTool->__construct()

The reference you gave me is timing out.

Regards, Gabriel.

[jpahullo]

Hi!

From logs, you are using a MSSQL Server. Is it ok for your site? Which version of mssql do you use?

I mean, useing $DB->get_tables() is standard API from Moodle, and it is implemented in Moodle core (/lib/dml/mssql_native_moodle_database.php). So that, it's weird it does not work. And now, it is something like your datase user does not have right access to perform such an operation (loocking for the INFORMATION_SCHEME or something like that). You could try this operation from a direct SQL into your MSSQL Server, using the same username and password for your Moodle database, to check if they have read access to that part of the database. Contact to your database administrator to ensure it is possible

In addition, as Administrador, could you perform this operation "SiteAdmin->Misc->XMLDB editor->Check Indexes"? If my hypothesis is right, you should be able to do that.

Regards,

Jordi

[arlequin]

Hi, there.

I am running Moodle 2.7.1+ under Apache 2.2.15 and PHP 5.4.30 in CentOS 6.7 x86_64. The RDBMS is located in a Windows box running MS-SQL Server 2012 Standard Ed. service pack 1.

The DBA executed the transaction using moodle DB user and password and successfully accesed the INFORMATION_SCHEME but using this other query:

SELECT DISTINCT column_name FROM INFORMATION_SCHEMA.COLUMNS

Please note using the table name COLUMNS in upper case because if in capitalized lower case doesn't work which is the way Moodle invokes it. This query is shorter than the original performed by Moodle.

Also, I did was able to perform SiteAdmin / Misc / XMLDB editor / Check Indexes.

[jpahullo]

Hi!

Perfect! We'll keep an eye on this.

If you want, to accelerate it, you can contribute with a PR with the necessary changes.

Anyway, thanks for sharing and reporting this issue.

Regards,

Jordi

[arlequin]

Sorry about this, but... what does PR stand for? "Pull Request"? Do you mean contributing with the change in $DB->get_tables()?

Thanks.

[arlequin]

Hello. I wont'be creating a Pull Request since the fix wasn't related to code itself. We solved the problem just asking our DBA to change the database property related to case "sensitiveness".

The DB was case sensitive; now it's case insensitive. Therefore, you can issue:

SELECT DISTINCT column_name FROM INFORMATION_SCHEMA.Columns

and so on without error.

Thanks!

[jpahullo]

Ok!

Thanks for reporting back your solution.

Nevertheless, for portability and sustainability, the change to $DB->get_tables() still applies to this project.

I therefore leave this issue open to change the way we are obtaining the list of tables by using the core API $DB->get_tables().

Thanks anyway for putting into light this issue.

Do not hesitate to report any other malfunctioning from this plugin.

Regards!

Jordi

[jpahullo]

please, consider update to latest version, since we updated the plugin to NOT to use SQLs specific for databases, but use the general $DB instance properly. So, now on, this plugin supports any kind of database that Moodle also supports.

Thanks for the feedback.