MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
141 views

what are the checklist when looking at MySQL database for first time?

What should I put on my checklist when I look at MySQL database for the first time? I would appreciate your help. Thanks.
1
vote
1answer
343 views

How to propagate a grandparent relationship throughout a tree?

I've got a MySQL table with columns id and parent, wherein each row has another row as a parent, except for some special root rows with parent=0. There are no cycles or broken relations. In order to ...
3
votes
3answers
1k views

Speed up search across multiple columns

We have a table: user_info (user_id,domain_id,first_name,middle_name,last_name,work_place,.,.,.) Now, we have a user search that search across three columns first_name,last_name and work_place. ...
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: ...
1
vote
1answer
258 views

xtrabackup “failed to read metadata” [closed]

Doing a staging run setting up a new xtrabackup restore script I'm running into a somewhat cryptic error I can't readily google a solution for. I'm working with an incremental backup design. During ...
6
votes
2answers
3k views

MySQL CAST vs. CONVERT

Let's say that I have a VARCHAR (which contains numeric data) that I want to use for a simple computation (adding 10 to it). According to the MySQL documentation on CAST functions, I could accomplish ...
2
votes
1answer
326 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
714 views

How can I check if the keys are enabled on a MyISAM table?

I have a script, which populates 4 tables. It goes like this: Disable the keys on all the tables Populate the tables Enabled the keys back. The problem is that windows decided to reboot the ...
3
votes
2answers
9k views

MySql recommended hardware

Our company servers are currently hosted on a VPS and we've decided to move into dedicated servers. When choosing the best hardware for a DB server, what should we invest more resources: better CPU ...
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 ...
1
vote
1answer
301 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 ...
3
votes
1answer
356 views

MySQL Config on Mac

I have a MacBook pro with 2.53 GHz Intel Core 2 Duo and 4GB DDR3 ram. I am running a huge database on my laptop with over 100 million records. I want to know : Which of the .cnf files is suitable ...
1
vote
1answer
792 views

How do you structure a database that allows for groups to be formed/added by users and also be a part of like facebook?

I'm new to databases and web development so a laymen explanation is best for the time being. I'm only in the data modeling phase so no need for specific code. I'm just trying to wrap my head around ...
1
vote
1answer
299 views

Why are these queries slow in some environments and not others? Can we fix their performance?

The following stored procedures in a MySQL 5.x environment: DELIMITER // CREATE PROCEDURE sp_belongs_to (IN schemaName VARCHAR(100), IN tableName VARCHAR(100)) BEGIN SELECT TABLE_NAME as ...
3
votes
1answer
221 views

MySQL chain replication

I have 3 servers A, B and C. What I want to do is replicate databases like this A->B->C, where A is replicating DB1 and DB2 to B and B is replicating those same databases to C, but I would also like ...
2
votes
1answer
162 views

Can not add list partitions

Following is the create table statement that is working as expected. But if I have a table without any partitions and if I try add partitions, it seems that I can not add more than 16 values in it. ...
4
votes
2answers
5k views

Performance settings for MyISAM tables - keep everything in memory

I have a 8-core MySQL server with 32 GB of RAM, holding 40 MyISAM tables (total: ~4GB of data). Since the databases size is several times smaller than the available memory, how can I make full usage ...
3
votes
1answer
1k views

MySQL ignoring index, index cardinality is the same as number of records in the table (?!)

Everyone, I could use an extra set of eyes on an issue we've come across. Here's what we're seeing: We're writing some code to migrate our messages to a new table called communications. We're ...
1
vote
1answer
105 views

How to determine if a column is the first part of an index?

Is there anyway to tell with SQL if an indexed column is the first part of an index. The closest thing looks to be information_schema.columns. But that seems to only tell me if it's a PRI or MUL key ...
1
vote
1answer
4k views

How to set a specific directory location for individual MySQL database?

Is it possible to set each database in MySQL to use a separate datadir? I'm running a userdir development (sandbox) server and would like to put the MySQL data files for the database(s) for that user ...
2
votes
2answers
159 views

How to index this table (a_level, b_level, item_id)

I have a table of items, each of it has an a_level, b_level, and an item_id. Any b_level is dedicated to only one a_level (example: b_level 14 is "child" of a_level 2 only) Lets say we have million ...
3
votes
3answers
2k views

Finding five-minute interval prior to current time

I want to find the five minute interval just before the current time. So if the current time is 8:43 I want to return 8:35 and 8:40. Here is the query that I have written and it is working as ...
4
votes
2answers
761 views

Read mysql config (my.cnf) using mysql console

I have a mysql server that is not under my control (I don't have ssh on that machine). I only have access to one database using mysql client. How can I have access to config options that are declared ...
3
votes
2answers
3k views

Creating and editing my.cnf by MySQL Workbench?

I just installed MySQL Server and MySQL Workbench on a clean Snow Leopard MacBook. I hooked up Workbench with the server and was able to start the MySQL Server from within the Workbench. I enter the ...
1
vote
1answer
217 views

Is there any way to increase the mysql reserved pool for super users beyond 1?

http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html States that there is 1 connection beyond your maximum number of connections for users with the super privilege. My question is can ...
3
votes
1answer
829 views

Keeping MySQL staging database on same server updated from production

I just migrated my MySQL 5.5.20 to Amazon RDS from MySQL on EC2. The one thing I'm having difficulty with is how to keep my staging database updated from production. Before, I just ran a nightly ...
3
votes
1answer
325 views

index not being used

I have an exact same myisam table on master and slave. The indexes are not being used on Master server. mysql> explain select count(date_time) from ox_data_summary_ad_hourly where date_time = ...
2
votes
1answer
95 views

Linking identifiers across tables in MySQL (Foreign Keys)

Given a simplified example of two tables table Contact(ID int primary key, Name text not null) table ContactPhone(ContactID int not null, PhoneNumber text not null) Is there a way to tell MySQL ...
2
votes
2answers
271 views

What does Threads_connected=-1 signify in MySQL?

I was running the following: mysqladmin -u root -p ext -ri 10 | grep Threads_connected and was kind of surprised to find that Threads_connected could be -1: | Threads_connected | 1 ...
5
votes
1answer
2k views

Find out MySQL index size for a concrete index

In MySQL, for InnoDB engine, how do I find out the size of one of the indexes I have in the same table ? I know I can select the index_length from information_schema.tables, but this gives the total ...
3
votes
3answers
102 views

A join aggregate behaviour I can't understand

Query 1: SELECT c.class_id, SUM ( CASE WHEN s.student_id IS NULL THEN 0 ELSE 1 END ) as total_students FROM classes c LEFT JOIN students s ...
2
votes
2answers
474 views

What is this host?

I found this in my local MySQL installation today: mysql> select host, user from user; +--------------+---------+ | host | user | +--------------+---------+ | ::1 | root | | ...
1
vote
1answer
250 views

MySQL replication risks for the master

I have a production MySQL server, fairly heavily loaded with an INSERT-only load on one of its databases. I'd like to attach a non-production (sitting on my desk, more or less) replication slave to ...
3
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
2answers
1k views

MySQL Replication via DNS Failure

I'm having some trouble using replication when the machines are configured to use host names instead of IP addresses. The "skip-name-resolve" is OFF. If I use IP addresses, everything is fine. Using ...
5
votes
4answers
2k views

MySQL subquery slows down drastically, but they work fine independently

Query 1: select distinct email from mybigtable where account_id=345 takes 0.1s Query 2: Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above ...
2
votes
2answers
3k views

MySql - Read only on slave

I want to make my MySql slave read only, i've been looking and found the read_only option, but its says that users with super privalges can still write (if i understand the text correctly), here is ...
2
votes
2answers
540 views

how to insert multiple columns(around 40) into my sql database using java

i have a program to read the content of an excel file to my-sql db. i have 40 columns in excel and i created a table of 40 fields. and i want to import the data from excel into corresponding table ...
2
votes
1answer
122 views

MySQL switch between data directories for individual databases

I'm in the middle of designing a solution to an issue we're currently experiencing while writing to certain large MyISAM tables. The database in question basically stores information regarding two ...
2
votes
1answer
878 views

ACID-compliance of MySQL master-master replication?

I've created two MySQL 5.1 servers, replicating off each other (log-bin) in a low latency environment (different AZs within an EC2 region). All tables that are being replicated are using the InnoDB ...
1
vote
1answer
1k views

When to open your MySQL cursor

A relatively simple question but from all my research I have been unable to find a decent answer. Which is technically more correct, open your cursor after your handlers have been defined or before ...
1
vote
2answers
713 views

Database Design - Employee Clocking In and Out System

I want to develop Employee Clocking In and Out System (website). I am concerned two things: If staff forgot to 'Clock Out' from yesterday and they have 'Clock In' today, it should flag to the ...
2
votes
1answer
173 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 ...
2
votes
1answer
1k views

“Data truncated” vs “Incorrect string value”

I'm trying to figure out the difference between some warning messages related to data truncation. Consider the following table: CREATE TABLE `txttest` ( `mycol` text NOT NULL ) ...
2
votes
1answer
1k views

alter table (mysql) adding column with default value of space (' ')

On my Mac, using 5.5.11 MySQL Community Server (GPL) alter table foobaz add column ( extendedTitle varchar(3000) DEFAULT NULL, clasindxIndent int, spacedash1 enum (' ', '-', '1') default ' ', ...
1
vote
1answer
494 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 ...
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 ...
5
votes
2answers
7k 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 ...
1
vote
1answer
417 views

Can I use the “import” tool on PHPMyAdmin multiple times?

I am moving databases between a couple of shared hosting accounts, and both of them only give access to the MySQL databases through phpmyadmin. The importing account's upload limit is 50MB. I would ...
3
votes
1answer
643 views

mysqladmin debug writes out Current locks but PROCESSLIST and INNODB STATUS don't show any

I'm using the 1.1.8 mysql-cacti-templates to gather metrics on MySQL 5.0.77 (CentOS 5.7). Both the InnoDB Current Lock Waits and InnoDB Lock Structures graphs are charting with Cur[rent] values but ...