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=
 
// ...

Richard A. Hoyle

9 months ago

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>

Nerijus

9 months ago

Your collation is different. Something with your

Richard A. Hoyle

9 months ago



  
 "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
}

Nerijus

9 months ago

Richard A. Hoyle

9 months ago



  
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}"

Nerijus

9 months ago

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

Richard A. Hoyle

9 months ago

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

Nerijus

9 months ago

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

Richard A. Hoyle

9 months ago

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]

Richard A. Hoyle

9 months ago

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

Oluwapelumi Babalola

9 months ago

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.

Nerijus

9 months ago

Had no problems with the default Laravel mysql settings.

Richard A. Hoyle

9 months ago

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?

Nerijus

9 months ago

File can be deleted

Richard A. Hoyle

9 months ago

File can be What? deleted or left!

Leo de Keijzer

9 months ago

Changed my driver from mysql to mariadb helped for me

Gio Dela Peña

8 months ago

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.

Abdulbasit Salah

8 months ago

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

to solve that publish the database config file php artisan config:publish database
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

Nerijus

8 months ago

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

Abdulbasit Salah

8 months ago

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

Laravel 11: How to Change Default SQLite to MySQL

Like our articles?

Recent New Courses

Testing in Laravel 11: Advanced Level

Multi-Language Laravel 11: All You Need to Know

[Mini-course] Filament: Visual Customizations