All Questions
2,050 questions
250
votes
5
answers
310k
views
'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
I am trying to backup mysql using the command
mysqldump -u root -p database_name > backup.sql
, but it is throwing an error:
'Access denied; you need (at least one of) the PROCESS privilege(s)
...
subhanshu kumar
69
votes
8
answers
188k
views
ERROR: no schema has been selected to create in
I am working on an amazon RDS postgresql database where I know there had been some issue with the public schema (maybe it was dropped). But apparently the schema exists, and anyway the problem is not ...
- 793
63
votes
1
answer
64k
views
What is the meaning of filtered in MySQL explain?
As described here in the MySQL docs:
The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows ...
- 685
39
votes
4
answers
46k
views
What is the optimal way to upgrade production RDS instance?
I have MySQL small RDS instance as part of my production system and I want to upgrade it to medium instance with provided IOPS.
As old-school DBA I'm aware about "add slave; promote to master; ...
36
votes
3
answers
78k
views
How to speed up select distinct?
I have a simple select distinct on some time series data:
SELECT DISTINCT user_id
FROM events
WHERE project_id = 6
AND time > '2015-01-11 8:00:00'
AND time < '2015-02-10 8:00:00';
And it takes ...
- 637
35
votes
3
answers
44k
views
How to remove string quotes in MySQL 5.7 for function JSON_EXTRACT?
Other than doing string manipulation after each JSON_EXTRACT, is there a simpler or more correct way to have the JSON_EXTRACT return the string WITHOUT the enclosing quotes?
Or should this question be ...
- 483
34
votes
1
answer
210k
views
What is mysql_native_password?
I was trying to set password for root. When I run:
mysql> SELECT * from mysql.user where User="root";
It shows:
+-----------+------+-------------+-------------+-------------+-------------+-------...
- 443
34
votes
3
answers
43k
views
Why do I get a PostgreSQL permission error when specifying a tablespace in the "create database" command?
When I create a database in PostgreSQL without explicitly specifying a default tablespace the database is created without issue (I'm logged in as the pgsys user):
postgres=> create database rich1;
...
- 441
32
votes
3
answers
65k
views
GRANT USAGE on all schemas in a database?
I want to GRANT USAGE to a user/role for a given database. The database has many schemas.
I know there's an ON ALL TABLES IN SCHEMA, but I want "all schemas". I tried GRANT USAGE .. ON DATABASE, but ...
- 431
32
votes
2
answers
24k
views
Amazon RDS for MySQL vs installing MySQL on an Amazon EC2 instance
At work, we host all our webservers on Amazon EC2 and usually have used MySQL databases installed on the same box as our Apache webserver, and communicated with them on localhost. We now face a need ...
- 1,095
26
votes
1
answer
43k
views
Multiple databases in same AWS RDS PostgreSQL instance
I have an existing database in a RDS AWS PostgreSQL instance. I need to create one more database in this instance as spinning up a separate cluster will be expensive. There doesn't seem to be any ...
- 565
23
votes
1
answer
48k
views
InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT
I am working with mysql version 5.7.14 (homebrew installation on OS X El Capitan). My system did not shutdown properly while the mysql was running and after rebooting when i try starting the mysql ...
22
votes
2
answers
21k
views
Does amazon rds PostgreSQL require vacuum?
It isn't clear if auto vacuum is running in rds. Thanks!
- 221
22
votes
1
answer
15k
views
PostgeSQL 9.3: Is it safe to stop VACUUM FULL?
I'm using PostgreSQL 9.3 on RDS. Once in a while, I run a VACUUM FULL operation on the database. However, such operation can take quite a while and it blocks other tables, so the need to stop the ...
- 11.9k
22
votes
2
answers
12k
views
Get postgres snapshot from Amazon RDS
I can not find a simple way to get a copy of the nightly backup I take of my Amazon RDS postgres instance so I can restore it on my local machine to play with.
Right now I'm doing a pg_dump when I ...
- 351
21
votes
1
answer
10k
views
CPU usage constantly high after upgrading from Postgres 9.5 to 9.6 to 10.6
I upgraded my RDS instance (db.t2.medium) from Postgres 9.5 to 9.6 couple of days back. Since the upgrade, the CPU usage was almost 100% all the time. This brought down my application. Presuming that ...
- 395
18
votes
2
answers
28k
views
Should I increase max_connections in AWS RDS t1-micro for MySQL?
I have an AWS RDS t1-micro running MySQL 5.5. It gives me too many connections error. I checked and it allows 34 maximum connections concurrently. What I have read is that i can increase this max ...
- 241
18
votes
2
answers
21k
views
Privileges for database owner; application user
Quick version:
What command should I issue to enable a database owner to allow it to access tables in this database and can this be done from that owner's account?
Longer Version:
I am creating a ...
- 293
17
votes
1
answer
17k
views
How to temporarily disable foreign keys in Amazon RDS PostgreSQL?
I'm migrating existing test environment to Amazon RDS PostgreSQL. The test framework has a feature of reloading data in certain tables to an earlier state. For this it disables foreign keys, deletes ...
- 581
16
votes
3
answers
33k
views
How can I determine how many IOPs I need for my AWS RDS database?
I am migrating part of a mysql database into AWS. The data in question is 99% write, and each row has about 1k of varchar fields, a datetime and 4 ints.
I estimate that we will need between 20-25k ...
- 1,232
16
votes
3
answers
9k
views
Replicate RDS-MySQL to a non-amazon host?
I have a fairly large MySQL (5.1) database running in Amazon as an RDS instance. I'm working on migrating it out of Amazon. To do that smoothly I'd like to set up the new hardware as a read-only ...
- 825
15
votes
1
answer
26k
views
Best way to pg_dump PostgreSQL on RDS to S3
I need to dump my PostgreSQL on RDS to a file on S3 periodically (to use it elsewhere than AWS).
Is there a better way than running a script that runs pg_dump and uploads results to S3 using e.g. ...
- 313
15
votes
2
answers
12k
views
Why use RDS over EC2 for Postgres?
I have a database currently running on EC2. As I have to move it to a bigger machine the question of using RDS came up.
Pricing:
I can get an EC2 c3.large instance with two SSDs (16GB each), two ...
- 397
15
votes
2
answers
4k
views
Neither DBCC FREEPROCCACHE nor DBCC FREESYSTEMCACHE('SQL Plans') do anything to free CACHESTORE_SQLCP memory
CACHESTORE_SQLCP Sql Plans takes up > 38 GB after a few days.
We are already running with "optimize for ad hoc workloads" option on. (Entity Framework and custom reporting creates a lot of ad hocs!)
...
15
votes
2
answers
6k
views
How can I seamlessly upgrade the major version of an AWS RDS postgres database?
This morning I was involved in upgrading a PostgreSQL database on AWS RDS. We wanted to move from version 9.3.3 to version 9.4.4. We had "tested" the upgrade on a staging database, but the staging ...
- 251
14
votes
1
answer
8k
views
What does KEY SHARE OF in a PostgreSQL statement mean?
Given the following SQL, what does "ONLY", "OPERATOR(pg_catalog.=)" and "FOR KEY SHARE" do?
SELECT 1
FROM ONLY "public"."acmeinstanceinfo" x
WHERE &...
- 543
14
votes
4
answers
17k
views
Why am I getting permission denied on an ownership change?
My current user is pronto
mediapop_staging=> select current_user;
current_user
--------------
pronto
(1 row)
This user a standard super user created on AWS RDS.
mediapop_staging=> \du ...
- 353
14
votes
1
answer
75k
views
invalid default value (mysql 5.7) for timestamp field
EDIT:
When updating an existing database from mysql 5.6 and executing:
UPDATE phppos_register_log SET shift_end = '2015-01-01 00:00:00' WHERE shift_end = '0000-00-00 00:00:00';
This produces:
#1292 ...
- 701
13
votes
2
answers
29k
views
Amazon RDS Postgresql adding new extensions
I want to download jsonbx to RDS instance. RDS PostgreSQL Features Supported shows only built-in features. Does this mean there is no way we can install an extension to RDS which is not in the Feature ...
- 233
13
votes
3
answers
25k
views
MySQL 5.7 Community Server End of Life/Support
Is there any official document that outlines the date till which MySQL Community Server 5.7 will receive security updates/patches? I see that page 20 of this document from Oracle outlines Premier and ...
- 131
13
votes
1
answer
8k
views
Debug query on big table that is sometimes slow
I have a web API that is backed by a Postgres database, and the performance is generally very good. I monitor the performance of both the database and the application as a whole. Most of my queries (...
- 233
13
votes
3
answers
11k
views
AWS RDS MYSQL How do I reduce the DB size? [duplicate]
Possible Duplicate:
How do I reduce the database size on AWS RDS MySQL?
I have a mysql DB on Amazon. It is bigger than I need and I don't want to pay so much.
How do I make it smaller? Their ...
- 302
13
votes
1
answer
2k
views
MySQL deadlock: upsert query acquiring gap lock twice?
I am trying to debug a deadlock scenario which has been puzzling me for quite some time now. I am wondering if someone can shed any light into how this can happen.
Here is the deadlock output:
------...
- 131
12
votes
2
answers
5k
views
Does AWS RDS support MEMORY storage engine?
Title says it all. Strangely can't find a result on this.
- 223
12
votes
2
answers
14k
views
MySQL - UUID/created_at cursor based pagination?
For a large dataset, paginating with an OFFSET is known to be slow and not the best way to paginate. A much better way to paginate is with a cursor, which is just a unique identifier on the row so we ...
- 145
12
votes
6
answers
12k
views
High CPU system time usage on MySQL server
A little backstory, some time ago we started to experience high CPU system time on one of our MySQL databases. This database was also suffering from high disk utilization so we figured that those ...
- 119
12
votes
3
answers
76k
views
Won't start: InnoDB: Corrupted page ... Tablespace was not found
After a server crash (Ubuntu 16.04), when I try to start Mysql (Mysql 5.7) using innodb_force_recovery=0 it doesn't start and error.log shows:
InnoDB: Checksum mismatch in datafile: ./...
- 223
12
votes
2
answers
2k
views
UPDATE STATISTICS options fail in Amazon RDS SQL Server
In Amazon RDS SQL Server (any version/edition), create a temp table and update statistics on it. This works fine:
IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #t;
GO
CREATE TABLE #t (id INT ...
- 43.2k
11
votes
2
answers
11k
views
Upgrade all MySQL columns, tables, and databases from utf8mb3 to utf8mb4
I have thousands of columns across hundreds of tables in about a hundred databases inside a MySQL instance that need to be upgraded from utf8mb3 to utf8mb4. Is there a way to generate ALTER ...
11
votes
1
answer
32k
views
Too Many database connections on Amazon RDS
We are having problems with users running queries/views in Drupal that occasionally cause our site to freeze. The freeze occurs because the query causes the number of database connections to go up to ...
- 111
11
votes
1
answer
7k
views
Does latin1 have performance benefits over utf8?
I have a table in utf8 with > 80M records and one of the columns (char(6) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL) can contain just latin symbols ([a-zA-Z0-9]). Does it have the sense to convert ...
- 288
10
votes
5
answers
20k
views
MySQL :: How to run ANALYZE TABLE for all tables in a database
I'm using MySQL 5.7
How can I run ANALYZE TABLE for all tables in a database. It should work if new tables are added to the database in future.
This is to refresh the statistics of table indexes.
- 203
10
votes
2
answers
969
views
Get incremental counts of an aggregated value in a joined table
I have two tables in a MySQL 5.7.22 database: posts and reasons. Each post row has and belongs to many reason rows. Each reason has a weight associated with it, and each post therefore has a total ...
- 205
10
votes
1
answer
14k
views
How to grant set the replication attribute on RDS PostgreSQL?
On the AWS RDS documentation I see that rds_superuser can:
Grant and revoke the replication attribute onto all roles that are not
the rds_superuser role. For more information, see this section in ...
- 211
10
votes
2
answers
7k
views
Amazon RDS I/O requests - 1 query = 1 billable I/O?
I have an InnoDB database that I'd like to migrate onto Amazon RDS.
My current implementation, hosted on my own server, shows roughly 8M queries per month.
The RDS site say that
I/O Rate $0.10 ...
- 101
10
votes
2
answers
7k
views
Why is mysql using the wrong index for order by query?
Here is my table with ~10,000,000 rows data
CREATE TABLE `votes` (
`subject_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`subject_id` int(11) NOT NULL,
`voter_id` int(11) NOT NULL,
`...
- 201
10
votes
1
answer
2k
views
How to add index to a big table with 60M records without downtime?
we have been struggling with one issue in the past few days. We want to add an index to a huge table with 60M records. At first we tried adding it with basic mysql syntax. But it clogged our ...
- 53
10
votes
2
answers
33k
views
Does ALTER TABLE - ADD COLUMN lock the table?
I have a question about the ALTER TABLE ... ADD COLUMN DDL statement.
On a Amazon RDS instance with MariaDB v10.2, I've noticed that INSERT statements complete and the rows are correctly inserted in ...
- 1,334
9
votes
3
answers
41k
views
How to delete duplicate records in MySQL, in a table without IDs?
I need to delete the duplicate records in this table. However, there is no id for each row.
Example Data
product
amount
quantity
table
2000
5
chair
300
25
TV
30000
4
bike
300
25
table
2000
5
...
- 193
9
votes
1
answer
754
views
MS SQL Express 2016 on Amazon AWS: I Can Take Database Offline but Can't Bring It Online
I can take databases offline (via GUI) but can't bring them back online. The server details are as follow:
RDBMS: MS SQL 2016 Express
Host: Amazon AWS/RDS Free Tier
Details/History of the Problem
A ...
