Most Popular
1500 questions
163
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,791
- 2
- 13
- 10
160
votes
12 answers
Should binary files be stored in the database?
What is the best place for storing binary files that are related to data in your database? Should you:
Store in the database with a blob
Store on the filesystem with a link in the database
Store in the filesystem but rename to a hash of the…
Jack Douglas
- 38,607
- 15
- 97
- 174
154
votes
12 answers
How do I move SQL Server database files?
I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server.
How can I do this?
user2645263
153
votes
6 answers
Good explanation of cascade (ON DELETE/UPDATE) behavior
I don't design schemas everyday, but when I do, I try to setup cascade updates/deletes correctly to make administration easier. I understand how cascades work, but I can never remember which table is which.
For example, if I have two tables -…
Johntron
- 1,663
- 2
- 11
- 7
145
votes
3 answers
PostgreSQL multi-column unique constraint and NULL values
I have a table like the following:
create table my_table (
id int8 not null,
id_A int8 not null,
id_B int8 not null,
id_C int8 null,
constraint pk_my_table primary key (id),
constraint u_constrainte unique (id_A, id_B,…
Manuel Leduc
- 1,561
- 2
- 10
- 5
145
votes
3 answers
Advantages and Disadvantages to using ENUM vs Integer types?
Lets say in some random table, you have a column named status. It's real-world values would be either enabled or disabled.
Is it better for this column's data type to be an int/bool (1 or zero) or to use ENUM with the values being enabled and…
Jake Wilson
- 2,377
- 8
- 21
- 23
144
votes
9 answers
How to properly format sqlite shell output?
If I go to mysql shell and type SELECT * FROM users I get -
+--------+----------------+---------------------------------+----------+-----------+--------------------+--------------------+
| USERID | NAME | EMAILID |…
Kshitiz Sharma
- 3,087
- 8
- 29
- 35
143
votes
4 answers
Optimizing queries on a range of timestamps (two columns)
I use PostgreSQL 9.1 on Ubuntu 12.04.
I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my actual table that are not involved with this…
Stephane Rolland
- 7,903
- 8
- 29
- 40
142
votes
5 answers
Possible to make MySQL use more than one core?
I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer than DBA for MySQL so need some help
Setup
The servers are quite hefty with an OLAP/DataWarehouse (DW) type load:
Primary: 96GB RAM, 8…
gbn
- 69,343
- 8
- 160
- 240
141
votes
8 answers
Why shouldn't we allow NULLs?
I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL. I don't remember why this was the case though.
All I can seem to think of is that, as an application developer, you…
Thomas Stringer
- 41,694
- 9
- 115
- 154
140
votes
4 answers
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 files like mysql-bin.000123, mysql-bin.000223 etc.…
user18530
136
votes
8 answers
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
I had to write a simple query where I go looking for people's name that start with a B or a D :
SELECT s.name
FROM spelers s
WHERE s.name LIKE 'B%' OR s.name LIKE 'D%'
ORDER BY 1
I was wondering if there is a way to rewrite this to become more…
Lucas Kauffman
- 1,695
- 4
- 16
- 18
134
votes
11 answers
How to easily convert utf8 tables to utf8mb4 in MySQL 5.5
I have a database which now needs to support 4 byte characters (Chinese). Luckily I already have MySQL 5.5 in production.
So I would just like to make all collations which are utf8_bin to utf8mb4_bin.
I believe there is no performance loss/gain with…
geoaxis
- 1,677
- 2
- 12
- 11
134
votes
15 answers
PostgreSQL not running on Mac
The error in its entirety reads:
psql: could not connect to server: No such file or directory. Is the
server running locally and accepting connections on Unix domain socket
"/tmp/.s.PGSQL.5432"?
This is my second time setting up Postgresql via…
Michael P.
- 2,001
- 2
- 12
- 8
134
votes
4 answers
How to list all constraints of a table in PostgreSQL?
How to list all constraints (Primary key, check, unique mutual exclusive, ..) of a table in PostgreSQL?
Thirumal
- 2,058
- 3
- 12
- 23