All Questions
3,782 questions
151
votes
4
answers
256k
views
Is it safe to delete mysql-bin files?
I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these mysql-bin files inside /var/db/mysql/ There are hundreds of those ...
user18530
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
49
votes
5
answers
53k
views
mysql to mariadb: unknown collation utf8mb4_0900_ai_ci
I have a mysql 8.0 that I exported using mysqldump. I am trying to import it onto a Mariadb 10.4 database with phpmyadmin, both are the most current versions. Each time I do it though, I get:
Error: ...
- 491
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; ...
39
votes
5
answers
6k
views
When is the right time to use MariaDB instead of MySQL, and Why?
What we earn and what we lost with this migration?
What should I expect as drawbacks after the migration?
Is it really unnecessary to change the applications in any situation?
- 2,748
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
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
30
votes
8
answers
50k
views
MariaDB Can't init tc log
I've tried every solution on the Internet but my MariaDb server continue to fail, continue to betray me, continue to destroy my tiny DevOps world. My attempts to smooth the situation included all ...
- 365
26
votes
3
answers
60k
views
How do I resolve this error, "ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'"?
When I run the following command I get an error, however one of my scripts requires it.
SET time_zone = 'UTC';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'
- 64.7k
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
26
votes
2
answers
10k
views
How do I properly perform a MySQL bake-off?
I want to performance test (aka bake-off) MySQL server rpm against some other forks such as Percona server, MariaDB, and possibly some others. I'm hoping that by asking this question I can better ...
- 3,934
25
votes
6
answers
116k
views
MySQL FOREIGN KEY constraint is incorrectly formed
I have the following table definition:
CREATE TABLE `async_task` (
`idasync_task` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`idasync_type` int(10) unsigned NOT NULL,
`priority` tinyint(3) ...
- 689
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
3
answers
12k
views
Why does importing a 12 GB .sql file take more than 36 hours?
I've been waiting now for 36 hours for a 12 GB .sql file to be imported with a simple type site.sql | mysql command. I can see the ibdata1 is growing still, currently nearly 40 GB.
Considering the ...
- 365
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
2
answers
29k
views
How do I disable MySQL on Linux from starting on boot or statup?
After adding MySQL to Ubuntu, it automatically starts up on server boot. I would like it to not start up with the system.
How can I disable it and manually start it?
- 64.7k
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
17
votes
2
answers
39k
views
Does MariaDB support native JSON column data type?
I’m not talking about dynamic columns, I’m asking about native column JSON data type. In simple words, can I run the following code on any MariaDB version?
CREATE TABLE example (names JSON);
As far ...
- 271
16
votes
5
answers
109k
views
How to determine the optimal sort_buffer_size?
I read from a sample configuration file which says the following:
# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY
# queries. If sorted data does not fit into the sort buffer, a ...
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
38k
views
Why does auto increment jumps by more than the number of rows inserted?
I am very perturbed by this weird behaviour I am seeing in the auto_increment value recorded in the bidID of a Bids table after performing bulk insertion using a stored procedure:
INSERT INTO Bids (...
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
10k
views
What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?
What are the drawbacks of using Galera Cluster instead of regular Master/Slave Replication? Galera's 0 slave lag time, Synchronous replication and no Single point of failure seem very appealing then ...
- 334
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
4
answers
133k
views
Error 1236 - "Could not find first log file name in binary log index file"
Our setup:
Master: MariaDB 10.0.21
Slave: MariaDB 10.0.17
Replication was working fine until recently at which point the slave's DBs had to be restored from a dump. I performed all of the necessary ...
- 447
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
2
answers
94k
views
Cannot drop column : needed in a foreign key constraint
I have a table with two foreign key constraints as below:
mysql> SHOW CREATE TABLE `user`;
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`region_id` int(11) unsigned ...
- 367
14
votes
4
answers
148k
views
Datatype for phone number: VARCHAR, INT or BIGINT?
So this will be the dummy question of the year but I need to ask since is not the first time I pass through this. Take a look to the following table definition:
Take a look at the column from_number ...
- 1,888
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
2
answers
8k
views
To what extent is MariaDB Java client a perfect replacement for MySQL JDBC (Connector/J)?
Premise: I ship a Java application which will make a JDBC connection to a MySQL database. (At the time of this writing it could be 5.1 or 5.5, but hopefully the question and answer don't rely heavily ...
- 357
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
1
answer
31k
views
MariaDB 10.1.38 - Specified key was too long; max key length is 767 bytes
I have a table that has 3 columns:
id(int 11) | user_id(int 4) | title(varchar 512)
____________|________________|___________________
1 | 3 | Thing X
2 | 3 ...
- 461
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
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
3
answers
36k
views
MariaDB: disable foreign key checks
On MySql, in order to truncate a table or delete rows when it's normally impossible because of foreign keys (InnoDB only), we use this command:
SET FOREIGN_KEY_CHECKS=0;
On MariaDB, while this ...
- 223
12
votes
1
answer
45k
views
How do you index a text column in MySQL?
If I create a table with a text column
CREATE TABLE foo ( bar text );
I can't index the bar column.
MariaDB [test]> CREATE INDEX foo_bar_idx ON foo ( bar );
ERROR 1170 (42000): BLOB/TEXT column ...
- 64.7k
12
votes
2
answers
11k
views
Install MariaDB 10 on Ubuntu without prompt and no root password
Pretty much the same as this question but I don't want to have a root password (this is just a dev machine).
Here's what I've got:
export DEBIAN_FRONTEND=noninteractive
sudo debconf-set-selections &...
- 427
12
votes
2
answers
14k
views
Which characters are allowed in MySQL/MariaDB user account names?
It's not clear to me which characters can occur in MySQL/MariaDB user account names, and which are illegal. I've read section 6.3.1 "User Names and Passwords" in the MySQL documentation. This section ...
- 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 ...
