How to temporarily turn SQL strict mode off in phpMyAdmin

Pre-pending the query with SET SESSION sql_mode = ”; worked for me:

Note that executing multiple queries separately did not work. Here I ran SET SESSION sql_mode =” first and then SELECT @@SESSION.sql_mode, which showed that strict mode was still active:

Example

After the update of a MySQL server on a shared hosting account, strict mode was enabled with the NO_ZERO_DATE option and there was no way to change this. This meant that – among other things – I had to remove a default timestamp value of “0000-00-00 00:00:00” from multiple columns:

However this failed with error #1067 as other columns still used this invalid default. In other words, altering column A to make it compliant failed as column B was not compliant yet:

I’m not sure what the reasoning behind this is. However, temporarily disabling strict mode by pre-pending the query with

SET SESSION sql_mode = '';
let me make the necessary changes:

Result:

While this probably comes too late to help Pete, I hope it can help you.

How to Display Full Query Results in phpMyAdmin

In phpMyAdmin, the results of some queries can be truncated, as shown below:

phpMyAdmin: truncated text

This isn’t due to the size of the browser window or any error on your part; phpMyAdmin simply truncates the text.

The solutions is very simple:

  1. Click on the + Options link above the results.
  2. In the options menu, select Full texts.
  3. Click on Go to re-execute your query.
phpMyAdmin: Full texts option

Once you’ve done this, phpMyAdmin will display the complete results of your query:

phpMyAdmin: Full text displayed

MySQL Workbench: This file type is not supported, valid options are CSV, JSON

The problem: You’re trying to use the Table Data Import Wizard in MySQL Workbench to import a CSV file into a database and it’s giving you the following error:

This file type is not supported, valid options are CSV, JSON

This file type is not supported, valid options are CSV, JSON

However, you are sure that the file is a CSV file.

The solution that worked for me (with MySQL Workbench 8.0): Renaming the CSV file from DATA.CSV to DATA.csv. Yes, MySQL Worbench did not like the capitalization of the file extension.