#208 [INSTALLER] Change required MySQL database character set variable for shared web hosting

Changing character_set_server requires root permission and rebooting server.

It is impossible on shared web hosting service.

So use character_set_database. This variable is can be changed by user permission with ALTER DATABASE.

character_set_database is assigned by character_set_server value when new database is created.

I think GNU social do not required server configuration. It is over permission. It is enough only database configuration.

In PostgreSQL, SHOW SERVER_ENCODING seems to be database encoding (not global server configuration). I checked on my local PostgreSQL.

My shared web hosting service is CORESERVER (MySQL 5.7 and PHP 7.3).

In today, I installed new GNU social service for GNU social development on https://gnusocial.senooken.jp/senooken.

On CORESERVER's global MySQL, character_set_server is utf8 and this cannot not be changed by user. So I fixed this issue.

Merged as c2508f8fa2

But in aed2344bd4 I replaced it with information from INFORMATION_SCHEMA.SCHEMATA).

In PostgreSQL, SHOW SERVER_ENCODING seems to be database encoding (not global server configuration). I checked on my local PostgreSQL.

Yes, that's right.

@XRevan86. Did you check this commit?

I think INFORMATION_SCHEMA.SCHEMATA refer to character_set_server. Not character_set_database.

I checked SELECT DEFAULT_CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.SCHEMATA; by phpnyadmin. This returned utf8 (not database encoding utf8mb4).

I will check this commit later on my server and I will report result here.

Did you check this commit?

@senooken, yes, I have. It didn't react to the ALTER DATABASE, so I went with the more robust, in my opinion, option.

I think INFORMATION_SCHEMA.SCHEMATA refer to character_set_server. Not character_set_database.

No, definitely not. It lists all the databases individually after all.

I checked SELECT DEFAULT_CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.SCHEMATA; by phpmyadmin. This returned utf8 (not database encoding utf8mb4).

There will be more than one database there. If it's the GNU social's one, then this really should do the trick:

ALTER DATABASE `gnusocial` DEFAULT CHARACTER SET 'utf8mb4' DEFAULT COLLATE 'utf8mb4_bin';

Surely, I run same as your SQL ALTER DATABASEgnusocialDEFAULT CHARACTER SET 'utf8mb4' DEFAULT COLLATE 'utf8mb4_bin';. On my commit, it is OK.

This changes character_set_database only.

I will check your commit on my server later. Please wait some hours. I have some jobs today.

Hi. I checked [acdff77176]. It is OK for encoding check.

But there is another database problem during installation...

Install GNU social

Page notice

        Initializing...
        Starting installation...
        Checking database...
        Creating database tables...
        Database error: MDB2 Error: invalid

Here is log from $res variable in lib/database/schema.php:runSqlSet().

class MDB2_Error { public $error_message_prefix = ''; public $mode = 1; public $level = 1024; public $code = -8;
  public $message = 'MDB2 Error: invalid'; 
  public $userinfo = '_doQuery: [Error message: Could not execute statement]\n[Last executed query: CREATE TABLE `config` (\nsection varchar(32) COLLATE utf8mb4_bin default NULL NOT NULL COMMENT \'configuration section\',\nsetting varchar(32) COLLATE utf8mb4_bin default NULL NOT NULL COMMENT \'configuration setting\',\nvalue text COLLATE utf8mb4_bin NULL COMMENT \'configuration value\',\nPRIMARY KEY (`section`,`setting`)\n) ENGINE \'InnoDB\' DEFAULT CHARACTER SET \'utf8mb4\' DEFAULT COLLATE \'utf8mb4_bin\']\n[Native code: 1067]\n[Native message: Invalid default value for \'section\']\n'; 

It seems default value for section column is invalid...

Does 06dfd91a82 fix your issue?

Yes. I checked bf12e3645f. Well done! Thanks!

Also I will be waiting #206. I use that patch manually now.