[Vito v2.x] The installation script failed to complete database migrations on Ubuntu 20.04

joglomedia commented 1 month ago

Migration Failed on Ubuntu 20.04: `SQLSTATE[HY000]: General error: 1 Cannot add a NOT NULL column with default value NULL`

Describe the bug Installing Vito v2.x on Ubuntu 20.04 using the standard installation script with the default SQLite3 library from the Ubuntu repository failed to complete the Laravel migrations.

In Connection.php line 813: SQLSTATE[HY000]: General error: 1 Cannot add a NOT NULL column with default value NULL (Connection: sqlite, SQL: alter table "storage_providers" add column "user_id" integer not null)

In Connection.php line 565: SQLSTATE[HY000]: General error: 1 Cannot add a NOT NULL column with default value NULL

To Reproduce Steps to reproduce the behavior:

Create Ubuntu 20.04 instance from your vps/cloud server provider (I use Amazon Lightsail)
Clone repo git clone -b v2.x https://github.com/vitodeploy/vito.git
Install Vito using default scripts/install.sh
See error

Expected behavior Laravel database successfully migrated, default admin user created.

Screenshots

Additional context Operating System:

$ sudo lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 20.04.6 LTS
Release:    20.04
Codename:   focal

SQLite3 library (default Ubuntu 20.04 repository packed with old version 3.31.1)

$ sudo apt-cache policy sqlite3
sqlite3:
  Installed: 3.31.1-4ubuntu0.6
  Candidate: 3.31.1-4ubuntu0.6
  ...

$ dpkg-query -l | awk '/sqlite3/'
ii  libsqlite3-0:amd64                 3.31.1-4ubuntu0.6                       amd64        SQLite 3 shared library
ii  php8.3-sqlite3                     8.3.12-1+ubuntu20.04.1+deb.sury.org+1   amd64        SQLite3 module for PHP
ii  sqlite3                            3.31.1-4ubuntu0.6                       amd64        Command line interface for SQLite 3

Proposed Solution

The only solution I found was to build the latest version of SQLite3 (at least version 3.37.0, as used by Jammy) from source. Here I write a function to install SQLite3 from source:

function sqlite3_install_from_source() {
  SQLITE_RELEASE_YEAR=${1:-"2024"} # 2024
  SQLITE_RELEASE_VERSION=${2:-"3460100"} # 3460100
  SQLITE_SOURCE_URL="https://www.sqlite.org/${SQLITE_RELEASE_YEAR}/sqlite-autoconf-${SQLITE_RELEASE_VERSION}.tar.gz"

  echo "Installing SQLite v3 from source"

  if curl -sLI "${SQLITE_SOURCE_URL}" | grep -q "HTTP/[.12]* [2].."; then
    curl -sSL -o "sqlite-autoconf-${SQLITE_RELEASE_VERSION}.tar.gz" "${SQLITE_SOURCE_URL}" && \
    tar -xzf "sqlite-autoconf-${SQLITE_RELEASE_VERSION}.tar.gz" && \
    cd "sqlite-autoconf-${SQLITE_RELEASE_VERSION}" && \
    apt -q -y install libreadline-dev && \
    CFLAGS="-O2 -DSQLITE_ENABLE_COLUMN_METADATA=1" ./configure && \
    make && make install && \
    ldconfig && \
    cd ../ || return 1
    rm -fr "sqlite-autoconf-${SQLITE_RELEASE_VERSION}"
    rm -f "sqlite-autoconf-${SQLITE_RELEASE_VERSION}.tar.gz"
    SQLITE_BIN=$(command -v sqlite3)
    [[ -x "${SQLITE_BIN}" ]] && "${SQLITE_BIN}" --version
  else
    echo "SQLite v3 source file could not be found"
  fi
}

sqlite3_install_from_source "$@"

I'm currently working on improving the scripts/install.sh script for Vito v2.x. Related issue #320

Aliyan-12 commented 4 weeks ago

The error SQLSTATE[HY000]: General error: 1 Cannot add a NOT NULL column with default value NULL shows that you are assigning NULL value to a column which is Not NULL type. Share the code of the update_storage_providers_table.php migration here so I can assist you further :)

joglomedia commented 4 weeks ago

The error SQLSTATE[HY000]: General error: 1 Cannot add a NOT NULL column with default value NULL shows that you are assigning NULL value to a column which is Not NULL type. Share the code of the update_storage_providers_table.php migration here so I can assist you further :)

The code available in database/migrations

Btw, this issue has long recognized as bug due to SQLite differnce behavour compared to other SQL database engine

https://sqlite.org/forum/info/ffa52447275d247a

The issue itself has been patched since SQLite v3.37.0

https://www.sqlite.org/changes.html

I think, we don't need to change the code, since the current migration code working fine on PostgreSQL (although Vito itself doesn't officially support Postgres)

saeedvaziry commented 2 weeks ago

I don't think the installation process you have to reproduce this issue is valid.

the error appears if you already have some rows in the storage_providers table which in case of new installation I don't think there must be any rows in that table!

Also its too early to raise any installation issues on v2 as it doesn't have a release yet. so lets wait for the first release (beta) and then test this part.

saeedvaziry commented 2 weeks ago

I could reproduce this on ubuntu 20.04 but still, vito itself must be installed on ubuntu 22.04 or higher versions

vitodeploy / vito