Need help with MySQL setup for 1.9.6 - invalid upc_date_update


#1

Ive been trying use Razuna with the MySQL database rather than the H2. All the connections are fine - it just wont go past the finalize screen (bars keep moving).

The error I’m getting in the log file is: Query Error: Invalid default value for ‘upc_date_update’

I’m using MySQL 5.7

Any help would be much appreciated!


#2

I had the exact same issue. I ended up removing some code from the file that contained that variable. You will need to get into the OpenBD error log to determine which file it is (unfortunately, I don’t remember the file name). Once that is variable is gone, the finalize will complete. DISCLAIMER: I am not a programmer, but I stayed at a Holiday Inn Express. That’s my funny way of saying that I am not afraid to try things like that and see what happens. You may not be as brave as I am, and I don’t know what the long-term consequences are of just removing lines of code. So far, I have been able to work through the issues and our Razuna system is running okay. * knocks on huge piece of wood * I’m running Razuna 1.9.6 on a Mac with MySQL 5.7.25


#3

Thanks - I’ll have a look through the code and see if I can figure it out. Will post here if I do.


#4

I had the same problem and solved it with your hint.
Thank you!


#5

I found out the cause of this issue. If you’re running MySQL Server version 5.7.8 or above, the default behavior in SQL_MODE was changed and interrupts the query in question:

CREATE TABLE razuna.raz1_upc_template ( upc_temp_id varchar(100), upc_date_create timestamp, upc_date_update timestamp, upc_who varchar(100), upc_active varchar(1) DEFAULT '0', host_id int, upc_name varchar(200), upc_description varchar(2000), PRIMARY KEY (upc_temp_id), key raz1_upc_t_upc_active (upc_active), key raz1_upc_t_host_id (host_id) ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT=DYNAMIC

If you look at the server variable SQL_MODE, you’ll see:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

The two modes NO_ZERO_IN_DATE and NO_ZERO_DATE are stopping the query and giving us the error (Error #1067, according to my SQL client).

To compensate for Razuna’s SQL, I added the following line to my mysqld.cnf file (or alternatively, the [mysqld] section in my.cnf, depending on which distribution you’re using):

sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

The installation/upgrade went smooth after that.

References:


https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_sql-mode


#6

Just wanted to add that you will need to remove another directive, ONLY_FULL_GROUP_BY.

Apparently this conflicts with the folder tree query, producing the following error message:

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘razuna.fn.FOLDER_NAME’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Dropping the mode from SQL_MODE allowed the query to resume.