MariaDB Error (two single quotes wrapped around field) with workaround

[almightynassar]

Similar to issue #3947 but the failing migration file is different. I have provided a workaround that get's my installation up and running, and should work for others with the same issue. Admittedly, degrading the MariaDB version to 10.0.0 as proposed in #3947 might be an easier option for most.

Expected Behavior (or desired behavior if a feature request)

Migrations complete successfully on MariaDB 10.2.13.

---

Actual Behavior

Migrations fail because MariaDB seems to wrap a single quotation mark around the DEFAULT field when it is edited. When a field is edited twice, it wraps another single quotation mark around the field

---

Please confirm you have done the following before posting your bug report:

• I have enabled debug mode
• I have read checked the Common Issues page

---

Provide answers to these questions:

• Fresh install using docker (docker version 17.12.0-ce)
• Snipe-IT: snipe/snipe-it:latest docker container running v4.1.14 - build 3446 (master)
• PHP: 7.0.28-0ubuntu0.16.04.1 (cli) ( NTS )
• Mariadb: mariadb:latest docker container running 10.2.13-MariaDB-10.2.13+maria~jessie
• OS: Linux server 4.9.0-4-amd64 Fix depreciation math #1 SMP Debian 4.9.65-3+deb9u1 (2017-12-23) x86_64 GNU/Linux
• Webserver: jwilder/nginx-proxy:latest docker container
• Error occurs on both the 2nd step of Pre-Flight setup or via running php artisan migrate
• Data was manually edited in the database to achieve the workaround

---

Workaround Summary:

1. Set up snipeit as normal up to the first Pre-Flight screen.
2. Fill in as required, and save.
3. I prefer to then run php artisan migrate because it gives me a shorter error message, but you can just click Next for the second Pre-Flight stage (with the APP_DEBUG page turned on!). My (snipped) migration error message was:

In Connection.php line 647:
                                                                                              
  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL synt  
  ax; check the manual that corresponds to your MariaDB server version for the right syntax   
  to use near '''' at line 1 (SQL: ALTER TABLE assets CHANGE _snipeit_mac_address _snipeit_m  
  ac_address_1 VARCHAR(191) DEFAULT ''NULL'')                                                 
...

4. Make sure that you have the two single quotes problem in your SQL statement (you can highlight an individual ' inside of '', while you cannot with the double quote "). In my error, the issue is because of the two single quotes wrapped around DEFAULT ''NULL''.
5. Open up your preferred client to your MariaDB server and edit the field without the two single quotes (repeat, *remove all quotes. MariaDB will wrap it with quotes and cause more issues). For example:

ALTER TABLE snipeit.assets MODIFY COLUMN `_snipeit_mac_address` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL NULL ;

• You might need to run a modified version of the query to avoid an error with the migration tool. This is what I had to do to get the migrate tool to 'skip' the mac_address column and complete the migration:

ALTER TABLE snipeit.assets CHANGE `_snipeit_mac_address` `_snipeit_mac_address_1` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL NULL ;

6. Re-run the migration. I only had to do this once, but you may have to go through the process multiple times. If all is well, you can continue Pre-Flight as normal. Otherwise, continue with the following:

• If you get a migration message saying things already exists or can't be removed, we have an issue where part of the transaction was committed while the rest failed. Delete all the created tables in MariaDB so we can start fresh.
• Open up your log file (storage\logs\laravel.log on the docker container) and find the line like what follows (use cat laravel_log.txt | grep Migrator\-\>runUp):

at Migrator->runUp('/var/www/html/database/migrations/2017_01_25_063357_fix_utf8_custom_field_column_names.php', 2, false)

• Move the migration file given by the above and EVERYTHING that comes after it to a temporary location.
• Run the first migration, and then apply the SQL fix you used in Step 5.
• Move all the migration files back and run the second migration.
• If you have multiple migration failures, you can either repeat these steps or just degrade your MariaDB version

---

Detailed Explanation:

Had a few initial teething problems but got to the first pre-flight screen where everything was green except the DEBUG flag. Ran the migrations on the preflight and got a timeout. So I ran a shell on the docker machine and executed php artisan migrate and eventually got the following truncated error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '''' at line 1 (SQL: ALTER TABLE assets CHANGE _snipeit_mac_address _snipeit_mac_address_1 VARCHAR(191) DEFAULT ''NULL'')
...
at Migrator->runUp('/var/www/html/database/migrations/2017_01_25_063357_fix_utf8_custom_field_column_names.php', 2, false)

Note that the final NULL is not in double quotes, but two single quotes. This is seems to be the main cause of the failure.

Running my MariaDB client and navigating to the assets table shows that some of the DEFAULT fields are NULL, while others are 'NULL' with single quotations. The _snipeit_mac_address indeed has it's DEFAULT set to ''NULL'' with two single quotes. Editing this field to have NO single quote seems to have fixed my issue (as MariaDB inserts single quotes for you):

ALTER TABLE snipeit.assets MODIFY COLUMN `_snipeit_mac_address` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL NULL ;

Alternatively I could just use the following to do the required name change manually (the migrate will just skip it):

ALTER TABLE snipeit.assets CHANGE `_snipeit_mac_address` `_snipeit_mac_address_1` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL NULL ;

Running php artisan migrate should no longer have any migration errors (at least it did not for me), and you should be able to proceed with the rest of the Pre-Flight. So far I have no issues creating Assets with MAC addresses (albeit only limited testing).

The following image just shows my final table set-up, with some DEFAULTS set to NULL and others to quoted 'NULL'. The only field I changed was to the mac address column; everything else was already like that when I applied my fix.

[almightynassar]

Uploading my initial error message.

laravel_log.txt

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions!