exclude views from tool parsing

[jpahullo]

(Reported on plugin page: https://moodle.org/plugins/view.php?plugin=tool_mergeusers)

Hello, it seems your tool is attempting to update views in addition to tables. I added a print_r($data); line to the function merge() in /admin/tool/mergeusers/lib/table/generictablemerger.php at line 61 to find out where the problem was and it happened on a view that I made in our system to help with some external reporting purposes. Views should be eliminated from this tool's searching. Here is the error message I received on the web page after it failed:

For further reference, these results are recorded in the log id 3. Some error occurred:

Exception thrown when merging: 'Error reading from database".
Unknown column 'id' in 'field list'
Trace:
#0 C:\Websites\moodle\lib\dml\mysqli_native_moodle_database.php(1014): moodle_database->query_end(false)
#1 C:\Websites\moodle\admin\tool\mergeusers\lib\table\generictablemerger.php(65): mysqli_native_moodle_database->get_records_sql('SELECT id FROM ...')
#2 C:\Websites\moodle\admin\tool\mergeusers\lib\mergeusertool.php(277): GenericTableMerger->merge(Array, Array, Array)
#3 C:\Websites\moodle\admin\tool\mergeusers\lib\mergeusertool.php(212): MergeUserTool->_merge('535', '1331')
#4 C:\Websites\moodle\admin\tool\mergeusers\index.php(135): MergeUserTool->merge('535', '1331')
#5 {main}

Merge failed! Your database engine supports transactions. Therefore, the whole current transaction has been rolled back and no modification has been made to your database. Report to moderator Imatge Perry Way Perry Way

dj, 15 gen 2015, 21:00 To add to my previous comment, I have a worthy enhancement to make things work right.

In /admin/tool/mergeusers/lib/mergeusertool.php line 135, if you take this code here:

$this->sqlListTables = 'SHOW TABLES like "' . $CFG->prefix . '%"';

and replace it with this:

$this->sqlListTables = "SHOW FULL TABLES WHERE `Tables_in_moodle` LIKE '" . $CFG->prefix . "%' AND `Table_type` = 'BASE TABLE'";

it should avoid attempting to set the values of records in views because it filters all views out from the results.

Once I did this then everything worked nicely!

Thanks for this tool!

Perry

[danbuntu]

This issue still seems to exist in the plugin. I've resolved it by added the following after line 163 of lib/mergeusertool.php:

`` $views = $DB->get_records_sql("SELECT replace(TABLE_NAME, :prefix, '') as tablename FROM information_schema.TABLES WHERE TABLE_SCHEMA LIKE :dbname AND TABLE_TYPE LIKE 'VIEW'", ['prefix' => $CFG->prefix, 'dbname' => $CFG->dbname]);

        $ignoreViews = [];
        foreach ($views as $view) {
            $ignoreViews[] = $view->tablename;
        }

        $tablesProcessedByTableMergers = array_merge($tablesProcessedByTableMergers, $ignoreViews);

``

[jpahullo]

Thanks @danbuntu for your patch and feedback.

We will consider that in the future. In our institution we use a different prefix for views (v_) so this is why we never got this error.

Thanks a lot for your feedback.

Jordi