MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
0answers
80 views

Why doesn't this query limit results by first and last name?

I have an ugly query, horrible table names courtesy of MySQL Workbench, and a severe lack of planning on my part to change to less verbose names prior to actually writing the code. Try to move past ...
1
vote
0answers
3k views

MySQL server taking up 100% CPU

I'm managing a site that does streaming video, the site is running off: Intel(R) Xeon(R) CPU E31220 @ 3.10GHz 16 GB RAM 2 x 2TB RAID1 1gbit unmetered bandwidth is not an issue as the ...
1
vote
0answers
199 views

What is a loose index scan?

I was reading that MySQL can not use an index on 2 range conditions due to not having a "loose index scan" What exactly does this mean? Is it related to the underlying or the implementation of the ...
1
vote
0answers
2k views

Table already exists error when trying to import sql file

I am trying to upload a backup sql file through phpMyAdmin. I create the empty db with the same db name as in my import file in phpMyAdmin then use the import function selected from within this empty ...
1
vote
0answers
168 views

MULTIPLE INSERT VALUES with INSERT into another table

here a scenario to have a more clear picture. The DB is MySQL. I've 2 tables events and *event_users* : EVENTS id int type int date date time time ... EVENT_USERS event_id ...
1
vote
0answers
412 views

using Aria vs InnoDB vs MyISAM

I was wondering (if anyone knows) if Aria database is crash safe . I had some tables with MyISAM which were getting corrupted very often and switch them to InnoDB . However innodb is a bit slower and ...
1
vote
0answers
60 views

Move all InnoDB databases into one subdirectory (innodb_file_per_table is ON)

First of all, excuse my English. I want to run a MySQL 5.6 on ZFS volume, and need to move all InnoDB databases to one subdirectory (which in fact will be a different ZFS filesystem). For example, by ...
1
vote
0answers
22 views

Is it possible for unique key to break master-master replication in mysql?

I have a master-master mysql setup with 2 servers running the exact same application making writes to such a table: CREATE TABLE `metric` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, ...
1
vote
0answers
157 views

What do spikes in InnoDB lock structures indicate?

I co-maintain a high traffic site similar to StackExchange. It runs MySQL 5.1 (working on replacing it with MySQL 5.5 or MariaDB). All our tables are InnoDB. MySQL goes down a couple of times a day. ...
1
vote
0answers
35 views

“dynamic set” in a MySQL table

I have a table where one column will have a few values many times (maybe 20 different values). Now I would like to use something like the set to improve performance and memory consumption. The problem ...
1
vote
0answers
1k views

Solved Mysql connection lost with HAProxy

I have master-master replication with mysql. and haproxy configured front of my master-master replication. When I connect my replication via HaProxy, mysql says that "MYSQL has gone away". But when I ...
1
vote
0answers
188 views

Master and slave tables with different character sets - will replication break?

We have some huge tables in our MySQL 5.6 database that have latin-1 as their character set, and want to convert them all to UTF-8 (along with converting the database's global character set). The ...
1
vote
0answers
162 views

Distribute records on different MySQL databases - MySQL Proxy alternative

My scenario is the following: Right now I am using one big MySQL database with multiple tables to store user data. Many tables contain auto increment columns. I would like to split this into 2 or ...
1
vote
0answers
57 views

Tagged questions

I have similar to StackExchange network that utilises tagged questions. In my web application Users also can find questions by tags. The query below returns a set of tagged questions: SELECT ...
1
vote
0answers
775 views

MySQL: Upgraded from 5.1 to 5.6 - no problem?

We're using Yum to maintain MySQL and our MySQL server only hosts a largish Drupal database. We recently upgraded via Yum from 5.1 to 5.6. The process went like so: Ran a mysqldump --all-databases ...
1
vote
0answers
71 views

MySQL query hanging on copying to tmp table

I'm stuck with a query hanging on 'copying to tmp table' for about 25 seconds according to the profiling. The query is used for forecasting the number of results after filtering by job_type / ...
1
vote
0answers
59 views

crossplatform mysql_install_db script in mysql 5.5.34 on Windows

I downloaded mysql-5.5.34-win32.zip and found out there is mysql_install_db.pl script. However mysql 5.5 documentation says: mysql_install_db is a shell script and is available only on Unix ...
1
vote
0answers
305 views

Upgrading from MySQL 5.5 to 5.6 - timestamp issue?

I'm looking at upgrading my Percona Server setup from 5.5 to 5.6. Reading the upgrade docs from MySQL sounds mostly straightforward except for anything involving time/date columns. As far as I can ...
1
vote
0answers
185 views

restore using mysqlbinlog failing

While doing restore from a MySQL binary log file, it is failing with the following error . mysqlbinlog mysql-bin.000036 | mysql -u root -p asdb ERROR 1032 (HY000) at line 48: Can't find record in ...
1
vote
0answers
460 views

USING to join multiple tables with same field

I have 4 tables, each with a common field QuestionnaireId. I am wondering if there is any difference in output or performance difference between using USING on the field in each join or by specifying ...
1
vote
0answers
350 views

How to set selinux permission for non-standard mysql bin log path?

By default, mysql bin log works in folder /var/log/mysql. but moving to any other share such /mylogs/mysql gives permission error. Below is the exact message: usr/sbin/mysqld: File ...
1
vote
0answers
45 views

Find repeating tickets with more than one criteria

Lets say I have 2 tables: atms & tickets each atm has many tickets. ATM table: ID | NAME ------------------------- A0109 | ATM 1 A0227 | ATM 2 etc. | etc. Ticket table: ID | ATM | ...
1
vote
0answers
51 views

Does union all have an enforced ordering

I know that a union does a de-duplication so can jumble the results, but I can't find a definitive answer for union all Example query select id from ( select id from table where id = 1 union all ...
1
vote
0answers
2k views

Amazon RDS - Can't access MySQL DB from local machine

I read many questions/answers here but they are from a different interface from AWS Console. I created a DB Instance but can't create a rule in the security groups that's allows me to connect from my ...
1
vote
0answers
142 views

How to join the tables in mysql to get wall messages and comments with name of person ? Only user and his friends messages

I have four tables in my mysql database as shown in the image url provided. I want to select data from the tables without duplication. I want to show messages and comments on wall according to the ...
1
vote
0answers
148 views

MySQL database problems all of a sudden— users' programs can't connect, I saw error 2013, error 32

I am editing my problem, based on what I've researched and done: Hi This is mysql running on an osx server. We suddenly have an issue where only one of anything can seem to connect to mysql at ...
1
vote
0answers
65 views

Mysql Replication Master/Slave and Master/Master

Looking for some insight and opinions from some DBAs, as I've been tasked to look into using Master/Master replication for a single table in a database. We currently run mysql replication using a ...
1
vote
0answers
155 views

Issue with MySql upgrade from 5.1 to 5.6 for Jboss SOA - p v 4.3

We are using JBoss SOA P v- 4.3, we have recently upgraded Mysql version to 5.6 from 5.1 and are facing some issue with the jboss start up. We are using MySQL instead of Hypersonic database for Jboss ...
1
vote
0answers
478 views

Prepared statement execution with variable number of parameters to be bound

Some stored procedures I work with need to interpolate WHERE criteria based on if procedure input parameters have been supplied. To avoid potential injection points, I'd like to utilize parameter ...
1
vote
0answers
368 views

MySQL Space efficient way of storing indexed key-value pairs associated with table

We're storing transactions in an accounting system using two tables, one contains the general description of the particular transaction (the other contains the entries, one for each account - which ...
1
vote
0answers
165 views

MySQL aggregation count performance

I have a table with about 30 million records, and I'm doing a query where I can group by field Year and count the results for that year. The table structure looks like: id | year | amount The ...
1
vote
0answers
124 views

#1046: No database selected in phpMyAdmin

I have a problem with phpMyAdmin. When I click on any database on the left I get an error like: SELECT tracking_active FROM.`pma_tracking` WHERE db_name = 'information_schema' AND table_name = ...
1
vote
0answers
1k views

MySQL connection timeout error retrieving big results

All of the sudden our MySQL 5.6 server (on Windows server) started to give us this error: "Lost connection to server during query". We never had this problem before and we haven't touch MySQL config ...
1
vote
0answers
14 views

MySQL: index statistics dropped when table dropped?

When a table is dropped, are its index statistics also removed? The reason I ask is that I was hoping to 'prep' the stats by running log playback via the percona-playback tool prior to cutting over ...
1
vote
0answers
44 views

“Binlog has bad magic number” - recoverying InnoDB tables

A system crash raised the error in the title. I've tried the suggestion here, but then I can't access any of my InnoDB tables. They are still there, but data is missing. The provided solution ...
1
vote
0answers
261 views

Missing rows in MyISAM tables with 10-20 billion records

I've got 2 tables with 10-20 billion records each. Currently these are in MyISAM (MySQL). They only require to be built and indexed once, after this only SELECT is used. They are currently ...
1
vote
0answers
3k views

mysql 5.6 GTID replication 'Got fatal error 1236 from master when reading data from binary log'

I Checked SHOW SLAVE STATUS error is "'Got fatal error 1236 from master when reading data from binary log: ''The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master ...
1
vote
0answers
107 views

MySQL SSL Master-Slave replication not syncing

I have originally posted this thread on stackoverflow and after a comment I moved it here. The original post can be found here. I have setup a typical Master-Slave replication scheme for my MySQL ...
1
vote
0answers
60 views

mysql vs mongodb cache/load balancing

I have a system where users can 'like' content. There will likely be many hundreds of these likes going on at once. I'd like it to be AJAX driven so you get an immediate response. At the moment I ...
1
vote
0answers
175 views

mysql/mariadb: ifnull changes collation

It seems that 'ifnull' changes the default collation that is set to be used. I have tried on both mysql and mariadb: Server version: 5.5.34-MariaDB-1~precise-log mariadb.org binary distribution ...
1
vote
0answers
108 views

join performance, index vs. index+foreignkey

I'm using an orm framework that handles data (and its integrity) on top of mysql. So far I ignored foreign keys because the orm handles that just fine. I recently read that foreign keys can enhance ...
1
vote
0answers
251 views

InnoDB: error: space object of table

131212 10:35:18 InnoDB: error: space object of table 'main/wp_m_membership_levels', InnoDB: space id 119 did not exist in memory. Retrying an open. 131212 10:35:18 InnoDB: error: space object of ...
1
vote
0answers
194 views

MySQL Link to PHPMyAdmin/MS Access

I have a mysql database stored on a server via phpmyadmin. I want to link this to MS Access for other users. I often link mysql databases to MS Access which are stored locally, but never DB's stored ...
1
vote
0answers
100 views

Schema for yearly data models

I am developing a school management system. Most data will not be reused after the end of the school year. I am contemplating using schemas to separate data in various school years, however I am ...
1
vote
0answers
140 views

MySQL join query indexes

After recently discovering the MySQL feature to log queries that aren't using indexes, I've been chasing these down in a large app, looking for places to improve performance. There is at least one ...
1
vote
0answers
45 views

Find the neighbouring polygons of a given polygon in a MySQL spatial table in ver < 5.6.1

Finding neighbouring shapes in MySQL spatial table made up of ID and 2D geometry (polygon) fields. I have a table with an ID column and a geometry column (holding a polygon) representing various ...
1
vote
0answers
167 views

Cumulative Function in MySQL

i have a problem creating cumulative function in mysql database. let say i have a stored procedure which getting data and counting quantity/sum_quantity example A table consist of consumer goods ...
1
vote
0answers
780 views

Analyzing MySQL crash due to corrupt InnoDB page. Failed page checksum

Lately we have been pestered by infrequent sporadic database crashes on one of our MySQL instances. Each time it is due to a failed page checksum. It happens on a specific database but spans multiple ...
1
vote
0answers
112 views

Migrate from Interbase to MySQL

I need to migrate a big database from Interbase to MySQL, I'd like to have suggestions about free and non-free tools to perform this task. I've found this one: ...
1
vote
0answers
55 views

Help with Mysql with 100K+ Table

Hi im trying to develop a database design in which its gonna have 100K+ tables residing in it. My major concern here is will Mysql be able to hold that much amount of tables with no performance ...