NextGEN does not work on WordPress 4.4 with SQLite Integration

@fgbmaster – Are there any errors messages being written to your server’s error_logs files that are related to NextGEN Gallery?

– Cais.

While trying to add a new gallery and upload one picture a database-error occurs:
Error while executing query! Error message was: SQLSTATE[23000]: Integrity constraint violation: 19 PRIMARY KEY must be unique

The Raw query: INSERT INTO wp_ngg_gallery (title, author, extras_post_id, gid, name, slug, path, pageid, previewpic) VALUES (‘test6’, ‘1’, ‘0’, ‘0’, ‘test6’, ‘test6’, ‘/wp-content/gallery/test6’, ‘0’, ‘0’)

The problem seems to be the 4th column with its value 0. Insertion succeeds without this column:
INSERT INTO wp_ngg_gallery (title, author, extras_post_id, name, slug, path, pageid, previewpic) VALUES (‘test6’, ‘1’, ‘0’, ‘test6’, ‘test6’, ‘/wp-content/gallery/test6’, ‘0’, ‘0’)

If i use Manage Galleries for adding a new gallery, no database error
occurs. The php-warning is described in my initial post.

Hope this helps a little bit.

@fgbmaster – Is the problem you are trying to describe that you cannot create a new gallery via Gallery > Add Gallery / Images?

There could be a plugin conflict going on here or at least it should be ruled out. Have you tried deactivating all of your plugins, clearing your browser cache (and site caches if you are using a caching plugin), then reactivating the NextGEN plugin?

If this fixes the problem then it was likely one of the deactivated plugins that was causing an issue. Now, retrace your steps activating each plugin and re-checking to see if the problem returns. If it does then that last activated plugin was probably the one. Keep reactivating and in most cases it is also best to keep checking after each plugin, sometimes there may be more than one plugin on a site that will create an issue. If you do find one (or more) plugins causing this problem we would greatly appreciate your feedback on them so the developers may address them as soon as they can.

Also to note, in rare cases it may be the theme itself that is causing a conflict so while you have just the NextGEN plugin activated you might consider temporarily switching to one of the default WordPress Twenty series themes and see if this corrects the issue as well.

If the problem still persists after all of this we would still want to know, even more so, as we will still want to sort out the issue and may need additional details.

Thanks!

– Cais.

My installed language is de_DE. Maybe therefore my description is opaque.
OK, I’ll try it again – switched language to English yet :-).

No plugin other except NextGEN Gallery by Photocrati is activated.
Drop-in db.php is active (sqlite integration)
Cache of localhost is cleared. No WordPress-Cache existing.
Theme Twentyfifteen is in use.

First trial
Going to [Gallery] – [Overview]
At a Glance: 0 Images – 5 Galleries – 0 Albums
Going to [Manage Galleries]
On the right: 5 Items
Next line: Notice: Undefined offset: 3 in /srv/www/htdocs/fa/wordpress/wp-admin/includes/class-wp-list-table.php on line 1050
Next line: the same notice
2nd line of the table: No entries found

Clicking the button [Add new gallery]
The dialog appears. New galley name: test 10 – [OK]
Message “Gallery ID 7 successfully created. You can show this gallery in your post or page with the shortcode [nggallery id=7].” appears. Record in the Database is really created.
Next line: [Edit gallery], 2nd line of the table: No entries found
Clicking [Edit gallery]
Message “Gallery not found.“

Second trial
Going to [Gallery/Images]
Input test 11 beside [Create a new gallery]
Clicking [Add Files] and selecting a jpg-file, the filename appears
Clicking [Start Upload]
Message: 0 images were uploaded

Clicking [Import from WordPress Media Library]
Input test 11 beside [Create a new gallery]
Clicking [Select Images] an select one images]
Clicking [Import 1 image]
Message: An unexpected error occured. This is most likely due to a server misconfiguration. Check your PHP error log or ask your hosting provider for assistance.
PHP error log: (I hope the message will be readable in this post)
WordPress database error <div style="clear:both"> </div><div class="queries" style="clear:both; margin_bottom:2px; border: red dotted thin;">Queries made or created this session were
\r\n\t

\r\n\t\t
1. Raw query:\nINSERT INTO wp_ngg_gallery (title, author, extras_post_id, gid, name, slug, path, pageid, previewpic) VALUES ('test 11', '1', '0', '0', 'test-11', 'test-11', '/wp-content/gallery/test-11', '0', '0')

\r\n\t\t

2. Rewritten:\nINSERT INTO wp_ngg_gallery (title, author, extras_post_id, gid, name, slug, path, pageid, previewpic) VALUES ('test 11', '1', '0', '0', 'test-11', 'test-11', '/wp-content/gallery/test-11', '0', '0')

\r\n\t\t

3. With Placeholders:\nINSERT INTO wp_ngg_gallery (title, author, extras_post_id, gid, name, slug, path, pageid, previewpic) VALUES ( :param_0 , :param_1 , :param_2 , :param_3 , :param_4 , :param_5 , :param_6 , :param_7 , :param_8 )

\r\n\t\t

4. Prepare:\nINSERT INTO wp_ngg_gallery (title, author, extras_post_id, gid, name, slug, path, pageid, previewpic) VALUES ( :param_0 , :param_1 , :param_2 , :param_3 , :param_4 , :param_5 , :param_6 , :param_7 , :param_8 )

\r\n\t\t

5. Executing:\narray (\n 0 => 'test 11',\n 1 => '1',\n 2 => '0',\n 3 => '0',\n 4 => 'test-11',\n 5 => 'test-11',\n 6 => '/wp-content/gallery/test-11',\n 7 => '0',\n 8 => '0',\n)

\r\n\t

\r\n</div><div style="clear:both; margin_bottom:2px; border: red dotted thin;" class="error_message" style="border-bottom:dotted blue thin;">Error occurred at line 692 in Function execute_query.
Error message was: Error while executing query! Error message was: SQLSTATE[23000]: Integrity constraint violation: 19 PRIMARY KEY must be unique </div>

#0  PDOEngine->get_error_message() called at [/srv/www/htdocs/fa/wordpress/wp-content/plugins/sqlite-integration/pdodb.class.php:255]\n#1  PDODB->query(INSERT INTO wp_ngg_gallery (title, author, extras_post_id, gid, name, slug, path, pageid, previewpic) VALUES ('test 11', '1', '0', '0', 'test-11', 'test-11', '/wp-content/gallery/test-11', '0', '0')) called at [/srv/www/htdocs/fa/wordpress/wp-includes/wp-db.php:1921]\n#2  wpdb->_insert_replace_helper(wp_ngg_gallery, Array ([title] => test 11,[author] => 1,[extras_post_id] => 0,[gid] => 0,[name] => test-11,[slug] => test-11,[path] => /wp-content/gallery/test-11,[pageid] => 0,[previewpic] => 0), , INSERT) called at [/srv/www/htdocs/fa/wordpress/wp-includes/wp-db.php:1842]\n#3  wpdb->insert(wp_ngg_gallery, Array ([title] => test 11,[author] => 1,[extras_post_id] => 0,[gid] => 0,[name] => test-11,[slug] => test-11,[path] => /wp-content/gallery/test-11,[pageid] => 0,[previewpic] => 0)) called at [/srv/www/htdocs/fa/wordpress/wp-content/plugins/nextgen-gallery/products/photocrati_nextgen/modules/datamapper/package.module.datamapper.php:939]\n#4  C_CustomTable_DataMapper_Driver_Mixin->_create(stdClass Object ([title] => test 11,[author] => 1,[extras_post_id] => 0,[gid] => 0,[name] => test-11,[slug] => test-11,[path] => /wp-content/gallery/test-11,[pageid] => 0,[previewpic] => 0))\n#5  ReflectionMethod->invokeArgs(C_CustomTable_DataMapper_Driver_Mixin Object ([object] => C_Gallery_Mapper Object ([_where_clauses] => Array ([0] => slug = 'test-11'),[_order_clauses] => Array (),[_group_by_columns] => Array ([0] => wp_ngg_gallery.gid),[_limit_clause] => LIMIT 1,[_select_clause] => SELECT DISTINCT gid,[_delete_clause] => ,[_use_cache] => 1,[_object_name] => ngg_gallery,[_model_factory_method] => gallery,[_columns] => Array ([gid] => Array ([type] => BIGINT,[default_value] => 0,[extra] => ),[name] => Array ([type] => VARCHAR(255),[default_value] => ,[extra] => ),[slug] => Array ([type] => VARCHAR(255,[default_value] => ,[extra] => ),[path] => Array ([type] => TEXT,[default_value] => ,[extra] => ),[title] => Array ([type] => TEXT,[default_value] => ,[extra] => ),[pageid] => Array ([type] => INT,[default_value] => 0,[extra] => ),[previewpic] => Array ([type] => INT,[default_value] => 0,[extra] => ),[author] => Array ([type] => INT,[default_value] => 0,[extra] => ),[extras_post_id] => Array ([type] => BIGINT,[default_value] => 0,[extra] => )),[_table_columns] => Array ([0] => gid,[1] => name,[2] => slug,[3] => path,[4] => title,[5] => galdesc,[6] => pageid,[7] => previewpic,[8] => author,[9] => extras_post_id),[_serialized_columns] => Array (),[context] => Array ([0] => ,[1] => gallery),[adapted] => 1,[_mixins] => Array ([Mixin_DataMapper_Driver_Base] => Mixin_DataMapper_Driver_Base Object ([object] => C_Gallery_Mapper Object ( *RECURSION*,[method_called] => ),[C_CustomTable_DataMapper_Driver_Mixin] => C_CustomTable_DataMapper_Driver_Mixin Object ( *RECURSION*,[Mixin_NextGen_Table_Extras] => Mixin_NextGen_Table_Extras Object ([object] => C_Gallery_Mapper Object ( *RECURSION*,[method_called] => ),[Mixin_Gallery_Mapper] => Mixin_Gallery_Mapper Object ([object] => C_Gallery_Mapper Object ( *RECURSION*,[method_called] => ),[A_I18N_Gallery_Translation] => A_I18N_Gallery_Translation Object ([object] => C_Gallery_Mapper Object ( *RECURSION*,[method_called] => )),[_mixin_priorities] => Array ([0] => A_I18N_Gallery_Translation,[1] => Mixin_Gallery_Mapper,[2] => Mixin_NextGen_Table_Extras,[3] => C_CustomTable_DataMapper_Driver_Mixin,[4] => Mixin_DataMapper_Driver_Base),[_method_map_cache] => Array ([migrate] => C_CustomTable_DataMapper_Driver_Mixin,[create] => Mixin_DataMapper_Driver_Base,[save] => Mixin_DataMapper_Driver_Base,[is_model] => Mixin_DataMapper_Driver_Base,[convert_to_model] => Mixin_DataMapper_Driver_Base,[select] => C_CustomTable_DataMapper_Driver_Mixin,[_init] => C_CustomTable_DataMapper_Driver_Mixin,[limit] => C_CustomTable_DataMapper_Driver_Mixin,[where_and] => Mixin_DataMapper_Driver_Base,[_where] => Mixin_DataMapper_Driver_Base,[_parse_where_clause] => Mixin_DataMapper_Driver_Base,[_add_where_clause] => C_CustomTable_DataMapper_Driver_Mixin,[run_query] => C_CustomTable_DataMapper_Driver_Mixin,[is_select_statement] => C_CustomTable_DataMapper_Driver_Mixin,[group_by] => C_CustomTable_DataMapper_Driver_Mixin,[_save_entity] => C_CustomTable_DataMapper_Driver_Mixin,[has_defined_column] => Mixin_DataMapper_Driver_Base,[_create] => C_CustomTable_DataMapper_Driver_Mixin,[_convert_to_table_data] => C_CustomTable_DataMapper_Driver_Mixin),[_disabled_map] => Array ([define_column] => Array (),[set_defaults] => Array (),[_convert_to_entity] => Array (),[get_generated_query] => Array (),[_save_entity] => Array ([0] => Mixin_Gallery_Mapper),[_create] => Array ([0] => Mixin_NextGen_Table_Extras)),[_interfaces] => Array ([0] => I_Component,[1] => I_DataMapper_Driver,[2] => I_CustomTable_DataMapper,[3] => I_Gallery_Mapper),[_throw_error] => 1,[_wrapped_instance] => ,[object] => C_Gallery_Mapper Object ( *RECURSION*,[_primary_key_column] => gid,[_custom_post_mapper] => C_CustomPost_DataMapper_Driver Object ([_query_args] => Array ([post_type] => ngg_gallery,[paged] => 1,[fields] => *,[post_status] => any,[datamapper] => 1,[posts_per_page] => 1,[is_select] => 1,[is_delete] => ,[post__in] => Array ([0] => 1658),[offset] => 0,[update_post_meta_cache] => ,[no_found_posts] => ),[_primary_key_column] => ID,[_use_cache] => 1,[_object_name] => ngg_gallery,[_model_factory_method] => extra_fields,[_columns] => Array (),[_table_columns] => Array ([0] => ID,[1] => post_author,[2] => post_date,[3] => post_date_gmt,[4] => post_content,[5] => post_title,[6] => post_excerpt,[7] => post_status,[8] => comment_status,[9] => ping_status,[10] => post_password,[11] => post_name,[12] => to_ping,[13] => pinged,[14] => post_modified,[15] => post_modified_gmt,[16] => post_content_filtered,[17] => post_parent,[18] => guid,[19] => menu_order,[20] => post_type,[21] => post_mime_type,[22] => comment_count),[_serialized_columns] => Array (),[context] => ,[adapted] => 1,[_mixins] => Array ([Mixin_DataMapper_Driver_Base] => Mixin_DataMapper_Driver_Base Object ([object] => C_CustomPost_DataMapper_Driver Object ( *RECURSION*,[method_called] => ),[Mixin_CustomPost_DataMapper_Driver] => Mixin_CustomPost_DataMapper_Driver Object ([object] => `

No database record -gallery or picture- is created.

That’s it.

In my humble opinion are there two different ways of sql statement creation. One is successful, see first trial (gallery). The other fails, because it contains gid=’0′ although gid is an autoincrement column. Maybe this is valid code in mysql, I don’t know…

Good luck for your search, if you need further information let me know what I can do.

FGBmaster

@fgbmaster – We are not aware of any particular issues with SQLite … can you provide any additional context for this detail:

Drop-in db.php is active (sqlite integration)

Thanks!

– Cais.

@photocrati – Here is the description of the file db.php:

* This file defines some constant required for SQLite Integration.
*
* This file must be placed in the directory wordpress/wp-content/db.php.
* WordPress loads this file automatically.
*
* @version 1.8.1
* @package SQLite Integration
* @author Kojima Toshiyasu

SQLite Integration is listed as WordPress plugin here.
The dashboard shows the SQLite Integration without enabled admin-plugin as <q>Drop-in</q>.

Until now i had no problems with SQLite Integration, so I’m not familiar with any details of that plugin.

FGBMaster

@fgbmaster – At a glance it appears the plugin is “translating” queries between MySQL and SQLite … have you checked with other plugin author for any insights into this issue?

Is there a specific need for your need to be written for SQLite versus the standard MySQL WordPress environment?

– Cais.

@photocrati – I think you’re right with your understanding of what SQLite integration does. SQLight integration is needed, because my WordPress site is running in an intranet without any mysql-installation. I have not checked this plugin because there were no problems until NextGEN Gallery was activated.

Two days ago i have posted the error message thrown by SQLite integration.
1. is the raw sql-string as it is given by NextGEN Gallery
2. is the <q>translated</q> string (here: no difference)
4. is the another way for the same purpose by preparing a statement and
5. execute it with the given values.

The insertion fails, because the raw-string contains the column gid and the corresponding value ‘0’ AND the database-column gid is set to PRIMARY KEY AND a record with gid=0 already exists.
This sql-string should not be valid in mysql too. Maybe mysql is more error-tolerant, but MySQL 5.7 Reference Manual says, you should not include the gid-column/value OR use gid and the keyword DEFAULT as value.

After all the final question must be: why fails SQLite integration in my second trial AND why is SQLite integration able to store an equivalent record in my first trial.

My read is there are different raw-strings given by NextGEN Gallery.

FGBmaster

@fgbmaster – We will likely still need more details about the SQLite plugin you are using, do you have a link to its public repository? One of our developer will likely have to debug this much more thoroughly as well.

Please send us a Bug Report (https://nextgen-gallery.com/report-bug/ … please reference this topic) so we can get a better look under the page at your site.

Please include as many details as you can about your site and the issue at hand so we can move on this as fast as possible.

Thanks!

– Cais.