Yourls: Unable to get YOURLS to install [Uncaught PDOException] on nginx

[blinkingline]

Technical details regarding my environment

• YOURLS version: 1.7.3 Fresh install
  
  
  
  • Plugins enabled: None
  
  
• PHP version: 7.0.22-0ubuntu0.16.04.1
• Any other useful information depending on context (server OS & version, MySQL version, browser version, …)
  Nginx version: 1.10.3-0ubuntu0.16.04.2

Bug description

What is the current behavior?

When I'm trying to install a fresh instance of YOURLS, I get everything set up and working and click "Install YOURLS", at which point the browser returns a 500 error. Looking at the error log, it looks like this is being caused by something in PDO:

`
2018/01/08 19:42:35 [error] 21546#21546: *5 FastCGI sent in stderr: "PHP message: PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or acces$
Stack trace:

0 /usr/share/nginx/YOURLS/includes/vendor/aura/sql/src/ExtendedPdo.php(748): PDOStatement->execute()

1 /usr/share/nginx/YOURLS/includes/functions-install.php(252): Aura\Sql\ExtendedPdo->perform('CREATE TABLE IF...')

2 /usr/share/nginx/YOURLS/admin/install.php(46): yourls_create_sql_tables()

3 {main}

thrown in /usr/share/nginx/YOURLS/includes/vendor/aura/sql/src/ExtendedPdo.php on line 748" while reading response header from upstream, client: 173.239.228.14$`

This is a bug because…

What is the expected or desired behavior?

It shouldn't throw an exception and install should complete.

Reproducible bug summary

I've double checked by DB credentials, and they look good. My nginx config looks like this (with server details removed)

# Rewrites
  location / {
    # Try static files, then yourls-loader.php
    try_files $uri $uri/ /yourls-loader.php?$args;
  }
    location ~ \.php$ {
        fastcgi_pass unix:/var/run/php7.0-fpm.sock;
        fastcgi_split_path_info ^(.+\.php)(/.+)$;
        fastcgi_index index.php;
        include /etc/nginx/fastcgi_params;
        include /etc/nginx/fastcgi.conf;
     }

What am I missing?

[dgw]

The error message part is truncated. You are missing the actual error, which is somewhere after "Syntax error or acces" but before the stack trace.

Perhaps you can get the full error message from PHP's own log?

[blinkingline]

Sorry about that! Full error message is:

2018/01/08 19:42:35 [error] 21546#21546: *5 FastCGI sent in stderr: "PHP message: PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes in /usr/share/nginx/YOURLS/includes/vendor/aura/sql/src/ExtendedPdo.php:748
I'm not sure what key it's talking about...

[dgw]

MySQL version could be relevant here.

Basically, the database is trying to create indexes and failing because the maximum index key length is too low for the character encoding you're using. IIRC YOURLS is more or less tweaked to operate on utf8 databases, and mileage with other charsets may vary.

Enabling innodb_large_prefix in MySQL options might be a fix too, but it's probably better to just change the DB charset.

[blinkingline]

I'm running 10.0.31-MariaDB-0ubuntu0.16.04.2, and it looks like I'm currently on charset utf8mb4.

I changed that to utf8 and enabled innodb_large_prefix too, just for fun, and that seems to have fixed it. Thanks!

[dgw]

Yep, utf8mb4 means you have a roughly a quarter of the normal key length limit. Changing charsets on its own would probably have fixed it, but as long as you're happy running your database with innodb_large_prefix I don't think there's any measurable performance impact. Glad you got up and running!

[blinkingline]

You can set the character set using the charset command MariaDB:

MariaDB [yourls]> charset utf8

Charset changed

MariaDB [yourls]> status

mysql Ver 15.1 Distrib 10.0.36-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Server version: 10.0.36-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 21 days 6 hours 49 min 5 sec

Hope that helps.

February 5, 2019 1:16 PM, "Jeremie Francois" <[email protected] (mailto:[email protected]?to=[e-mail])> wrote:
@blinkingline (https://github.com/blinkingline) or @dgw (https://github.com/dgw) may I ask how/where you fix that exactly?
I added innodb_large_prefix=1 tomy.cnf and restarted the sql server but it had no effect (mariadb 10.1 on ubuntu 18)

—

You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub (https://github.com/YOURLS/YOURLS/issues/2350#issuecomment-460806666), or mute the thread (https://github.com/notifications/unsubscribe-auth/AHBvdHluCsazO3KCMu8QYC-aiDbNX5Kmks5vKfSkgaJpZM4RW3xv).

[MoonCactus]

Thanks @blinkingline for your quick reply & for your time! I had removed my post b/c I saw other similar queries and I did not try them at the time and did not want to lose your time!

Sadly, it does not change anything to me:

MariaDB [(none)]> charset utf8
Charset changed
MariaDB [(none)]> status
--------------
mariadb  Ver 15.1 Distrib 10.1.37-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:      3
Current database:   
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server:         MariaDB
Server version:     10.1.37-MariaDB-0+deb9u1 Debian 9.6
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:         10 hours 39 min 4 sec

Threads: 1  Questions: 10  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 11  Queries per second avg: 0.000
--------------

I tried before to switch my main config, which probably explains why I have a Db characterset: utf8mb4 that differs from yours. May be I shoudl revert that but I do not see how it would harm.

Accessing admin/install.php? still gives me:

2019/02/06 09:21:37 [error] 9330#9330: *516 FastCGI sent in stderr: "PHP message: PHP Fatal error:  Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes in [...]/includes/vendor/aura/sql/src/ExtendedPdo.php:748
Stack trace:
#0 [...]/includes/vendor/aura/sql/src/ExtendedPdo.php(748): PDOStatement->execute()
#1 [...]/includes/functions-install.php(252): Aura\Sql\ExtendedPdo->perform('CREATE TABLE IF...')
#2 [...]/admin/install.php(46): yourls_create_sql_tables()
#3 {main}

(my yourls version is the origin/master from yesterday)

[blinkingline]

I'd try to set the charset for the DB to utf8 as well, I think that's what fixed it for me. Unfortunately, it was a while back when I did this so I don't exactly remember.
Hope that helps.

-gee

February 6, 2019 12:29 AM, "Jeremie Francois" <[email protected] (mailto:[email protected]?to=[e-mail])> wrote:
Thanks @blinkingline (https://github.com/blinkingline) for your quick reply & for your time! I had removed my post b/c I saw other similar queries and I did not try them at the time and did not want to lose your time!

Sadly, it does not change anything to me: 

MariaDB [(none)]> charset utf8 Charset changed MariaDB [(none)]> status -------------- mariadb Ver 15.1 Distrib 10.1.37-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 Connection id: 3 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6 Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 10 hours 39 min 4 sec Threads: 1 Questions: 10 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 0.000 --------------  

I tried before to switch my main config, which probably explains why I have a Db characterset: utf8mb4 that differs from yours. May be I shoudl revert that but I do not see how it would harm. 

Accessing admin/install.php? still gives me: 

2019/02/06 09:21:37 [error] 9330#9330: *516 FastCGI sent in stderr: "PHP message: PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes in [...]/includes/vendor/aura/sql/src/ExtendedPdo.php:748 Stack trace: #0 [...]/includes/vendor/aura/sql/src/ExtendedPdo.php(748): PDOStatement->execute() #1 [...]/includes/functions-install.php(252): AuraSqlExtendedPdo->perform('CREATE TABLE IF...') #2 [...]/admin/install.php(46): yourls_create_sql_tables() #3 {main}  

(my yourls version is the origin/master from yesterday) 

—

You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub (https://github.com/YOURLS/YOURLS/issues/2350#issuecomment-460939171), or mute the thread (https://github.com/notifications/unsubscribe-auth/AHBvdHTQpLYikZ_Sqp6KZj1GXbwjPg6wks5vKpJWgaJpZM4RW3xv).

[MoonCactus]

It did fix my issue, many thanks!

ALTER DATABASE yourls CHARACTER SET utf8 COLLATE utf8_general_ci;

[joshp23]

I have my character set as InnoDB and was getting this error on upgrade. I changed collate to utf8_unicode_cli and the update ran fine.