MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

3
votes
1answer
311 views

Slow-running query on clustered MySQL environment

I've got a very simple query, directly equivalent to SELECT * FROM sometable t WHERE somecol = 'somevalue' ORDER BY createdon DESC LIMIT 0,20000; When I run this query on my non-clustered ...
3
votes
2answers
347 views

Writing transactions to .MYD file to take Backup

I'm trying to backup MySQL DB by archiving the data directory of required databases. I'm doing FLUSH TABLES WITH READ LOCK before taking the backup, but even then some of the transactions were not ...
5
votes
1answer
579 views

MySQL replication - replicate to multiple DBs on slave?

I've got a single table in a master DB that I would like to replicate to multiple databases on one slave server. Like this: masterDB.tableA -> slaveDB1.tableA masterDB.tableA -> slaveDB2.tableA ...
3
votes
1answer
771 views

Table was not locked with LOCK TABLES when recreating an index

I have a weird problem when recreating the index. This is what happens. This is done through JDBC, in Java btw. A table 'stuff' is truncated by a process. The application is reinitalizing a cycle ...
1
vote
1answer
609 views

Mysql Commands out of sync; you can't run this command now

I have a script which is heavily doing select and insert nearly every seconds without fail and the total queries varies from a second to another. Below is the results of running the queries and ...
6
votes
2answers
25k views

way to prevent queries from waiting for table level lock

We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in ...
2
votes
2answers
117 views

Long JOINS Returns No result all tables have one to many relationship with a particular table [closed]

I have a Table Enterpries with Primary Key 'RCNO'. All other tables have a one to many relationship with Enterprise via it's RCNO column. I make a Join of all the tables and i get nothing. Below is ...
2
votes
1answer
102 views

Want to select distinct from 3 fields

I have a mySQL table with a town_id and 3 categories - cat1, cat2, cat3. I need to be able to come up with a distinct list of all the categories in the table selected by town_id, not duplicated and ...
3
votes
1answer
1k views

User autoincrement ID for username

I want to automatically create user's, which all have a unique ID. Now here's the problem. If two users are created at exactly the same nanosecond, it may not cause problems. Thus, what i do now is: ...
3
votes
3answers
234 views

Database tables design

If I have, in my Java code, the following classes: Patient :contains instance of anther classes (mentioned bellow) PatientBasicInformation: contains PatientId, phoneNumber, Name, Gender, Age. ...
2
votes
2answers
979 views

Why are parallel writes in MySQL as slow as sequential writes?

I have many MySQL write-only processes. They all go to the same MySQL master. I run them sequentially to get a baseline time. I basically chain together several mysql command-line clients, ...
3
votes
1answer
167 views

Database design - many to many

I have one MySQL table which contains: a question 4 possible answers separated by a comma a number (0-3) representing the correct answer. unique id with about 2000 entries. I also have another ...
3
votes
1answer
167 views

MySQL optimized query

Now I came across an issue, I have table as below: Task Id StudentId Marks 1 1 12 1 2 15 1 3 17 2 ...
3
votes
2answers
2k views

Mysql load from infile stuck waiting on hard drive

I've got a windows 7 64 bit machine that I'm using for some load testing of a mysql db. My program uses sqlalchemy to connect and run several load from infile statements on said database. These bulk ...
3
votes
3answers
3k views

Cannot write to mysql database while select query in process

Running Mysql 5.1.57 on InnoDB, and have a database locking issue. I have two separate sessions connected to a single database MySQL. On the first session, I run a long SELECT query (technically a ...
2
votes
1answer
908 views

MySQL: AVG of defined variable per month

I need help with the following code: SELECT DISTINCT userid , count( userid ) as login_count FROM ( SELECT DISTINCT userid , ...
2
votes
2answers
391 views

MySql Vs. Postgresql for a blog application

I am designing a blog application. Which of these fits better? In which use cases will mysql be good and for what use cases will postgresql be good?
7
votes
2answers
6k views

Slow MySQL Database Import with mysqldump and USE .. SOURCE

I have about 12 tables in MySQL innoDB, one of them has 11 million records in it. I used this command to back things up: mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz ...
1
vote
1answer
235 views

Archive table does not work

I have a 19GB archive table that is working as expected on one server. I copied the following 2 files to another server. The table is not working on the new server. r_mi_raw_20120712.ARZ ...
5
votes
3answers
2k views

CPU usage on RDS instance monotonically increasing with no change to query volume

I have two RDS instances: a R/W master and a read-only replica. On 29th June, the replica stopped registering replication data - not sure if this is related. On July 3rd, the master's CPU usage ...
2
votes
1answer
239 views

MySQL: Very slow query, not using index, no joins involved

I am running what I think is a very simple query: SELECT SQL_NO_CACHE m.mbr_nbr FROM tran.member m WHERE m.cardnbr = 000099999930 The table member is a poorly written one, with 133 columns and ...
2
votes
1answer
268 views

Best approach to design a database model for this situation? Clients and records

We need to store the following information for each client: Three types of record per file, for example (obviously, with real data): HDR2007022399999900000000000Soft/XX 0.0 XXX ...
3
votes
1answer
226 views

Get profiles values where message.sender_user_id = profile.user_id

I have this tables: default_messages CREATE TABLE IF NOT EXISTS `default_messages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL, `message` text ...
1
vote
2answers
562 views

MySQL table design / architecture, table is too big

I have a MySQL DB that contains a lot of text, I'm fetching data from a website and inserting it into a table. I'm using a SSD HD (100GB) for the DB and I'm out of space, I think that something in ...
1
vote
1answer
169 views

mysql: show which users have permissions to a DB

Is there a way to show all users that have permissions on a database? I'm looking for something like: > show_permissions_for_db <DB>; +----------+----------------------+ | User | ...
3
votes
1answer
497 views

Simple survey database design [closed]

I'm creating a survey for visitors of my event. However it's been a while since I created a database. So I need some help. I found some solutions but they are way too extensive and that is something ...
1
vote
1answer
1k views

MySQL, “columns partitioning” when multiple columns are individiually important

MySQL 5.5 introduces "columns partitioning". http://dev.mysql.com/doc/refman/5.5/en/partitioning-columns.html I'm trying to better understand how it works when two columns are important individually. ...
2
votes
2answers
5k 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
86 views

Where can I find a drawing of the physical structure of a MySQL Server?

I'm new to MySQL but want to learn more about it. I know Oracle and Rdb quite well. Can you show me where I can locate some type of drawing of the physical structure of a MySQL Server?
0
votes
1answer
1k views

Slow performance of MySQL Join Query

I have inherited a site (running Coldfusion8 and MySQL 5.0.88) with a product search using multiple criteria. I'm struggling with optimizing the search, especially a pricelist check, which makes the ...
1
vote
1answer
824 views

decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

i recently testing the move of our db from one server to another. as part of this process i copied all the data to the new db and started testing it. as a comparison i run a store procedure whose ...
3
votes
1answer
4k views

How can I insert smiley faces into MySQL ( 😊 )

I'm on MySQL 5.5.21, and trying to insert the '\xF0\x9F\x98\x8A' smiley face character. But for the life of me, I can't figure out how to do it. According to various forums which I've been reading, ...
1
vote
2answers
167 views

How do automaticly delete rows from other tables

I have a MySql DB. I have a table - recipes. Also have 2 tables -ingredients and instructions. both have for each entry - a foreign key to the related recipe. I want that when I delete an entry from ...
1
vote
1answer
119 views

mysql dummy where check to utilize multi column index

I have multi-column index on col1,col2. Both columns are unsigned int with values 1 or greater. I want WHERE col2=100. I write WHERE col1>0 AND col2=100. The query should now use my index. Does ...
1
vote
2answers
4k views

MySQL - INSERT INTO … SELECT … UNION

I'm having an issue with a fairly complex query where I need to store the ids from one table (based on heavy criteria) into a narrow, alternate table for future reference, but I keep running into a ...
0
votes
2answers
881 views

Trying to import a large sql file using BigDump. Partial fail occuring

Problem: I am using the php script: BigDump to import a sql file that phpMyAdmin says has 9344926 rows. However when I run it, BigDump calculates and inserts 4050623 rows It should have Total: ...
3
votes
2answers
3k views

How to setup mysql on tmpfs?

How to setup mysql on tmpfs ? I am reading a tutorial in french, where the author do it this way : 1.) Create a tmpfs directory : mkdir /var/tmpfs mount -t tmpfs -o size=1G tmpfs /var/tmpfs chown ...
3
votes
4answers
2k views

Optimising MySQL

As per this post, it looks like I'm having some serious issues with MySQL optimisation. Unfortunately, I have no idea where to start! I'm running a DirectAdmin-based CentOS dedicated server. It's an ...
0
votes
1answer
849 views

How to restore mysql from only myi files?

our server hdds has crashed and we didnt have a current backup so we sent our hdds to a data recovery company, they have been working on hdd's for over a week and they have finally managed to save ...
1
vote
1answer
136 views

slave issue with load data

When I use "load data infile" on master, the data is properly transferred to slave. But when I use "load data local infile" from remote server to master, the data does not pass on to slave. Why does ...
2
votes
3answers
1k views

MySQL backup with no downtime [closed]

I'm running a M/S replication of MySQL 5.5 / innoDB (over ubuntu). My DB is pretty large: ~250GB. I'm looking for a backup tool/service that will do the following: full backup no downtime / locks ...
1
vote
2answers
188 views

Is there a way to get slow-query-log-like profiling information on a single query?

Specifically, I'm looking for this information: # Query_time: 0.011922 Lock_time: 0.000088 Rows_sent: 107 Rows_examined: 663 Rows_affected: 0 Rows_read: 107 # Bytes_sent: 4152 Tmp_tables: 3 ...
4
votes
3answers
2k views

What are the limits on the amount of slaves per master?

I currently have a Master with 2 slaves, all running MySql 5.5. What are the limitations on the amount of slaves I can connect to a single master? what parameters should be taken into affect?
4
votes
1answer
9k views

What can cause TRUNCATE TABLE to take a really long time?

I'm running MySQL5.5 with Master/Slave replication (1 master, 2 slaves). I have a process that runs once a week and truncate a specific table. The table is not large and only has a few thousands ...
1
vote
1answer
1k views

Solutions to 4-byte utf-8 characters in mysql 5.1

I'm running into a problem that seems to be an issue w/ mysql 5.1s limitations on UTF8 characters. From http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-utf8.html Currently, MySQL support for ...
4
votes
4answers
953 views

MySQL console: Ctrl+C driving me nuts

There is something in the MySQL console that drives me nuts. When I hit ctrl+c to cancel the current command being typed, the terminal exits. In every terminal I know (*nix terminals, Python, ...
3
votes
2answers
3k views

pt-table-checksum help required

I am trying to figure out pt-table-checksum, as I am using it for the first time. It look likes the documentation is complex and not easy to understand stuff. I have a complex replication topology ...
0
votes
1answer
569 views

percona mysql crashing at innodb initialization

i have a slave percona db setup that also maintains few of it's own tables it started crashing with this in error.log: InnoDB: In a MySQL replication slave the last master binlog file InnoDB: ...
2
votes
1answer
3k views

mysql taking too long to send data

I have a simple table with million of records (14,000,000) and for a simple query it is spending too much time "sending data". The table CREATE TABLE IF NOT EXISTS details ( id int(11) NOT NULL, ...
2
votes
2answers
1k views

What's wrong with this MySQL query?

I am getting a syntax error for my MySQL (5.5.24) query. The error is: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right ...