MySQL Database issues after server upgrade (to PHP 7)

I have updated my Ubuntu server to 16.04 which also brought PHP 7. After fixing most migration issues, I now see that various issues with my (uptodate) Phorum installations occur:

I can no longer register as a new user. Error message:

Quote

Phorum Database Error
Sorry, a Phorum database error occurred.
The error message has been written
to the phorum-sql-errors.log error log.
Please try again later!

I don't see any phorum-sql-errors.log on my server. Where do I have to define its path? I see other phorum apache error logs, but they only state that no error.php file can be found inside my phorum folder.

When logged in as an admin, a click on "Database Integrity" shows ... nothing.

I wonder what I have overseen. Any help is really appreciated.

Regards, Ulf Dunkel

in general settings you have "Database error handling" to define whether to write to a file or show on screen.
By default its written to the phorum-sql-errors.log in the cache folder.

---

Thomas Seifert

Thank you for pointing me at this, Thomas. I switched the setting to "Errors will be emailed to the system email address" and could see the issue. Here it is:

A database error occured in your Phorum installation.

Error message:
--------------

Field 'password' doesn't have a default value (1364): INSERT INTO P5_iC_users
                (username, signature, moderator_data, settings_data)
         VALUES ('TESTman', '', '', '')

Backtrace:
----------

Function phorum_database_error called at
{path to Phorum}/include/db/mysql/mysqli.php:212
----
Function phorum_db_interact called at
{path to Phorum}/include/db/mysql.php:3725
----
Function phorum_db_user_add called at
{path to Phorum}/include/api/user.php:722
----
Function phorum_api_user_save called at
{path to Phorum}/register.php:231
----

Of course the registration form was completely filled in. What do I have to do to fix this issue?

Regards, Ulf Dunkel

Hi Ulf,

other errors oder warnings in the Apache log file?

Regards
Oliver

---

Using Phorum since 7/2000: forum.langzeittest.de (actual version 5.2.23)
Modules "Made in Germany" for version 5.2: Author_as_Sender, CarCost, Close_Topic, Conceal_Message_Timestamp,
Format_Email, Index_Structure, Mailing_List, Pervasive_Forum, Spritmonitor, Terms_of_Service and German_Language_Files_Package.

No, always the same error:

script '/[...]/phorum/error.php' not found or unable to stat

Regards, Ulf Dunkel

Short conclusion for everybody:

After some debugging together with Ulf I found that every default value '' clause (empty string default value) was missing in the table definitions. We weren't able to find a reason for this behavior. The table definitions were already incomplete before the update.

The MySQL update added the SQL Mode Option STRICT_TRANS_TABLE so the incomplete table definitions became noticeable.

Regards
Oliver

---

Using Phorum since 7/2000: forum.langzeittest.de (actual version 5.2.23)
Modules "Made in Germany" for version 5.2: Author_as_Sender, CarCost, Close_Topic, Conceal_Message_Timestamp,
Format_Email, Index_Structure, Mailing_List, Pervasive_Forum, Spritmonitor, Terms_of_Service and German_Language_Files_Package.

Here is another issue of this kind - can you please give me some advice what to do in order to fix it?

A database error occured in your Phorum installation.

Error message:
--------------

Unknown column 'moderator_data' in 'field list' (1054): INSERT INTO P5_iC_users
                (username, signature, moderator_data, settings_data)
         VALUES ('134998', '', '', '')

Backtrace:
----------

Function phorum_database_error called at
{path to Phorum}/include/db/mysql/mysqli.php:212
----
Function phorum_db_interact called at
{path to Phorum}/include/db/mysql.php:3725
----
Function phorum_db_user_add called at
{path to Phorum}/include/api/user.php:722
----
Function phorum_api_user_save called at
{path to Phorum}/register.php:237
----

Regards, Ulf Dunkel

Hi Ulf,

that's some different kind of error... Column moderator_data is missing... But why?

The users table should be defined like this:

[code="sql"]
CREATE TABLE `P5_iC_users` (
`user_id` int(10) UNSIGNED NOT NULL,
`username` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`real_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`display_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`password` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`password_temp` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`sessid_lt` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`sessid_st` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`sessid_st_timeout` int(10) UNSIGNED NOT NULL DEFAULT '0',
`email` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`email_temp` varchar(110) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`hide_email` tinyint(1) NOT NULL DEFAULT '0',
`active` tinyint(1) NOT NULL DEFAULT '0',
`signature` text COLLATE utf8_unicode_ci NOT NULL,
`threaded_list` tinyint(1) NOT NULL DEFAULT '0',
`posts` int(10) NOT NULL DEFAULT '0',
`admin` tinyint(1) NOT NULL DEFAULT '0',
`threaded_read` tinyint(1) NOT NULL DEFAULT '0',
`date_added` int(10) UNSIGNED NOT NULL DEFAULT '0',
`date_last_active` int(10) UNSIGNED NOT NULL DEFAULT '0',
`last_active_forum` int(10) UNSIGNED NOT NULL DEFAULT '0',
`hide_activity` tinyint(1) NOT NULL DEFAULT '0',
`show_signature` tinyint(1) NOT NULL DEFAULT '0',
`email_notify` tinyint(1) NOT NULL DEFAULT '0',
`pm_email_notify` tinyint(1) NOT NULL DEFAULT '1',
`tz_offset` float(4,2) NOT NULL DEFAULT '-99.00',
`is_dst` tinyint(1) NOT NULL DEFAULT '0',
`user_language` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`user_template` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`moderator_data` text COLLATE utf8_unicode_ci NOT NULL,
`moderation_email` tinyint(1) NOT NULL DEFAULT '1',
`settings_data` mediumtext COLLATE utf8_unicode_ci NOT NULL,
`force_password_change` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
[/code]

The column was introduced in 2005, see /include/db/upgrade/mysql/2005070500.php:

[code="php"]
$upgrade_queries[]="alter table {$PHORUM['user_table']} add column moderator_data text not null";
[/code]

Regards
Oliver

---

Using Phorum since 7/2000: forum.langzeittest.de (actual version 5.2.23)
Modules "Made in Germany" for version 5.2: Author_as_Sender, CarCost, Close_Topic, Conceal_Message_Timestamp,
Format_Email, Index_Structure, Mailing_List, Pervasive_Forum, Spritmonitor, Terms_of_Service and German_Language_Files_Package.

Thank you for that listing, Oliver.

My one looks like this:
[code="sql"]-- phpMyAdmin SQL Dump
-- version 4.5.4.1deb2ubuntu2
-- [www.phpmyadmin.net]
--
-- Host: localhost
-- Erstellungszeit: 25. Okt 2016 um 15:34
-- Server-Version: 5.7.13-0ubuntu0.16.04.2
-- PHP-Version: 7.0.8-0ubuntu0.16.04.2

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Datenbank: `P5_iCalamus`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `P5_iC_users`
--

CREATE TABLE `P5_iC_users` (
`user_id` int(10) UNSIGNED NOT NULL,
`username` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
`password_temp` varchar(50) NOT NULL,
`email` varchar(100) NOT NULL,
`email_temp` varchar(110) NOT NULL,
`hide_email` tinyint(1) NOT NULL DEFAULT '0',
`active` tinyint(1) NOT NULL DEFAULT '0',
`signature` text NOT NULL,
`threaded_list` tinyint(4) NOT NULL DEFAULT '0',
`posts` int(10) NOT NULL DEFAULT '0',
`admin` tinyint(1) NOT NULL DEFAULT '0',
`threaded_read` tinyint(4) NOT NULL DEFAULT '0',
`date_added` int(10) UNSIGNED NOT NULL DEFAULT '0',
`date_last_active` int(10) UNSIGNED NOT NULL DEFAULT '0',
`last_active_forum` int(10) UNSIGNED NOT NULL DEFAULT '0',
`hide_activity` tinyint(1) NOT NULL DEFAULT '0',
`show_signature` tinyint(1) NOT NULL DEFAULT '0',
`email_notify` tinyint(1) NOT NULL DEFAULT '0',
`tz_offset` float(4,2) NOT NULL DEFAULT '-99.00',
`is_dst` tinyint(1) NOT NULL DEFAULT '0',
`user_language` varchar(100) NOT NULL,
`user_template` varchar(100) NOT NULL,
`sessid_st` varchar(50) NOT NULL,
`pm_email_notify` tinyint(1) NOT NULL DEFAULT '1',
`sessid_lt` varchar(50) NOT NULL,
`sessid_st_timeout` int(10) UNSIGNED NOT NULL DEFAULT '0',
`moderation_email` tinyint(2) UNSIGNED NOT NULL DEFAULT '1',
`settings_data` mediumtext NOT NULL,
`real_name` varchar(255) NOT NULL,
`display_name` varchar(255) NOT NULL,
`pm_new_count` int(10) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Indizes der exportierten Tabellen
--

--
-- Indizes für die Tabelle `P5_iC_users`
--
ALTER TABLE `P5_iC_users`
ADD PRIMARY KEY (`user_id`),
ADD UNIQUE KEY `username` (`username`),
ADD KEY `userpass` (`username`,`password`),
ADD KEY `activity` (`date_last_active`,`hide_activity`,`last_active_forum`),
ADD KEY `date_added` (`date_added`),
ADD KEY `email_temp` (`email_temp`),
ADD KEY `sessid_st` (`sessid_st`),
ADD KEY `cookie_sessid_lt` (`sessid_lt`),
ADD KEY `real_name` (`real_name`),
ADD KEY `admin` (`admin`),
ADD KEY `active` (`active`,`admin`);

--
-- AUTO_INCREMENT für exportierte Tabellen
--

--
-- AUTO_INCREMENT für Tabelle `P5_iC_users`
--
ALTER TABLE `P5_iC_users`
MODIFY `user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;[/code]

What should I do to update my table?

I now have tried this:
[code="sql"]ALTER TABLE `P5_iC_users` ADD COLUMN `moderator_data` text COLLATE utf8_unicode_ci NOT NULL;[/code]

Seems to work fine.

Regards, Ulf Dunkel



Edited 2 time(s). Last edit at 10/25/2016 08:43AM by Ulf Dunkel.

Ulf,

Out of curiosity:
Check an older backup(s), see if the missing column is there.

Hi Scott,
thank you for your question. I checked my last two backups. The column wasn't there.

In another Phorum installation (on calamus.net), everything is fine.

Regards, Ulf Dunkel

Dear all,

Can you help on GitHub?

I have created a new ticket to have a new team, to have PHP support improvements, etc.:
- [github.com]

There are two PRs:
- [github.com]
- [github.com]

captcha in GitHub tickets:
- [github.com]

I have created a ticket to report all vulnerabilities and security flaws:
- [github.com]

Thanks in advance.