CreateTable() silently fails when specifying default charset

[dalers]

Help please :-)

I created a new table in a db update script using the following snippet:

// 4. ADD TABLE "stockfils" STOCK ITEM RELATED FILE/URL TABLE
//    - for file name with path OR URL
//    - equivalent to P&V FIL table
//    - Parts&Vendors allows ?? char TODO confirm max number of characters
//    - has foreign key constraint to stockmaster.stockid

// CreateTable($Table, $SQL)
CreateTable('stockfils', 'CREATE TABLE `stockfils` (
  `filid` INTEGER NOT NULL AUTO_INCREMENT, 
  `filstockid` VARCHAR(64),
  `filfilepath` VARCHAR(255), 
  `filefilename` VARCHAR(255), 
  `filview` TINYINT(1) DEFAULT 0, 
  `filnotes` VARCHAR(50), 
  INDEX (`filid`, `filstockid`), 
  INDEX (`filstockid`), 
  PRIMARY KEY (`filid`)
)');

and the table is created as expected:

I then tried creating a foreign key constraint from stockfils.filstockid to stockmaster.stockid:

// AddConstraint($Table, $Constraint, $Field, $ReferenceTable, $ReferenceField)
AddConstraint('stockfils', 'stockfils_ibfk_1', 'filstockid', 'stockmaster', 'stockid');

but when Z_UpgradeDatabase.php runs it reports:

Warning: mysqli_query(): (HY000/1822): Failed to add the foreign key constraint. Missing index for constraint 'stockfils_ibfk_1' in the referenced table 'stockmaster' in C:\xampp\htdocs\weberp\includes\ConnectDB_mariadb.php on line 63

The constraint stockfils_ibfk_1 could not be added
ALTER TABLE stockfils ADD CONSTRAINT stockfils_ibfk_1 FOREIGN KEY (filstockid) REFERENCES stockmaster (stockid)

Attempting to execute the equivalent (IIUC) SQL query:

ALTER TABLE stockfilsADD CONSTRAINTstockfils_ibfk_1 FOREIGN KEY (filstockid) REFERENCES stockmaster(stockid) ON DELETE RESTRICT ON UPDATE RESTRICT;

directly in phpMyAdmin results in the error:

Error creating foreign key on filstockid (check data types)

I'm confused because both stockfils.filestockid and stockmaster.stockid are VARCHAR(64). Can anyone point me in the right direction to resolve this?

Fwiw, I noticed the collation for stockfils.filstockid is utf8_general_ci (see above screenshot), but the collation for stockmaster.stockid is utf8mb4_general_ci.

[timschofield]

I think your problem is that your new table is using is using a different charset to stockmaster. All the tables should use utf8mb4 as the charset

Tim

[dalers]

Thanks Tim, I had wondered about that. How would I specify utf8mb4_general_ci in the CreateTable() call?

[timschofield]

CREATE TABLE stockfils (
filid INTEGER NOT NULL AUTO_INCREMENT,
filstockid VARCHAR(64),
filfilepath VARCHAR(255),
filefilename VARCHAR(255),
filview TINYINT(1) DEFAULT 0,
filnotes VARCHAR(50),
INDEX (filid, filstockid),
INDEX (filstockid),
PRIMARY KEY (filid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

should do it.

[dalers]

Thanks Tim, I updated my call to CreateTable() as you proposed (IIUC) but now the table creation fails apparently due to:

Warning: mysqli_query(): (HY000/1302): Conflicting declarations: 'CHARACTER SET utf8mb4' and 'CHARACTER SET utf8' in C:\xampp\htdocs\weberp\includes\ConnectDB_mariadb.php on line 63

Any ideas?

Revised CreateTable() call:

CreateTable('stockfils', 'CREATE TABLE `stockfils` (
  `filid` INTEGER NOT NULL AUTO_INCREMENT, 
  `filstockid` VARCHAR(64),
  `filfilepath` VARCHAR(255), 
  `filefilename` VARCHAR(255), 
  `filview` TINYINT(1) DEFAULT 0, 
  `filnotes` VARCHAR(50), 
  INDEX (`filid`, `filstockid`), 
  INDEX (`filstockid`), 
  PRIMARY KEY (`filid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');

[dalers]

The table is created correctly using the same SQL query directly (also the fk constraint). I did not test whether the AddConstraint() worked correctly after the table was created .

$SQL = "CREATE TABLE stockfils (
  filid INTEGER NOT NULL AUTO_INCREMENT,
  filstockid VARCHAR(64),
  filfilepath VARCHAR(255),
  filefilename VARCHAR(255),
  filview TINYINT(1) DEFAULT 0,
  filnotes VARCHAR(50),
  INDEX (filid, filstockid),
  INDEX (filstockid),
  PRIMARY KEY (filid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
$ErrMsg = __('Error creating stockfils table');
$Result = DB_query($SQL, $ErrMsg);

$SQL = "ALTER TABLE stockfils ADD CONSTRAINT stockfils_ibfk_1 FOREIGN KEY (filstockid) REFERENCES stockmaster(stockid) ON DELETE RESTRICT ON UPDATE RESTRICT";
$ErrMsg = __('Error creating stockfils_ibfk_1 fk constraint');
$Result = DB_query($SQL, $ErrMsg);

[dalers]

P.S. the CreateTable() call in comment #853 (comment) silently fails if the backticks are removed.

[pakricard]

I seem to remember some similar issue (but documentation is not my strong point).

Have you checked the default collation used at the server?

Maybe https://dev.mysql.com/doc/refman/8.0/en/charset-connection.html can point you in the right direction

R

[timschofield]

@pakricard is right, the best solution is just to change the server default, unless you have a major reason for eaving it as is.

Tim

[dalers]

Thanks @pakricard for the suggestion, IIUC the server default charset is utf8mb4.

I assume there is some reason why using CreateTable() without specifying a CHARSET results in text columns having utf8_general_ci collation instead of utf8mb4_general_ci. I also suspect @timschofield was on the right path suggesting to include "DEFAULT CHARSET=utf8mb4" in the CreateTable() call but some issue in the CreateTable() implementation is preventing correct execution (as well as not reporting the query failed) since using the same SQL directly with DB_query() does work.

Fwiw, the db dump I've been using (generated by phpMyAdmin) specifies utf8mb4 in the header:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

I'm also thinking this issue should be renamed to "CreateTable() silently fails when specifying default charset" now that the cause of the issue as originally posted has been proven to be the columns having different character sets (utf8mb4 vs utf8).

[timschofield]

Thanks @pakricard for the suggestion, IIUC the server default charset is utf8mb4.

The default character set is whatever you set the server up as.

I have added an extra parameter to the CreateTable() function fot character set. It should default to using utf8mb4 if the parameter is not set.

Thanks
Tim

[dalers]

The default character set is whatever you set the server up as.

Per my screenshot, phpMyAdmin indicates the default charset the server was set up as is utf8mb4 (iiuc of course).

I have added an extra parameter to the CreateTable() function fot character set. It should default to using utf8mb4 if the parameter is not set.

Thanks, I will test the next opportunity.