Can not get MySQL connector to show with MySQL>5.6

[arkytn]

Trying to use the MySQL in connector. Created the database and elfinder_file table with the SQL Script
Added the MySQL root

//MySQL Volume
array(
  'driver'        => 'MySQL',
  'host'          => 'localhost',
  'user'          => 'elfinder',
  'pass'          => 'elfinder',
  'db'            => 'elfinder',
  'files_table'   => 'elfinder_file',
  'path'          => 1
),

And nothing shows in elfinder. Tried putting some debug code in the MySQL Driver to be sure that it is connecting. It initializes and makes the connection and properly finds the elfinder_file table from SHOW TABLES.

Still nothing in the elfinder window. LocalSystem and Trash work fine. MySQL nothing.

Using elFinder v2.1.28 Any hints?

[nao-pon]

@arkytn You need to register the initial data in the SQL database. Please send this SQL query.

[arkytn]

Yes that was already there. I used the script in the php directory initially. Record one is there. Here's a dump of it.

<?php
/**
 * Export to PHP Array plugin for PHPMyAdmin
 * @version 4.6.4
 */

/**
 * Database `elfinder`
 */

/* `elfinder`.`elfinder_file` */
$elfinder_file = array(
  array(
 'id' => '1',
 'parent_id' => '0',
 'name' => 'DATABASE',
 'content' => '',
 'size' => '0',
 'mtime' => '0',
 'mime' => 'directory',
 'read' => '1',
 'write' => '1',
 'locked' => '0',
 'hidden' => '0',
 'width' => '0',
 'height' => '0')
);

Still no luck.

[nao-pon]

What is results of $res = $this->query($sql); in elFinderVolumeMySQL.class.php L.535?

[arkytn]

No result. Get a SQL error. Here's the $sql generated:

SELECT f.id, f.parent_id, f.name, f.size, f.mtime AS ts, f.mime, f.read, f.write, f.locked, f.hidden, f.width, f.height, IF(ch.id, 1, 0) AS dirs 
FROM elfinder_file AS f 
LEFT JOIN elfinder_file AS p ON p.id=f.parent_id 
LEFT JOIN elfinder_file AS ch ON ch.parent_id=f.id 
  AND ch.mime='directory' 
WHERE f.id='1' 
GROUP BY f.id

I ran this in phpMyAdmin and I get this Error:

#1055 - Expression #13 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'elfinder.ch.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

[arkytn]

This fixed by adding ch.id to the GROUP BY clause.

GROUP BY f.id, ch.id

NOW I can see the DATEBASE tree. Great !

Apparently ONLY_FULL_GROUP_BY became standard in version 5.7.5

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

[arkytn]

NOTE there are several other GROUP BY clauses with the same problem. Better check them all.
Like Line 235

[nao-pon]

@arkytn Thanks! 👍