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

learn more… | top users | synonyms

0
votes
0answers
48 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, ...
3
votes
2answers
1k views

How to split a string to and get info from another table in mysql

I have a system to assign score for each sentence stored in a column: TABLE 1: word_scores word score this 3 is 4 a 5 test 1 another 0 sentence 8 TABLE 2: ...
1
vote
2answers
12k views

Connect Error (2054) - mysql_old_password issue still not solved

i use php 5.4.3 and i want to connect to a MySQL DB remotely (php script resides on a different server than the db) with normal oop way: $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME); ...
2
votes
1answer
35k views

How to get only month from MySQL date/time?

In my MySQL server, I have rows that have the date of my created content. Example: 2013-03-17 02:53:47 For now, I am using: $date = $fetch[thedaterownamegoeshere]; <?php echo $date; ?> But ...
1
vote
3answers
100 views

Mysql Master Offline

In a unidirectional Master-Slave setup, should the slave be manually stopped in cases where the Master is offline due to Maintenance, Crash etc. ? Or should one just leave it so that it keeps trying ...
0
votes
1answer
52 views

Mysql Master - Master Replication

i have established a master A and master B replication between two servers.Both are getting replicated.But it is limited with only one database. My questions 1.How to create a new database in ...
1
vote
0answers
41 views

Delete using join or using sub-query?

I need to delete rows from a table based on what is present in a temporary table. For me, both of these statements work : DELETE from main_table where `id` in (select `deletable_id` from ...
1
vote
1answer
39 views

MySQL: Incorrect binary log format - OFF.000

I have a Percona MySQL 5.5.30 as a master(Debian 6.0.7). I got a weird issue that all my binary logs started saving in format OFF.00* instead of the specified format in .cnf file. mysql> show ...
0
votes
0answers
79 views

pt-online-schema-change with statement based replication in MYSQL

Is it possible/safe to use pt-online-schema-change in a Master slave environment where as the way replication is setup is statement based(SBR). What is the better way of doing it as currently I have ...
1
vote
3answers
181 views

Index optimization and cardinality

I'm by no means a DBA expert. Rather, a Magento developer having to troubleshoot some slow queries. I'll jump right in -- here's my attempt to EXPLAIN the troublesome query: EXPLAIN SELECT ...
1
vote
2answers
105 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 ...
12
votes
1answer
8k views

MySQL transaction size - how big is too big?

I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction. There are many aspects, and the imports may yield anywhere between ...
0
votes
0answers
46 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
481 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 ...
0
votes
2answers
146 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 ...
1
vote
2answers
372 views

insert row if table is empty, else update row (mysql)

i have 2 tables "table1" and "table2" both have TIMESTAMP cols for starttime and endtime. i want something like if (table2 is empty) insert into table2 (table2.starttime, table2.endtime) value ...
0
votes
1answer
233 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 ...
0
votes
0answers
45 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
97 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' ...
1
vote
0answers
1k views

MySQL server: Unable to connect with root because of skip-name-resolve option

We had our MySQL server on an old server. We decided to move all of the databases to our new server. Old MySQL Server engine 5.5.31 New MySQL Server engine 5.5.38 We created one general dump file ...
0
votes
0answers
56 views

Got an error from unknown thread, /pb2/build/sb_0-13157587-1410170252.03/rpm/BUILD/mysql-5.5.40/mysql-5.5.40/storage/myisam/mi_write.c:226

I am using mysql 5.5.40-log and my default storage engine is Innodb, I found this error in my error log "[ERROR] Got an error from unknown thread, ...
1
vote
2answers
463 views

about giving same column name in every table of database

I have a database with 5 tables. In every table there is a column for checking status. If i give the name to that column which is same in all the 5 tables; What will happen? Is it correct according ...
0
votes
0answers
44 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
63 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
63 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
74 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
13 views

What does mysqltest's view-protocol option do?

The documentation on mysqltest lists this at the bottom of the list of options: --view-protocol Every SELECT statement is wrapped inside a view. What does this option do? Using mysqltest ...
1
vote
1answer
72 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
31 views

get the min index of each group of consecutive records

I have a table of records. Each record has in index field. records are inserted sequentially and then individual records are deleted. think of a sequence 1 - 10 and randomly remove 4 numbers I need ...
0
votes
0answers
231 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 ...
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 ...
2
votes
3answers
621 views

mysql replication master 5.5 slave 5.1 error on create database

I have a replication system where my master has a mysql 5.5 version while the slave is mysql 5.1. I know that this is not supported because the slave version should be greater than the master, but we ...
1
vote
1answer
2k views

How to Reclaim Data Free in InnoDB Tables?

I came up with an idea on how to shrink ibdata1 in mysql database then i got this solution on stackoverflow STEP 01) MySQLDump all databases into a SQL text file (call it SQLData.sql) STEP 02) Drop ...
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 ...
2
votes
1answer
111 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 ...
1
vote
2answers
1k views

DELIMITER issue in MySQL 5.6

Drop procedure if EXISTS rotatepjp; DELIMITER // CREATE PROCEDURE rotatepjp() BEGIN DECLARE u_id, settingval, pjp_userid, pjp_beatid, u_frequency INT; DECLARE u_pjpstartdate, pjp_fordate, ...
0
votes
0answers
115 views

ERROR 1644 (HY000) at line 1852: Unexpected content found in the performance_schema database

I was upgrading MySQL from 5.1 to MySQL 5.6 on a Linux server installed with RHEL6. I already have 5.1 installed and running with about 300GB of database data, so I want to do a in-place upgrade to ...
1
vote
2answers
101 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
476 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
112 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
77 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
114 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
190 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
0answers
170 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
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
53 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
391 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 ...
8
votes
2answers
1k 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
140 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
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 ...