Author: Ian

6 Ways to Fix Error 1055 “Expression … of SELECT list is not in GROUP BY clause and contains nonaggregated column…” in MySQL

Posted on by Ian

If you’ve been using MySQL for any decent amount of time, it’s likely you’ll be familiar with error 1055 that reads something like “Expression #N of SELECT list is not in GROUP BY clause and contains nonaggregated column…“, where #N is the expression number of an expression/column in your SELECT list.

This error can occur when we include a column in the SELECT list, but we omit it from the GROUP BY clause.

There are several ways we can go about resolving this issue. Below are six options for dealing with this issue.

Continue reading →

4 Ways to Fix Error 1048 “Column ‘…’ cannot be null” in MySQL

Posted on by Ian

If you’re getting error 1048 that reads something like “Column ‘ColumnName’ cannot be null” (where ColumnName is the name of a column you’re trying to insert data into), then it’s probably because you’re trying to insert a NULL value into that column, but the column has a NOT NULL constraint (which prevents NULL values from being inserted).

We have a few options when it comes to fixing this issue. The most obvious is to ensure we provide a non-NULL value for the column. Alternatively, if the column should be able to accept NULL values, then we can remove the NOT NULL constraint from the column. Another option is to use the IGNORE keyword to ignore the error. And another way to deal with the error is to disable strict mode.

Continue reading →

MySQL IGNORE Clause Explained

Posted on by Ian

In MySQL, we can use the IGNORE clause in statements that change data in order to ignore certain errors that might occur had we not used it. When IGNORE is used, such errors are downgraded to warnings.

For example, we can use IGNORE in an INSERT statement to ignore any errors we might normally get if we tried to insert a NULL value into a NOT NULL column. In such a case, MySQL won’t return an error. Instead, it will deal with the issue in another way, and provide us with a warning.

If we have strict mode enabled, we can use IGNORE to force MySQL to act as though strict mode is disabled. However, IGNORE can also be used to downgrade certain errors regardless of the strict mode setting.

Continue reading →

How to Disable Strict Mode in MySQL

Posted on by Ian

Strict mode controls how MySQL handles invalid or missing values in statements that change data, such as in INSERT and UPDATE statements.

Strict mode (aka strict SQL mode) is in effect if either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled.

In MySQL 8.0, the default SQL mode includes STRICT_TRANS_TABLES, which enables strict SQL mode for transactional storage engines, and when possible for nontransactional storage engines.

We can disable strict mode in our session by removing it from our @@sql_mode variable. We can also disable strict mode at server startup, so that we don’t have to do it at runtime.

Continue reading →

Fix “ERROR:  step size cannot equal zero” When Creating a PostgreSQL Series

Posted on by Ian

If you’re getting an error that reads “step size cannot equal zero” when creating a series with the generate_series() function in PostgreSQL, it’s because you’re using a step size of 0 (i.e. zero).

The generate_series() function doesn’t accept a zero step size.

To fix this issue, either use a non-zero step size, or remove the step size altogether (so that the default step is used).

Continue reading →

MySQL GROUPING() Function Explained

Posted on by Ian

In MySQL, the GROUPING() function allows us to identity which rows have been generated by the WITH ROLLUP modifier of the GROUP BY clause.

By default, WITH ROLLUP outputs NULL as the value that represents all aggregate values. By this, I mean it doesn’t provide us with a nice easy to read label. It simply outputs NULL. This makes it more difficult for us to distinguish between normal rows and super aggregate rows that were generated by WITH ROLLUP.

Continue reading →

How to Remove a SQL Mode from sql_mode in MySQL

Posted on by Ian

MySQL can operate in different SQL modes. These can be set at server startup or at runtime.

DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements. Users can also set their own SQL mode at the session level via the @@sql_mode system variable.

By default, the sql_mode system variable contains a list of SQL modes that are applied.

As a user, we can modify our sql_mode system variable as required at runtime. This article demonstrates how we can remove a SQL mode from our sql_mode system variable.

Continue reading →

How to Remove NOT NULL from a Column in MySQL

Posted on by Ian

NOT NULL constraints are an important tool for maintaining data integrity in SQL databases. However, we may occasionally find that a NOT NULL constraint gets in the way of a legitimate operation that we want to perform, and it prevents us from entering data. In such cases, we may need to remove the NOT NULL constraint in order to proceed.

To remove a NOT NULL constraint in MySQL, we use the ALTER TABLE statement to redefine the relevant column without the NOT NULL constraint.

Continue reading →