2
votes
3answers
7k views
Cannot GRANT privileges as root
Because of having some problems, I decided to re-create all users except for root@localhost. This works fine, but the newly created user has no right to do anything. What I want is to simply give all ...
2
votes
3answers
1k views
What is the mysql.db table used for?
Related to my MySQL issue
I have a user with USAGE grant in mysql.user, but in mysql.db this user has Select, Insert, Update, Delete. Thus the user can successfully query the database.
I can't find ...
2
votes
2answers
513 views
MySQL general log
Is there away to have MySQL dump its log to a remote MySQL server?
I would like to audit MySQL connections but I don't want the server to store all the information local on its own hardware.
2
votes
1answer
929 views
Mysql Memory table getting many locks
On my site I log every pageview (date, ip, referrer, page, etc) in a simple mysql table.
This table has the following activity:
1 SELECT per minute
1 DELETE per minute
no UPDATE queries
Lots of ...
2
votes
2answers
242 views
get column from too many tables in mysql
.I have too many tables X1,X2,...Xn (they might exceed 75 tables) in Mysql DB to represent departments of a very large company.
every table has structure something like this
desc X1;
...
2
votes
1answer
468 views
LOCK IN SHARE MODE
On dev mysql LOCK IN SHARE MODE is described as...
SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify ...
1
vote
1answer
2k views
How to enable MySQL general log?
I would like to enable the MySQL General Log to view all the (slow) queries. I'm using XAMPP as server and have no idea how to enable it. I can't find a good guide on the internet.
Can someone help ...
1
vote
2answers
469 views
MySQL Community Edition: Can I replicate my entire server with another server in one go?
I need to migrate all the databases from one server to another. There are about 100 databases, and all databases are used daily and updated/inserted too and read through their respective websites.
...
0
votes
0answers
279 views
Inserting query result to another table hangs on “Copying to temp table on disk” on MySQL
I started the process of inserting returned results to another table. The query groups the rows in respect of indexed IDs. This causes 149,000,000 rows to be decreased to 460,000 rows.
The query ...
0
votes
1answer
711 views
Resolving ORA-01033 “initialization or shutdown in progress”
I followed the instructions at http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php to create a database export.
I used:
expdp scott/tiger@db10g full=Y directory=TEST_DIR ...
-6
votes
1answer
725 views
How do I convert this Access query for SQL Server? [closed]
I have a Query used in Access which does not work in SQL Server as it stands:
SELECT
mstPatient.PatientID,
mstPatient.PatientID AS PKNO,
...
41
votes
5answers
7k views
What are the differences between NoSQL and a traditional RDBMS?
What are the differences between NoSQL and a traditional RDBMS?
Over the last few months, NoSQL has been frequently mentioned in the technical news. What are its most significant features relative to ...
31
votes
11answers
2k views
How could DBAs be more 'programmer friendly'?
The answers and comments on the dba.se version and programmers.se version of the question "What are the arguments against or for putting application logic in the database layer?" are very revealing ...
14
votes
2answers
1k views
How to make a transition from SQL Server DBA to Oracle?
As an interest, if I would to transition from a SQL Server DBA to Oracle what will be the major learning or unlearning that I would have to do?
I would assume the concepts are the same and the ...
42
votes
4answers
3k views
Why are numbers tables “invaluable”?
Our resident database expert is telling us that numbers tables are invaluable. I don't quite understand why. Here's a numbers table:
USE Model
GO
CREATE TABLE Numbers
(
Number INT NOT NULL,
...
26
votes
4answers
8k views
What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?
My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other.
Our ...
7
votes
3answers
457 views
Can spatial index help a “range - order by - limit” query
Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes.
We have the following table with a tree structure (Nested Set model) of words and their ...
7
votes
2answers
3k views
MySQL Sharding vs MySQL Cluster
Considering performance only, can a MySQL Cluster beat a custom data sharding MySQL solution? sharding = horizontal partitioning
When I refer to sharding, I'm considering sharding made in the ...
12
votes
5answers
473 views
Needing A Database Design Book
I am designing a database and it has so many relationships among my tables and i need a book that teaches database design very well.I am looking for a book where table relationships simple and complex ...
28
votes
8answers
5k views
Is there a tool like Microsoft's “SQL Server Profiler” for MySQL?
While developing on MySQL I really miss being able to fire up a profiler. I find SQLyog is a good enough replacement for Query Analyzer but have not found a tool that works like SQL profiler.
For ...
25
votes
6answers
1k views
The smallest backup possible … with SQL Server
Daily we ship our SQL Server backups across the WAN. We need to minimize the size of these backups so it does not take forever.
We don't mind if our backup process takes a bit longer; as it stands ...
19
votes
13answers
2k views
How to implement a 'default' flag that can only be set on a single row
For example, with a table similar to this:
create table foo(bar int identity, chk char(1) check (chk in('Y', 'N')));
It doesn't matter if the flag is implemented as a char(1), a bit or whatever. I ...
7
votes
3answers
456 views
More Databases Per Sql Server 2005 Instance
Our company currently hosts about 400 databases per SQL Server 2005 instance. 500-600 databases and we see performance start to deteriorate.
Database size ranges from 50mb to 40-60gb in the extreme, ...
5
votes
3answers
3k views
MySQL general query log performance effects
One of our customers who has a somewhat problematic web application would like us to log all the queries for a period of 24 hours.
I wouldn't know how much data it will actually write.
The amount ...
17
votes
1answer
2k views
Designing a platform: one database or multiple databases?
We are building a web platform that incorporates multiple services, each with its own underlying data. These services are being built independently following the principles of Service-Oriented ...
16
votes
2answers
4k views
I/O requests taking longer than 15 seconds
Usually our weekly full backups finish in about 35 minutes, with daily diff backups finishing in ~5 minutes. Since tuesday the dailies have taken almost 4 hours to complete, way more than should be ...
15
votes
3answers
2k views
Traversing tree-like data in a relational database using SQL
Is there a way to traverse tree data in SQL? I know about 'connect by' in Oracle, but is there another way to do this in other SQL implementations? I'm asking because using 'connect by' is easier ...
11
votes
5answers
593 views
Why do primary keys have names of their own?
From a mathematical view, granted that a table has at most one primary key, it seems to be a shortsighted design decision to refer to primary keys by some arbitrary name instead of a simple table ...
8
votes
1answer
4k views
How is INFORMATION_SCHEMA implemented in MySQL?
The INFORMATION_SCHEMA is, in theory, a set of views specified in the SQL standard that allow the user to inspect the system's metadata. How is it this implemented in MySQL?
When I connect to a fresh ...
5
votes
3answers
3k views
How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically ...
12
votes
2answers
857 views
What can we do in MySQL 5.0 Replication to address bandwidth concerns?
I am developing an application to run on the client PC (Win) which is configured with a MySQL server 5.1 instance that will act as read-only slave to the remote master. The remote master has dozens of ...
12
votes
5answers
2k views
Why use an int as a lookup table's primary key?
I want to know why I should use an int as a lookup table's primary key instead of just using the lookup value as the primary key (which in most cases would be a string).
I understand that using a ...
11
votes
5answers
8k views
Oracle Creating ER Diagrams and Data Dictionaries
I am new to Oracle Databases and would like to develop a data dictionary and ER Diagrams for our existing databases. Do you have any tips, scripts, tools for doing so?
10
votes
4answers
8k views
How do you identify InnoDB table corruption?
I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and ...
9
votes
2answers
5k views
What is a Clustered Index?
I need a short explaining of clustered index.
What is a clustered index?
What are best practices for using the clustered index?
7
votes
4answers
1k views
Help me choose a RAID level combination for a SQL Server 2008 instance
I am going to rebuild one IBM 3400 server from scratch. This server is dedicated to a SQL Server 2008 instance running on Windows 2008 R2.
I am going to make new RAID configuration. I have 6 SCSI ...
6
votes
3answers
808 views
Testing stored procedure scalability
I have an email application that will be called upon to deliver to the UI the number of new messages for a given user on each page load. I have a few variations of things I am testing on the DB level ...
5
votes
6answers
6k views
Is it possible to mysqldump a subset of a database required to reproduce a query?
Background
I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research).
Question
...
4
votes
1answer
1k views
Capabilities of InnoDB INSERT Performance
Hi I am running the most recent version of Percona Server.
Server version: 5.5.24-55 Percona Server (GPL), Release 26.0
I have a 10 cpu box of these characteristics.
processor : 0
vendor_id ...
11
votes
2answers
7k views
What are database statistics, and how can I benefit from them?
I've heard mention of statistics that SQL Server keeps by default. What are they tracking, and how can I use this information to improve my database?
11
votes
3answers
2k views
Find out what queries are causing the biggest amount of network traffic
On a production SQL server I am seeing intermittent enormous spikes in data traffic. Up to 200Mbit/s which is causing NETWORK IO waits which in turn cause query timeouts. How can I find out what ...
10
votes
5answers
2k views
How do I force one record to have a true value for a boolean column, and all others a false value?
I want to enforce that only one record in a table is considered the "default" value for other queries or views that might access that table.
Basically, I want to guarantee that this query will always ...
8
votes
1answer
3k views
How can I alter an existing Primary Key on SQL Azure?
I want to modify an existing primary key on a SQL Azure table.
It currently has one column, and I want to add another.
Now, on SQL Server 2008 this was a piece of cake, just did it in SSMS, poof. ...
7
votes
3answers
2k views
SQL Server performance sudden degradation
I have a SQL Server 2005 that has become unpredictable of late, and I'm scratching my head as to why. Queries that executed in seconds are changing plans and taking minutes (taking the time in full ...
6
votes
3answers
2k views
Measure the size of a PostgreSQL table row
I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
6
votes
2answers
1k views
How to create an Event Notification that runs a Job/procedure when mirroring state changes
I'm asking this question in the sequence of this one Can I send a string over TCP using T-SQL?
Remus Rusanu exposes what it seems to be an optimal solution for my problem, but... I'm too immature to ...
5
votes
5answers
2k views
Is there any good tool for making Database Design and Prototypes?
I would like to have a good tool for designing the Database Schema with all the tables, columns, data types and relations. Today I mostly do this with pen and paper but I would like to do it in a good ...
4
votes
1answer
1k views
Best solution for cross-datacenter MySQL master-slave replication
We are working on a new system architecture for our company. We have a HPC, which runs in our own data-center and we are planning our front-end and a fallback system on Amazon Web Service.
System ...
4
votes
6answers
2k views
Too many columns in MySQL
I have a table with 1699 columns and when I'm trying to insert more columns I get the too many columns in the table error. In this table I have only 1000 rows. For me the most important thing is the ...
3
votes
1answer
475 views
How to determine the sweet spot between pool size and database connections in PostgreSQL
We are having trouble handling the traffic during peak hours to our database server. We're looking into improving the hardware (see this question about that side of things) but we also want to work on ...
