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

learn more… | top users | synonyms

0
votes
1answer
178 views

Select unique value whereas the time is highest in the most optimal way

Given a simple, with a text and time column, I want to select X unique values from the text column, whereas that row contains the highest value for time. INSERT INTO `test` (`id`, `text`, `time`) ...
0
votes
1answer
127 views

Am I wrong in table design or wrong in selected index when made the table?

I've build web application as a tool to eliminate unnecessary data in peoples table, this application mainly to filter all data of peoples who valid to get an election rights. At first, it wasn't a ...
0
votes
1answer
1k views

HOW to “SUM” and “JOIN” for single query of a poll results?

I have a poll database as CREATE TABLE poll_answer answer_id, answer varchar(255), poll_id int(11), FOREIGN KEY(poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE, PRIMARY KEY(answer_id) ) ENGINE=...
0
votes
1answer
108 views

Will Partitions and Indexes on the same table help in performace of Inserts and Selects?

I have a table containing the list of visitors and this table has the following information. Visitor Browser Information Visitor Location Information Visitor Time Information No of Visits I have a ...
0
votes
1answer
254 views

Do certain string patterns affect query performance on a VARCHAR column?

I am a beginner on MySQL Administration and performance problems. Now this is confusing me. I have a VARCHAR column identity, the string in it is consisted of a Class type and an id, like note:123, ...
0
votes
1answer
4k views

In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table

On Windows, I upgraded from 5.1 to 5.5 no problem. Copied my 5.1 data folder into my 5.5 instance Started mysqld skipping grants Ran mysql_upgrade All good, but going from 5.5 to 5.6: Copied ...
0
votes
1answer
58 views

Optimizing Query

Can anyone help me tuning this query..... explain extended SELECT DISTINCT a.msisdn FROM `std_msc_opr_wise` a LEFT OUTER JOIN (SELECT msisdn FROM as_treat_pre_usage_30days GROUP BY ...
0
votes
3answers
159 views

Database migration, how to

I have two databases DB1 and DB2. In both databases exists this two tables mo_sms and mt_sms. This is the structure of those tables: CREATE TABLE IF NOT EXISTS `mo_sms` ( `id_MO` int(11) unsigned ...
0
votes
2answers
87 views

Error log - What does this mean and what can I do about it?

I have the following repeated errors in my error.log and I don't know what it means or what I should/can do about it: Access denied for user 'root'@'[some IP]' (using password: YES) There are ...
0
votes
1answer
8k views

MySQL slave stuck in “Reading event from the relay log”?

5.5.28-log MySQL Community Server (GPL) by Remi binlog-format=MIXED My problem is similar to this question. *************************** 2. row *************************** Id: 973415 User: ...
0
votes
1answer
612 views

MySQL not allowing text columns?

I'm using MySQL 5.5.27. I currently have a BLOB column, that I want to convert to text to allow fulltext searching. The ALTER TABLE command to create a text column is successful, but the ALTER TABLE ...
0
votes
1answer
37 views

Why isn't my includeThreadNamesAsStatementComment JDBC parameter working?

The JDBC API lists a includeThreadNamesAsStatementComment parameter that seems to indicate it will prefix all queries with the Java thread ID. I thought this would be useful in tying back DB activity ...
0
votes
2answers
566 views

Using MySQL InnoDB as an Archive

My site has a main MySQL InnoDB table that it does most of its work on. New rows get inserted at a rate of 1 million per week, and rows older than a week gets moved over to an archive table on a daily ...
0
votes
3answers
1k views

Mysql optimization help

I've tried optimizing mysql as good as I could, but apparently I'm not to good at it :-) So I come to you guys for help. Server specs are: AMD Opteron CPU 8 cores 16 GB RAM 2x2.000 GB HDD 7.200 RPM ...
0
votes
2answers
339 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 `...
0
votes
1answer
42 views

Not able to create a new table new_table from t1 by comparing primary key values of another table t2 in MYSQL

I am trying to create a new table new_table from t1 by comparing primary key values of another table t2 ie, 1) created table new_table 2) gave the query insert into new_table select a.* ...
0
votes
2answers
3k 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 mysqld....
0
votes
1answer
177 views

Handling error of foreign key

How to set a FK value NULL upon receiving error for foreign key constraint? Consider a simple table a CREATE TABLE test ( id int(11) NOT NULL AUTO_INCREMENT ex_key int(11), FOREIGN KEY(...
0
votes
1answer
2k views

How can I enable my-huge file on windows server?

I installed Windows Server 2008 and MySQL 5.5. Now I need configure MySQL for huge InnoDB tables and a lot of connections. I see that on my Windows Server, there is a my-huge file(configs files that ...
0
votes
1answer
60 views

Move databases on same Mac

I have just updated my MacBook to Mountain Lion. Since then, my MAMP installation does not work properly so I decided to use the build httpd/php stuff. For this, I have additionally installed a new ...
0
votes
1answer
2k views

MySQL 5.5 not starting after master-master configuration

The following master-master configuration worked on MySQL Server 5.1.x(Ubuntu 10.04). Master1(192.168.2.80) ################################## #MySQL Replication ################################## ...
0
votes
1answer
3k views

mysql closing tables taking double the time of process

I have a delete query that is taking a long time. When I check in processlist that status is showing as closing table. It is taking a long time to close tables. ex: I run a query and the total time ...
0
votes
2answers
1k views

Some queries slower in MySQL 5.5 than 5.0?

We have an exceptionally inefficient process (1.3 million selects, 232k updates) that I'm now recoding to be 1 select and 50 updates. In the meantime, though, today's process ran. We recently updated ...
0
votes
1answer
106 views

Can MySQL be patched

I just saw a note about a security issue in MySQL so thought that I'd better patch my test server just in case. The MySQL site is not very helpful and a quick google revealed lots of people asking how ...
0
votes
1answer
3k views

MySql 5.5 in ubuntu repository

Sorry for duplicate question. I am looking for the best way to install mysql 5.5 on ubuntu. So far, there are few options: Installing from tar.gz Installing from PPA. Alien on RPM. All of them ...
0
votes
1answer
11 views

Why is my statement results in a syntax error?

I tried to run this SQL statement in MySQL 5.5 and 5.7.1 but always got error. DELIMITER // CREATE PROCEDURE one_off_update_user_info_view_type() BEGIN DECLARE @batchsize BIGINT DEFAULT 1; ...
0
votes
0answers
54 views

MySQL: Log only 'select' queries executed on a specific set of tables of a database

I have a Magento site in which I want to log only SELECT queries(after SELECT it could be any column or all columns, in short all SELECT statements) executed on a specific set of tables, also as it's ...
0
votes
0answers
19 views

Innodb Change Buffer merge

I have been testing the performance of my innodb mysql. I ran a script which inserts and selects one after the other for 100000 times. So according to the change buffer concept, the inserted ...
0
votes
0answers
14 views

MySQL: physical table vs temporary table

This question is part of this question MySQL: excessive memory usage I am in process of debugging MySQL high memory usage, I need to know the difference and impact of the physical tables and ...
0
votes
0answers
22 views

MySQL: excessive memory usage

In our production system, MySQL service uses above 95 % of the available memory. System Info: OS: CentOS 6 RAM: 32 GB CPU: 4 core MySQL version: MySQL 5.5 Database Size: 158 GB my.cnf info: # ...
0
votes
1answer
53 views

MySQL 5.5 to 5.7 doesn't work, apparently this is a feature?

Has anyone found a way yet of circumventing MySQL 5.7s insistence that it's Master must have a server_UUID. I currently have multiple instances of MySQL 5.5 over various sites, and want to look at ...
0
votes
0answers
26 views

Query Across Multiple MySQL Databases

The majority of our clients use the same MySQL schema and a number (subject to the usual conditions) are willing to give us a copy of their data for research. I have written a utility which merges the ...
0
votes
0answers
18 views

Check MySQL database growth

I need to check the growth rate for my database (MySQL 5.5). So far I found only a query that answers me the current size - despite the title of the article - (How to check growth of database in ...
0
votes
1answer
46 views

MySQL Replication Duplicate Entry for Primary Key

160523 13:07:29 [ERROR] Slave SQL: Error 'Duplicate entry '914166' for key 'PRIMARY'' on query. Default database: 'zo_dev_20121216'. Query: 'UPDATE activity SET activity_type_id = 9, ...
0
votes
1answer
44 views

DDL doesn't work in MySQL master to master replication while DML can work?

I have two master in replication left master -> 5.5.47-log MySQL Community Server (GPL) rigth master ->5.5.47-log MySQL Community Server (GPL) "left master" mysql> create table test.left ...
0
votes
1answer
38 views

Display the error message returned form Mysql

I need to get the whole error message returned from MySQL and assign it to @error_string : DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN SET @error_string = '...
0
votes
1answer
38 views

Centos 7 install MySQL5..5

Install MySQL5.5 in CentOS7 by following instructions on this page: A Quick Guide to Using the MySQL Yum Repository After that start the service: service mysqld start This returns the error: Job ...
0
votes
0answers
13 views

MySQL master- master replication

I am in the process of setting up Master - Master replication between 2 servers on a production environment. My settings are as follows, Existing System: Server 1: Status: Master databases: db1, ...
0
votes
0answers
69 views

After update triggers that deal with multiple column updates

I want to create a table for audit (audit in the sense of recording whatever a user has done in terms of data manipulation – e.g. update first name, delete contact etc.). For that I need to write a ...
0
votes
0answers
10 views

Server memory consumption not changed after innodb buffer size change

I observed a strange thing while applying the innodb_buffer_pool_size on mysql. I applied the innodb_buffer_pool_size=20G (75% of my physical RAM size) and as per logs and innodb status the mysql is ...
0
votes
0answers
36 views

MySQL Upgrade query

I have 5.5.44-log MySQL Community Server (GPL) running in one of my RHEL 6.4 server. I need to upgrade the server to Percona Server-5.6.29-76.2. How to go about the upgrade? Should I completely remove ...
0
votes
0answers
52 views

Very Very Veeery slow insert queries

I am writing here because I`ve already spent days reading tutorials, other topics in here, and trying different configurations. The project : Fashion website with ~200k products which should be ...
0
votes
0answers
24 views

Boosting select queries during INSERT ON DUPLICATE KEY UPDATE into 150Gb innoDb table

I have a single server with 2 innoDb tables: 35Gb (partitioned, 24M rows), 150Gb (without partitioning, 220M rows). 25% data, 75% index. 99% of select queries are SUM + WHERE + GROUP BY. 100% of ...
0
votes
0answers
36 views

how to force mysql 5.5 server to allow tls/ssl encrypted connections only?

given a mysql Server 5.5 community edition (which came with SLES 11sp4). i configured it to support TLS and it works fine. but i did not found any options or parameters to FORCE IT to do TLS/SSL ...
0
votes
0answers
35 views

Can't connect to MySQL server via SSL

I'm trying to connect to a MySQL server via SSL. I know the server is set up correctly to handle SSL connections because I can connect fine using MySQL workbench, PHP, and command line MySQL client ...
0
votes
0answers
26 views

Trigger query fails to execute because of local variable

I have the following table: |-------------------------------------| |Column |Type |Null|Default| |-------------------------------------| |//**id**// | bigint(11) | No | | |soldier_id ...
0
votes
2answers
59 views

MySQL SELECT n-th row between dates (time interval)

I have a server which records various sensor data each second and inserts it into a MySQL table. To view the sensor data on a dashboard I have a chart which also gets updated with live data each ...
0
votes
0answers
25 views

MySQL 5.5 client SSL connection only works when adding cipher list argument, why?

I set up the packaged MySQL server 5.5 and client on two Ubuntu VM. I configured the server to support SSL. I can connect to the server with the MySQL CLI tool between the VMs, but the connection is ...
0
votes
1answer
12 views

What configs influence whether user credentials/grants are written to the binlog in Mysql

I have a fairly trivial master-master setup. When testing this setup I see that mysql users are binlogged and replicated on slaves. However on each of the masters the binlog-do-db* is for a specific ...
0
votes
0answers
56 views

EXPLAIN SELECT query is writing to tmp table

I am getting a "copying to tmp table" when running an EXPLAIN SELECT on a query. This is a pseudo-query showing the structure of the query (I can't post the actual query): SELECT colA , colB , ...