tom

I am running this on the Flux Network (runonflux.io), it is a decentralized web3 network.

To keep the SQL DB synced across many nodes and facilitate new nodes coming up on demand they have a shared-db connector between the app (FA in our case, WP in first usage) and for exact reasons I do not know they intercept 'SET NAMES utf8' and translate it to utf8mb4

WP needs mysql 8+ and in mysql8 for backwards compatibility uses a specially encoded COMMENT so that earlier mysql versions will ignore the statement and that is what the connector looked for, in FA we just use the SET NAME command with no fanfare.

I did a lot of messing with my copy of the code before I found this, but have reverted to minimal changes as once I get everything working I'd like to get the changes pushed back to the official code.

I think I only 2 or maybe 3 changes that could be controlled by a global flag

They are:

db_query look for set name utf8 to reformat it to what the connector needs (Temporary while they fix their bug)

session.inc move SECURE_ONLY to a define in config.default.php also allow setting with Env Var

install/index.php allow setting DB settings from Env Vars

config.default.php - Flux specific - test db connection and if can not connect return 503 error to browser

That last one I can just append some lines to the file is the change is too specific to merge into master.

I am not going to submit anything until I have it all working, but it is looking good.

Tom

Also in the CoA sql/en_US-new.sql we have SET NAMES latin1; and it may be utf8 for other language CoAs.

Please list the changes you have made.

I'd explain but nobody would understand LOL

Tom

I'll report back when I have an update

Tom

Array
(
    [0] => Array
        (
            [0] => SET NAMES latin1

            [1] => 11
        )

)

So it saw the SET NAMES latin1;
but yet it sends utf8

Why is the default utf8, was that used prior to FA 2.3?

Found it!

Would there be any harm changing the default latin1?

2023-02-24 09:40:59 +0100 Joe
PHP 8.1 onwards db_set_charset needs second parameter not null. Fixed with default utf8.
M    admin/db/maintenance_db.inc

Tom

after
2024-12-29T17:07:18.634304Z        10 Query     SET foreign_key_checks=1
2024-12-29T17:07:18.634803Z        10 Query     SET NAMES utf8

See below - another mysql setting I am missing?

2024-12-29T17:07:18.204770Z         9 Execute   INSERT INTO backlog (seq, query, timestamp) VALUES (1066,'INSERT INTO `0_tax_group_items` VALUES\n(\'1\', \'1\', \'1\')',1735492038161)
2024-12-29T17:07:18.208097Z        10 Query     INSERT INTO `0_tax_group_items` VALUES
('1', '1', '1')
2024-12-29T17:07:18.294283Z         9 Execute   INSERT INTO backlog (seq, query, timestamp) VALUES (1067,'INSERT INTO `0_tax_groups` VALUES\n(\'1\', \'Tax\', \'0\'),\n(\'2\', \'Tax Exempt\', \'0\')',1735492038253)
2024-12-29T17:07:18.296397Z        10 Query     INSERT INTO `0_tax_groups` VALUES
('1', 'Tax', '0'),
('2', 'Tax Exempt', '0')
2024-12-29T17:07:18.379515Z         9 Execute   INSERT INTO backlog (seq, query, timestamp) VALUES (1068,'INSERT INTO `0_tax_types` VALUES\n(\'1\', \'5\', \'2150\', \'2150\', \'Tax\', \'0\')',1735492038338)
2024-12-29T17:07:18.382010Z        10 Query     INSERT INTO `0_tax_types` VALUES
('1', '5', '2150', '2150', 'Tax', '0')
2024-12-29T17:07:18.465588Z         9 Execute   INSERT INTO backlog (seq, query, timestamp) VALUES (1069,'INSERT INTO `0_users` VALUES\n(\'1\', \'admin\', \'5f4dcc3b5aa765d61d8327deb882cf99\', \'Administrator\', \'2\', \'\', \'adm@example.com\', \'C\', \'0\', \'0\', \'0\', \'0\', \'default\', \'Letter\', \'2\', \'2\', \'4\', \'1\', \'1\', \'0\', \'0\', \'2021-05-07 13:58:33\', \'10\', \'1\', \'1\', \'1\', \'1\', \'0\', \'orders\', \'30\', \'0\', \'1\', \'0\', \'0\', \'0\')',1735492038424)
2024-12-29T17:07:18.468261Z        10 Query     INSERT INTO `0_users` VALUES
('1', 'admin', '5f4dcc3b5aa765d61d8327deb882cf99', 'Administrator', '2', '', 'adm@example.com', 'C', '0', '0', '0', '0', 'default', 'Letter', '2', '2', '4', '1', '1', '0', '0', '2021-05-07 13:58:33', '10', '1', '1', '1', '1', '0', 'orders', '30', '0', '1', '0', '0', '0')
2024-12-29T17:07:18.551540Z         9 Execute   INSERT INTO backlog (seq, query, timestamp) VALUES (1070,'INSERT INTO `0_workcentres` VALUES\n(\'1\', \'Work Centre\', \'\', \'0\')',1735492038511)
2024-12-29T17:07:18.554467Z        10 Query     INSERT INTO `0_workcentres` VALUES
('1', 'Work Centre', '', '0')
2024-12-29T17:07:18.631815Z         9 Execute   INSERT INTO backlog (seq, query, timestamp) VALUES (1071,'SET foreign_key_checks=1',1735492038595)
2024-12-29T17:07:18.634304Z        10 Query     SET foreign_key_checks=1
2024-12-29T17:07:18.634803Z        10 Query     SET NAMES utf8
2024-12-29T17:07:18.714956Z         9 Execute   INSERT INTO backlog (seq, query, timestamp) VALUES (1072,'UPDATE 0_sys_prefs SET value = \'Flux Testing\' WHERE name=\'coy_name\'',1735492038674)

Also check the parameter for legacy dates in my.cnf

[mysqld]

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

ERROR 1067 (42000) at line 35: Invalid default value for 'tran_date'

The table is:

CREATE TABLE `0_attachments` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(60) NOT NULL DEFAULT '',
  `type_no` int(11) NOT NULL DEFAULT '0',
  `trans_no` int(11) NOT NULL DEFAULT '0',
  `unique_name` varchar(60) NOT NULL DEFAULT '',
  `tran_date` date NOT NULL DEFAULT '0000-00-00',
  `filename` varchar(60) NOT NULL DEFAULT '',
  `filesize` int(11) NOT NULL DEFAULT '0',
  `filetype` varchar(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `type_no` (`type_no`,`trans_no`)
) ENGINE=InnoDB;

I am using mysql:8.3.0 (docker image)
Maybe I need to use an older version. The docs did not recommend any specific mySQL version...

Thanks!
ps. I don't think I can use an older mysql due to some clustering that is being done.

I did see that the valid date range is : The supported range is '1000-01-01' to '9999-12-31'.

Will the default value of 1000-01-01 cause any SQL issues?

In the setup wizard I get the db connection setup ok, but when I import the COA with or without demo data I get

504 Gateway Timeout

any suggestions where to look?

Is the import data local or being pulled from an FA site?

Tom