search

fr0tt / benotes

An open source self hosted notes and bookmarks taking web app.
https://benotes.org
MIT License
718 stars 49 forks source link

MariaDB: SQL problem during installation #103

Closed s22-tech closed 5 months ago

s22-tech commented 6 months ago

I get the following when creating the db. I've done this a couple of times and get the same response every time. Any idea how to fix it?

% php artisan install
Initiate installation...

 0/4 [░░░░░░░░░░░░░░░░░░░░░░░░░░░░]   0%

Application key set successfully.

 This will invalidate all existing tokens. Are you sure you want to override the secret key? (yes/no) [no]:
 > yes

jwt-auth secret [xxxxxxx] set successfully.

 2/4 [▓▓▓▓▓▓▓▓▓▓▓▓▓▓░░░░░░░░░░░░░░]  50%

**************************************
*     Application In Production!     *
**************************************

 Do you really wish to run this command? (yes/no) [no]:
 > yes

Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table

   Illuminate\Database\QueryException

  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes (SQL: alter table `users` add unique `users_email_unique`(`email`))

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:712
    708▕         // If an exception occurs when attempting to run a query, we'll format the error
    709▕         // message to include the bindings with SQL, which will make this exception a
    710▕         // lot more helpful to the developer instead of just the database's errors.
    711▕         catch (Exception $e) {
  ➜ 712▕             throw new QueryException(
    713▕                 $query, $this->prepareBindings($bindings), $e
    714▕             );
    715▕         }
    716▕     }

      +9 vendor frames
  10  database/migrations/2014_10_12_000000_create_users_table.php:23
      Illuminate\Support\Facades\Facade::__callStatic("create")

      +19 vendor frames
  30  app/Console/Commands/InstallCommand.php:57
      Illuminate\Console\Command::call("migrate")

EDIT: I'm using the manual installation. PHP 8.0.30

Also, this may be because the install didn't finish, but I just get a blank screen when I visit the URL.

fr0tt commented 6 months ago

Well that's a new one.

What database and version of it are you using ?

s22-tech commented 6 months ago

10.3.39-MariaDB

s22-tech commented 6 months ago

In case this helps, here's my laravel.log laravel.log

fr0tt commented 6 months ago

So I think the issue is 1071 Specified key was too long; max key length is 1000 bytes.

Seems to be a limitation which was apparently lifted with version 10.4. So it probably should work if you either update your database or try something like that: https://dba.stackexchange.com/a/231234

fr0tt commented 6 months ago

Just out of curiosity, maybe it might work by simply changing the following in database/migrations/2014_10_12_000000_create_users_table.php in line 19:

$table->string('email', 125)->unique();

Would be interesting to know if that's all it takes, without trying anything I wrote in my previous answer.

s22-tech commented 6 months ago

I changed line 19 and this is what I got:

% php artisan install
Initiate installation...

 0/4 [░░░░░░░░░░░░░░░░░░░░░░░░░░░░]   0%

**************************************
*     Application In Production!     *
**************************************

 Do you really wish to run this command? (yes/no) [no]:
 > yes

Application key set successfully.
 1/4 [▓▓▓▓▓▓▓░░░░░░░░░░░░░░░░░░░░░]  25%
 This will invalidate all existing tokens. Are you sure you want to override the secret key? (yes/no) [no]:
 > yes

jwt-auth secret [xxxxxxx] set successfully.

 2/4 [▓▓▓▓▓▓▓▓▓▓▓▓▓▓░░░░░░░░░░░░░░]  50%

**************************************
*     Application In Production!     *
**************************************

 Do you really wish to run this command? (yes/no) [no]:
 > yes

Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table (7.24ms)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (5.07ms)
Migrating: 2019_08_28_093831_create_collections_table
Migrated:  2019_08_28_093831_create_collections_table (4.29ms)
Migrating: 2020_02_29_231807_create_posts_table
Migrated:  2020_02_29_231807_create_posts_table (6.60ms)
Migrating: 2020_03_04_132210_remove_unused_columns_and_add_permissions_to_users
Migrated:  2020_03_04_132210_remove_unused_columns_and_add_permissions_to_users (5.26ms)
Migrating: 2020_03_23_150126_create_shares_table
Migrated:  2020_03_23_150126_create_shares_table (9.50ms)
Migrating: 2020_12_20_133609_change_column_type_from_posts
Migrated:  2020_12_20_133609_change_column_type_from_posts (28.26ms)
Migrating: 2021_07_24_224526_change_color_from_posts
Migrated:  2021_07_24_224526_change_color_from_posts (5.90ms)
Migrating: 2022_03_04_162953_add_icon_to_collections_table
Migrated:  2022_03_04_162953_add_icon_to_collections_table (2.77ms)
Migrating: 2022_07_18_233713_create_tags_table
Migrated:  2022_07_18_233713_create_tags_table (4.73ms)
Migrating: 2022_07_18_234358_create_post_tag_table
Migrated:  2022_07_18_234358_create_post_tag_table (7.00ms)
Migrating: 2022_12_12_000000_create_failed_jobs_table

   Illuminate\Database\QueryException

  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes (SQL: alter table `failed_jobs` add unique `failed_jobs_uuid_unique`(`uuid`))

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:712
    708▕         // If an exception occurs when attempting to run a query, we'll format the error
    709▕         // message to include the bindings with SQL, which will make this exception a
    710▕         // lot more helpful to the developer instead of just the database's errors.
    711▕         catch (Exception $e) {
  ➜ 712▕             throw new QueryException(
    713▕                 $query, $this->prepareBindings($bindings), $e
    714▕             );
    715▕         }
    716▕     }

      +9 vendor frames
  10  database/migrations/2022_12_12_000000_create_failed_jobs_table.php:24
      Illuminate\Support\Facades\Facade::__callStatic("create")

      +19 vendor frames
  30  app/Console/Commands/InstallCommand.php:57
      Illuminate\Console\Command::call("migrate")

More tables were created this time around.

s22-tech commented 6 months ago

OK. I updated my server to 10.6.17-MariaDB but still can't install this script. Here's the latest error report:

% php artisan install
Initiate installation...

 0/4 [░░░░░░░░░░░░░░░░░░░░░░░░░░░░]   0%

**************************************
*     Application In Production!     *
**************************************

 Do you really wish to run this command? (yes/no) [no]:
 > yes

Application key set successfully.
 1/4 [▓▓▓▓▓▓▓░░░░░░░░░░░░░░░░░░░░░]  25%
 This will invalidate all existing tokens. Are you sure you want to override the secret key? (yes/no) [no]:
 > yes

jwt-auth secret [xxxxxxx] set successfully.

 2/4 [▓▓▓▓▓▓▓▓▓▓▓▓▓▓░░░░░░░░░░░░░░]  50%

**************************************
*     Application In Production!     *
**************************************

 Do you really wish to run this command? (yes/no) [no]:
 > yes

Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table (12.85ms)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (11.87ms)
Migrating: 2019_08_28_093831_create_collections_table
Migrated:  2019_08_28_093831_create_collections_table (10.34ms)
Migrating: 2020_02_29_231807_create_posts_table
Migrated:  2020_02_29_231807_create_posts_table (17.68ms)
Migrating: 2020_03_04_132210_remove_unused_columns_and_add_permissions_to_users
Migrated:  2020_03_04_132210_remove_unused_columns_and_add_permissions_to_users (15.23ms)
Migrating: 2020_03_23_150126_create_shares_table
Migrated:  2020_03_23_150126_create_shares_table (24.31ms)
Migrating: 2020_12_20_133609_change_column_type_from_posts
Migrated:  2020_12_20_133609_change_column_type_from_posts (37.73ms)
Migrating: 2021_07_24_224526_change_color_from_posts
Migrated:  2021_07_24_224526_change_color_from_posts (10.59ms)
Migrating: 2022_03_04_162953_add_icon_to_collections_table
Migrated:  2022_03_04_162953_add_icon_to_collections_table (7.95ms)
Migrating: 2022_07_18_233713_create_tags_table
Migrated:  2022_07_18_233713_create_tags_table (11.21ms)
Migrating: 2022_07_18_234358_create_post_tag_table
Migrated:  2022_07_18_234358_create_post_tag_table (18.06ms)
Migrating: 2022_12_12_000000_create_failed_jobs_table
Migrated:  2022_12_12_000000_create_failed_jobs_table (10.52ms)
Migrating: 2022_12_14_000001_create_personal_access_tokens_table

   Illuminate\Database\QueryException

  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes (SQL: alter table `personal_access_tokens` add index `personal_access_tokens_tokenable_type_tokenable_id_index`(`tokenable_type`, `tokenable_id`))

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:712
    708▕         // If an exception occurs when attempting to run a query, we'll format the error
    709▕         // message to include the bindings with SQL, which will make this exception a
    710▕         // lot more helpful to the developer instead of just the database's errors.
    711▕         catch (Exception $e) {
  ➜ 712▕             throw new QueryException(
    713▕                 $query, $this->prepareBindings($bindings), $e
    714▕             );
    715▕         }
    716▕     }

      +9 vendor frames
  10  database/migrations/2022_12_14_000001_create_personal_access_tokens_table.php:24
      Illuminate\Support\Facades\Facade::__callStatic("create")

      +19 vendor frames
  30  app/Console/Commands/InstallCommand.php:57
      Illuminate\Console\Command::call("migrate")
s22-tech commented 6 months ago

I was able to get the installation to complete by doing this: laravel-news

However I still have a blank screen when I go to benotes.domain/public, so at this point, I'm stumped.

fr0tt commented 6 months ago

Yeah I was going to suggest your solution as well but it comes with some drawbacks (e.g. a shorter title length of your links/posts).

I hoped updating your database would solve the issue, which also seemed appropriate because 10.3 reached it's end of life. Strangely enough it works just fine with MySQL or PostgreSQL.

You have to set the public directory as root directory for your webserver (which is also from a security perspective much better). So e.g. if you use the built-in webserver: php -S localhost:8000 -t public

s22-tech commented 6 months ago

As for the public directory, are you talking about something different than this?

  RewriteEngine on

  RewriteCond %{REQUEST_URI} !^/public/
  RewriteRule ^(.*)$ /public/$1 [L,R=301]
fr0tt commented 6 months ago

If you are using Apache2 and have no access to change its configuration directly, you can create an .htaccess file with the following content:

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteCond %{REQUEST_URI} !/public
RewriteRule ^(.*)$ public/$1 [L]
</IfModule>

If you're using Apache2 and you can change it then use the DocumentRoot Directive instead.

s22-tech commented 6 months ago

That's pretty much what I already had. Anyway, I'm finally in. After looking in my Apache logs, I saw that the installer had set the permssions too high for both /public/ and /public/index.php. After lowering them to 755 and 644 respectively, I was able to get in. I actually lowered permissions for all directories and files since they were all writeable.

Looks nice so far! I'm off to see what all it can do. 🙂

s22-tech commented 6 months ago

it comes with some drawbacks (e.g. a shorter title length of your links/posts).

Can you post the SQL for the tables so I can recreate those by hand? Thanks!

fr0tt commented 5 months ago

That's pretty much what I already had. Anyway, I'm finally in. After looking in my Apache logs, I saw that the installer had set the permssions too high for both /public/ and /public/index.php. After lowering them to 755 and 644 respectively, I was able to get in. I actually lowered permissions for all directories and files since they were all writeable.

Looks nice so far! I'm off to see what all it can do. 🙂

Well your version didn't work. That's why I posted an alternative.

fr0tt commented 5 months ago

it comes with some drawbacks (e.g. a shorter title length of your links/posts).

Can you post the SQL for the tables so I can recreate those by hand? Thanks!

That could be problematic in the future if your database is inconsistent with the migrations. For now you might be fine leaving things as they are. (However the title column in posts might benefit from a larger size.)

s22-tech commented 5 months ago

Just to clear up an assumption I made above, the high permissions did not come from this script's installer. They came from the git clone command. Setting umask 0022 on my command line fixed that problem.

https://stackoverflow.com/questions/23667859/why-is-git-clone-changing-file-permissions