Laravel 11: How to Change Default SQLite to MySQL

Laravel 11 uses SQLite as the default database for the local environment. Let's see how we can change it to MySQL.

When creating a new Laravel project using Laravel Installer, starting with Installer version 5.1, there is an option to choose which database your application will use.

After choosing MySQL, the installer will change the DB_CONNECTION env value to mysql and set DB values.

But if you don't use such Laravel Installer, the default will be SQLite, with all the other DB parameters commented out in the .env file:

.env:

// ...
 
DB_CONNECTION=sqlite
# DB_HOST=127.0.0.1
# DB_PORT=3306
# DB_DATABASE=laravel
# DB_USERNAME=root
# DB_PASSWORD=
 
// ...

If that is your case and you want to change the database driver to MySQL, you must set the env values manually.

First, you must set the DB_CONNECTION to mysql. Then, uncomment the DB values and put the correct values for your settings. The env values should look similar:

.env:

// ...
 
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel
DB_USERNAME=root
DB_PASSWORD=
 
// ...
Login or register to comment or ask questions
avatar
Richard A. Hoyle

That did not work what else do we need to do? My Laravel Installer is v5.1.3 My Code: My Error message:


***************

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=20@imr$bkl11
DB_USERNAME=20@imr$bkl11
DB_PASSWORD=

***************

   Illuminate\Database\QueryException 

  SQLSTATE[HY000]: General error: 1273 Unknown collation: 'utf8mb4_0900_ai_ci' (Connection: mysql, SQL: select table_name as `name`, (data_length + inde
x_length) as `size`, table_comment as `comment`, engine as `engine`, table_collation as `collation` from information_schema.tables where table_schema = '20@imr$bkl11' and table_type in ('BASE TABLE', 'SYSTEM VERSIONED') order by table_name)

  at vendor\laravel\framework\src\Illuminate\Database\Connection.php:813
    809▕                     $this->getName(), $query, $this->prepareBindings($bindings), $e
    810▕                 );
    811▕             }
    812▕
  ➜ 813▕             throw new QueryException(
    814▕                 $this->getName(), $query, $this->prepareBindings($bindings), $e
    815▕             );
    816▕         }
    817▕     }

  1   vendor\laravel\framework\src\Illuminate\Database\Connectors\MySqlConnector.php:75
      PDOException::("SQLSTATE[HY000]: General error: 1273 Unknown collation: 'utf8mb4_0900_ai_ci'")

  2   vendor\laravel\framework\src\Illuminate\Database\Connectors\MySqlConnector.php:75
      PDO::prepare("set names 'utf8mb4' collate 'utf8mb4_0900_ai_ci'")

PS C:\xampp\htdocs\awi\imrbkL11>
avatar
Nerijus

Your collation is different. Something with your

avatar
Richard A. Hoyle 


  
 "name": "laravel/laravel",
    "type": "project",
    "description": "The skeleton application for the Laravel framework.",
    "keywords": ["laravel", "framework"],
    "license": "MIT",
    "require": {
        "php": "^8.2",
        "laravel/framework": "^11.0",
        "laravel/helpers": "^1.7",
        "laravel/tinker": "^2.9"
    },
    "require-dev": {
        "fakerphp/faker": "^1.23.1",
        "laravel/pint": "^1.13.10",
        "laravel/sail": "^1.27.3",
        "mockery/mockery": "^1.6.7",
        "nunomaduro/collision": "^8.1",
        "pestphp/pest": "^2.33.4",
        "pestphp/pest-plugin-laravel": "^2.2",
        "spatie/laravel-ignition": "^2.4.1"
    },
    "autoload": {
        "psr-4": {
            "App\\": "app/",
            "Database\\Factories\\": "database/factories/",
            "Database\\Seeders\\": "database/seeders/"
        }
    },
    "autoload-dev": {
        "psr-4": {
            "Tests\\": "tests/"
        }
    },
    "scripts": {
        "post-autoload-dump": [
            "Illuminate\\Foundation\\ComposerScripts::postAutoloadDump",
            "@php artisan package:discover --ansi"
        ],
        "post-update-cmd": [
            "@php artisan vendor:publish --tag=laravel-assets --ansi --force"
        ],
        "post-root-package-install": [
            "@php -r \"file_exists('.env') || copy('.env.example', '.env');\""
        ],
        "post-create-project-cmd": [
            "@php artisan key:generate --ansi",
            "@php -r \"file_exists('database/database.sqlite') || touch('database/database.sqlite');\"",
            "@php artisan migrate --ansi"
        ]
    },
    "extra": {
        "branch-alias": {
            "dev-master": "11.x-dev"
        },
        "laravel": {
            "dont-discover": []
        }
    },
    "config": {
        "optimize-autoloader": true,
        "preferred-install": "dist",
        "sort-packages": true,
        "allow-plugins": {
            "pestphp/pest-plugin": true,
            "php-http/discovery": true
        }
    },
    "minimum-stability": "dev",
    "prefer-stable": true
}
avatar
Richard A. Hoyle 


  
APP_NAME=laravel
APP_ENV=local
APP_KEY=base64:/xhIILl2JSh4ytzuEwzbIZHhhIaKn5xnNbh0cJy3f1k=
APP_DEBUG=true
APP_TIMEZONE=UTC
APP_URL=http://imrbkL11.lxl

APP_LOCALE=en
APP_FALLBACK_LOCALE=en
APP_FAKER_LOCALE=en_US

APP_MAINTENANCE_DRIVER=file
APP_MAINTENANCE_STORE=database

BCRYPT_ROUNDS=12

LOG_CHANNEL=stack
LOG_STACK=single
LOG_DEPRECATIONS_CHANNEL=null
LOG_LEVEL=debug

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=20@imr$bkl11
DB_USERNAME=20@imr$bkl11
DB_PASSWORD=

BROADCAST_CONNECTION=log
CACHE_STORE=database
FILESYSTEM_DISK=local
QUEUE_CONNECTION=database
SESSION_DRIVER=database
SESSION_LIFETIME=120

MEMCACHED_HOST=127.0.0.1

REDIS_CLIENT=phpredis
REDIS_HOST=127.0.0.1
REDIS_PASSWORD=null
REDIS_PORT=6379

MAIL_MAILER=log
MAIL_HOST=127.0.0.1
MAIL_PORT=2525
MAIL_USERNAME=null
MAIL_PASSWORD=null
MAIL_ENCRYPTION=null
MAIL_FROM_ADDRESS="hello@example.com"
MAIL_FROM_NAME="${APP_NAME}"

AWS_ACCESS_KEY_ID=
AWS_SECRET_ACCESS_KEY=
AWS_DEFAULT_REGION=us-east-1
AWS_BUCKET=
AWS_USE_PATH_STYLE_ENDPOINT=false

PUSHER_APP_ID=
PUSHER_APP_KEY=
PUSHER_APP_SECRET=
PUSHER_HOST=
PUSHER_PORT=443
PUSHER_SCHEME=https
PUSHER_APP_CLUSTER=mt1

VITE_APP_NAME="${APP_NAME}"
VITE_PUSHER_APP_KEY="${PUSHER_APP_KEY}"
VITE_PUSHER_HOST="${PUSHER_HOST}"
VITE_PUSHER_PORT="${PUSHER_PORT}"
VITE_PUSHER_SCHEME="${PUSHER_SCHEME}"
VITE_PUSHER_APP_CLUSTER="${PUSHER_APP_CLUSTER}"
avatar
Nerijus

Why are you posting composer and env when the problem is on your db level?

avatar
Richard A. Hoyle

This is a fresh install; the only thing I have done so far is setup this .env file and nothing else. Well running the install I did select the mysql for the database. You said that I needed to make the changes shown here; that is all I have done to this point this is why I am asking what else we have to do to get this to work?

I am using Composer v2.6.6 and Laravel Installer V5.1.3

avatar
Nerijus

Your db collation is wrong. It's the problem with your setup. Google your error message

avatar
Richard A. Hoyle

I am using XAMPP v3.3.0 and according to it I am using utf8mb4_unicode_ci My system works just fine in Laravel 10.43.0 . The only thing I am doing deferent is installing Laravel 11 using the –dev to do so “Laravel new imrbkL11 –dev” If I leave off the –dev I get version 10.43.0 and It works Grate that Is why I still am wondering what else we have to do to get this to work!?


# Example MySQL config file for small systems.
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.

## UTF 8 Settings
#init-connect=\'SET NAMES utf8\'
#collation_server=utf8_unicode_ci
#character_set_server=utf8
#skip-character-set-client-handshake
#character_sets-dir="C:/xampp/mysql/share/charsets"
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION
log_bin_trust_function_creators=1

character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
[mysqldump]
max_allowed_packet=16M

[mysql]
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer=20M
sort_buffer_size=20M
read_buffer=2M
write_buffer=2M

[myisamchk]
key_buffer=20M
sort_buffer_size=20M
read_buffer=2M
write_buffer=2M

[mysqlhotcopy]
avatar
Richard A. Hoyle

Where in Laravel 11 do I change the utf8mb4_0900_ai_ci back to the original utf8mb4_general_ci? Sense the config.php database file is not here? I tried to run the config:publish dashboard but I am getting an error doing that:


 config
  config:cache              Create a cache file for faster configuration loading
  config:clear              Remove the configuration cache file
  config:publish            Publish configuration files to your application
  config:show               Display all of the values for a given configuration file



PS C:\xampp\htdocs\awi\imrbkL11>  config:publish database
config:publish : The term 'config:publish' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling 
of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:2
 config:publish database
 ~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (config:publish:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException
avatar
Oluwapelumi Babalola

This is what you need to do.

php artisan config:publish

That will show you all the available configs options select database (when you get to database, click on enter) and you should see a mesaage that it's successful. After that scroll to the mysql part if database.php, you'll see the collate part change it to

utf8mb4_unicode_ci.

Everything should work perfectly.

Additional: when creating databse from terminal, i used this command

create database scores11 character set utf8mb4 collate utf8mb4_unicode_ci;

scores11 is the name of the database.

avatar
Nerijus

Had no problems with the default Laravel mysql settings.

avatar
Richard A. Hoyle

started over and did the php artisan config:publish database option and then changed the 'collation' => 'utf8mb4_0900_ai_ci', TO
'collation' => 'utf8mb4_unicode_ci',

Everything is working just fine now.

Question can I remove the database.sqlite file or do I have to keep it there for the testing I am using Pest?

avatar
Nerijus

File can be deleted

avatar
Richard A. Hoyle

File can be What? deleted or left!

avatar
Leo de Keijzer

Changed my driver from mysql to mariadb helped for me

avatar
Gio Dela Peña

If you are facing collation problem just upgrade your mysql version or if you don't want to, just navigate to config/database.php and in connections, change the collation for mysql from 'collation' => env('DB_COLLATION', 'utf8mb4_0900_ai_ci') to 'collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci').

Or just simply add 'DB_COLLATION=utf8mb4_unicode_ci' to your .env file.

avatar
Abdulbasit Salah

I just installed the laravel 11 stable version. and i face same error. SQLSTATE[HY000]: General error: 1273 Unknown collation: 'utf8mb4_0900_ai_ci'

  1. to solve that publish the database config file php artisan config:publish database
  2. go to the database.php config and in connections array in mysql cofigartation change the DB_COLLATION to utf8mb4_unicode_ci the default in laravel 11 is utf8mb4_0900_ai_ci
avatar
Nerijus

Database config is published when you install laravel 11 as I remember. Better way upgrade your mysql version to have laravels collation

avatar
Abdulbasit Salah

yes, my bad the database.php config is aleady exisit with laravel 11. and i am using xammp any idea how to only upgrade the mysql part as far i check i use the latest xampp which is php 8

Like our articles?

Become a Premium Member for $129/year or $29/month
What else you will get:
  • 68 courses (1188 lessons, total 43 h 18 min)
  • 90 long-form tutorials (one new every week)
  • access to project repositories
  • access to private Discord

Recent New Courses