SQL Errors and Database Problems

SQL Error 'db.#__docman_nodes' is not VIEW

While installing or upgrading DOCman, you might get a 1146 error which

JInstaller:: Install: SQL Error 'dbname.#__docman_nodes' is not VIEW.

This error means something didn’t go well with the installation and the view was not created correctly. You can easily resolve this with a few simple steps:

1. Go to phpMyAdmin.
2. Make sure that your database user has CREATE VIEW permissions.
3. Look for the #__docman_nodes table and delete it.
4. Without uninstalling the existing version, re-install DOCman. This should create the missing view.

SQL Error 'db.#__docman_document_contents' doesn't exist

A 1146 error indicates that at least one database table is missing from your database. The error might look like one of these:

1146 - Internal Server Error

1146 Table ‘dbname.#__docman_document_contents' doesn't exist of the following query : SHOW INDEX FROM `#__docman_document_contents`

To resolve the issue, simply re-install DOCman without uninstalling it. The re-installation should create the missing database tables.

SQL Error SELECT list is not in GROUP BY clause

This error indicates that the ONLY_FULL_GROUP_BY setting enabled is in MySQL. While this value is enabled by default in MySQL 5.7, the majority of web-hosts have disabled it (see: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html).

Both Joomla and DOCman cannot run with the ONLY_FULL_GROUP_BY setting enabled in MySQL and we suggest you or your host remove the value of it from your MySQL configuration's sql-mode value.

One of the working configurations that we're aware of is:
sql-mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Joomla smart search problems when indexing large documents

You may encounter problems with indexing of large documents in Joomla smart search if your server's MySQL MEMORY engine maximum size is too small for Joomla smart search indexing. There are two options to overcome these problems.

1. Ask your hosting company increase the max_heap_table_size value
2. If you are not using smart search for DOCman you can disable the Smart Search - DOCman plugin

MYSQL VIEW error when importing the database for a Joomla 3.x site

When you are importing the database for a Joomla 3.x site, with DOCman installed, you might get the following error when your database user does not have CREATE VIEW permissions, or when the CREATE VIEW permissions are not properly configured.

SQL query: [Documentation]
CREATE ALGORITHM=UNDEFINED DEFINER=`xxxxxxxxxxx`@`xx.xxx.xxx.x/xxx.xxx.xxx.x` SQL SECURITY DEFINER VIEW `jos_docman_file_counts` AS select `jos_docman_documents`.`storage_path` AS `storage_path`,count(0) AS `count` from `jos_docman_documents` where (`jos_docman_documents`.`storage_type` = 'file') group by `jos_docman_documents`.`storage_path`
MySQL said: [Documentation]
#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

You can fix this error in one of two ways:

1. Ask your hosting company to allow your database user to perform CREATE VIEW queries
2. If your database user already has CREATE VIEW permissions then you will have to manually tweak the SQL query. Change the query from
   
   CREATE ALGORITHM=UNDEFINED DEFINER=xxxxxxxxxxx@xx.xxx.xxx.x/xxx.xxx.xxx.xSQL SECURITY DEFINER VIEW
   
   to
   
   CREATE VIEW
   
   so that you end up with something like this:
   

   CREATE VIEW `jos_docman_file_counts` AS select `jos_docman_documents`.`storage_path` AS `storage_path`,count(0) AS `count` from `jos_docman_documents` where (`jos_docman_documents`.`storage_type` = 'file') group by `jos_docman_documents`.`storage_path`
   	

Unknown storage engine 'MEMORY'

When publishing documents if you encounter the following error, it means that your database does not have the MEMORY engine enabled:

Unknown storage engine 'MEMORY'
.../libraries/vendor/joomla/database/src/Mysqli/MysqliStatement.php:435

The MEMORY engine is required by Joomla's com_finder component. You need to either enable it in your database or disable Joomla's com_finder component, for more information please see: https://forum.joomla.org/viewtopic.php?t=990519