Questions tagged [mysql]
All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.
22,203
questions
10
votes
2
answers
9k
views
How can you stop MySQL slave from replicating changes to the 'mysql' database?
I have my slave set to not replicate the 'mysql' database as described in this SHOW SLAVE STATUS\G;
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
...
- 23.4k
20
votes
4
answers
977
views
Is "CREATE INDEX` in MySQL a Linear Operation?
What I mean is the following:
If creating an index on a table with n rows takes t time. Will creating an index on the same table with 1000*ntake approximately 1000*t time.
What I'm trying to achieve ...
- 1,452
20
votes
6
answers
48k
views
How do I find out if a procedure or function exists in a mysql database?
How do I find out if a procedure or function exists in a mysql database? and is there any discovery option? like a show procedures; (e.g. like show tables;)
- 2,831
24
votes
2
answers
16k
views
Can I create index on a table in a MySQL database while in use
And if I can, any reason (performance/stability) why I shouldn't?
- 1,452
2
votes
2
answers
570
views
Transferring concepts - users/groups/permissions of db objects from mysql to postgresql
This seemed like a better fit here than stackoverflow, so here goes:
I've been using mysql for a while since on most of the cheap webhosts it is the provided database. However, now I've started ...
user369
13
votes
5
answers
5k
views
Is it unwise to run replication on same physical server?
I'm contemplating setting up a Master-Slave replication for my database. The slave server will be used for redundancy and possibly a reports server. However, one of the biggest issues I'm running into ...
- 23.4k
5
votes
1
answer
383
views
MySQL and Solid State Disks on EMC
Since all writes and sequential reads are cached by the EMC storage system, the best use for solid state disk is to store data that has a random READ pattern. On Oracle, that is the temp tablespace ...
- 493
7
votes
4
answers
3k
views
Can you "su -" in MySQL?
If I have root on a mysql database and I want to drop privileges to that of a normal user, without using their password, can I do it? if so how? think # su - username on unix. Basically, I'm just ...
- 2,831
3
votes
1
answer
656
views
MySQL CREATE statement are really slow (Mac OS X / Macport install) [closed]
I recently noticed that recreating the database for the web application I am working on is taking a lot of time. I had just reinstalled Mysql from Macport...
I am getting really lousy performances, ...
- 31
5
votes
1
answer
2k
views
Foreign Key violation - not sure why
I have the following two tables with a foreign key on status_id:
mysql> describe usr_cookbook;
+-----------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | ...
- 4,883
25
votes
8
answers
33k
views
Tool to generate large datasets of test data [closed]
Many times when trying to come up with an efficient database design the best course of action is to build two sample databases, fill them with data, and run some queries against them to see which one ...
- 4,883
18
votes
4
answers
2k
views
How do convert a 66,862,521 row table from MyISAM to InnoDB without going offline for several hours?
is it possible (and how) to convert a huge MyISAM table into InnoDB without taking the application offline. It requires to insert a couple of rows into that table every second but it is possible to ...
9
votes
1
answer
14k
views
Error creating foreign key from MySQL Workbench
I'm trying to synchronize schema changes from MySQL Workbench to my database. I'm getting the following error when it tries to create a foreign key:
Executing SQL script in server
ERROR: Error 1005: ...
- 4,883
60
votes
6
answers
32k
views
Performance implications of MySQL VARCHAR sizes
Is there a performance difference in MySQL between varchar sizes? For example, varchar(25) and varchar(64000). If not, is there a reason not to declare all varchars with the max size just to ensure ...
- 4,883
22
votes
2
answers
40k
views
What is the difference between MySQL VARCHAR and TEXT data types?
After version 5.0.3 (which allowed VARCHAR to be 65,535 bytes and stopped truncating trailing spaces), is there any major difference between these two data types?
I was reading the list of ...
- 23.4k
25
votes
3
answers
18k
views
Is it common practice to mix InnoDB and MyISAM tables on same server?
I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
- 23.4k
32
votes
1
answer
61k
views
INT(5) vs SMALLINT(5): numbers in parenthesis after the numeric type
In MySQL table definitions is there a different between INT(5) and SMALLINT(5)? Or do they both represent the same size?
- 4,883
13
votes
3
answers
7k
views
Dynamic SQL in MySQL stored routines
According to the restrictions on stored routines and triggers, dynamic sql cannot be used (restriction lifted for stored procedures in version 5.0.13 and later). Why is this limitation in place? And ...
- 23.4k
7
votes
1
answer
2k
views
What are some important MySQL status variables to keep an eye on? [closed]
I'm a fairly new DBA with a programming background. As such, I like to build tools to manage tasks to help me familiarize myself with the intricacies of a technology.
In this case, I am managing ...
- 23.4k
6
votes
2
answers
482
views
different login credentials based on user
I run a PHP symfony-framework web app for my company and it uses a single database login for all connections, whether the user is an admin or a client.
I know that higher security systems often use ...
- 4,229
1
vote
1
answer
167
views
version controll application/system for mysql databases [duplicate]
Possible Duplicate:
Keeping track of schema changes
Hello,
How do you keep your mysql database schema under version control.
- 985
8
votes
3
answers
222
views
Is there a performance gain by handling data with stored procedures versus feeding it into functions after retrieval?
Title sums it up. I'm reasonably well off with PHP, and comfortable getting it to do what I need it to with values pulled from MySQL, however in the interest of expanding my knowledge I've been ...
- 937
109
votes
5
answers
33k
views
Storing vs calculating aggregate values
Are there any guidelines or rules of thumb to determine when to store aggregate values and when to calculate them on the fly?
For example, suppose I have widgets which users can rate (see schema ...
- 4,883
31
votes
3
answers
9k
views
Character vs Integer primary keys
I'm designing a database with multiple lookup tables containing possible attributes of the main entities. I'm thinking of using a 4 or 5-character key to identify these lookup values rather than an ...
- 4,883
6
votes
4
answers
3k
views
Is there a way to measure replication lag in MySQL with a resolution less than 1 second?
Is there a way by which you can measure replication lag in MySQL with a resolution of less than 1 second?
That is, can replication lag be measured on the microsecond or millisecond level?
- 1,377
5
votes
1
answer
455
views
What are the pros and cons of using the Sphinx Storage Engine?
What are the pros/cons of using the Sphinx Storage Engine in MySQL (doc) over the normal api?
- 1,377
40
votes
6
answers
70k
views
How do you tune MySQL for a heavy InnoDB workload?
Assuming a production OLTP system with predominantly InnoDB tables
What are the common symptoms of a mistuned/misconfigured system?
What configuration parameters do you most commonly change from ...
- 1,377
4
votes
2
answers
204
views
How to create multiple entries on index based on the fields of one row?
I never found a good way to index multiple fields of one row as entries of an index or simulate this feature on MySQL.
The problem arises when you have fields working as tags or similar concept. Ex.: ...
- 2,708
169
votes
13
answers
339k
views
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, ...
- 1,851
8
votes
4
answers
7k
views
For a MySQL query, how do you determine physical and logical I/O?
I'm used to having a showplan in Microsoft SQL and have gotten lazy with using tools to tune MySQL queries. How can I determine physical/logical I/O for a query in MySQL? (EXPLAIN shows the index ...
- 2,277
7
votes
1
answer
3k
views
What's the maximum number of tables that can be referenced in a query in MySQL?
In the days of SQL Server 2000 and 2005, the maximum number of tables in a query was 256.
Does MySQL have a similar limitation?
I know that such query of such size means that either there's ...
- 1,485
4
votes
1
answer
392
views
Using CoreData on a client-side application
This may be a bit more of an SO question, but...
If I'm writing a client-side application for OS X that's going to sync with an online MySQL database, is there any difference on if I have CD use ...
- 937
31
votes
5
answers
55k
views
How can I change the default storage engine in phpmyadmin?
I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the ...
- 937
18
votes
6
answers
390
views
Securing DB passwords
Looking at the structure of most PHP/MySQL-based websites I've seen, it appears that it's not terribly difficult to discern the database password if you dig a bit, as there's invariably a setup or ...
- 937
8
votes
3
answers
349
views
What does the output of a JOIN statement look like?
I've been wanting to use joins for a while, but I'm having trouble visualizing the output so I know how to put it to use.
Let's say I have 2 tables:
CREATE TABLE Cities (
id INT UNSIGNED PRIMARY ...
- 937
8
votes
3
answers
2k
views
MySQL LDAP authentication
Is LDAP authentication and authorization in MySQL possible? What are the requirements (MySQL version, third-party software etc.)? How it's organized?
Thanks.
- 3,115
8
votes
6
answers
2k
views
Alternative Database Tools for Web Admin [closed]
I currently use phpmyadmin for most of my database administration. It works well enough, but I'm interested in other tools that might work more quickly/efficiently/etc...
What are the best and/or ...
- 4,229
42
votes
2
answers
20k
views
NULL or NOT NULL by default?
In MySQL, is it better to always allow nulls unless you know a field is required, or always use Not Null unless you know a field will contain nulls? Or doesn't it matter?
I know in some DBMSs they ...
- 4,883
29
votes
6
answers
23k
views
How to create a conditional index in MySQL?
How to create an index to filter a specific range or subset of the table in MySQL? AFAIK it's impossible to create directly but I think it's possible to simulate this feature.
Example: I want to ...
- 2,708
12
votes
5
answers
8k
views
Are there any MySQL Benchmarking tools? [closed]
I've heard a long time ago that there is this tool that helps you tweek mysql settings for better performance but i can't seam to find it. I am aware that I can use ab for apache to simulate high ...
- 2,865
31
votes
5
answers
71k
views
Where can I find the mysql slow log?
Just as the title says, where can I see it ?
Are there any config options for it ( like how many ms would determine if a query is slow or not ) ?
- 2,865
207
votes
7
answers
295k
views
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 ...
- 4,229
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,708
68
votes
5
answers
12k
views
How can a group track database schema changes?
What version control methodologies help teams of people track database schema changes?
- 1,128
282
votes
10
answers
284k
views
What are the main differences between InnoDB and MyISAM?
What are the main differences between InnoDB and MyISAM?
- 3,049
5
votes
3
answers
3k
views
With MySQL Replication, what level of resilience is possible?
I've found lots of good material on setting up MySQL replication, but not much on what to do in the event of failures. Understanding the degree of resilience I can achieve is fundamental to designing ...
djna
8
votes
4
answers
15k
views
How to do a mysqldump with a use database in the dump
So the command I normally use to get a database sent to another server is:
mysqldump -u user -p --add-drop-tables database *file*
But I always have to go into the file and add a line at the top:
use ...
user481826
3
votes
4
answers
16k
views
In MySQL Workbench, insert a new column at the "top" of a table?
I must keep a table's columns as specified in a specification that changes quite often.
In MySQL Workbench, adding a new columns after all the others is easy, but I can't find a way to insert a new ...
- 319
2
votes
1
answer
4k
views
MySQL : "FLUSH TABLES WITH READ LOCK" started automatically
I would like to understand how this happened. I was running a query that would take a long time, but should not lock up any table. However, my dbs were practically down - it seems like it was being ...
ming yeow
43
votes
9
answers
253k
views
MySQL DB import/export command line in Windows
How do I import and export a local instance of a MySQL database via command-line interface?
My operating system is Windows and WampServer server is installed.
- 539