All Collections
Troubleshooting Tips
SQL Troubleshooting Initial Setup
SQL Troubleshooting Initial Setup
John Pauler avatar
Written by John Pauler
Updated over a week ago

Hey everyone, we put this one together to help with some of the most common issues folks run into when they are building the initial schemas and tables for our SQL courses.

Immediately below is a list of the most common errors people run into. Find the error message or issue that's got you stuck, and then scroll down to read more about that issue in detail below.


COMMON MYSQL ERRORS

  1. [WinError 32] The process cannot access the file because it is being used by another process:
    ​

  2. Error Code: 2006. MySQL server has gone away
    ​

  3. Error Code: 2013. Lost connection to MySQL server during query
    ​

  4. Error executing SQL script. '>' not supported between instances of 'NoneType' and 'int'
    ​

  5. Error Code: 1292. Incorrect datetime value
    ​

  6. "My numbers are slightly off from the videos" (timezone discrepancy)
    ​

  7. "I built the data tables successfully, but they don't have data in them"
    ​

  8. "I’m trying to use this course with a non-MySQL flavor of SQL" (like T-SQL, PostgreSQL, SQLite, etc)

Now that you've found your error on the list above, scroll down for more detail:


​

[WinError 32] The process cannot access the file because it is being used by another process:

THE PROBLEM: this is the result of a bug that has surfaced in some recent versions of MySQL Workbench, which specifically impacts the RUN SCRIPT option. The good news, is OPEN SCRIPT is not impacted, so we can easily get around it.


​THE SOLUTION: instead of using RUN SCRIPT to open the create script file, use OPEN SCRIPT instead, and then run the script from the editor window.

Error Code: 2006. MySQL server has gone away

THE PROBLEM: You're trying to run a script that's too big for your max_allowed_packet settings. In plain English... it's a huge file and your Workbench settings won't accept it. Huge datasets are awesome for analysis, but can sometimes be cumbersome to handle.


​THE SOLUTION: Adjust your max_allowed_packet setting using the code below, then close Workbench and re-open it (re-opening let's the Workbench global setting take hold). Note: #1 below will adjust your max packet, and #2 and #3 will help you with some other issues proactively while you're at it.
​

-- 1) adjusting max packet size to allow large files to run
SET GLOBAL max_allowed_packet = 1073741824;

-- 2) adjusting your SQL mode to allow invalid dates and use a smarter GROUP BY setting
SET GLOBAL SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES,ONLY_FULL_GROUP_BY';

-- 3) adjusting your timeout settings to run longer queries
SET GLOBAL connect_timeout=28800;

SET GLOBAL wait_timeout=28800;

SET GLOBAL interactive_timeout=28800;


​

Error Code: 2013. Lost connection to MySQL server during query

THE PROBLEM: Your query is taking too long to run and it is bumping into your timeout settings. When this happens, Workbench kills the query and throws an error.


​THE SOLUTION: Adjust your timeout settings to be able to accommodate the longer running query, by running the code below, then close Workbench and re-open it. NOTE: the 3 lines under #3 below are the lines adjusting the timeout, and the other code will proactively address other potential issues. I recommend you run the entire code block.
​

-- 1) adjusting max packet size to allow large files to run
SET GLOBAL max_allowed_packet = 1073741824;

-- 2) adjusting your SQL mode to allow invalid dates and use a smarter GROUP BY setting
SET GLOBAL SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES,ONLY_FULL_GROUP_BY';

-- 3) adjusting your timeout settings to run longer queries
SET GLOBAL connect_timeout=28800;

SET GLOBAL wait_timeout=28800;

SET GLOBAL interactive_timeout=28800;

Error executing SQL script. '>' not supported between instances of 'NoneType' and 'int'

THE PROBLEM: You're running an older version of the create script that was using TIMESTAMPS, which caused an issue here with newer versions of MySQL. We've updated the script to use DATETIME in place of TIMESTAMP, which has solved the issue.


​THE SOLUTION: Make sure you are using the updated versions of the create scripts, which include _vApril2022 at the end of the file name. If you are using the updated version and still having a similar issue, please get in touch with me and I'll look into it.

Error Code: 1292. Incorrect datetime value

THE PROBLEM: Your machine is reading one or more of the values you are trying to import as incorrect. This can happen for a number of reasons, like local daylight savings time differences, etc.


​THE SOLUTION: adjust your Workbench settings to allow invalid dates, and the database will important despite the couple of dates your system doesn't like. This won't hurt anything else. Run the following code block, then close Workbench and re-open it. Then run the create script again. It should work for you now.
​

-- 1) adjusting max packet size to allow large files to run
SET GLOBAL max_allowed_packet = 1073741824;

-- 2) adjusting your SQL mode to allow invalid dates and use a smarter GROUP BY setting
SET GLOBAL SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES,ONLY_FULL_GROUP_BY';

-- 3) adjusting your timeout settings to run longer queries
SET GLOBAL connect_timeout=28800;

SET GLOBAL wait_timeout=28800;

SET GLOBAL interactive_timeout=28800;

"My numbers are slightly off from the videos" (timezone discrepancy)

THE PROBLEM: This was a legacy problem with an older version of our create script which was attempting to prescribe timezone to the data. This should be entirely fixed as long as you are using the _vApril2022 version of the create scripts.


​THE SOLUTION: Use the _vApril2022 version of the create scripts and you should be okay. If you are still experiencing this issue, please let me know and I will investigate with you.

"I built the data tables successfully, but they don't have data in them"

THE PROBLEM: The create script ran, but with errors. The portion of the create script that creates the tables worked fine (this comes at the beginning of the script) but before the data gets inserted into those tables (which happens later) there was some error.


​THE SOLUTION: You'll need to figure out which error you are encountering. First, DROP the schema(s) that you have created so you can start fresh. Then, run the create script again, paying attention to the response you are seeing at the bottom of Workbench. Look for Error messages and read them. Then consult with this list. The error you are seeing is likely on this list.

"I’m trying to use this course with a non-MySQL flavor of SQL" (like T-SQL, PostgreSQL, SQLite, etc)

THE PROBLEM: You're trying to use a script (and a course) that was designed specifically for MySQL. While MySQL and the other variants are all built on the same universal standard and are very similar, they are not identical and have minor syntax differences which can cause issues. We are unable to support students using other SQL flavors for this particular course, so you will need to download the free software MySQL Community Server and MySQL Workbench in order to participate in this course.


​THE SOLUTION: Download the free software MySQL Community Server and MySQL Workbench. We walk through how to download these programs in the course videos. You will need to download these specific programs to participate in this course. If you are unable to download software, contact your system administrator.
​
​
​
​

Did this answer your question?