I've been working locally on my new backdrop site using XAMPP ( PHP/7.2.21 and 10.4.6-MariaDB mysql 5.0.12)

The database is InnoDB utf8mb4_general_ci  and everything is working fine and so I exported the db using PHPMyAdmin and imported it to cpanel online also using PMA.

I had one message after import and that was: -------------- Error

SQL query:

--
-- Indexes for table `cache`
--
ALTER TABLE `cache`
  ADD PRIMARY KEY (`cid`),
  ADD KEY `expire` (`expire`)

MySQL said: Documentation

#1071 - Specified key was too long; max key length is 767 bytes --------------

I went to the website and was able to log in but when I tried to navigate to any page I get the following error and can't move beyond this.

SQLSTATE[HY000]: General error: 1364 Field 'item_id' doesn't have a default value: INSERT INTO {queue} (name, data, created) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2); Array ( [:db_insert_placeholder_0] => update_fetch_tasks [:db_insert_placeholder_1] => a:8:{s:4:"name";s:8:"backdrop";s:4:"info";a:6:{s:4:"name";s:18:"Administration Bar";s:7:"package";s:14:"Administration";s:7:"version";s:6:"1.14.1";s:7:"project";s:8:"backdrop";s:16:"_info_file_ctime";i:1573401003;s:9:"datestamp";i:0;}s:9:"datestamp";i:0;s:8:"includes";a:47:{s:9:"admin_bar";s:18:"Administration Bar";s:5:"block";s:5:"Block";s:8:"ckeditor";s:8:"CKEditor";s:5:"color";s:5:"Color";s:6:"config";s:21:"Configuration Manager";s:10:"contextual";s:16:"Contextual Links";s:9:"dashboard";s:9:"Dashboard";s:4:"date";s:4:"Date";s:5:"dblog";s:16:"Database Logging";s:5:"email";s:5:"Email";s:6:"entity";s:6:"Entity";s:5:"field";s:5:"Field";s:17:"field_sql_storage";s:17:"Field SQL Storage";s:8:"field_ui";s:8:"Field UI";s:4:"file";s:4:"File";s:6:"filter";s:6:"Filter";s:5:"image";s:5:"Image";s:6:"layout";s:6:"Layout";s:4:"link";s:4:"Link";s:4:"list";s:4:"List";s:4:"menu";s:4:"Menu";s:4:"node";s:4:"Node";s:6:"number";s:6:"Number";s:7:"options";s:7:"Options";s:4:"path";s:4:"Path";s:8:"redirect";s:8:"Redirect";s:6:"search";s:6:"Search";s:6:"syslog";s:14:"System Logging";s:6:"system";s:6:"System";s:8:"taxonomy";s:8:"Taxonomy";s:4:"text";s:4:"Text";s:6:"update";s:14:"Update Manager";s:4:"user";s:4:"User";s:5:"views";s:5:"Views";s:8:"views_ui";s:8:"Views UI";s:5:"basis";s:5:"Basis";s:5:"seven";s:5:"Seven";s:6:"boxton";s:6:"boxton";s:5:"geary";s:5:"geary";s:6:"harris";s:6:"harris";s:7:"moscone";s:7:"moscone";s:15:"moscone_flipped";s:15:"moscone_flipped";s:5:"rolph";s:5:"rolph";s:7:"simmons";s:7:"simmons";s:5:"sutro";s:5:"sutro";s:6:"taylor";s:6:"taylor";s:14:"taylor_flipped";s:14:"taylor_flipped";}s:12:"project_type";s:4:"core";s:14:"project_status";b:1;s:10:"sub_themes";a:0:{}s:11:"base_themes";a:0:{}} [:db_insert_placeholder_2] => 1573407081 )

I don't know this message is telling me and have no idea how to proceed to a fix.  It seems like all modules and themes are affected by this error.

I did comment out **$database_charset = 'utf8mb4';**/ which fixed one error but the General error: 1364 Field 'item_id' was not.

Where is Field 'item_id' and how can I insert a default value?

 

Comments

klonos's picture

Hello ,

It seems that there may be some database mismatch between your local and the server you are trying to import the db to. Can you please share the server specs (php, MySQL, webserver versions)?

The message you got also points to some upgrade issues in d.org, but at this point I am not able to pinpoint it to something specific.

This sounds related: https://drupal.stackexchange.com/questions/241771/sql-sync-issues-with-key-length

Hi, thanks for reply.

Here's the server specs:

REMOTE Database server     Server: Localhost via UNIX socket     Server type: MariaDB     Server version: 10.1.43-MariaDB - MariaDB Server     Protocol version: 10     Server charset: UTF-8 Unicode (utf8)

Web server     cpsrvd 11.82.0.17     Database client version: libmysql - 5.6.43     PHP extension: mysqli curl mbstring     PHP version: 7.2.7

default storage engine: MyISAM default tmp storage engine: enforce storage engine storage engine: MyISAM innodb_file_per_table=ON innodb_file_format=antelope innodb_large_prefix=OFF

LOCAL (XAMPP) Database server     Server type: MariaDB     Server version: 10.4.6-MariaDB - mariadb.org binary distribution     Protocol version: 10     Server charset: UTF-8 Unicode (utf8)

Web server     Apache/2.4.39 (Win64) OpenSSL/1.1.1c PHP/7.2.21     Database client version: libmysql - mysqlnd 5.0.12-dev     PHP extension: mysqli     PHP version: 7.2.21

default storage engine: InnoDB default tmp storage engine: enforce storage engine gtid pos auto engines storage engine: InnoDB innodb_file_per_table=ON innodb_file_format=antelope innodb_large_prefix=OFF ----

I checked another cPanel remote server that I use with a two working Drupal 8 installations and it is similar to the remote config above except uses MySQL 5.6.45 - MySQL Community Server and the following:

default storage engine: MyISAM default tmp storage engine:: InnoDB storage engine: MyISAM innodb_file_per_table=ON innodb_file_format=antelope innodb_large_prefix=OFF

 

I've restarted with a clean install of BD on the remote server and it seems to be functioning. I'm enabling modules and it looks like there was some corruption on the local database as I'm seeing differences in the admin interface between the two.

What is best way to export nodes, taxonomy and layouts from the local to the remote? I don't want any of the bad database structure migrated.

I've not used the Feeds module and wonder if that would be the route to take? The site is fairly small but I did create several custom flexible layouts/templates and blocks along with custom css which I hope I wont have to recreate.

thanks