PDOException: SQLSTATE[HY000]: General error: 1 near "MODIFY": syntax error

[Mahmoudz]

Getting this error:

Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 near "MODIFY": syntax error (SQL: ALTER TABLE countries MODIFY country_code CHAR(3) NOT NULL DEFAULT '')

Since I just installed it in my project, I applied the charifying directly to the countries table and commented the modifications.

[kkomelin]

Just noticed this issue after switching from MySQL to Postgres. Postgres also has different format for altering tables, so the CharifyCountriesTable migration is not database agnostic and it works for MySQL only.

We would need to replace such raw SQL queries:

DB::statement("ALTER TABLE " . DB::getTablePrefix() . \Config::get('countries.table_name') . " MODIFY country_code CHAR(3) NOT NULL DEFAULT ''");

with something like:

$table->char('country_code', 3)->default('')->change();

However, the change() feature requires doctrine/dbal package installed and doctrine/dbal doesn't support CHAR type, as I understand. It means we can't charify the fields easily in all db types.

I see a couple of ways to address the problem:

1. Write own raw SQL queries for each supported database in the CharifyCountriesTable migration and choose them depending on the used database type.
2. Get rid of CharifyCountriesTable migration and only use char type in the main SetupCountriesTable migration so that all old users would use strings and all new users would use chars. Not a big difference.
3. Get rid of CharifyCountriesTable and rollback SetupCountriesTable migration to creating fields of string type.

[bryanrscott]

I used postgres as well. An example of 1.

if (Schema::getConnection()->getDriverName() == 'pgsql') { } else { }

[Braunson]

I ended up using DB::raw as an alternative for Sqlite and it seems to have worked as a workaround but it's not ideal.

[Dennis-Mwea]

We can fix this by adding a char type to Doctrine. Check the open PR for the fix https://github.com/webpatser/laravel-countries/pull/120