MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.

learn more… | top users | synonyms

1
vote
2answers
31 views

Why is restoration from binary logs so slow?

We have a MySQL 5.5 server with binary logging enabled and a backup script that collects the binary logs and, once a day, generates a complete database dump with mysqldump. In testing database ...
1
vote
2answers
66 views

Mysql causing high load average with large amount of server resources left

I've have 2 servers running a application and the server that runs the mysql sgbd is with the load average around 3 times more than the webserver. I think that is something wrong because i have a lot ...
0
votes
2answers
111 views

Mysql 5.5.25 must be restarted to connect

We have MySQL v5.5.25 running on Windows Server 2008 R2. Occasionally people are unable to connect, and the only way we have found to fix this is to restart the mysql service. This happens every 2-3 ...
1
vote
1answer
20 views

MySQL, can't find MYD files on OS X, MAMP

Try as I may I can't find the data files for my MAMP installation! FRM files are here: /Applications/MAMP/db/mysql/database_name/ but, there are no corresponding MYD or MYI files anywhere. The ...
1
vote
1answer
23 views

In slow_log tables, sql_text column showing “throttle: index not used' warning(s) suppressed” instead of exact query

I'm trying to find the slow running queries from slow_log table (as I don't have direct access to server to check slow log file). In sql_text column instead of query it is showing "throttle: index not ...
1
vote
2answers
40 views

Awful execution plans after migrating from MySQL 5.5.11 to MariaDB 5.5.41

We just migrated our web site database from a server running MySQL 5.5.11 to one running MariaDB 5.5.41. Everything is working mostly fine, except there's at least one query from our storefront that's ...
0
votes
1answer
37 views

'USE db_name' takes too long to change when login remotely

I gave SELECT privilege to a user on a particular database, Now whenever I try to connect remotely $ mysql -h ip-of-remote -u username -p with that user credentials and try to change the database ...
0
votes
1answer
144 views

mysqldump 5.5.25 use swap instead of memory

mysql version 5.5.25, while use mysqldump to dump big tables, it eat lots of swap while there is lots of free memory. # free -m total used free shared buffers ...
0
votes
2answers
154 views

Table Design for Student Attendance hourly for each day

I have created a table (attendance_details) for storing every day attendance of Students in MySQL. Need to capture attendance for hourly (1-8hrs) every day. I have the student details in separate ...
0
votes
0answers
40 views

how can i connect from yahoo small business webhosting domain to remote mysql server

so far I have done these: set up remote access using Remote Mysql.Added the yahoo website's domain ip and yahoo nameservers. went on to my website on yahoo small business and set up an Aname and ...
1
vote
2answers
119 views

grant select on all databases except one mysql

I have user called test_user created under MySQL 5.5.17 under MS Windows 2008R2, I want to grant this user select privileges on all databases except MySQL database, note I have around 200 database ...
1
vote
0answers
19 views

Unsafe statement written to the binary log using statement format

In MySQL error log I am getting this warning message: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it ...
5
votes
1answer
69 views

MySQL overhead of calling a Stored Procedure

Within Java, we access MySQL (v5.5.40) via JDBC -- a prepare statement, setting parameters then calling the stored procedure. It appears that every execute() has the overhead of 4 meta-calls (per ...
7
votes
2answers
808 views

Why are mysql password hashes internally saved with a star (asterisk)?

I was reading upon some mysql internals, when going through the mysql.user table in the my mysql shell, I get mysql> select * from mysql.user limit 1 \G *************************** 1. row ...
0
votes
2answers
40 views

How to most efficiently store possible combinations of categories?

I have a table of categories (currently with 14 rows, not likely to change). A need to cache combinations of categories emerged, so I'm creating a table of allowed combinations (only few hundreds of ...
1
vote
0answers
17 views

How to make an indexed search over a list (set)?

A follow-up question for How to most efficiently store possible combinations of categories? An answer pointed to SET datatype. As far as I know, the default search option for it, FIND_IN_SET, doesn't ...
0
votes
1answer
16 views

manual transfer fails with table does not exist

I performed a manual transfer of data manually (zip up the dir and rsync'ed it over. I moved the members.ibd (the only .ibd file) to a save place, extracted and then replaced members.ibd with the ...
0
votes
1answer
42 views

Error: Incorrect usage of UNION and ORDER BY

I have this query: SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`)) from wp_result_plugin WHERE Bet=2 AND month(`Date`) = EXTRACT(month FROM (NOW())) ORDER BY MONTH(`Date`) ASC UNION ...
0
votes
1answer
11 views

How to store list data in our LMS?

I am looking at how we can store list information in our database. The example in question is regarding products. For every employee there will be a primary product and as many secondary products as ...
0
votes
2answers
212 views

Slow INSERT/UPDATE on InnoDB

I have a problem with the performance of my MySQL server, let me explain: I instaled a MySQL server, version 5.5.20 Server version: 5.5.20-log Source distribution SELECT queries runs fast on ...
0
votes
1answer
14 views

Import lots of mysqldumps with missing CREATE DATABASE

I have a bunch of mysqldumps of my old server with one database each. Unfortunately we forgot to add CREATE DATABASE ... in all those dumps. How can I import those files in one batch?
0
votes
2answers
2k views

MySQL 5.5 Fail start Fedora 16

I installed mysql and mysql-server from the repos (MySQL version 5.5). Then tried to start it, but got an error. [root@server]# service mysqld start Redirecting to /bin/systemctl start ...
0
votes
2answers
896 views

SQL Query is sometimes slow, sometimes fast

I have two hosts, one with my rails app, one with my MySQL database. Going through my rails Logfile I can see that sometimes queries take much more time than the same query at other times. Think 200ms ...
0
votes
1answer
36 views

mysql database under symlinked directory: ERROR 1018 (HY000): Can't read dir of './<dbname>/' (errno: 13)

I'm using mysql 5.5 on an ubuntu linux server. I need to move a database from its current location (under /var/lib/mysql) to a disk with more space, so I attempted to move the directory and create a ...
0
votes
1answer
18 views

Can combine five mysql statements into just one statement?

Can I combine all the five statements into just one statement? update mytable set myfield1 = replace(myfield1, '…' ,'-') ; update mytable set myfield1 = replace(myfield1, '‖' ,'|') ; update mytable ...
0
votes
0answers
28 views

IF statement inside stored procedure, seems to cause locks by side effects

We are running a MySQL 5.5. server with READ-COMMITTED transaction level to store and process a large time series dataset. We have a large time-series-table, into which we write batch updates every ...
2
votes
2answers
9k views

MySQL Error Reading Communication Packets

In MySQL error logs, I see these quite few a warnings like these: 120611 16:12:30 [Warning] Aborted connection 2619503 to db: 'db_name' user: 'user_name' host: 'webapp_hostname' (Got an error reading ...
0
votes
0answers
9 views

Optimizing MySQL for write throughput ceiling

We have a large MySQL database on AWS RDS, with a huge table around ~1.8TB with massive write workload. We recently hit a write throughput bottleneck, which caps our provisioned IOPS way below the ...
0
votes
0answers
12 views

creating best results from two tables

I have two tables, one is the results of an assessment from users(per row) and column starting with 819....X1 represents the qid. The other table holds the score for the answers (A1,A2,A3), both are ...
0
votes
0answers
37 views

Tunning MySQL Variables

we have a dedicated mysql server with 10 CPU and 32G of memory After doing some readings came up with those configs : # This was formally known as [safe_mysqld]. Both versions are currently parsed. ...
1
vote
1answer
6k views

Error 1044 Access denied to user

This is driving me crazy. When I try to create a few tables from my Workbench model I get this error 1044. I've been trying to find a solution but nothing works for me. Curiously when I run ...
0
votes
2answers
32 views

Get rows where lastLoginTimestamp is in last 7 days

I'm trying to get the rows from a table where lastLoginTimestamp is in last 7 days and I'm doing as follow: SELECT * FROM representative WHERE DATE_SUB(lastLoginTimestamp, INTERVAL 7 DAY) ...
1
vote
1answer
30 views

phpMyAdmin and mysql not showing the same database

I have setup WAMP which I managed by using a tutorial and everything seems fine... I am logging into mysql as the root user with full privileges granted and I am able to create databases in mysql and ...
0
votes
1answer
38 views

MySQL Crash After Starting Binary Logs

Recently I have added binary logs to mysql to start replication but after that setting, mysql process failed to start and keep crashing constantly. In mysql error logs some messages are there but not ...
1
vote
2answers
518 views

MySQL - grant all privileges on *.* brings back Access denied

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'somepassword'; Brings back error : ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) If ...
0
votes
0answers
28 views

Daemon Waiting for activation in mysql event

When I do, SHOW PROCESSLIST; on mysql, for my event, I see the following result: Q1 )Why does it shows Waiting for next activation all the time? Q2) At times my scheduler turns off which causes my ...
0
votes
1answer
27 views

Unable to get the latest value added to Mysql table based on posted time

SELECT * FROM discussion_comments GROUP BY disc_id ORDER BY posted_date DESC I have table example like given below: CREATE TABLE example ( id int(11), cname varchar(11), posted_date date, ...
0
votes
0answers
44 views

MySQL is pretty slow after 50-100k rows whats happen?

i have trying now a lots of time, and i try to find a way there make sense to find out of my problem, but this problem stil make a lot perfumes issues. i testing it of on a developer server ( 2 ...
1
vote
1answer
21 views

Installed Percona-Toolkit, but no sign of xtrabackup

I've installed Percona-Toolkit on my Debian (Wheezy) test Server, using dpkg -i percona-toolkit.deb apt-get install --fix-missing -f dpkg -i percona-toolkit.deb However, there is no sign of ...
0
votes
1answer
96 views

Too much IO to MySQL InnoDB log files

I am using MySQL version: 5.5.38 I have dedicated HDD to InnoDB log files using innodb-log-group-home-dir option. atopsar -d 30 shows big load (>50%) to this HDD The thing is that I have only 75 ...
0
votes
1answer
28 views

“show procedure status” displaying the non existing databases stored procedures in mysql server

i have taken a mysql server backup using mysqldump and imported the backup file in another mysql server.Import failed because of disk full issue. Only 11 databases are created out of 15 . when i issue ...
0
votes
1answer
33 views

No records in Mysql.func table even though there are stored functions in server

When i am creating a user defined function in one of my mysql server, it's not creating any row in mysql.func table . Even though i have multiple functions in my mysql server , this table is empty . ...
2
votes
1answer
1k views

Space not reclaimed after dropping MyISAM table

I just dropped a rather large MyISAM table (~4 gb); it was 350k rows with about 11k of data per row. However, when I run df -h on my database server, I am still at 100% disk usage, so much so that I ...
0
votes
0answers
17 views

How to create relations in database

I have two table: users and projects. In projects admin can add projects like: web design, SEO , etc.... I need to add abilty to admin can add some users to certain project... For example I need ...
0
votes
0answers
27 views

Mysql Trigger to call a java method, its pros and cons?

I have a requirement to call a Java method once a new record has been inserted in to the DB. I would like to know is it a efficient way to do ? what could be the performance issues as my table expects ...
1
vote
1answer
38 views

Warehouse Database Design Question

Apologies if this has been answered previously but I have been unable to get a specific model to work. I have a database table that needs to store some very basic info for a reporting system. The ...
1
vote
3answers
3k views

MySQL Table not repairing

Table info: Database name: user_motiva Table name: wp_options.frm wp_options.MYD wp_options.MYI wp_options.TMD when I do a mysqlcheck -r --all-databases it gets hung on that table even if you ...
5
votes
2answers
17k views

ALTER TABLE - Rename a column

This got me nuts! I want to rename a column from read-more to read_more in blog table Tried all this: ALTER TABLE blog RENAME COLUMN read-more to read_more; ALTER TABLE blog CHANGE COLUMN ...
0
votes
1answer
57 views

How to turn Event Scheduler On permenantly in mysql

How do i turn on the event scheduler in mysql permanently. I'm using phpmyadmin and i have scheduled an event to copy records from one table to another everyday at a specific time, but the records are ...
0
votes
1answer
70 views

MySQL: How to write a recursive function to show menu tree?

I try to build a menu tree inside the SQL with this function: DROP FUNCTION IF EXISTS hello_world; DELIMITER $$ CREATE FUNCTION hello_world(_typeId INT, _typeText TEXT) RETURNS TEXT BEGIN ...