-1

My ISP has updated the shared server which hosts my application. It involved an update from MySQL 5.6, to MariaDB 10.3. The ISP writes: MariaDB is largely compatible with MySQL and most web applications are written to work just fine with MariaDB. In other words, they don't suffer from the minimal differences.

However, since the update many of my EDIT and INSERT do not work anymore and produce a fatal error. In my search I found:

  • the problem is in writing to the database
  • seems to occur when not all fields have a value, while empty fields are allowed in my application

On my further investigation I came across: https://forums.cpanel.net/threads/mariadb-10-2-disabling-strict-mode.635157/ According to this thread I understand the solution might be in changing the sql mode in the server settings from:

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

to:

NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

I tried to change it through DirectAdmin/PHPAdmin variabes. Since it is a shared server I am not allowed to change the settings so I can not check (SUPER privilege needed).

I now have 3 questions:

  1. Would this be the solution? In which case I would ask my ISP to change this.
  2. Is this the best way to fix it?
  3. Can I solve this in another way?

Suggestions very much appreciated.

zef
  • 649
  • 1
  • 7
  • 22
  • 1
    If its a shared server, they are unlikely to change params like that as it would effect all other users of that database, there are likely hundreds of others using that same mariadb instance – RiggsFolly Feb 18 '21 at 00:05
  • 4
    Long term, the best solution would be to fix the queries that are causing the issues. Those checks are there to help protect against unintended consequences. – Nick Feb 18 '21 at 00:06
  • 1
    A short term fix would be changing the `sql_mode` in the connection to the server. The code changes to work with the default mode are likely to be fairly trivial to fix. Include the SQL error, the SQL statement, and the table structures `show create table {tablename}` and advice can be given on correcting the code/structure. – danblack Feb 18 '21 at 00:20
  • Please give examples of the tables and the problematic queries and also any error messages you receive. Without more info it's not really possible to answer your questions. – Wodin Feb 18 '21 at 00:39
  • The error produced is: Fatal error: Call to a member function getLabel() on null in .../edit.phtml on line ... If in my form all fields have a value is works, so that indicates the queries are fine. I tried adjusting the table structure to allow for null values but did not help. – zef Feb 18 '21 at 19:17
  • Show us the actual query, the actual data, the function involved, and the actual error message. We can't debug without details. – Rick James Mar 01 '21 at 22:40

1 Answers1

0

The problem is caused by writing empty values to the database. If in a form empty values are allowed it is saved as an empty string "". However, if the field in the database is of type INT or DOUBLE this causes the error. To resolve this the fields in the database tables need to be changed to type VARCHAR. Another option would be to change the queries to write empty strings to 0 values for INT, DOUBLE types. Changing the field types to VARCHAR is the easier solution.

zef
  • 649
  • 1
  • 7
  • 22