MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

14
votes
0answers
2k views

MySQL 5.1.66 SSL connection error ERROR 2026 (HY000)

UPDATE2 Using WireShark I found out the problem string (I hope I did): 28 | 9.582638 | 192.168.18.128 | 192.168.18.129 | MySQL Response Error 1043 And the error is (according to docs): Error: ...
3
votes
0answers
86 views

3 table merge with some condition

I am new to this forum and I am learning mysql with the help of online resources, currently I am trying to compare and merge table with some conditions Fiddle These are my tables; ...
3
votes
0answers
203 views

“InnoDB: error clustered record for sec rec not found” MySQL 5.5.28

We have a high-end server, 128GB RAM, 32 Core , Xeon, SSD RAID 10 - running Ubuntu 12.04 with MySQL 5.5.28. During high load, randomly we got the below error. MySQLcheck, innochecksum shows no ...
3
votes
0answers
96 views

Once in a while synchronization of the independently updated mysql databases problem

I have a production database and a test database running on MySQL with MyISAM and InnoDB tables, which are updated independently. The Production database is updated by system and sometimes by a user. ...
2
votes
0answers
16 views

Translate SQL Server update statement to MySQL

I have a development environment with MySQL and a production environment with SQL Server. This update statement works with SQL Server: UPDATE THIS SET <list_of_column_assignments> FROM ...
2
votes
0answers
26 views

longitude lattitude performance

I have to store frequently (at least every min) the position (longitude, latitude) of animals in a DB, and perform for each update a search in order to determinate if ananother animal is near to it. ...
2
votes
0answers
70 views

Almost identical queries but large time difference in execution

Exhibit A SELECT SUM(value) FROM Credits RIGHT JOIN calendar ON datefield = DATE(FROM_UNIXTIME(created_at)) WHERE confirm = 1 && reason = 1 && country = 'it' && created_at ...
2
votes
0answers
27 views

Performance difference between two MariaDB slaves

I have a pool of MariaDB servers like this : master (5.5.38-MariaDB-log) slave1 (5.5.38-MariaDB-log) slave2 (5.5.38-MariaDB-log) The two slaves servers run on the exact same hardware (Dell ...
2
votes
0answers
35 views

Emulating WITH clause in MySQL/query perfomance review

I have never really worked with MySQL queries till this last week. I was tasked with making a simple tool for my department that tracks contacts. Here are the 2 tables and the type of data they ...
2
votes
0answers
30 views

Sample a Mysql table to increase performance

we are storing all our clients traffic data in a MySQL 5.1.73 database (MyISAM) and then we use it to generate user and internal statistics/graphs, always searchinng for a client id and a date/hour. ...
2
votes
0answers
57 views

Denormalizing to speed up inserts

I have a MySQL InnoDb table with approximately 40 million rows and the following columns: param1 (int), param2 (int), param3 (int), geo (char2), value1 (bigint), value2 (bigint) I have an ...
2
votes
0answers
41 views

Proper design of routes in Database MySQL

I'am trying to find out the best database design and try to stick with data integrity by using Foreign Keys. But I'm stuck right at the beginning because of limit relations between tables with FK to ...
2
votes
0answers
28 views

Difference of write authorization on file system with mySQL

On my local mySQL (on CentOS 6 VM) database I wanted to export some data from the DB to a file like this: SELECT html FROM mytable WHERE lang = 'en' and id='KEY01' INTO OUTFILE 'key01_en.html' By ...
2
votes
0answers
187 views

How can I connect to a network MySQL server using ODBC?

Somewhere on my company's network there's a (Oracle) MySQL server named FOO. I see FOO reference in SAS code written by my team's analysts, who use SAS's PROC SQL command to connect to it using ODBC. ...
2
votes
0answers
137 views

Recover innodb database from ibdata1 and *.frm files only

I have a problem on my server. I had a hard disk error on my server, and I restarted it. After that, I started wamp server software, but mysql services were not started. I checked all mysql data, ...
2
votes
0answers
50 views

Single database won't load MySQL

When I turned off my MySQL server, and turned it back on, it wouldn't read my database anymore. Just a single database, everything else in mySQL was fine. When I try looking at any of the tables it ...
2
votes
0answers
161 views

RDS MYSQL instance rebooted and won't load INNODB table

So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error ...
2
votes
0answers
131 views

How to model (a many to many) to many relationship

Hello i am facing the following scenario. I have a student table with the following structure. Student id(PK), matriculation number, first_name, last_name, email A student can have more than one ...
2
votes
0answers
46 views

Count how many locks required for a MySQL (InnoDB) query

I'm performance tuning a very data-heavy mysql application. It has a purge system that often seems to cause lock timeout errors for users. Based on my research I have a few adjustments to the query to ...
2
votes
0answers
1k views

MySQL Queries taking too long to execute

I have a database table, with 300,000 rows and 113.7 MB in size. I have my database running on Ubuntu 13.10 with 8 Cores and 8GB of RAM. As things are now, the MySQL server uses up an average of 750% ...
2
votes
0answers
94 views

Understanding Indexes and using them better

I've been spending some time trying to optimise our MySQL queries that we use which essentially joins one table with 2 million rows onto another which has 1-2 million. I know MySQL is able to easily ...
2
votes
0answers
80 views

Does it make a difference to add the DEFAULT keyword in CREATE TABLE?

The MySQL manual says: CREATE DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] [[DEFAULT] COLLATE collation_name] ALTER DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] ...
2
votes
0answers
603 views

Mysql slow writes

i have a strange problem with mysql. My selects goes fast. My insert/create/delete/drop/update go very slow. I have done some tests on 2 machines. My old machine and my new machine. Old is worst ...
2
votes
0answers
93 views

Why does MySQL force the tradeoff between swallowing errors and leaking commits in transactions?

Referring specifically to the choice of whether to use DECLARE EXIT HANDLER FOR SQLEXCEPTION. If you use it, you swallow errors and there doesn't seem to be any way to access them programmatically, ...
2
votes
0answers
678 views

Query getting periodically stuck in 'copying to tmp table' state, never completes

I am running Wordpress on a dedicated server with a MySQL backend. I have a query that usually takes <1 second to execute, but periodically, this query will get stuck in a 'copying to tmp table' ...
2
votes
0answers
946 views

How to prevent “ibdata files do not match the log sequence number”?

I am dealing with a very large set of databases that are all innodb. I've enountered this on mysql restart too many times for my comfort: ibdata files do not match the log sequence number But I've ...
2
votes
0answers
300 views

Insert Speeds for large batches

In my application, my INSERTs seem to be taking a major chunk of the time. I have a large number of objects in memory (~40-50,000) which i want to insert into a table. Lets take a sample table ...
2
votes
0answers
432 views

The nature of the relationship between two tables in MySQL

Good day. I'm designing a database for a research project where we're predicting whether or not young female athletes will rupture their ACL(s) based on the nature of a test jump recorded with a ...
2
votes
0answers
186 views

InnoDB forum DB design and Composite Primary Key (Cluster Primary Key)

I am designing forum database using InnoDB and got few questions , Could any one please help me to clarify them? Posts and Comments table design below CREATE TABLE `my_posts` ( `forum_id` ...
2
votes
0answers
84 views

MySQL Partitioning Performance

I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM. I am unable to index ...
2
votes
0answers
241 views

Storing history of full/partial tables

I'm building a web-application with Django and MySQL (InnoDB) and am currently pondering over how to manage historical changes on various tables. I wonder if it's efficient to store a lot of rows ...
2
votes
0answers
111 views

Changing Charset to utf8 for all the fields and tables in mysql

How should I use the mysql and awk to change the charset for all the fields and tables for a specific database? I did not include username or password, so for sure it will not work. Should I run it ...
2
votes
0answers
63 views

How can one improve the performance of a query that selects on a low cardinality column in MySQL?

I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah') AFAIK you need something like a bitmap index ...
2
votes
0answers
430 views

MySQL: Improve performance for one row insert into table with unique constraint

I have a table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255). Now the insert speed is about 30/s, is this expected? how could I improve it? I can't use bulk ...
2
votes
0answers
256 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 ...
2
votes
0answers
353 views

Incorrect data when we using the CONVERT_TZ function on DST boundaries

We have a problem in converting time zone from "America/New_York" to "UTC". This problem occurs only on times representing 2.00 AM during the daylight saving. Timezone conversion with correct output ...
2
votes
0answers
2k views

'Access Denied' in MySQL on AWS RDS

I'm trying to use an ETL (in my current case, the free version of Talend). I've successfully set up a MySQL on Amazon RDS, logged in through the MySQL Workbench for Mac, and uploaded my company's ...
2
votes
0answers
548 views

SQL set profiling

I'm setting up an automatic database backup for a website. When I try to do a backup it comes up with the error" MySQL error: The 'SHOW PROFILE' feature is disabled; you need MySQL built with ...
2
votes
0answers
855 views

MySQL Server hitting 100% unexpectedly (Amazon AWS RDS)

We've been struggling with this issue for months. This week we upped our RDS instance to the highest performing instance and although the occurrences have reduced, we're still seeing our database ...
2
votes
0answers
1k views

MySQL re-install fails under Mountain Lion

I have problems to reinstall MySQL on Mac OS X after it was running on my system. I uninstalled it because the PreferencePane wasn't working as expected, using the procedure described here: ...
2
votes
0answers
2k views

mysql taking too long to send data

I have a simple table with million of records (14,000,000) and for a simple query it is spending too much time "sending data". The table CREATE TABLE IF NOT EXISTS details ( id int(11) NOT NULL, ...
2
votes
0answers
523 views

Index usage for database server

How can i fix the Index usage on one of our database server? It says from our Nagios control panel that: Service: DB Index Usage Host: MySQL Cluster Address: ( our IP ) State: CRITICAL Additional ...
2
votes
0answers
90 views

Can't connect to MySQL Cluster Storage (data) nodes

I've deployed a MySQL Cluster with a few nodes, and I want to access the storage nodes directly from another machine using NDBAPI. When I tried to connect from the same host it worked, but when I try ...
2
votes
0answers
271 views

MYSQL & Access ODBC

I am trying to connect mysql with access, i am settings up the ODBC correctly from the data sources, however i get a driver error. I am using access 2010 and the driver version of the ODBC conector ...
1
vote
0answers
13 views

Copying to tmp table slow for query … but only sometimes

I have a query, and I admit it may not be the prettiest query in the world. Sometimes this query runs fairly fast (e.g. 200ms) and other times it runs super slow (e.g. 30s). I don't know why, and ...
1
vote
0answers
16 views

MySql Audit Changes from Db Admin, Security

I know that you can use triggers (before insert, update, delete) to track the changes in history tables. But this only works if the changes are made by the applications. But in some circumstance, ...
1
vote
0answers
16 views

How to calculate geographic distances using MySQL

Given a MySQL table with a series of latitude and longitude points, stored as strings, how can I find the distances between each point in the table and a given point, call it "originlat, originlon" ? ...
1
vote
0answers
11 views

Conversion to Barracuda/Compressed Rows Doesn't Free Disk Space

I'm currently converting large tables to the Barracuda file format. We are using file_per_table. I had one table that decreased from 4.3gb in size down to 1.8gb and we're very happy about this. ...
1
vote
0answers
36 views

Indexed query fails to return rows in large (15 billion rows) MyISAM (MySQL) table

I have a very large table (15 billion rows) with a structure like this: CREATE TABLE `textlines` ( `PGUID` binary(16) DEFAULT NULL, `StatID` int(11) DEFAULT NULL, `Time` datetime(3) DEFAULT ...
1
vote
0answers
35 views

GROUP BY multiple columns

I'm trying to draw stats out of a table and depending on the increment, group them by year, month, date. The table is as follows CREATE TABLE `channel_stats` ( `channel_id` int(10) unsigned ...