MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
2answers
4k views

How does mysql decide if concurrent insert is possible for a MyISAM table?

For "concurrent inserts", MySQL reference manual has the following explanation: The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given ...
2
votes
1answer
394 views

Data type for protobuf objects in mysql

I would like to store some protobuf objects in a MySQL database. The objects are created in a Java application. The object is accompanied with a textual type identifier and a timestamp. ...
2
votes
1answer
991 views

Why does MySQL only sometimes use my index for a range query?

I have a table of IP address ranges and their country, here is the structure: CREATE TABLE `geoIP` ( `startBlock` int(11) unsigned NOT NULL DEFAULT '0', `endBlock` int(11) unsigned NOT NULL ...
2
votes
2answers
458 views

Is there an easy way to copy one database to another

In MySQL? Say I have a local database. Now I want the remote database to mimic the local database. One way I can think of is to simply dump the database, upload and let things work. Well, database ...
2
votes
2answers
992 views

Find the largest databases on my server

I have several hundred databases running on the same MySQL server. How can I get a list sorted by the size of the databases?
2
votes
1answer
746 views

php/mysql merge duplicate records in table and create a new table

please with respect to username and storetype, how can I merge rows to achieve Final table? storetype should be unique per username. There are thousands of usernames and records so php/mysql would ...
2
votes
2answers
5k views

Copy complete structure of a table in MySQL without using a backup?

In MySQL, how can someone copy a table structure without using a backup? jcho360> show create table actor\G *************************** 1. row *************************** Table: ...
2
votes
3answers
236 views

How to get all non deleted messages from one user

I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like: CREATE TABLE IF NOT EXISTS ...
2
votes
1answer
100 views

SELECT for values in a table

I have this table structure: id1 id2 1 2 1 3 2 1 2 4 id1 and id2 are not unique as you can see in the values. I need to retrieve those values that exists in the two columns ...
2
votes
1answer
180 views

MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?

I'm trying to estimate my db traffic not including backups (mysql 5.1.41). I use show global status; to get the parameters bytes_sent and bytes_received. Questions Do these numbers include ...
2
votes
1answer
1k views

Are many NULL columns harmful in mysql InnoDB?

As I explored the clustered B-tree index system of InnoDB, I think that the presence of many NULL (or small) columns has no significant effect on InnoDB performance. Does the presence of excess ...
2
votes
1answer
314 views

Why is mysqlhotcopy slower compared to rsync?

My understanding is mysqlhotcopy works very similar to rsync. In one of our servers backing the database file using rsync takes about 15 minutes for 50GB data. Backing up the same data using ...
2
votes
1answer
2k views

Can I mysqldump an entire database while using where='condition' on individual tables?

I have used mysqldump to dump an entire database, However, when I want to use --where=condition", on individual tables, I have dumped individual tables mysqldump --where='condition' mydb table ...
2
votes
1answer
503 views

Is the “load data infile” command in MySQL unsafe in general, or just when using statement based replication?

I'm also trying to understand why MySQL considers "load data infile" unsafe and switches to RBR if running in mixed mode, does that mean it is only unsafe when using SBR?
2
votes
1answer
587 views

Number of locks exceeded when adding foreign key constraint

I'm adding a foreign key constraint to table a (17mil rows) that references table b (15mil rows): >ALTER TABLE a ADD FOREIGN KEY (b_id) REFERENCES b (id); ERROR 1206: The total number of locks ...
2
votes
1answer
101 views

Which of the following data duplication options across shards is recommended?

High performance mysql book suggests that for sharding a blog application, one may want to put comments data across 2 shards: first, on the shard of a person posting comment, and on the shard where ...
2
votes
1answer
252 views

Converting 3 Master + 1 Slave to 4 Master - Any advice?

so we have a beautiful 3 master 1 slave cluster running. we would like to convert this to a 4 master cluster. here is how the topology currently looks s1 enslaved by s3 s2 enslaved by s1 s3 ...
2
votes
2answers
516 views

MyISAM table keeps crashing. What are my options?

My MYSQL table is very large (250GB, around 200M rows). Recently I've been putting it through a lot -- parallel processes that do a large number of reads. It has started locking and crashing with ...
2
votes
1answer
287 views

Has anyone ran into this replication breaking bug before?

I've been having this issue where replication was breaking on colliding primary keys. The annoying thing was these were auto increment generated. I think my issues been tracked down to ...
2
votes
1answer
2k views

Mysql thinks Master & Slave have the same server-id

I've been setting up a master->slave_a->slave_b relationship and having a little trouble. I currently have the master->slave_a replication working fine. Each of the two slaves have exactly ...
2
votes
1answer
1k views

MySQL Config for Large MyISAM tables

I have to do some updates on myisam tables which have around 20 M records. Our server has 24GB of ram and 2 quad core xeon with 24 MB of cache. I want to know how I can change the my.ini file so I get ...
2
votes
3answers
2k views

What's the difference between master and slave nodes in MySQL Cluster?

I use MySQL Cluster 7.1.15a. I have 2 nodes, ndb_mgm -e shows: [ndbd(NDB)] 2 node(s) id=3 @X (mysql-5.1.56 ndb-7.1.15, starting, Nodegroup: 0, Master) id=4 @Y (mysql-5.1.56 ndb-7.1.15, ...
2
votes
2answers
3k views

How can I define the datatype of a view in MySQL?

I've written a simple aggregation view over a table, and the view returns different data types that the table. This causes some issues on the application side. Here's a simplified query for the sake ...
2
votes
1answer
2k views

Rebooting a MySQL Replication Slave

For the first time since it was set up I need to reboot a read-only MySQL replication slave. I found this article about downing a slave for maintenance (albeit he's just describing stopping the mysql ...
2
votes
2answers
652 views

MySQL Workbench Loses Saved Connections

I've been using MySQL Workbench as my go-to management application for quite some time. However, recently (namely, in my most recent install), it will every so often lose the saved connections I've ...
2
votes
1answer
105 views

MySQL user account 'From Host' setting

I've just installed Wamp server 2 and I get the following 4 default MySQL users: USER FROM HOST <anonymous> localhost root localhost root 127.0.0.1 ...
1
vote
2answers
161 views

Relational Model vs. Database Products [on hold]

The formal Relational Model (logical level) for databases is not exactly the same thing as you get with MySQL, Oracle or other major products / implementations of ‘relational’ database management ...
1
vote
1answer
53 views

MySQL - varchar length and performance

Is declaring VARCHAR size make sense for performance? Is there any difference (in speed) between VARCHAR(50) and VARCHAR(255)? Or defining length is logic/design constraint?
1
vote
1answer
31 views

Adding time to an event that runs on a weekly basis

I have this event that runs every Thursday CREATE EVENT le_drop_database ON SCHEDULE EVERY 1 WEEK STARTS CURRENT_DATE + INTERVAL 3 - WEEKDAY(CURRENT_DATE) DAY DO BEGIN drop database carbon; ...
1
vote
1answer
31 views

Change column without interrupting service

We need to change the data type of a column in a large table (~1.7 GiB) from VARCHAR(1024) to VARCHAR(512) in order to be able to create a secondary index over two columns. We're using MySQL 5.6 with ...
1
vote
2answers
43 views

MySQL user without SUPER privilege

This may be a simple question and may not be the right place to ask but I couldn't find any useful information from Google. Here is my question. When creating users in mysql using grant all ...
1
vote
0answers
49 views

MySQL replication of single DB to same server

I have a DB Server running CentOS 6.4 and MySQL 5.5 from RPMs (not source). I have dozens of databases but one of them is being hit quite hard with large selects which locks out other updates which ...
1
vote
1answer
67 views

temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave

I executed some alter statements on my master server, which have been successfully executed on master (now there is no temp table on master) and all alter also have been replicated on slave server ...
1
vote
1answer
63 views

Why am I getting different results from a query vs. a view

The base problem I'm facing is that I need the latest record for each item. A little setup... MySQL 5.6.14. I need to create two views (because MySQL won't let me have a subquery in a view). My ...
1
vote
2answers
64 views

Freeing up disk space in 'mysql\data'

I am trying to free up some space used by a MySQL instance, but I know pretty much nothing about MySQL. I notice that in the mysql\data folder there are lots of localhost-bin.0001** files, as ...
1
vote
0answers
50 views

Significant difference between duration for querys in SHOW PROFILE and SHOW PROFILES?

I have use mysql profiler to optimize an opencart query which takes more than 10 seconds to executes. mysql> SHOW PROFILES; ...
1
vote
1answer
64 views

Slow queries InnoDB occasionaly

I have the next table: CREATE TABLE `sessions` ( `session_id` char(32) COLLATE utf8_unicode_ci NOT NULL, `session_expires` int(11) NOT NULL DEFAULT '0', `session_data` varchar(10000) COLLATE ...
1
vote
1answer
22 views

Is there any value in adding an already indexed column to a composite index?

The title says it all. Does the query optimizer see composite indexes where each column is already indexed as redundant? Or, is it used when each of the composite index's columns are in the WHERE ...
1
vote
2answers
77 views

Do Inactive MySQL Databases Consume Memory?

I have inactive databases that are on the server just for archive purposes. I know I can do mysqldump for all of them, but I am not 100% sure they are inactive. When I started my system I didn't ...
1
vote
2answers
163 views

Securing Your MySQL Server within your team?

Recently I have been having an issue with our internal team members changing the password of the root user. I noticed the following things: OLD_PASSWORD = 1 (So all passwords on our development ...
1
vote
1answer
60 views

Is there any way to get the estimate cost of executing a query in MySQL?

In PostgreSQL, EXPLAIN or EXPLAIN ANALYZE will show the estimate cost of executing a query. But EXPLAIN in MySQL doesn't provide this information. How can I get the estimate cost without installation ...
1
vote
1answer
75 views

creating table result in errno 150

I'm trying to create the following schema, but I'm getting errors when I'm trying to create project and staffphone tables. Not sure what is it complaining about. mysql> CREATE TABLE staff( ...
1
vote
1answer
86 views

Migrate from innodb_file_per_table to off in MySQL

We have a large number (2,500+) of databases and users in MySQL 5.6 on a single virtualized server (4 cpus and 8GB of memory). All the tables are using InnoDB. The actual data size is not very large ...
1
vote
1answer
74 views

How does the transactions interact if you set a different isolation level per connection?

I read that I can have different isolation levels per connection and in the server. The default isolation level is REPEATABLE-READ. So if I have a transaction that issues a SERIALIZED to the client ...
1
vote
1answer
284 views

MyISAM with Innodb in the same Wordpress database reduce performace?

My new Wordpress database all the Wordpress created tables use InnoDB engine and plugin created table use MyISAM. But older site database all the tables use MyISAM table engine. I use MySQL ...
1
vote
3answers
3k views

How long will a temporary MEMORY table persist if I don't drop it (MySQL)

I'm using a recursive stored procedure in MySQL to generate a temporary table called id_list, but I must use the results of that procedure in a follow up select query, so I can't DROP the temporary ...
1
vote
3answers
306 views

What happens when we take a timediff between timestamp and datetime

Say I have following table in mysql (5.5+): CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL, `x_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ...
1
vote
0answers
161 views

Missing rows in MyISAM tables with 10-20 billion records

I've got 2 tables with 10-20 billion records each. Currently these are in MyISAM (MySQL). They only require to be built and indexed once, after this only SELECT is used. They are currently ...
1
vote
0answers
2k views

mysql 5.6 GTID replication 'Got fatal error 1236 from master when reading data from binary log'

I Checked SHOW SLAVE STATUS error is "'Got fatal error 1236 from master when reading data from binary log: ''The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master ...
1
vote
2answers
99 views

Is column order reliable in UPDATE?

Can we reply on the order of columns in an UPDATE? SET @x = 0 UPDATE table1 SET col1=IF(col1 IS NULL, NULL, @x:=@x+col1), col2=IF(col2 IS NULL, NULL, @x:=@x+col2), col3=IF(col3 IS NULL, NULL, @x:=0) ...