Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
236
votes
4 answers

How large should be mysql innodb_buffer_pool_size?

I have a busy database with solely InnoDB tables which is about 5GB in size. The database runs on a Debian server using SSD disks and I've set max connections = 800 which sometimes saturate and grind the server to halt. The average query per second…
alfish
  • 2,754
  • 6
  • 19
  • 18
207
votes
6 answers

Force drop db while others may be connected

I need to remove a database from a PostgreSQL DB cluster. How can I do it even if there are active connections? I need sort of a -force flag, that will drop all connections and then the DB. How can I implement it? I'm using dropdb currently, but…
Alex
  • 2,265
  • 2
  • 15
  • 7
206
votes
3 answers

Granting a user account permission to create databases in PostgreSQL

How do I give a user account in PostgreSQL the ability to create and drop databases? Is there a way to do this with GRANT?
GSto
  • 2,163
  • 2
  • 12
  • 6
205
votes
2 answers

Create a MySQL database with charset UTF-8

I'm new to MySQL and I would like to know: How can I create a database with charset utf-8 like I did in navicat? create mydatabase; ...seems to be using some kind of default charset.
user3397998
  • 2,161
  • 2
  • 11
  • 4
202
votes
7 answers

How can I optimize a mysqldump of a large database?

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to backup the database nightly. Due to my comcast…
Patrick
  • 4,129
  • 7
  • 26
  • 26
199
votes
5 answers

When should a primary key be declared non-clustered?

While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED When would you use a NONCLUSTERED primary key as opposed to a CLUSTERED primary key? Thanks in advance
Stuart Blackler
  • 4,392
  • 7
  • 27
  • 42
194
votes
5 answers

Granting access to all tables for a user

I'm new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, and DELETE privileges on a low privileged user and enable those grants to apply to all tables in a specified database. I must be…
PlaidFan
  • 2,045
  • 2
  • 12
  • 7
186
votes
7 answers

What's the difference between a CTE and a Temp Table?

What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other? CTE WITH cte (Column1, Column2, Column3) AS ( SELECT Column1, Column2, Column3 FROM SomeTable ) SELECT * FROM…
Rachel
  • 8,299
  • 20
  • 46
  • 74
185
votes
17 answers

How do I find PostgreSQL's data directory?

I forgot how I started PostgreSQL the last time (it was months ago) and I don't remember where the data directory is located. The postgres command seems to require the location of the data directory. I'm on MacOsX if that helps. /usr/local/postgres…
randomguy
  • 2,001
  • 2
  • 13
  • 5
173
votes
4 answers

'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) for this operation' when trying to dump…
subhanshu kumar
171
votes
5 answers

How to get the name of the current database from within PostgreSQL?

Using \c in PostgreSQL will connect to the named database. How can the name of the current database be determined? Entering: my_db> current_database(); produces: ERROR: syntax error at or near "current_database" LINE 1:…
Amelio Vazquez-Reina
  • 1,837
  • 2
  • 12
  • 8
171
votes
4 answers

Help installing SQL Server 2017 - VS Shell installation has failed with exit code 1638

Any suggestions on how to deal with this error: TITLE: Microsoft SQL Server 2017 Setup ------------------------------ The following error has occurred: VS Shell installation has failed with exit code 1638. For help, click:…
Jonathan Allen
  • 3,522
  • 6
  • 22
  • 24
171
votes
19 answers

Should developers be able to query production databases?

Should developers be given permission to query (SELECT / read only) production databases? The previous place I worked, the development team had the db_datareader role; where I work now the development team can't even connect to the production…
Tom Hunter
  • 2,109
  • 3
  • 16
  • 11
163
votes
12 answers

SQL: SELECT All columns except some

Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table. Something like: SELECT * -the_geom FROM segments; I once heard that this…
Adam Matan
  • 10,609
  • 28
  • 76
  • 94
160
votes
13 answers

How can I move a database from one server to another?

How can I move MySQL tables from one physical server to another? Such as this exact scenario: I have a MySQL server that uses innodb table and is about 20GB size. I want to move it to a new server, what's the most efficient way to do this?
John
  • 1,761
  • 2
  • 13
  • 10
1
2
3
…
99 100