MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
1answer
824 views

shopping cart database schema

I need help creating the schema for a database to hold information relative to a web user's shopping cart/session. Each session will have a session ID, a time stamp holding time of creation, an expiry ...
2
votes
2answers
2k views

How do I replicate a MySql server's data without using mysqldump?

I've got a working server with a lot of data on it. I've built a new server that will be lots faster. Both are running x686 code on Debian Squeeze. A quick test using one of the tables shows that it ...
2
votes
2answers
116 views

How to see what was executed in the database via binlogs for specific date?

I am trying figure out how to "query" binlogs to see what was exectued in the database for a specific date. For example, if I execute mysqlbinlog binlog then the thing just runs forever and it is had ...
2
votes
3answers
297 views

designing multiple tables while eliminating duplication?

As I'm working on an educational website, I'm facing a problem with my database design scheme - I can't find the correct design that eliminates duplication in the tables I'm creating. What I'm trying ...
2
votes
1answer
4k views

MySQL Workbench asking for password

I am trying to set MySQL server on my Windows 7 64 bit machine. I have downloaded MySQL server and MySQLWorkbench. I have started mysql service by command C:\> net start mysql as suggested ...
2
votes
1answer
1k views

Set-up MySQL Server on Windows 7 64-bit

I am trying to install MySQL Server on my Windows-7 64-bit machine. I have downloaded MySQL Server Community Edition and MySQL Workbench. I am trying to start a new Server Instance from Workbench. I ...
2
votes
3answers
207 views

What is running right now?

I know I can use "show processlist" to see what is running when I'm in the office. How can I see what is running when I'm not there. Something is running on my server at 3am every morning that is ...
2
votes
1answer
2k views

Setting up replication for MyISAM tables?

Before setting up a replication within MySQL, the data first needs to be transferred to the slave. Since MyISAM tables can be copied as files. Would it be okay to copy MyISAM files, .myd .myi .frm, ...
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
400 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
1k 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
467 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
1k 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
769 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
238 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
102 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
183 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
504 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
594 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
253 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
524 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
289 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
655 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
106 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
168 views

Relational Model vs. Database Products [closed]

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
2answers
20 views

mysql information schema database last used datetime

I have a lot of tables in my database, almost 1,000. Most of them I am not using now. Is there any way to know from the information schema tables when I have last used my tables?
1
vote
1answer
33 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
46 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
50 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
74 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
64 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
65 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
53 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
67 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
79 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
62 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
78 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( ...