Compatibility with MySQL

[amitavroy]

In it's current state, the application is not compatible with MySQL database.

Some migrations have text fields with default value which is not possible with MySQL and hence they give errors.

Also, during the migration the workspace table has some issues due to which the migration gives an error saying

There is no active transaction

I was able to fix the issue of default value by adding code in mutator which will allow handling default values across all DB drivers. Have a PR ready as well. However, for the workspace thing, some changes might be required.

[JhumanJ]

Hey there, can you please share your PR? What exactly is missing for you to make it work with the workspaces? Happy to help!

[amitavroy]

This is my fork where I am working

https://github.com/amitavroy/OpnForm/commit/3fc058b2d0ade794a7e4ba69656037799a015244

It fails because of Transaction issue

[chiragnotionforms]

Hi @amitavroy

Working fine for me (Without doing any changes)

Can you please try again from scratch and let me know if you need any help.

[amitavroy]

That's a bit strange.

I am getting an error with the Transaction that you have in file: database/migrations/2021_10_13_142022_create_users_workspaces_table.php

When I remove the entire transaction, it works. Something to do with the foreign key constrains which I am not able to deduce why. I am on MySQL 8.x

[chiragnotionforms]

Hi @amitavroy

it's already fixed. Can you please pull latest changes from main branch

[mchlbowyer]

@chiragnotionforms which version of MySQL are you using for testing? I have tried both 5.7.23 and 8.0.27 however still getting the same error as reported regarding default values for text fields:

SQLSTATE[42000]: Syntax error or access violation: 1101 BLOB, TEXT, GEOMETRY or JSON column 'submit_button_text' can't have a default value (SQL: create table `forms` (`id` bigint unsigned not null auto_increment primary key, `workspace_id` bigint unsigned not null, `title` varchar(255) not null, `slug` varchar(255) not null, `properties` json not null, `created_at` timestamp null, `updated_at` timestamp null, `notifies` tinyint(1) not null default '0', `description` text null, `submit_button_text` text not null default 'Submit', `re_fillable` tinyint(1) not null default '0', `re_fill_button_text` text not null default 'Fill Again', `color` varchar(255) not null default '#3B82F6', `uppercase_labels` tinyint(1) not null default '1', `no_branding` tinyint(1) not null default '0', `hide_title` tinyint(1) not null default '0', `submitted_text` text not null default 'Amazing, we saved your answers. Thank you for your time and have a great day!', `dark_mode` varchar(255) not null default 'auto', `webhook_url` varchar(255) null, `send_submission_confirmation` tinyint(1) not null default '0', `logo_picture` varchar(255) null, `cover_picture` varchar(255) null, `redirect_url` varchar(255) null, `custom_code` text null, `notification_emails` text null, `theme` varchar(255) not null default 'default', `database_fields_update` json null, `width` varchar(255) not null default 'centered', `transparent_background` tinyint(1) not null default '0', `closes_at` timestamp null, `closed_text` text null, `notification_subject` varchar(255) not null default 'We saved your answers', `notification_body` text not null default '<p>Hello there 👋 <br>This is a confirmation that your submission was successfully saved.</p>', `notifications_include_submission` tinyint(1) not null default '1', `use_captcha` tinyint(1) not null default '0', `can_be_indexed` tinyint(1) not null default '1', `password` varchar(255) null, `notification_sender` varchar(255) not null default 'OpenForm', `tags` json not null default '[]') default character set utf8mb4 collate 'utf8mb4_unicode_ci')

Thanks :)

[chiragnotionforms]

@mchlbowyer Hope you are using latest version of main branch, If not then please pull it and then try again.

Here is my local server information

Can you please tell me your steps for setup this repo ? So I can guide you properly.

Thanks :)

[amitavroy]

Let me see if I can create a small video and reference it here. I am getting the same error as @mchlbowyer mentioned.

[mchlbowyer]

@chiragnotionforms I've found the difference - MariaDB allows defaults for BLOB, TEXT, GEOMETRY and JSON column types, however MySQL does not. Your local environment is using MariaDB which is why it is working OK for you. I am using MySQL and I am presuming @amitavroy is too.

~~On another note, the tests are geared towards PostgreSQL, so at some point these will need updating to support MySQL/MariaDB too.~~ - The tests do not work with MySQL however they do with MariaDB.

[hncvj]

I'm also using MySQL and the migrations worked fine. The image uploads aren't working and giving 500 internal server error possibly due to the symlink issue or permissions issue to the storage folder, rest whole system looks working fine.

[mchlbowyer]

@hncvj have you tried running php artisan storage:link ?

[hncvj]

@mchlbowyer Doing that. Hold on

[hncvj]

@mchlbowyer Can you tell me how to avoid S3 and use Local/Public for file storage?

[mchlbowyer]

@hncvj best to look at #62 as it's more related to your issue.

[mhphilip]

I've got S3 working, but I wouldn't mind or even prefer to be able to use a local storage driver as well.

And as far as the MySQL compatability, I ran into the same issues and tried to fix it. I got the migrations working but had trouble with the default values that the rules assumed.

I ended up using a PostgreSQL db instead. That one worked out of the box...

[amitavroy]

I was able to fix them till quite a lot by adding model mutators. However, there is one migration which is adding indexes and stuff. And that's where the stuff fails.

It's a lot to do with the entire package and hence I am stuck with my PR or else, I would have merged

[erik-dekker]

Running into the same issue with MySQL 8.0.27

OpnForm main 1h1m ➜ php artisan migrate

   WARN  The database 'opnform' does not exist on the 'mysql' connection.

  Would you like to create it? (yes/no) [no]
❯ y

   INFO  Preparing database.

  Creating migration table ............................................................................................................... 24ms DONE

   INFO  Running migrations.

  2014_10_12_000000_create_users_table ................................................................................................... 23ms DONE
  2014_10_12_100000_create_password_resets_table ......................................................................................... 15ms DONE
  2017_12_07_122845_create_oauth_providers_table ......................................................................................... 36ms DONE
  2019_05_03_000001_create_customer_columns .............................................................................................. 23ms DONE
  2019_05_03_000002_create_subscriptions_table ........................................................................................... 18ms DONE
  2019_05_03_000003_create_subscription_items_table ...................................................................................... 26ms DONE
  2019_08_19_000000_create_failed_jobs_table ............................................................................................. 14ms DONE
  2021_05_17_142505_create_workspaces_table .............................................................................................. 17ms DONE
  2021_05_19_140326_create_forms_table .................................................................................................... 2ms FAIL

   Illuminate\Database\QueryException

  SQLSTATE[42000]: Syntax error or access violation: 1101 BLOB, TEXT, GEOMETRY or JSON column 'submit_button_text' can't have a default value (SQL: create table `forms` (`id` bigint unsigned not null auto_increment primary key, `workspace_id` bigint unsigned not null, `title` varchar(255) not null, `slug` varchar(255) not null, `properties` json not null, `created_at` timestamp null, `updated_at` timestamp null, `notifies` tinyint(1) not null default '0', `description` text null, `submit_button_text` text not null default 'Submit', `re_fillable` tinyint(1) not null default '0', `re_fill_button_text` text not null default 'Fill Again', `color` varchar(255) not null default '#3B82F6', `uppercase_labels` tinyint(1) not null default '1', `no_branding` tinyint(1) not null default '0', `hide_title` tinyint(1) not null default '0', `submitted_text` text not null default 'Amazing, we saved your answers. Thank you for your time and have a great day!', `dark_mode` varchar(255) not null default 'auto', `webhook_url` varchar(255) null, `send_submission_confirmation` tinyint(1) not null default '0', `logo_picture` varchar(255) null, `cover_picture` varchar(255) null, `redirect_url` varchar(255) null, `custom_code` text null, `notification_emails` text null, `theme` varchar(255) not null default 'default', `database_fields_update` json null, `width` varchar(255) not null default 'centered', `transparent_background` tinyint(1) not null default '0', `closes_at` timestamp null, `closed_text` text null, `notification_subject` varchar(255) not null default 'We saved your answers', `notification_body` text not null default '<p>Hello there 👋 <br>This is a confirmation that your submission was successfully saved.</p>', `notifications_include_submission` tinyint(1) not null default '1', `use_captcha` tinyint(1) not null default '0', `can_be_indexed` tinyint(1) not null default '1', `password` varchar(255) null, `notification_sender` varchar(255) not null default 'OpenForm', `tags` json not null default '[]') default character set utf8mb4 collate 'utf8mb4_unicode_ci')

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:760

[dgillier]

Same error here, Anyone with a solution to have this run on mysql ?

Thanks ! Denis

[JhumanJ]

We would accept a PR but we don't have short term plans on working on this.