Fix Error 1138 “Invalid use of NULL value” in MySQL

If you’re getting error 1138 that reads “invalid use of NULL value“, it could be because you’re trying to change the definition of a column to require non-NULL values, but the column contains NULL values.

This can happen when we try to change a column from NOT NULL to NULL. It can also happen when we try to set a column as a primary key, but that column contains NULL values.

To fix this issue, be sure to ensure that the column you’re modifying doesn’t contain any NULL values. Alternatively, if the column should be able to accept NULL values, then you should change your column definition to allow NULL values.

Continue reading

Fix “column … can only be updated to DEFAULT” in PostgreSQL

If you’re getting an error in PostgreSQL that tells you that a column “…can only be updated to DEFAULT” with detail that explains that it “…is an identity column defined as GENERATED ALWAYS“, it’s probably because you’re trying to update an identity column with your own value, but the identity column was created with the GENERATED ALWAYS option.

Continue reading

6 Ways to Fix “nextval: reached minimum value of sequence” in PostgreSQL

If you’re getting an error that reads something like “nextval: reached minimum value of sequence “sequence1” (-3)” in PostgreSQL, it’s probably because you’re trying to generate a new value from a descending sequence, but the sequence has already reached its minimum value.

We have a number options when it comes to dealing with this issue.

Continue reading

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

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

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

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

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

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

Fix “LIMIT is only supported in combination with either BYSCORE or BYLEX” in Redis

If you’re getting an error that reads “LIMIT is only supported in combination with either BYSCORE or BYLEX” it’s probably because you’re trying to use the LIMIT clause without using the BYSCORE or BYLEX arguments.

This can happen when using the VRANGE command without either of the BYSCORE or BYLEX arguments.

To fix this issue, use either the BYSCORE or BYLEX argument when using the command. This obviously means that we need to adjust our query so that it’s querying by score or lexicographically.

Continue reading

Fix Error 6358 “…is not a valid style number when converting to XML” in SQL Server

If you’re getting error msg 6358 which reads something like 300 is not a valid style number when converting to XML, it’s probably because you’re trying to convert a value to XML, but the style that you’re specifying isn’t supported for conversions to that data type.

It’s not that the conversion can’t happen, it’s just that it can’t happen using the style that you’re specifying.

Continue reading