MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
94 views

Long running query

This is my long running query: select * from t_keyword where id in ( select rel.element_id from t_event event join t_event_element_rel rel on event.id = rel.event_id where ...
1
vote
2answers
143 views

How to optimize this query?

Query: Select * from `t_event` where `create_user_id`=7 and (`event_create_date`)=('2012-12-18 00:00:00') and `event_type_cd`=11 and `domain_id` =602 and `job_id` =1 limit 1 Table structure: ...
0
votes
2answers
284 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 ...
3
votes
1answer
3k views

How to re-assign two variables in a conditional UPDATE

Consider a simple update as SET @var1 = 20; SET @var2 = 26; UPDATE table1 SET col1=IF(@var1>50, @var1 := @var1-col4, @var1 := @var1+col5), col2=IF(@var2>50, @var2 := @var2-col6, @var2); ...
2
votes
1answer
3k views

Update MySQL slave when changing the hostname/IP of master

I have one master and one slave replicating great, but the master is about to have both it's hostname and IP changed. In master.info on the slave server I see the current hostname for the master, if I ...
1
vote
1answer
362 views

How to re-arrange rows to best fit within groups?

I categorized a table with the method introduced here to categorize my items to folders with a limit size of 100. mixing_order id num_items cat order_in_cat cumulative_sum folder 1 ...
2
votes
1answer
184 views

how to deal with foreign keys with fixed values (on a db-table-basis)

I have a control table that looks like this: CREATE TABLE `tbl_control` ( `tm_db_tbl` varchar(64) NOT NULL, `tm_key` int(11) unsigned NOT NULL, /* a bunch of other fields here */ PRIMARY KEY ...
3
votes
1answer
11k views

MySQL my.cnf vs my-huge.cnf

I've recently installed MySQL server and client 5.5.28 through rpm packages on an openSUSE machine, and I was not able to locate my.cnf file. I look into all directories mysql --verbose --help | grep ...
1
vote
1answer
2k views

Getting the first day of week

In continuation of my previous question: Get week/month data I have a MySQL table with day column of type DATE and position column of type INT. I need to fetch average position for every week in ...
3
votes
1answer
242 views

What is the right way to manage users data?

I'm trying to create a simple register login script with user profiles, I created a table with the following structure: +------------+---------------+------+-----+---------+----------------+ | Field ...
2
votes
2answers
506 views

MySQL index on column comparison

How do you go about setting an index to be used for a column comparison like WHERE col1 > col2? col1 and col2 are of DATETIME type.
1
vote
1answer
7k views

“Waiting for initial communication packet” error 0 in MySQL

I am using MySQL server 5.1.51 on a Linux machine. The application was working fine for the last 32 days, but since yesterday a number of clients are not able to connect to the database from our ...
15
votes
4answers
53k views

Access denied for user 'root'@'%'

I used to access the root user in MySQL just fine. But recently, I am no longer able to. I am able to login fine : mysql -u root -p Here is the mysql status after login : mysql> status ...
0
votes
1answer
243 views

DBT2 benchmark for AWS RDS

I am new to MySQL and linux and also AWS. Previously i have worked with windows azure and .NET framework. Currently I am trying to get DBT2 benchmark to run for AWS RDS for MySQL. I was able to ...
5
votes
1answer
2k views

Unknown option '--no-beep'

I installed a new version of MySQL server (previously 5.0, now 5.5) and I copied the data folder in to the new one. Now I should perform mysql_upgrade. I opened the command prompt (server is located ...
3
votes
2answers
2k views

MySQL binary logs - skip a table

Is there a way to skip a specific table in the binary logs? I've got binary logs configured as my incremental backup strategy between nightly backups. But we have one huge reference table that we're ...
0
votes
1answer
219 views

Converting dbo file from windows to linux

I have a .dbo file which is created from windows. This file is succesfully reloaded into the mysql database in windows. I need to reload the dbo file into the mysql/mariadb database in linux. How I ...
3
votes
0answers
417 views

Dynamic MySQL partitioning based on UnixTime

My DB design includes multiple MYISAM tables with measurements collected online, Each row record contains auto-incremented id, some data and an integer representing unixtime. I am designing an aging ...
1
vote
0answers
195 views

MySQL Partitioning, Reorganize Max Value partition (Known Bug / Feature)

I have written a crone job which reorganizes last max value partition every week into 2 new partitions , one for the previous week , and one for the future data (new max value partition which ...
3
votes
1answer
856 views

Mysql Server optimization tips needed! (InnoDB)

I've recently been trying to optimize our mysql server but I've encountered some pretty serious problems on the way. We're running a game server which is coded in C#. We have two dedicated servers. ...
1
vote
1answer
458 views

Fill ibdata1 With Zeros/Nulls/etc Or Change A Table's Tablespace

I have a MySQL 5.5 server running on CentOS 6. The MySQL package is a MySQL-released version (MySQL-server-5.5.28-1.el6.x86_64), not the stock CentOS one. I have moved an existing database over from a ...
3
votes
1answer
482 views

How to categorize rows by SUM of a column in mysql?

I have a table as id num_items folder 1 4 2 33 3 74 4 41 5 24 6 34 7 46 8 55 9 11 I want to assign folder number to rows but limiting the number of items in each folder (e.g. ...
1
vote
1answer
644 views

MySql proxy to replication configuration

Is there any solution (say a proxy) that makes MySQL replication cluster behave like one database towards developers? And developers then don't need to worry about using master to write and slaves to ...
1
vote
0answers
352 views

big mysql database optimization

My Joomla mysql database has about 3.4 million rows in content table. It is just static article. Very slow and often use out of resources. To speed up it, I segmented it into 6 sub-tables and added ...
9
votes
1answer
691 views

Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE

I am trying to have a score for entire section of SELECT statements SELECT *,MATCH(`result`) AGAINST('keyword') as `score` FROM `table1` WHERE MATCH(`result`) AGAINST('keyword') UNION SELECT ...
2
votes
1answer
1k views

Can't create/write to file MySQL

Server Configuration: MySQL 5.051a-community-nt-log Community Edition installed on Windows Server 2003 Standard Edition SP1. This MySQL Server is part of a client's WAMP installation Problem: ...
0
votes
1answer
268 views

mysql index on derived table [duplicate]

Possible Duplicate: Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE I want to have a FULLTEXT INDEX on data name in the base SELECT that selects data from another ...
2
votes
2answers
1k views

Enable slow_query_log tries to write to slow_log.CSV

I'm running MySQL 5.5 I wanted to truncate the slow log file, so I did: SET GLOBAL slow_query_log = 0; then truncated the file in filesystem. But, when I enable it back it fails: SET GLOBAL ...
0
votes
2answers
714 views

MySQL concurrent INSERTs

I have a MySQL database with InnoDB tables. There are different client processes making SELECT (to check the existence of a value) and INSERT or UPDATE (depending on the result of the select) ...
3
votes
2answers
5k views

How to solve MySQL “The table is full” error 1114 with Amazon RDS?

I am trying to add a new column in a table and the query giving me this error 1114. My RDS instance size is small. 1.7 GB memory, 1 ECU (1 virtual core with 1 ECU) Table i am trying to modify is ...
0
votes
1answer
2k views

Importing CSV file to MySQL via PHPMyAdmin

I am currently stuck and unable to upload a complete CSV file into a MySQL database. Unfortunately, the nice folks at Stackoverflow haven't been able to help me solve this issue, so I'm hoping someone ...
1
vote
2answers
981 views

how to insert the remote table data into another table in my local computer in mysql?

can any body tell me. I have a remote table from which I need to import part of the data and I need to place into my table which is in my computer(local). I know select-into command and its use but ...
2
votes
2answers
114 views

Optimizing index creation

One thing that has bothered me for awhile is creating many many indexes for the one table where the query only differs slightly or contains many of the same columns. An example case would be: table1 ...
0
votes
2answers
2k views

Saving every mysql user login attempts

Is there a way to save the username and the IP from which the user tries to login to a MySQL server?? Enabling general-log seems to log everything but that seems to write too much details taking more ...
0
votes
1answer
3k views

How to UPDATE a column to SET previous row value to NULL cells?

I have a table as id value 1 music 2 3 movie 4 5 6 book 7 8 Some values are missing, and I want to assign the value of previous row to produce id value 1 music 2 music 3 movie 4 ...
5
votes
3answers
10k views

Why does MySQL produce so many temporary MYD files?

On a Debian Linux server, hosting many PHP/MySQL websites (photo galleries), sometimes I have "many" files like /tmp/#sql_6405_58.MYD. For example today : [2012-12-15 15:18:11] /tmp/#sql_6405_6.MYD ...
1
vote
1answer
119 views

Issues with a Table having 120 fields in mysql

I am creating a table with 120 fields in MySQL. It will have 50 varchar(255) fields, other are maximum fields with char(1) , 1 date field and 5 double fields and others are int(11) fields. My ...
1
vote
3answers
187 views

Where can I learn about MySQL master/slave, master/master, or clustering setups?

What are the best resources you've read or watched about setting up master/slave or clustering for database replication? I don't know much about this. I have a database that is getting rather large ...
2
votes
2answers
952 views

How to GROUP_CONCAT between given rows?

I have a table as id diary 1 breakfast 2 walk 3 start 4 office works 5 office projects 6 end 7 taxi 8 start 9 preparing for meeting 10 doing the meeting 11 end 12 night I want ...
2
votes
1answer
297 views

Improve the performance and stability of a MySQL server

We provide a B2B solution, which is doing quite a lot of UPDATE, INSERT an DELETE operations. Our infrastructure at the moment looks like this: Home-Server ( MySQL (INNODB) + website ) 5 worker ...
1
vote
1answer
48 views

Is it possible to CREATE EVENT to another server?

I have serverA and ServerB. I need ServerA to copy a database to ServerB and UPDATE/INSERT/DELETE as it changes. I think this can be done with replication but I don't want to continually open a ...
1
vote
1answer
204 views

Server load discrepency in Slave with Master load being low and constant

We have MySQL Replication Master/Slave Setup db01 Master db02 Slave (ReadOnly) We are getting load discrepency on the Slave (db02), but the Master's (db01) server load is low. MySQL is the only ...
0
votes
1answer
234 views

My SQL Replication to an online database server

I have two MySQL Databases: On a local machine (connected to internet via a static IP) My SQL database on my webserver (JustHost, which I can access remotely) I want a replicate my local database ...
2
votes
1answer
500 views

Analysis of MySQL database in real time Amazon RDS similar to phpMyAdmin

I'm using a single Amazon RDS instance (medium) to power several WordPress websites running on a single Amazon EC2 instance. I used to have my databases on another EC2 instance and installed ...
1
vote
1answer
1k views

Splitting a column to normalized table

I have a de-normalized table as Original De-Normalized Table id text 23 first,second|third,fourth,fifth|sixth I want to normalize the database to create the following tables Table 1: (Split by ...
1
vote
1answer
325 views

Simplify and optimize a complex query

Our site has a chronological event feed that uses MySQL. We have noticed that some of the queries tend to run for quite a bit longer than others. For example, some queries for our heaviest users take ...
1
vote
1answer
793 views

MariaDB allowing select only user to create databases

I'm baffled. I have a select only user, but somehow, the user can create a new database, and create table inside the new database. It can't create tables inside other db. It's really odd. This is ...
6
votes
4answers
3k views

MySQL InnoDB locks primary key on delete even in READ COMMITTED

Preface Our application runs several threads that execute DELETE queries in parallel. The queries affect isolated data, i.e. there should be no possibility that concurrent DELETE occurs on the same ...
2
votes
2answers
8k views

Getting last row for each day in MySQL

I have problem in selecting other columns for the max(datetime) records. In layman terms I need to get relevant columns where max(DialDateTime) records for all the dates in Mysql. mysql> select ...
2
votes
1answer
966 views

How to properly secure MySQL database?

We have a web application based on the famous triad apache + php + mysql which we sell to our customers and gets installed on their servers. Currently, we are using MySQL 5.1.41 which has only a ...