MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

3
votes
1answer
3k views

How do I show warnings when loading data created from mysqldump?

I've got a large .sql file with large insert into ... values ... statements. Many of these statements are generating warnings during execution. How can I get mysql to print the warnings? If it hit ...
3
votes
1answer
6k views

Creating a table from variable names

Is it possible to create a table from a variable name? For example I have the following stored procedure that I'm trying to write but it will not compile past with the create statement. CREATE ...
4
votes
4answers
992 views

Help with Query. Finding records that have the same relationships (MySQL)

I have a table of Listings that has a many to many relationship with a Taxons table. The table structure looks like this: listings ---------------- id (int) name (varchar) listings_taxons ...
1
vote
1answer
72 views

How can this data be joined?

Definition: table_A table_B id,description id,description,id_table_A Column id_table_A is a foreign key to the primary key of table_A and is not mandatory. Sample data: ...
2
votes
1answer
201 views

Entries disappeared, auto_increment also reversed, web app security vulnerability or DB Error?

I am using Mediatemple's Grid Hosting (GS) platform to host a wordpress site. The DB tables are using the MyISAM engine. I had noticed that a previous article had disappeared from the site. At first ...
5
votes
2answers
612 views

Large accounting table, best way to partitioning by two dates

I need to create a log table to stores the connections to out network (last 3 years, after that the log will go into backup). The hardware/software used is proprietary, and for accounting it just call ...
1
vote
2answers
1k views

How to install MySQL server on Mac (OS X 10.6.8) from command line?

How to install MySQL server on Mac (OS X 10.6.8) from command line? If I donwload the .dmg file, is it possible to install it from command line?
0
votes
2answers
1k views

MySQL replication fails due to duplicate keys after master is restarted

I'm new to MySQL load balancing, I want to try the master-slave concept. Whenever I setup master-slave for the first time, and both have up-to-date fresh data, that works perfectly fine. However, ...
1
vote
1answer
1k views

Soft delete: Copy row data to archive table when deleting

How can I move data from my current table to an archive table when a delete is called on the table? My current table and archive table needs to be in sync in their schema too. Can someone help ...
6
votes
2answers
9k views

Calculating disk space usage per MySQL DB

I am currently using information_schema.TABLES to calculate the total disk space usage grouped by the database name, but it is running terribly slowly. On servers with hundreds of databases, it can ...
3
votes
2answers
894 views

How to backup a MySQL database without slowing the application?

I'm an application developer and wondering about our database backup strategy. Right now our sysadmins backup our database every 8 hours. I don't really know how the do it, but while the backup is ...
1
vote
1answer
563 views

Unable to connect to MySQL cluster database

I am unable to connect to mysql cluster database after starting my management node, data node, mysql node. I made the necessary changes by adding the loadbalance in the dbURL. But I am not able to ...
6
votes
2answers
17k views

How to log verbose output from mysqldump?

You typically save the verbose output of a Linux command like this: # command > output.txt But when you use a command like mysqldump, the > option outputs the dump of the database tables to ...
7
votes
3answers
7k views

InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)

This server is a virtual machine running CentOS 6.0, MySQL 5.5.21 There is a database named devSystem. There are InnoDB tables within. Running the following commands causes error ERROR 2006 ...
1
vote
1answer
859 views

MySQL Cluster over WAN: Best Architecture Options?

My company is looking at a multi-data-centre solution for high-availability, fun, and profit. We have businesses across the planet, with the need for MySQL database clustering solutions that can keep ...
4
votes
1answer
4k views

How can I find who holds the lock based on the hex-dumped?

I've been reading the Diagnosing MySQL InnoDB Locks article. Karl E. Jørgensen writes on 2008 so I'm consfusing it is in effect. I would like to supply a snippet of the SHOW ENGINE INNODB STATUS: ...
2
votes
1answer
4k views

How is INDEX on Composite Primary Key in mysql?

When making a composite primary key for two or more columns, e.g. PRIMARY KEY(col1, col2, col3); will the system INDEX each column individually? The reason that I am asking this question is that when ...
2
votes
1answer
1k views

Why can't I get a lock on an InnoDB table?

I have problem with lock table command: LOCK TABLE v3_cam_date WRITE; the command always running with state: "Waiting for table metadata lock". Try to find out which thread was hold the lock on ...
1
vote
2answers
392 views

How to model double relationships?

Background: I have designed a solution for Filemaker to input data into a Question Database. After we are done with inputting data into Filemaker, we will be exporting the data as excel and ...
10
votes
2answers
7k views

innodb_file_format Barracuda

I have a couple questions for those more familiar. Most of my instances have been running Antelope despite having support for Barracuda. I was looking to play around with some compresses innodb ...
3
votes
1answer
525 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?
3
votes
1answer
2k views

How do I diagnose missing rows on a MySQL slave?

I'm running traditional master/slave replication on MySQL 5.1.52, my master is set for statement based replication. A few tables are missing rows on the slave (differing row counts), these tables are ...
2
votes
1answer
1k views

how do I limit the characters in selected row and con-cat with '…'

I have a query which return a column named comment. I want to limit the number of characters returned by select statement to 50 and if the comment row contain more than 50 characters, I want to append ...
8
votes
2answers
21k views

How can I move a MySQL database to another drive?

I'm using MySQL 5.5 on a local machine to analyze a large amount of government data. I've created a local database that resides on my default drive (Win7 C: drive). I'd like to store the data on my E: ...
3
votes
2answers
4k views

mysql out of available memory

We have a dedicated MySQL server with 16GB of RAM that's serving 8 web workers with relatively high traffic (over 1000 requests per second at peak traffic) At peak we're seeing the following errors ...
0
votes
1answer
425 views

What are the parameters that are needed to be monitored for MYSQL Cluster

I am new to MySQL cluster. I had installed MySQL cluster successfully with 2 data nodes and 1 Management Node and 1 SQL node. I want to know where and how exactly the core data is getting stored. ...
3
votes
1answer
1k views

Getting duplicate primary key for auto increment column

I have a MySQL Innodb table with 'id' as auto increment primary key column. My Scripts are doing following operations: insert ignore into tablename set x='a', y='b' update ignore tablename set ...
1
vote
1answer
2k views

strategy for enabling a general query log on MySQL 5.0.95 with no restart?

I would like to configure a general query log for a busy MySQL 5.0.95 production server without effecting the availability. Obviously I can set the "log" option in the my.cnf file and restart the ...
2
votes
2answers
7k views

How to use RESTRICT for Foreign Key in mysql?

In the database structure of CREATE TABLE Country ( name varchar(40) NOT NULL, PRIMARY KEY (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE City ( name varchar(40) NOT NULL, ...
5
votes
3answers
692 views

how do I model this in database?

I am using mysql as my backend database. Here is the use case; A HolidayPackage is configured with some default offerings. These default offerings can be up/downgraded based on the options provided. ...
3
votes
3answers
665 views

Need help configuring Memcached to store values in MySQL Cluster

I am working on a Memcached implementation with MySQL Cluster 7.2. I have installed and configured memcached with following parameters: PORT="11211" USER="mysql" MAXCONN="1024" CACHESIZE="1024" ...
1
vote
1answer
312 views

Installing Sphinx on Mac OS with XAMPP

We have: Sphinx 2.0.3 Mac OS 10.6.8 XAMPP 1.7.3 (with MySQL 5.1.44) Here is PATH: user:sphinx-2.0.3-release user$ echo $PATH ...
1
vote
1answer
893 views

MySQL hang in load data infile command

From the link: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process? I had changed the command order (unlock before commit + replace some ...
3
votes
5answers
2k views

Two-sided UNIQUE INDEX for two columns

Creating a table with composite PRIMARY KEY or using UNIQUE INDEX for two columns guarantee uniqueness of col1, col2. Is there a tricky approach to make the reverse order of two columns UNIQUE too ...
1
vote
1answer
793 views

Usefulness of Foreign Key WITHOUT reference option

Sorry, if this is naive question, but I searched a lot and did not get a vivid answers. Consider a simple InnoDB FOREIGN KEY without ON DELETE or ON UPDATE I think this will avoid INSERT of a value ...
4
votes
1answer
1k views

How can I prevent gaps in a MySQL table AUTO_INCREMENT field?

For example, I have a table for user registration: CREATE TABLE IF NOT EXISTS test_user ( user_id int(10) unsigned NOT NULL AUTO_INCREMENT, user_name varchar(50) NOT NULL, PRIMARY KEY ...
2
votes
1answer
996 views

MySQL using trigger instead of check

Is there any way that I could use trigger instead of check? like I have CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER test BEFORE UPDATE ON apply FOR EACH ROW BEGIN ...
1
vote
0answers
506 views

How to customize Place Order Functionality In Magento? [closed]

In Magento, when any customer buys product from front-end, then at last customer goes through order placing. According to my need, I have created one CUSTOM TABLE in Magento database. So my ...
3
votes
2answers
1k views

Using Foreign Key and References for ON DELETE CASCADE in mysql

I was thinking that these two ways for creating foreign keys are the same CREATE TABLE child1 ( id int(11) not null auto_increment, parent_id int(11) REFERENCES parent_table(parent_id) ON DELETE ...
2
votes
1answer
759 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 ...
0
votes
0answers
436 views

Why is MySQL making the CPU run at about 80%?

MySQL is eating up about 80% of my CPU for no reason as far as I can see. Right now this server is rarely used, more of a test site I set up that will eventually be a used for production once I fix ...
0
votes
1answer
357 views

Database Design: What is the best structure for storing site offices and locations?

Background I am creating a database driven web application for the management team at the place I work. Part of this application requires fairly specific locations to be linked with where equipment ...
3
votes
1answer
997 views

mysql, changing tables from myisam to ndb

Gents, Converting a table from myisam to ndb via: ALTER TABLE piwik_log_profiling ENGINE NDBCLUSTER; This will fail with: BLOB column 'query' can't be used in key specification with the used ...
1
vote
2answers
4k views

Innodb UPDATE performance

We recently switched one of our tables to innodb and now we are experiencing very slow UPDATE execution times. An update which used to take 0.010-0.030 seconds can now take over 70 seconds. Some ...
4
votes
1answer
1k views

Can SHOW TABLE STATUS query performance on MySQL be improved - Amazon RDS

I have a fairly large database on the Amazon Relational Database Service (RDS) running on MySQL 5.1.57. There are 900+ tables, so that running the "SHOW TABLE STATUS" query can take up to 30 seconds. ...
3
votes
2answers
2k views

MySQL connection count much larger than query count

I'm maintaining a relatively simple LAMP server. It does how ever have a some what high load. What I'm noticing is that in a one hour time span there are roughly 50k connections made to the mysql ...
-15
votes
1answer
1k views

Please help with these database interview questions [closed]

What is your company backup policy? If something happend to your db how to restore it? What type of clustering present used in your enviroment? Whis is the best replication? Why? If sql server want ...
2
votes
3answers
725 views

Which database is best for deeply embedded database or through C DLL?

I want a deeply embedded database. Deeply embedded means that the server is started by an application and closed by the application with no tcp/ip or 0 port. The main features of consideration are: ...
2
votes
1answer
974 views

How can I use a MySQL query in an openquery query on Microsoft SQL Server.?

I have the following query which works fine in MySQL: DELETE sb FROM stockbatches sb INNER JOIN batchestodelete btd ON sb.batch_batch = btd.batchno AND sb.batch_part = btd.batchpart ...
0
votes
1answer
442 views

How do I restore a SQL backup, while excluding a specific table?

How do I exclude a specific table during a SQL backup RESTORE operation? My question is specific to restoring a backup sql file. I am using Putty and bash server.