Clean install of r756 on CentOS 7.3, php 7.1

[mbmcclelland]

After doing a clean install, getting generic php and vhosts working installing projectsend and chowning and chmoding permissions correctly for it... I get to the start page, and upon filling out the form and clicking "install" I get the following:

The text of this is below, it seems to have issues with the database which was created with the following mysql commands:

CREATE DATABASE projectsenddb;
GRANT ALL PRIVILEGES ON projectsenddb.* TO 'projectsenduser'@'localhost' IDENTIFIED BY 'your-password' WITH GRANT OPTION;
FLUSH PRIVILEGES;
quit

Is this a known issue? Am I using to fresh a version? Thanks for any assistance.

Text of attached screenshot

There seems to be an error. Please try again.
PDOException: SQLSTATE[HY000]: General error: 1293 Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause in /var/www/html/projectsend.domainnamehere.com/install/index.php:31 Stack trace: #0 /var/www/html/projectsend.domainnamehere.com/install/index.php(31): PDOStatement->execute(Array) #1 /var/www/html/projectsend.domainnamehere.com/install/index.php(108): try_query(Array) #2 {main}
PDOException: SQLSTATE[HY000]: General error: 1005 Can't create table 'projectsenddb.tbl_files_relations' (errno: 150) in /var/www/html/projectsend.domainnamehere.com/install/index.php:31 Stack trace: #0 /var/www/html/projectsend.domainnamehere.com/install/index.php(31): PDOStatement->execute(Array) #1 /var/www/html/projectsend.domainnamehere.com/install/index.php(108): try_query(Array) #2 {main}
PDOException: SQLSTATE[HY000]: General error: 1005 Can't create table 'projectsenddb.tbl_notifications' (errno: 150) in /var/www/html/projectsend.domainnamehere.com/install/index.php:31 Stack trace: #0 /var/www/html/projectsend.domainnamehere.com/install/index.php(31): PDOStatement->execute(Array) #1 /var/www/html/projectsend.domainnamehere.com/install/index.php(108): try_query(Array) #2 {main}
PDOException: SQLSTATE[HY000]: General error: 1005 Can't create table 'projectsenddb.tbl_downloads' (errno: 150) in /var/www/html/projectsend.domainnamehere.com/install/index.php:31 Stack trace: #0 /var/www/html/projectsend.domainnamehere.com/install/index.php(31): PDOStatement->execute(Array) #1 /var/www/html/projectsend.domainnamehere.com/install/index.php(108): try_query(Array) #2 {main}
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY' in /var/www/html/projectsend.domainnamehere.com/install/index.php:31 Stack trace: #0 /var/www/html/projectsend.domainnamehere.com/install/index.php(31): PDOStatement->execute(Array) #1 /var/www/html/projectsend.domainnamehere.com/install/index.php(108): try_query(Array) #2 {main}
PDOException: SQLSTATE[HY000]: General error: 1005 Can't create table 'projectsenddb.tbl_categories_relations' (errno: 150) in /var/www/html/projectsend.domainnamehere.com/install/index.php:31 Stack trace: #0 /var/www/html/projectsend.domainnamehere.com/install/index.php(31): PDOStatement->execute(Array) #1 /var/www/html/projectsend.domainnamehere.com/install/index.php(108): try_query(Array) #2 {main}

[andyshilton]

@mbmcclelland I think this maybe related to the issues myself and others are having which is to do with the mode MySQL is running in. Have a look at the comments on my ticket #319
I think if you change your server's SQL Mode and try installing again, you won't have the issues above. Ideally the code needs changing to support the newer default modes of MySQL, but in the meantime it might help you out.

[mbmcclelland]

Thanks for the reply, unfortunately setting the sql_mode didn't correct the issue, though I think you are on the right track. I ran

MariaDB [(none)]> SET SESSION sql_mode = '';

and also added

[mysqld]
sql_mode = '' 

to my /etc/my.cnf

I am running Server version: 5.5.52-MariaDB MariaDB Server, what version mysql are you running?

[andyshilton]

I can successfully install, out the box, on MySQL 5.4, but 5.5, 5.6 and 5.7 require changes to the server config. I've not tried on MariaDB.

Add to to your findings above though, I also found that adding lines to the /etc/my.cnf had no effect. I had to find the lines in the files in the conf.d directory and update them there. I'm not sure why but in my.cnf the lines had no effect.

[mbmcclelland]

Can you tell me what running
mysql -u <user> -p<password> -e "select @@sql_mode"

returns?

I get:

+------------+
| @@sql_mode |
+------------+
|            |
+------------+

Also which conf.d directory do you mean? The MySQL or the Apache? I assume the MySQL... which in my install if I run:
/usr/libexec/mysqld --verbose --help | grep -A 1 "Default options"
gives me:

Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

Are you editing your equivalent of the my.cnf file?

[mbmcclelland]

May I see the text of the files you had to edit in your conf.d directory? Maybe I can find what I need to that way. Thanks for your help by the way. (I mistakenly closed the issue)

[flipswitchingmonkey]

The problem is with the second CURRENT_TIMESTAMP in database.php. Changing the line
expiry_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
to
expiry_date TIMESTAMP NOT NULL,
will let the installation proceed without error. Of course now files will not get a default expiry date, but as long as it causes no issues otherwise in the code, I can live with that.

[Ciscog33k]

Can anyone vet @flipswitchingmonkey's patch for long term issues?

My error is similar, but different in textual content

`
There seems to be an error. Please try again.

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'expiry_date' in /var/www/html/install/index.php:31 Stack trace: #0 /var/www/html/install/index.php(31): PDOStatement->execute(Array) #1 /var/www/html/install/index.php(108): try_query(Array) #2 {main}
PDOException: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint in /var/www/html/install/index.php:31 Stack trace: #0 /var/www/html/install/index.php(31): PDOStatement->execute(Array) #1 /var/www/html/install/index.php(108): try_query(Array) #2 {main}
PDOException: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint in /var/www/html/install/index.php:31 Stack trace: #0 /var/www/html/install/index.php(31): PDOStatement->execute(Array) #1 /var/www/html/install/index.php(108): try_query(Array) #2 {main}
PDOException: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint in /var/www/html/install/index.php:31 Stack trace: #0 /var/www/html/install/index.php(31): PDOStatement->execute(Array) #1 /var/www/html/install/index.php(108): try_query(Array) #2 {main}
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY' in /var/www/html/install/index.php:31 Stack trace: #0 /var/www/html/install/index.php(31): PDOStatement->execute(Array) #1 /var/www/html/install/index.php(108): try_query(Array) #2 {main}
PDOException: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint in /var/www/html/install/index.php:31 Stack trace: #0 /var/www/html/install/index.php(31): PDOStatement->execute(Array) #1 /var/www/html/install/index.php(108): try_query(Array) #2 {main}
`

Server Details:
OS: Ubuntu server 16.04
WWW: Apache/2.4.18
PHP: PHP 7.0.13-0ubuntu0.16.04.1
SQL: 5.7.17-0ubuntu0.16.04.

[flipswitchingmonkey]

I guess the safest would be to write the timestamp client side, when the row is written. At least for older MySQL versions. I thought I read that multiple current_timestamp() were now allowed in current versions - and your error looks like something different I think.

[Ciscog33k]

When I removed all the default mysql states via the cnf file, It was able to 'install' but still had one remaining error:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY' in /var/www/html/install/index.php:31

Unsure how it will affect the server's operation.

[pitilago]

Ao instalar ocorria erro
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'expiry_date' in /var/www/html/install/index.php:31 ...

Alterei a linha 26 de database.php para
expiry_date TIMESTAMP NOT NULL DEFAULT '2017-01-01 00:00:00',

A instalação encerrou corretamente.
WampServer Version 3.0.6 32bit; Apache 2.4.23; PHP 5.6.24; MySql 5.7.14; PS r754

[F2210]

The problem is with the second CURRENT_TIMESTAMP in database.php. Changing the line
expiry_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
to
expiry_date TIMESTAMP NOT NULL,
will let the installation proceed without error. Of course now files will not get a default expiry date, but as long as it causes no issues otherwise in the code, I can live with that.

But where is that file located?

[vanderlee]

The hack proposed by @pitilago and @flipswitchingmonkey works for me.

Change file install/database.php, definition for table files, column expiry_date to...
expiry_date TIMESTAMP NOT NULL DEFAULT "2017-01-01 00:00:00",

Given the context, I think this may be a bit more secure than not giving it any expiry date at all.

[duke-m]

When I removed all the default mysql states via the cnf file, It was able to 'install' but still had one remaining error:
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY' in /var/www/html/install/index.php:31

just drop the db and recreate

[ignacionelson]

This should be fixed with commit d469ea3