MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
1answer
67 views

Query difference with where condition

I need to find the ids of all rows in a table A without a matching row on table B. Following the answer on this question I'm using a left join like this: select A.id from A left join B on B.id_A = ...
0
votes
0answers
85 views

Configure phpmyadmin to run on different hosts

Just created locally a new host pointing to a new Virtual Host. And added through command line a new user at the new host. So like 'user1'@'localhost' I now have 'user2'@'local2' . The problem is I ...
5
votes
1answer
245 views

Walking a BTREE index as far as possible in MySQL

Suppose one has a column of words on which one builds a BTREE index: CREATE TABLE myTable ( words VARCHAR(25), INDEX USING BTREE (words) ); LOAD DATA LOCAL INFILE '/usr/share/dict/words' INTO ...
1
vote
1answer
232 views

Query for localization app

Here are my tables: mysql> SELECT * FROM locale; +-----------+---------+ | locale_id | value | +-----------+---------+ | 1 | English | | 2 | Deutch | | 3 | French | ...
5
votes
1answer
5k views

Enabling “log_queries_not_using_indexes” disables “long_query_time”?

I noticed that, in Mysql, when enabling log_queries_not_using_indexes, slow_query_log and long_query_time, the generated slow query log file will contain a lot of queries even if the query time is far ...
0
votes
2answers
2k views

How often does the slow_log table get updated in MySQL?

I've changed the long_query_time from 10 secs to 5 secs. I haven't seen any queries logged for a few hours now. Am I supposed to tweak my database for the slow_log table to gets updated more often? ...
3
votes
2answers
4k views

How much memory do I need for innodb buffer pool?

I read here that You need buffer pool a bit (say 10%) larger than your data (total size of Innodb TableSpaces) On the other hand I've read elswher that innodb_buffer_pool_size must be up to ...
0
votes
2answers
335 views

Autosync master and slave when master goes offline

I have a master slave setup using MySQL 5.0. I have very frequent writes to the master and slave is used only for backup purpose (no explicit writes). In my current configuration, the slave database ...
5
votes
2answers
7k views

How to search for a specific column name in all the tables in MySQL Workbench?

In MySQL Workbench, is it possible to search for a specific column name in all the tables? (Writing the string to look for in the field at the top right does nothing). Thank you.
2
votes
2answers
316 views

INSERT succeeds but all inserted values become NULL

I tired to execute an insert directly from phpMyAdmin as follows: INSERT INTO oracle.PLAYLIST_MUSIC ( TID, ID, STATUS, CREATED_BY, ...
0
votes
1answer
716 views

What to do with alerted variables phpMyAdmin for MySQL databases?

I've noticed the following alerted variables in phpMyAdmin Variable Value Description Aborted clients 5 The number of connections that were aborted because the client died without closing the ...
1
vote
2answers
92 views

Change the Existing Topology of a MySQL Replication Setup

I want to be able to edit the topology of an existing replication, is that possible? For example if I have a star topology, I want to edit it to make it a ring one. How can I do that? I'm thinking ...
1
vote
1answer
302 views

MySQL Dump all rountines except one

We want to export a large amount of tables and routines and we've do this often. Each database, table and routine should be dumped with one exception: a procedure with a specific name. So I was ...
1
vote
2answers
4k views

Getting repeated value in a group_concat only if ids are different

I have two tables, first the table Product: id|category_id --+----------- 1 | 12345 2 | 12345 3 | 12465 And then a table activity: id|prod_id|activity_type |description ...
0
votes
1answer
115 views

MS SQL and MYSQL [closed]

I was going through some theoretical concepts about SQL based databases. One thing that I found interesting was that MySql and MS SQL are Relational-SQL-Database Management-Systems. Since they both ...
0
votes
2answers
2k views

How can you monitor if MySQL binlog files get corrupted?

I was wondering if there was any nice way to continuously monitor mysql master binlog files and detect if get corrupted.
3
votes
1answer
255 views

What's the latest on MySQL Proxy?

Does somebody here know the latest state of the development of MySQL Proxy? Most materials I found on web are outdated.
1
vote
2answers
135 views

Can't create table with this script

Trying to create a table with the script below. Every time I execute it I get the following error and can't work out why?! #1064 - You have an error in your SQL syntax; check the manual that ...
1
vote
3answers
258 views

How can I best combine 3 queries into 1?

I need the following queries combined into one query that returns the following from all results from the first query you see below (Right now I load the first query into an array in php then run two ...
2
votes
2answers
2k views

Best option to back up mysql innodb partition

We have a number of tables which we plan to partition based on months. The issue now before we can drop those partition we would like to perform backup. One option we found is this link ...
0
votes
2answers
274 views

Best Way to Partition By City / State in MySQL 5.5

I have a table with ~20M rows and every query against it will include state or city or a combination so I was thinking of partitioning the table using a combo of the two fields like so: ALTER TABLE ...
2
votes
1answer
3k views

MySQL 5.5 - determining correct write[read]_io_threads on high-end system?

Specifically dual-CPU 32 core - 128RAM - RAID 10 SSD . Ubuntu 64 server. Heavy Innodb load at times - ~ 2000+ queries per sec. Heavy read and write. Currently we are running on the default ...
2
votes
2answers
1k views

Having Problems Installing 64-bit MySQL 5.5.8 x86 on OS X 10.6.6

I'm really new to databases and SQL in general, previously having installed MySQL only to hand it over completely to some CMS or other. Now, I want to get serious about adding SQL and MySQL or ...
1
vote
1answer
112 views

mysql allow access for localhost and named server

Hope we're enjoying this Fine Friday afternoon and getting ready for the weekend. I've got a quick question. I'm trying to set up a user to allow them access from localhost and from a specific named ...
2
votes
1answer
111 views

Slow Query Gets Even Slower After Indexing

I have a MySQL database with just one table containing 8 million records. I need to query the table to find a single record that matches a specific number: select * from my_table where ...
3
votes
1answer
1k views

MySQL 5.5 : (InnoDB) - Manually edited an .ibd file to test recovery, now entire MySQL installation is broken!

So I wanted to test MySQL recovery from Innodb corruption. file-per-table is enabled. I have a few test db's on the MySQL instance. I shutdown mysql. Manually edited/added random characters to an ...
0
votes
3answers
115 views

Mysql: privileges to get access to “mysql” database

Tell me please - what minimum set of privileges the user should to have to get access to the "mysql" database on the server. For example to get ability to execute query: select * from ...
3
votes
1answer
18k views

MySQL: Execute CREATE TEMPORARY TABLE SELECT just one time?

I want to create a temporary table with some select-data. My (strange) problem is that I have to do the same query more than one time. CREATE TEMPORARY TABLE IF NOT EXISTS cache (id int(11) NOT ...
4
votes
1answer
2k views

Proper tuning for 30GB InnoDB table on server with 48GB RAM

I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the ...
3
votes
1answer
7k views

MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?

When reading online about innodb_log_file_size, it says The combined size of the log files must be less than 4GB So what happens if it needs to be larger? In books and online, it says to set ...
4
votes
2answers
1k views

MySQL Slow Query Log - SELECT /*!N SQL_NO_CACHE */

Using mysqldumpslow, I can see that the most common entries in a MySQL slow query log are of the following form: SELECT /*!N SQL_NO_CACHE */ from wp_posts (or wp_comments). I can't seem to find ...
1
vote
2answers
1k views

Split date range into indiviual records

I have a table called staff_leave, and I want to split date range to days per row. is it possible. I am a newbie please take it easy . like: there is a date range 01/01/2013 - 05/01/2013 and i want ...
0
votes
1answer
85 views

Is there any way to get recently inserted value in a table without using aggregate functions?

I tried to get the users list who are all accessed our application in past 6 months . In our table , we are storing login_time , user_id ..etc . It's a huge table approximately it will contain over 3 ...
1
vote
1answer
447 views

Can I use symlink or data directory for ARCHIVE MySQL tables?

All mention of either using a symlink or data/index separate data directories state that MyISAM is the only supported (https://dev.mysql.com/doc/refman/5.5/en/symbolic-links.html). However, I found ...
0
votes
2answers
331 views

Best MySQL storage engine for read heavy table with two indexes

I have a pretty big MySQL table (70G) with about 15 columns and 1.5M rows. There is one index on the autoincrement id and one on a foreign key (integer). Apart from that there are several VARCHAR and ...
4
votes
2answers
492 views

Using MyISAM for reading and InnoDB for writing data

I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data. I want to use two structure like ...
1
vote
1answer
124 views

Phantom Table in MySQL

Programmer here, be gentle. I'm working in a MySQL database with only a single table, as shown by show tables: mysql> show tables; +----------------------+ | Tables_in_widget_web | ...
0
votes
2answers
55 views

creating user related user specific content

Should you create a table for each user to contain information on specific content for an account, say favorite articles, interests, ect. User defined lists that would be used to generate useful ...
2
votes
1answer
267 views

Restore InnoDB tables / database using only data files

I have MySQL Server 5.1.51 installed on Windows Server 2003 as Operating System and using InnoDB as database engine. I have run DROP DATABASE dbName statement by mistake. I have no backup of that ...
1
vote
1answer
130 views

Is there any tool to convert Oracle commands to MySQL?

Is there any tool which I can use to convert Oracle commands to mysql commands?
2
votes
1answer
137 views

Finding similar values in columns

For example I have a column id and column email. I would like to find all values from id and column that share similar pattern. let's say: in id 'column' I have Alexander and in 'email' column - ...
0
votes
2answers
74 views

Fetch Records alternatively from database

I have following table where each record indicates either start time or end time of a shift for staff. The first record is for start time, and the next immediate record is for end time. SELECT * ...
1
vote
1answer
101 views

tuning feed item's table query with multiple condition

I have a query like this: SELECT * FROM `rss_item` WHERE `feed_id` = 1 and `timestamp` < 1350698635 ORDER BY `rss_item`.`timestamp` ASC MySQL: 5.1; table engine: innodb Explain result: ...
10
votes
5answers
3k views

Is it unwise to run replication on same physical server?

I'm contemplating setting up a Master-Slave replication for my database. The slave server will be used for redundancy and possibly a reports server. However, one of the biggest issues I'm running into ...
0
votes
1answer
270 views

Storing 5TB web server access log vs OLAP DB

We have over 5TB compressed web server log in a HDFS and we often analyse using Hadoop. It is painful to run map reduce on 5TB of data and most importantly, not many developer are familiar with it. ...
4
votes
2answers
5k views

mysql auto adjusting max connections values

I have set max connections to around 2000 in my.cnf file. max_connections=2048 I have upgraded to 5.5.20 and now I see the following line in the error log. 120201 19:40:24 [Warning] Changed ...
1
vote
1answer
2k views

MySQL master/slave replication… structure not being replicated

Edit: I might have answered my own question while going back thru the links for this post. I would still like to get insight from more experienced DBA's and perhaps make the information easier to ...
8
votes
3answers
5k views

How to stress test a SQL server?

I've been tasked to stress test our MSSQL Server and MySQL Server. I would like to know if there are any tools or scripts which I could use on our current systems and the new system to compare ...
10
votes
3answers
19k views

Any better way out of MySQL InnoDB log “in the future”?

I've got this InnoDB error in MySQL 5.0. Mysqld was stopped cleanly, but I managed to lose ib_logfile0 & ib_logfile1 afterward. Now after a clean startup, InnoDB has done its "crash recovery". ...
1
vote
1answer
160 views

Setting up simple relational database structure

I'd like to set up simple MySQL database but I'm a bit confused about everything, so let me try to explain... I have 4 relevant tables, let's call them POS, POS_STRUCT, POS_STRUCT_DIV and MEMBERS. ...