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.

Filter by
Sorted by
Tagged with
13 votes
5 answers
41k views

Audit logins on MySQL database

Is there a way to audit logins to MySQL? I'd like to be able to create a username for each employee and thereby create an audit trail of logins. However, googling has turned up no good results. The ...
asbestossupply's user avatar
3 votes
3 answers
544 views

What kind of hardware/software config should I look at for a MySQL database with 10,000 plus tables

I am looking at rolling out a CMS system that will require the creation of around 10,000 tables within the primary MySQL database of the system. The database will be the data store for several ...
Shawn's user avatar
  • 33
0 votes
1 answer
679 views

One record, multiple updates in one query (MySQL)

I run into a problem for which I didn't find an answer anywhere: If I have a table in which one specific value(here amount) from multiple records can add into a target record(from the same table), can ...
user avatar
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 ...
Derek Downey's user avatar
  • 23.5k
20 votes
4 answers
1k 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 ...
Nifle's user avatar
  • 1,472
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;)
xenoterracide's user avatar
24 votes
2 answers
17k 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?
Nifle's user avatar
  • 1,472
2 votes
2 answers
571 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 ...
user avatar
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 ...
Derek Downey's user avatar
  • 23.5k
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 ...
SteveHarville's user avatar
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 ...
xenoterracide's user avatar
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, ...
perrick's user avatar
  • 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 | ...
BenV's user avatar
  • 4,903
25 votes
8 answers
34k 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 ...
BenV's user avatar
  • 4,903
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 ...
Hendrik Brummermann's user avatar
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: ...
BenV's user avatar
  • 4,903
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 ...
BenV's user avatar
  • 4,903
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 ...
Derek Downey's user avatar
  • 23.5k
25 votes
3 answers
19k 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 ...
Derek Downey's user avatar
  • 23.5k
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?
BenV's user avatar
  • 4,903
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 ...
Derek Downey's user avatar
  • 23.5k
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 ...
Derek Downey's user avatar
  • 23.5k
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 ...
Patrick's user avatar
  • 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.
Gabriel Solomon's user avatar
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 ...
Kaji's user avatar
  • 937
111 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 ...
BenV's user avatar
  • 4,903
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 ...
BenV's user avatar
  • 4,903
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?
Riedsio's user avatar
  • 1,377
5 votes
1 answer
459 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?
Riedsio's user avatar
  • 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 ...
Riedsio's user avatar
  • 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.: ...
Maniero's user avatar
  • 2,728
169 votes
13 answers
346k 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, ...
John's user avatar
  • 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 ...
David Hall's user avatar
  • 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 ...
Marek Grzenkowicz's user avatar
4 votes
1 answer
393 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 ...
Kaji's user avatar
  • 937
31 votes
5 answers
56k 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 ...
Kaji's user avatar
  • 937
18 votes
6 answers
393 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 ...
Kaji's user avatar
  • 937
8 votes
3 answers
350 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 ...
Kaji's user avatar
  • 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.
Yasir Arsanukayev's user avatar
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 ...
Patrick's user avatar
  • 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 ...
BenV's user avatar
  • 4,903
29 votes
6 answers
24k 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 ...
Maniero's user avatar
  • 2,728
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 ...
poelinca's user avatar
  • 2,865
31 votes
5 answers
72k 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 ) ?
poelinca's user avatar
  • 2,865
207 votes
7 answers
300k 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 ...
Patrick's user avatar
  • 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?
Maniero's user avatar
  • 2,728
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?
Toby's user avatar
  • 1,128
282 votes
10 answers
285k views

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?
ilhan's user avatar
  • 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's user avatar
  • 151
9 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 ...
user avatar