Tag: how to

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 →

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 →

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

Posted on by Ian

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

Posted on by Ian

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 →

Fix Error Msg 6855 “Inline schema is not supported with FOR XML PATH” in SQL Server

Posted on by Ian

If you’re getting error Msg 6855 in SQL Server that reads “Inline schema is not supported with FOR XML PATH“, it’s because you’re trying to add an inline schema to an XML document that you’re generating using PATH mode with the FOR XML clause.

As the message alludes to, PATH mode doesn’t support the ability to create an inline schema when using the FOR XML clause.

To fix this issue, either use a different mode to generate the XML with an inline schema (specifically, use either AUTO or RAW mode), or don’t generate an inline schema at all.

Continue reading →