MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
1answer
286 views

mysql emits BINLOG rows even though binlog_­format=­STATEMENT

We have MySQL 5.5 installed, we have the binlog_format=STATEMENT (the default) According to the MySQL Documentation, the binary log should contain SQL statements only, but we still see BINLOG rows in ...
2
votes
1answer
108 views

Created databases and inserted data on MySQL slaves in Master-Slave replication - how to fix?

I got MySQL Master-Slave replication running on 1 master with 2 slaves. SHOW MASTER STATUS / SHOW SLAVE STATUS says they are in sync, but databases have been created on the slaves and filled with data ...
2
votes
1answer
704 views

Monstrous query hanging on “copying to tmp table” in MySQL

I have a huge query being run by one of my developers which seems to hang no matter which performance tunables I adjust. Though I'm somewhat of novice at writing SQL queries, at first glance the ...
1
vote
1answer
648 views

processlist showing '%' as host from trigger

I'm trying to populate a certain column in a table with the host/IP of the caller. I have an insert trigger on the table which sets the column like so: CREATE TRIGGER access_insert_trg BEFORE INSERT ...
0
votes
2answers
3k views

Foreign key part of composite primary key of new table

I have tried searching loads but I have not received a concrete answer to my problem. Suppose I have Table 'A' with primary key as Apk. This Apk is a foreign key in Table 'B'. In Table 'B' I have ...
4
votes
1answer
201 views

Can I partially invalidate the MySQL table cache?

I want to set a fairly large table to MyISAM mode to keep it cached proactively. The table file is approximately 3GB. After several hours, MySQL invalidates the entire table from cache, while < ...
2
votes
2answers
558 views

MySQL, import SQL dump with another database

I have an SQL dump of db1 that contains this line: "USE db1" If I import that file into db2 with: mysql -D db2 -uroot -p < /var/backups/db1.sql.gz Is there the risk that db1 will be overriden? ...
1
vote
1answer
354 views

mysql session logging tables

I've been building a session logging system for our sites. We get around ~15m hits a year and at the moment we're really only interested in stats about people who logon to our sites. Our table ...
3
votes
2answers
773 views

Save history editable data RDBMS

I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means ...
1
vote
2answers
329 views

mysqlslap number in sql statement to change with iteration

I am trying to figure out how to increment a number in a mysqlslap query with every iteratiom. Let me explain what I exactly mean: I got a query: SELECT age FROM member WHERE id = [some number] ...
2
votes
2answers
1k views

Which is better for long term database scalability: adding columns or having key/value store

For application, which has few (5-10) settings at the moment but will have a lot more (up to 100) in the future which would be better approach: to add new columns to settings table every time new ...
3
votes
2answers
673 views

Redirecting MySQL errors and slow logs into another database

I am running a number mysql servers as slaves and masters. It is quite annoying to look for slow logs and errors in every server, so I would like to log all the errors into external database. DB1 ...
3
votes
2answers
5k views

How to make a trigger that will compare input with a value of other table?

I had made two tables with the following fields: TABLE 1: "Bookings" FIELD: "fk_flight_number" FIELD: "date_of_reservation" TABLE 2: "Flights" FIELD: "flight_number" FIELD: ...
4
votes
4answers
13k views

MySQL vs SQL Server Express (Compare Free DBMS)

I am searching for a comparison table between MySQL and SQL Server Express. Comparisons should include... Usage limit Tools (management/dev/backup/monitoring/...) "simultaneous connection" It's ...
2
votes
2answers
5k views

Mysql replication and ignore tables

When setting up master/slave replication in Mysql, do you then need to ignore any tables in /etc/mysql/my.cnf or should replicate_wild_ignore_table be leaved out under normal circumstances? This is ...
19
votes
2answers
25k views

Is MySql's LAST_INSERT_ID() function guaranteed to be correct?

When I do a single row INSERT to a table that has an AUTO_INCREMENT column I'd like to use the LAST_INSERT_ID() function to return the new AUTO_INCREMENT'ed value stored for that row. As many SQL ...
0
votes
2answers
148 views

Data Recovery in Mysql

Let the data have been backed up at time interval of 2 hr and taking backup at time "A" and after 1 hr Database crashes so how can I recover in MySql from Logs.
4
votes
3answers
666 views

Is Oracle DB immune to the InnoDB deadlocks found in MySQL?

It seems odd to me that MySQL would not handle this internally: "Deadlock found when trying to get lock; try restarting transaction" Does Oracle DB work around this issue? After all, Oracle owns ...
1
vote
2answers
417 views

Mysql: Large Query with subqueries optimization

Any query optimization gurus in the house?? Anyway, I trying to attach this on two fronts, one I'm trying to code a way around it but secondly, I'm thinking some real optimization might help in the ...
5
votes
1answer
4k views

MySql - Changing expire_logs_days without restarting the server

I'm using MySql 5.5. Is it possible to change expire_logs_days and have the changes take effect without restarting the server?
4
votes
4answers
986 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 ...
3
votes
2answers
2k views

Can I do anything about a very long running KILL command?

I've got a table with 103M+ records in it and takes up 70GB on the disk. I decided to DELETE (in one statement) 70% of the records. As expected, this took an awfully long time to run. I decided to ...
1
vote
1answer
2k views

Can we back up mysql databases by copying files?

I tried to reinstall mysql on my machine, but it keeps failing. Everytime it fails, it does this locking and that locking and future effort would fail again. Finally I decided to do truly clean ...
1
vote
1answer
241 views

Change mysql storage engine from Innodb to MyIsam: Max locks exceeded error

I have about 5 million records in a table which uses Innodb as storage engine. I have about 6000 users who use (do transactions) the same table. It sometimes becomes very slow or hangs. I decided to ...
1
vote
1answer
871 views

Query to take partial backup in MYSQL

I tried to take full backup in mysql using enterprise backup by using the command mysqlbackup -u root -p --backup-dir=g:\full_bck backup But I could not take backup of a single database , I ...
1
vote
1answer
114 views

How can I reinitialize some MyISAM tables without restarting MySQL?

I'm using rsync to copy some measurement data from one PC to the server. Is it possible to load the new MyISAM tables without restarting the MySQL server?
3
votes
2answers
6k views

GROUP BY two columns

I want to count two columns, but need to get the results of first column in one row. SELECT country, COUNT(*) FROM table1 GROUP BY country, type This query gives me country type COUNT(*) ...
1
vote
0answers
82 views

Mysql: Large Query with subqueries optimization

Any query optimization gurus in the house?? Anyway, I am trying to attack this on two fronts, one being to code a way around it but secondly, I'm thinking some real optimization might help in the ...
0
votes
0answers
25 views

keeping track of certain related field changes [duplicate]

Possible Duplicate: Save history editable data RDBMS I am totally new to databases and MySQL. I am working on a project in which I have a few related fields such as question and multiple ...
1
vote
1answer
354 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
515 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
291 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
136 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
4k 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
2k 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
966 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
137 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
730 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
489 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
608 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 ...