In MySQL, we can use the sys.sys_get_config() function to get a configuration option value from the sys_config table.
Category: DBMS
Database Management Systems
Fix ERROR 3580 “There is a circularity in the window dependency graph” in MySQL
If you’re getting error 3580 in MySQL, which reads “There is a circularity in the window dependency graph“, it’s probably because you’re using named windows that refer to each other in a way that creates a circular reference.
To fix this issue, make sure you don’t create a circular reference when referring to multiple named windows.
Continue readingHow to Change a CHECK Constraint’s Enforcement State in MySQL
When we create or alter a CHECK constraint in MySQL, we have the option of setting it to ENFORCED or NOT ENFORCED. This determines whether or not MySQL will enforce the rules of the constraint whenever data that violates the constraint tries to enter the database.
We can use the ALTER TABLE statement to change the enforcement state of an existing CHECK constraint in MySQL, as shown in the following example.
Fix ERROR 1140 “In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column…” in MySQL
If you’re getting MySQL error 1140, which reads something like “In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ‘world.City.District’; this is incompatible with sql_mode=only_full_group_by“, it could be that you need to introduce a window function to the query.
This error can happen when we want multiple rows to contain aggregate values. For example, we might want to return subtotals of all rows within a group of rows. A window function can help us achieve this outcome.
Continue reading2 Ways to Verify Whether a CHECK Constraint is Enforced in MySQL
When we create a CHECK constraint in MySQL, we have the option of defining it as ENFORCED or NOT ENFORCED.
MySQL CHECK constraints are enforced by default, so unless we explicitly define the constraint as NOT ENFORCED, it will automatically be enforced.
In any case, we can use either of the following two methods to check whether or not an existing CHECK constraint is enforced.
Fix ERROR 3582 “Window ‘…’ has a frame definition, so cannot be referenced by another window” in MySQL
If you’re getting MySQL error 3582, which reads something like “Window ‘w1’ has a frame definition, so cannot be referenced by another window“, it’s probably because you’re referencing a named window that has a frame clause.
This can happen when we use the WINDOW clause to define a named window for a window function, and we include a frame clause in that window. When we then try to reference that window, MySQL tells us that we can’t, due to the frame clause.
How to Add Totals and Subtotals When Using GROUP BY in MySQL
Sometimes when we use the SQL GROUP BY clause to group queries, it would be nice to have totals and subtotals included in the query results.
For example, if our query returns cities and their populations, grouped by the city’s district and perhaps also grouped by country, it would be nice to include subtotals for each district (based on the aggregate populations of all cities in the district). And it might also be nice to have the total population of each country, based on the same aggregate data.
Another way of putting it is that we want to get a summary of the summary. Or an aggregate of the aggregate. This is sometimes referred to as “super aggregate”.
Fortunately, in MySQL we can use the WITH ROLLUP modifier of the GROUP BY clause to achieve exactly that.
Fix ERROR 3583 “Window ‘…’ cannot inherit ‘…’ since both contain an ORDER BY clause” in MySQL
If you’re getting MySQL error 3583, which reads something like “Window ‘<unnamed window>’ cannot inherit ‘wf’ since both contain an ORDER BY clause“, it’s probably because you’re using an ORDER BY clause when referring to a named window that also has its own ORDER BY clause.
To fix this issue, don’t use the ORDER BY clause when referring to a named window that already has an ORDER BY clause in its definition.
Understanding the WINDOW Clause in MySQL
In MySQL, the WINDOW clause is an optional clause that we can use to create a named window. The named window can then be referred to from a window function.
Many SQL developers define their window functions directly in the OVER clause. But that’s not the only way to do it. We can also define them in a WINDOW clause, and then refer to them in the OVER clause.
When we define the window function in a WINDOW clause, we name it. When we do this, we can refer to that name from the OVER clause. This eliminates the need to include the definition directly inside the OVER clause.
How to Drop a CHECK Constraint in MySQL
If we want to remove a CHECK constraint from a table, but we don’t want to drop the table or column, we can use the ALTER TABLE statement with DROP CHECK.
Once the constraint has been dropped, data can be entered into the table without having to conform to the rules of the CHECK constraint.