MySQL version 5.5 - please also tag with mysql for search purposes.

learn more… | top users | synonyms

0
votes
1answer
252 views

Non-Replicating commands from (master) Mysql 5.5.40 to (slave) MariaDB 10.0.19

CRUD replicates just fine. But we noticed that DB structure altering commands such as ALTER TABLE and/or adding/updating columns to a table and even creating new ones do not replicate to the slave at ...
0
votes
0answers
52 views

Index pages in innodb file when innodb_file_per_table = ON

I have MySQL 5.5, and innodb_file_per_table was always in config file. After some time of using MySQL DB was produced big ibdata1 file, but i don't know how explain why this file is so big, i try to ...
1
vote
0answers
675 views

Reclaim disk space after dropping table(innodb_file_per_table = ON), strange scenario

I know it is often asking question. But I can't find answer for my scenario with specific condition. We use MySQL 5.5 We have always(I am sure) innodb_file_per_table = ON option in our mysql ...
1
vote
2answers
185 views

Difference between Handler_write and Innodb_data_writes

Handler write is the number of requests to insert a row in a table. InnoDB data writes is the total number of data writes. Questions are Does inserting a row count as a disk write? If I do an ...
0
votes
2answers
237 views

Fixing MyISAM database without losing data

My old server was crashed but I was able to recover the databases from MySQL through testdrive. I'm trying to dump a database using mysqldump but it gives error for certain tables are crashed and ...
0
votes
0answers
47 views

MYSQL taking 17.5 GB of RAM

This is what top shows how MySQL is taking a large amount of memory. VIRT RES SHR S 3967 mysql 20 0 17.5g 1.4g 7008 S 41.2 2.3 17426:16 mysqld this is ...
1
vote
1answer
128 views

How to speed up query containing concat() in WHERE clause?

In my company we use a commercial software running with mysql 5.5 (MyISAM) that often executes a query of this form: SELECT table1.attrib1 FROM table2, table1 WHERE table2.status = 'Active' ...
0
votes
0answers
46 views

Does MySQL InnoDB locks multiple rows with READ_COMMITTED isolation level?

I am in trouble while resolving LOCK WAIT TIMEOUT EXCEED error with MySQL InnoDB. I have gone through this article and it says if we use isolation level READ_COMMITTED then my update query should ...
0
votes
1answer
80 views

Backup a huge database 200gb+

i have a mysql database where i need to take a backup of the log database. the problem is the log database i over 200gb and contains over 280 millions of rows, i need right now to remove a kind of ...
1
vote
1answer
270 views

“Remote” connection to a local MYSQL database on LAN

I have a mySQL (5.5.38) server running on my desktop (windows 7), I can connect just fine into any of the servers Users, and skip-networking is off when checked via show variables. I am trying to give ...
1
vote
1answer
84 views

MySQL Drop User Fails for Remote Host

We're clearing out some old MySQL users that connected from remote hosts during a database transfer. Why is this DROP USER query failing for the user asdfasdf_imac4? I'm logged in as root. mysql> ...
0
votes
1answer
97 views

Working with variables in stored proc

I want to create a meta table on the fly using a stored pdocedure and passing a table prefix as "in" parameter and then build the final table name somehow. My code looks like this: SET @table_ = ...
0
votes
0answers
52 views

MySQL - Get actual precision and scale of number

I would like to query the integer part and fractional part of all decimal(8,4) values in a column. By that I mean, I want to know the actual given values, and not the max allowed by the data type, ...
1
vote
1answer
102 views

Unknown sticky Problem after MySQL upgrade

few weeks ago we upgraded mysql server from 5.5 to 5.6 on 1st server few days ago (5May) I reboot mysql server , after this customer reported that some database data table not load to their site . ...
0
votes
0answers
289 views

Plesk 12 Debian 7 MySQL CPU usage is too high

I have a problem with MySQL and MySQL CPU usage at Plesk. I have a news site with Joomla, and about 100-150 online user. CPU: AMD Opteron(tm) Processor 6366 HE 3 core Memory: 4GB HDD: 250 GB Plesk 12 ...
0
votes
2answers
763 views

MYSQL Row Count Limits?

What is the maximum row count for a table in MYSQL? I have a system with around 600,000,000 rows in three tables. Average Data Length (from data_length column) for these tables is 58,000,000,000 ...
0
votes
1answer
19 views

'mysql' database in fresh MySQL installation

I installed MySQL database and according to show databases command I have a database named 'mysql'. Is this database used for holding some internal values for MySQL? In addition, is it possible to ...
0
votes
1answer
345 views

InnoDB Index Doesn't Fit in Memory

I am using InnoDB tables in my database and total index_size is 250 GB and my data size is 500 GB. Server has 128 GB RAM, and innodb_buffer_pool_size is set to 64 GB. Data size is increasing every day ...
1
vote
2answers
166 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
721 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 ...
1
vote
1answer
167 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
128 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
1answer
25 views

Indexed varchar column efficiency

On a table with several million rows, with an index on the only column in the where clause (varchar 100, full length indexed), would there be performance gains in making sure that the differences ...
0
votes
1answer
256 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 ...
1
vote
2answers
132 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 ...
1
vote
2answers
2k 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
59 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 ...
2
votes
1answer
187 views

Is there an easy way to update a mysqldump file from version 9.11 to 10.11? [closed]

We have a client that has had their servers for soo long, they were still running MySQL 4.0.24. Now that they are refreshing their servers, we took a mysqldump of their current database to put into ...
5
votes
1answer
608 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 ...
1
vote
0answers
23 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
75 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 ...
8
votes
2answers
2k 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
194 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 ...
0
votes
1answer
21 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
1answer
123 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
305 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
22 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 ...
1
vote
0answers
26 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
165 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. ...
0
votes
2answers
258 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
329 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
142 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 ...
0
votes
1answer
46 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
86 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
50 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
71 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
80 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 . ...
1
vote
1answer
58 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 ...
2
votes
1answer
3k 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
955 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 ...