Hot answers tagged

37 votes
Accepted

How do I resolve this error, "ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'"?

Simply run mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p $ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p Enter password: Warning: Unable to load '/usr/share/...
Evan Carroll's user avatar
  • 62.1k
26 votes
Accepted

PostgreSQL 9.3.13, How do I refresh Materialised Views with different users?

You can do this with a function that runs in the security context of its owner. Function that refreshes the view (create it with the user that owns the MV/table): CREATE OR REPLACE FUNCTION ...
Philᵀᴹ's user avatar
  • 31.6k
23 votes

The operating system returned error 21 (The device is not ready.)

Every time I reboot windows, for some databases comes out this error. (OS Error 21 - Device not ready) This is due to a disk being offline or not being online either at the time SQL Server started, ...
Sean Gallardy's user avatar
20 votes

Can't remote access MySQL server running on Mac OS X

I have been banging my head against this error today on OSX Yosemite with MySQL 5.7 recently updated with Homebrew. Following suggestions on StackOverflow and elsewhere, I hunted around after my.cnf ...
Emma Burrows's user avatar
19 votes

What could cause SQL Server to deny execution of a SP at first, but allow it later with no privileges change?

If the Windows user account was added to an Active Directory (AD) group in the meantime, and that AD group had permission to run the procedure, then that could create this scenario where a user gained ...
Josh Darnell's user avatar
16 votes
Accepted

What is the maximum number of local variables that can participate in SET operation?

Msg 8631, Level 17, State 1, Line xxx Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it. This error occurs with long ...
Paul White's user avatar
  • 82.9k
15 votes
Accepted

SQL Server on Linux hangs on initial startup, no errors, and no new / updated ErrorLog file

This ended up as a case of not being careful when working as root. I had been researching if SQLCLR on Linux would have access to the app.Config file like it does in Windows (sadly, it does not: SQL ...
Solomon Rutzky's user avatar
14 votes

The operating system returned error 21 (The device is not ready.)

I think I've found the cause. Most likely the problem is due to "Fast startup" power options. It'a a Windows technique to reduce the boot time; Fast Startup combines elements of a cold shutdown and ...
Max's user avatar
  • 461
14 votes

What could cause SQL Server to deny execution of a SP at first, but allow it later with no privileges change?

Another possible root cause would be a misunderstanding of how a stored procedure definition is terminated. Take this for example: CREATE OR ALTER PROCEDURE dbo.MyTestProc AS BEGIN SELECT result =...
Hannah Vernon's user avatar
  • 69.7k
14 votes
Accepted

Getting an "Incorrect syntax near '(' " error message when using the standard create table using a select in SQL Server

Congratulations! You found SQL dialects. There are many SQL database systems, and each system has its own SQL dialect. Sometimes the difference is small, sometimes it is big. Usually, the difference ...
White Owl's user avatar
  • 645
13 votes

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values

You can use the following syntax: insert into tableName (column1) select '1' union all select '2' union all ... select '1000' union all select '1001' -- Last line should not contain union all and so ...
Yaroslav Fomenko's user avatar
13 votes
Accepted

"Invalid object name" error when executing query

I'd guess the problem is your SQL Server login does not have it's "default database" properly set. This will show you what your "default database" is set to: SELECT sp.name , sp....
Hannah Vernon's user avatar
  • 69.7k
13 votes

Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade

Maybe this information is useful. https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-23.html Note This release includes a change to the innodb_index_stats and innodb_table_stats system tables. ...
Chrysweel's user avatar
  • 239
13 votes

string or binary data would be truncated in table '******', column '******'. Truncated value: '******'

It looks like this table was defined with dynamic data masking, and the user that the application uses to access the database doesn't have permission to view masked data (which is good!). This is why ...
Josh Darnell's user avatar
12 votes

Why does this query fail when I run it from a SQL Server job?

It fails because you aren't using a language neutral datetime format. You are assuming that SQL Server understands that the day comes first, then the month and finally the year. When you run this ...
Tibor Karaszi's user avatar
11 votes

error 2013 (hy000) lost connection to mysql server during query while load of mysqldump

If all the other solutions here fail - check your syslog (/var/log/syslog or similar) to see if your server is running out of memory during the query. Had this issue when innodb_buffer_pool_size was ...
A_funs's user avatar
  • 219
11 votes

What could cause SQL Server to deny execution of a SP at first, but allow it later with no privileges change?

Perhaps the user had an invalid Kerberos ticket (for example expired), which caused the two "access denied" errors. At some point in time after that the user's workstation contacted a DC and ...
Greenstone Walker's user avatar
10 votes

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values

For select using VALUES you can go beyond the 1000 limit INSERT INTO tableName (column1) SELECT t.column1 FROM (VALUES (1), (2), (3), ... (1000), (1001) ) AS t(column1)
Ako's user avatar
  • 201
10 votes
Accepted

Arithmetic overflow error

The rules that SQL Server uses to derive the type of literals are complex, with some odd behaviours that are maintained these days for backward compatibility. For example, the derived type may depend ...
Paul White's user avatar
  • 82.9k
10 votes

Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade

Do you have any clue what is going on when the warning is being generated? It sounds like some form of version mismatch. Do you have any plug ins? Please file a bug at bugs.mysql.com. Meanwhile, do ...
Rick James's user avatar
  • 76.9k
9 votes

Can't remote access MySQL server running on Mac OS X

Running locate my.cnf found the file /usr/local/etc/my.cnf Edit that file and set bind-address = 0.0.0.0 Restart mysql: brew services restart mysql
Pablo Díaz Ogni's user avatar
9 votes

how to solve this warning in select

There's nothing to fix, really. The message refers to this computed column in the Sales.SalesOrderHeader table: [SalesOrderNumber] AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ...
Josh Darnell's user avatar
8 votes
Accepted

mysql update query error Code 1054 unknown column in field list

The error message is quite clear. The table krneki_2 does not have a column named id_naslov. Unless there is some corruption in system tables or a bug, there is no question about that. So we have to ...
ypercubeᵀᴹ's user avatar
8 votes

PostgreSQL 9.3.13, How do I refresh Materialised Views with different users?

@Philᵀᴹ's second function attempt is vulnerable to SQL injection by using: SELECT refresh_mv_xxx('example_mview_name with data; TRUNCATE TABLE example_table_name CASCADE; REFRESH MATERIALIZED VIEW ...
ndrwnaguib's user avatar
8 votes
Accepted

How to get only the first error with psql --single-transaction

You may just ask it to stop on the first error: $ psql -v ON_ERROR_STOP=on --single-transaction ...
Daniel Vérité's user avatar
8 votes
Accepted

No such column when column exists

The problem was that I had an space at the end of the name of the columns, solved the problem by deleting such spaces. Before: CREATE TABLE IF NOT EXISTS "trackedinfo" ( "id" ...
Victor Castillo Torres's user avatar
8 votes
Accepted

Error when creating a stored procedure in postgreSQL-10

As mentioned in the comments, PostgreSQL-10 does not have stored procedures. Same is true about earlier versions. Workaround alternatives: Upgrade to version 11 or later Use a function Thanks, @...
Granny Aching's user avatar
8 votes

What could cause SQL Server to deny execution of a SP at first, but allow it later with no privileges change?

Related to Josh's answer: An alternative could be that this person was a member of an AD group for which there was an explicit DENY to do the operation. And then this person was removed from the AD ...
Tibor Karaszi's user avatar
8 votes

Constraint in ON CONFLICT clause has no associated index

The ON CONFLICT clause is strictly for handling unique violation and exclusion constraint violation errors, as documented in the manual. There is no similar syntax allowing you to handle foreign key ...
Andriy M's user avatar
  • 22.8k
7 votes
Accepted

How to restore everything (including "postgres" role) from pg_dumpall backup

I ended up using grep for this: egrep -v '^(CREATE|DROP) ROLE postgres;' dump.sql \ | psql -X -v ON_ERROR_STOP=1 -1 ...
11181's user avatar
  • 186

Only top scored, non community-wiki answers of a minimum length are eligible