MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

12
votes
4answers
23k 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
201 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 ...
3
votes
1answer
1k 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 ...
1
vote
2answers
820 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
173 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 ...
2
votes
0answers
222 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
123 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
664 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
233 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
305 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
444 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
198 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
358 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
574 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
186 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
762 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
385 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) ...
2
votes
2answers
2k 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
1k 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
516 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
106 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
859 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
972 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 ...
3
votes
3answers
5k 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
97 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
151 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
713 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
187 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
39 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
136 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
108 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
314 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
558 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
226 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
506 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
2k 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
3k 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 ...
1
vote
1answer
682 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 ...
2
votes
1answer
814 views

how to improve query performance

## Only 1 query which returns 10 rows. ## It took about 66 secs mysql> explain SELECT al.log_id , al.user_id , al.page_name , al.section_name , al.submit_time , al.url , ...
0
votes
1answer
386 views

what's the optimal partition setup for a linux db server?

My host wants to know how I want to partition my server before installing CentOS. I need to be able to do use xtrabackup to create quick backups from 20-50GB databases. I know I need to use a LVM, but ...
1
vote
1answer
910 views

Where is the MySQL variable - innodb_flush_method?

I would like to tweak the value of innodb_flush_method to find out its performance impact on a database server. That variable is listed when I run the command SHOW VARIABLES. But I could not find it ...
2
votes
1answer
1k views

Issue with connecting to the mysql database

We have an outstanding issue with VCC in chicago, there is an issue connecting to the mysql database If an agent does a refresh of the monitor they will be logged out. This is the error we ...
1
vote
0answers
68 views

pt-table-checksum generates no output

I've been using pt-table-checksum on our staging server successfully, but when I run ./pt-table-checksum h=localhost,u=user --ask-pass --lock-wait-time=50 --databases prod It just sits there. No ...
2
votes
3answers
247 views

mariadb ignoring force index

I'm trying to tune a relatively simple query: select bt.col1,bt.col2 from bigtable bt join smalltable st on bt.smalltable_id = st.smalltable_id where st.name = 'some name occuring only once in st' ...
2
votes
2answers
278 views

Bin logs with high i/o

I'm DBA (IBM Informix) and already use MySQL with Drupal , where have low workload. So, my knowledge of MySQL is minimal. Few days ago I start use MySQL with a basic configuration as back-end for a ...
5
votes
1answer
2k views

Deleting MySQL table with pending transactions

Is there a way to delete an InnoDB table or database with pending transactions in MySQL (preferably on file system level)? What happened: I use MySQL 5.5.28 and ran LOAD DATA INFILE… to import a ...
1
vote
3answers
349 views

Which RDBMS has the most advanced “table-valued” functions?

Every Database Management System focuses on specific features and applications. I am using mysql, probably as it is popular and satisfies common needs. However, I came across a problem, which needs ...
0
votes
2answers
72 views

MySQL query slow even with indexes

Structure of my table mysql> show create table t_group_tag_relation\G *************************** 1. row *************************** Table: t_group_tag_relation Create Table: CREATE TABLE ...
1
vote
1answer
186 views

mysql freeze/crash on JOIN

here is query: SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image, nw.status, src.title as src_title, src.keyword as src_keyword, src.url as src_url, src.icon as ...
3
votes
1answer
69 views

Way to add referencing and referenced entities of a specific entity in E-R Diagram MYSQL

Is there any way to add entities that are referencing a specific entity in an E-R Diagram. I have a database containing 400 tables. I want to see a view containing a specific entity and its ...