add_if_not_exists doesn't seem to work

[anamba]

When doing this:

    alter table(:cms_pages) do
      add_if_not_exists(:layout, :string, size: 191)
    end

I get:

10:38:44.789 [info]  alter table cms_pages
** (MyXQL.Error) (1064) (ER_PARSE_ERROR) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS `layout` varchar(191)' at line 1
    (ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:593: Ecto.Adapters.SQL.raise_sql_call_error/1

Is this supposed to work? If not, any suggestions on how to accomplish what I'm trying to do?

[josevalim]

What is your MySQL version?

[anamba]

I am using mysql 8.0.21 from Homebrew.

[josevalim]

It turns out it is not actually supported by MySQL, so if you really need to do it, you will need to use execute to define a procedure by hand and then execute it: https://stackoverflow.com/questions/972922/add-column-to-mysql-table-if-it-does-not-exist/33755258

I will improve ecto_sql to improve the error message, thanks!

[josevalim]

Actually, it is supported by MariaDB: https://mariadb.com/kb/en/alter-table/ so that's also an option if you can change the DB choice. But mysql does not seem to support it.