MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
348 views

Should this be an identifying relationship or not?

Well I've attached the image to explain what I need to know: I thought it was supposed to be an identifying relationship, but now I kinda thought maybe it wasn't? The thing is the user won't ...
2
votes
1answer
1k views

Is a query with UNIX_TIMESTAMP using indexes in mysql?

If I use .. WHERE UNIX_TIMESTAMP(`some_timestamp`)>NOW() in a SELECT, does this query still make use on an index, set on some_timestamp ?
2
votes
1answer
1k views

MySQL - Create a function from another function

I want to create a function same like my old functions. When I create a table, I can use `AS to other table as follows: CREATE TABLE dummy2 AS SELECT * FROM dummy1; Can I do the same thing with ...
0
votes
1answer
506 views

INNER JOIN between 2 tables vs WHERE on one table performance

Suppose I have two tables: CREATE TABLE `doctors` ( `id` int(11) NOT NULL AUTO_INCREMENT, `clinic_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; ...
1
vote
2answers
286 views

Mix of Cyclic and Chained MySQL replication - What do you do with “log-slave-updates”..?

I have the following MySQL replication topology set up: Master1 (M1) replicates to Master2 (M2) Master2 (M2) replicates to Master1 (M1) Master1 (M1) replicates to Slave1 (S1) Pictorial View ...
0
votes
1answer
133 views

MySQL query optimization

I'm quite new to big MySQL queries (I used to extract raw data from tables then manipulate them with PHP) so I'd like to know if my query below is too "heavy" or slow for some reason (it seems to be ...
5
votes
3answers
1k views

InnoDB Master-Master replication goes inconsistant after power failure tests

Afternoon gents, I'm currently stress testing a Master-Master replication setup using InnoDB as database engine. We're using this simple script for testing which we run in Linux CLI from a remote ...
1
vote
2answers
3k views

How to analyse innodb status on deadlock in insert Query?

I have a dead lock which i have posted here. I managed to get the innodb status from my server. Here is the Innodb status. OS WAIT ARRAY INFO: reservation count 247864, signal count 247470 Mutex ...
0
votes
1answer
165 views

What MySQL storage engine and table schema should I use?

I am making a web project and I created necessary and the most important table, but I am not sure that I did it right. In this site users will post data like text, picture, text's category, etc. I ...
-2
votes
1answer
1k views

Which is the shortest way to find out if a float IS NULL or equals 0 in mysql [closed]

I want to type the least possible characters, to find out all columns in a table that are 0 or NULL In this example I explain it further: lets assume the table test CREATE TABLE IF NOT EXISTS ...
0
votes
1answer
946 views

MySQL query with an OUTFILE to csv not working when called from a batch file

When I execute the following query, it works: SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' INTO OUTFILE 'D:/stats.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; But when I ...
0
votes
0answers
145 views

can not insert big data to mysql database [closed]

I am trying to save big data to database I extend the max_allow_packet and post_size but nothing happened. the code and database released to production server and to stage server on production server ...
1
vote
1answer
135 views

Regularly import tab-seperated log file to MySQL

I'm looking to keep arpwatch entries in a MySQL database to crossreference with other information I'm storing based on mac addresses. I've manually imported the arpwatch database into my mysql ...
0
votes
1answer
59 views

Searching through lots of applications

Google search has instant search feature. While you are typing, it finds the pages. Apple.com search box. StackExchange ask question part finds similar questions while you are typing question title. ...
0
votes
1answer
722 views

MySQL slow sending data

I have a large table that frequently sends 3,000 rows of data to a client. The longest wait state is Sending data. I would like to minimize this if possible! Is there a foolproof way of doing so? ...
1
vote
1answer
3k views

How to COUNT number of rows with LIMIT?

I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too? Currently, I use two separate queries to do so as SELECT COUNT(*) FROM col WHERE CLAUSE ...
0
votes
1answer
79 views

Max and group by query help [closed]

We have query as below. What we need is to select particular training with latest date and only single data but now when we run the query below we get more then one results. SELECT ...
3
votes
1answer
49 views

MySQL Help with Desktop Based Administration

I am making a MySQL based database for a professor and she would like to have a version uploaded to the web and a version on her desktop for personal use. The web version would be "official" whereas ...
3
votes
2answers
2k views

Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE

If I understand SELECT ... LOCK IN SHARE MODE correctly, you can place it into a mysql transaction to select the rows you will be working with during that transaction. This is done in order to "lock ...
1
vote
1answer
481 views

Failed to restore mysql database with zmanda

I want to setup a slave from my backup version of mysql using zmanda (it's a full backup). I've followed this guide. Result of mysql-zrm-reporter backup_set backup_date ...
2
votes
1answer
597 views

Do I have to filter the percona.checksums table in replication for use with pt-table-checksum?

I've set up MySQL replication, one master and one slave. Now I just starting to check the data for integrity on both sides by using Percona's pt-table-checksum. Wonderful tool to detect any changes or ...
1
vote
0answers
82 views

MySQL: Assessing performance of a query by using EXPLAIN

It is said that the number of rows are scanned is smaller the performance is higher when use Explain to optimize query. So I wonder When the EXPLAIN output includes many rows, is there any formula ...
1
vote
2answers
771 views

How do I remove duplicate entries in a given timeframe?

My table looks like this: +-----------+ | id | <- primary key | time | <- DateTime | row2 | <- varchar | row3 | | ... | +-----------+ I want to remove all ...
7
votes
2answers
447 views

Data Compression Through Base36 Encoding

I'm trying to store a lot of large numbers that truncate when stored as INT values. Unfortunately they are too large for this type. I'm thinking about using Base36 encoding to accomplish this. Are ...
1
vote
1answer
137 views

Simple Slow Query Not Using Indexes

I have a slow query running on a large forum. The EXPLAIN synopsis: mysql> EXPLAIN SELECT COUNT(post_thanks.id) as thanked, post_thanks.postid, post.title, post.dateline FROM ...
6
votes
1answer
7k views

Struggling to debug high CPU usage on Amazon RDS MySQL instance

We're running an m1.xlarge MySQL RDS server and having some issues with high CPU usage. We had some problems a couple of weeks ago with CPU utilization reaching 100% on a large instance. When we ...
6
votes
2answers
911 views

SQL ANSI JOIN precedence

I have a query that looks like this: SELECT * FROM TBLA A LEFT JOIN TBLB B ON A.Col1 = B.Col2 RIGHT JOIN TBLC C ON B.Col3 = C.Col4 JOIN TBLD D ON C.Col5 = D.Col6 In which order ...
3
votes
2answers
1k views

MySQL replication problem: slave has become out of sync

I have a Master & Slave setup. Embarrassingly I managed to execute some data changing statements (INSERT and UPDATEs across 3 tables) on the Slave. Obviously this stopped the Slave replication. I ...
0
votes
1answer
401 views

MySQL non-sequential primary key of certain length

what's the best way to create a non-sequential primary key of a certain length in MySQL? I'm looking to use the primary key as a 'Pin' number which real actual human people can use to refer to the ...
1
vote
1answer
2k views

MySql: #1221 - Incorrect usage of UNION and ORDER BY

I have the following query which is intended to retrieve the newest 3 comments for each post in an array of posts. SELECT * FROM comment AS tbl WHERE sscm_id IN ( SELECT sscm_id FROM comment AS t1 ...
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
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 ...
1
vote
1answer
2k views

Retrieve double data type value in non exponential form

Is it possible to retrieve non-exponential value in double data type column? After the width of the column is increased to 15 it shows the values in exponential form. Now I get the following: ...
1
vote
0answers
112 views

migrations within transactions

I have a bash script to run migrations on our MySQL databases. Is it possible to wrap migrations within a single transaction when running multiple migration scripts from the command line? ...
1
vote
2answers
116 views

How can I improve this query

I have the following table: CREATE TABLE `twitter_relationships` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `source_twitter_id` bigint(20) NOT NULL, ...
0
votes
1answer
37 views

Mysql database connector table or keys

This may be a simple a question but I just wanted to clarify, at work I have a table containing a list of titles and I also have a list of authors in two different tables. What would be best way to ...
1
vote
1answer
248 views

Can't start MySQL after creating new slave from innobackupex [closed]

I am setting up a new MySQL Slave and I can't start mysql after replacing the datadir (/db/mysql) with the innobackupex backup from the master. I have given the directory the permissions with: sudo ...
1
vote
0answers
780 views

MySQL: Writing log entries from stored procedure

Is there a standard way to write log messages in MySQL stored procedures? I can write log messages to a designated log table, but I prefer the standard log mechanism where each message is coupled ...
2
votes
1answer
262 views

How to solve the error “Error 'Got error 0 'No error' from NDBCLUSTER' on query”?

I'm currently working with a large network of MySQL 5.1 DBs (using MyISAM tables) and I'm evaluating a move to MySQL Cluster. I would like to have the MySQL Cluster replicate off of one of the ...
0
votes
2answers
209 views

MySQL: SELECT the n-th item from an ordered result

SELECTing the top n elements from a table is easy: SELECT id FROM pixels WHERE pixel_id='some_pixel_id' ORDER BY id DESC LIMIT 1000; Finding the n-th element with a subquery is quite ...
6
votes
1answer
2k views

MySQL table creation insanely slow

A simple table creation on one of my MySQL databases takes forever: mysql> CREATE TABLE blah (id BIGINT UNSIGNED NOT NULL PRIMARY KEY); Query OK, 0 rows affected (16.58 sec) The machine is quite ...
0
votes
1answer
48 views

How to summarize Bridge results?

I am trying to summarize Bridge results for each pair (north/south pair and east/west pair). Match points for pair north/south is MPNS, for pair east/west is MPEW. Is there a way to summarize all ...
2
votes
1answer
142 views

Rank in more tournaments and boards

Source data is of sample tournamet board player score 1 1 2 990 1 1 4 980 1 1 6 480 1 1 7 450 1 1 9 980 ...
9
votes
4answers
6k views

Adding an index very slow…is there a mysql cmd to get an ETA or show progress?

I'm currently running an alter query on my table (20M entries) in order to add an index. it's running for more than 3 days already (stuck on 'copy to tmp table'). is there a way I can see the ...
0
votes
3answers
132 views

Joining two tables and query them

I want to select the name and date of birth of all living artists who have value more than 50. My tables are artist and picture and I wrote this SQL statement but it is not giving me the desired ...
2
votes
3answers
231 views

MySQL data view

I have a table that looks like this: +----------------------------------------+ |Name | kode | jum | +----------------------------------------+ | aman |kode1 ...
1
vote
2answers
148 views

How to remove tables and indexes?

I've a file with the SQL statements to create tables, indexes, etc, and another file with data test to use in the test database. Then, I want that they can be removed. How to remove table and indexes ...
7
votes
7answers
2k views

MySQL Indexing VarChar

I am trying to index my blogentries database for better performance but found an issue. Here is the structure: CREATE TABLE IF NOT EXISTS `blogentries` ( `id_id` int(11) NOT NULL AUTO_INCREMENT, ...
1
vote
1answer
54 views

Query from database

I have a table named mip. The table has primary key id, a foreign key product_id and a column usage_type. product_id is a primary key id to a table named product. It is one to many relationship from ...
1
vote
0answers
119 views

What would make a MySQL database create tables in a different collation other than its default?

I am adding tables to a Wordpress database on my localhost and it is changing my collation from utf8_general_ci to latin1_swedish_ci. I'm kinda stumped because I'm not familiar with working directly ...