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