MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
0answers
1k 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
92 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
97 views

Best database for fast read all in one table

I currently have a large database in SQLite that I was using for rapid-prototyping for a software I am working on. Now that I am planning to move it to another database that should give better ...
1
vote
0answers
39 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
15 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
91 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
31 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
34 views

Optimizing MySQL query

I have a Sql query to optimize, its execution time is 8.5 Sec. I have made index on fields (timesheetdate,deleted) and able to reduce execution time to 8 Sec. A behavior I noticed that when I change ...
1
vote
0answers
114 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
88 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
1k views

MySQL - high CPU usage (MyISAM tables)

We have a server running LAMP stack on CentOS release 5.6 (Final) MySQL is having high CPU usage constantly which causes server to lag as shown top - 13:50:47 up 80 days, 13:53, 1 user, load ...
1
vote
0answers
54 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
147 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
43 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
170 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
119 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
165 views

mysql query doesn't work inside a stored procedure

this is my stored procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `isUserValid`(IN `facebookId` VARCHAR(20), IN `userAccessToken` TEXT) NO SQL COMMENT 'check if user is valid. return ...
1
vote
0answers
202 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
141 views

mysql database is corrupt after system power failure

I have mysql database using xampp in my widows system. its got currupt after power failure i have back of database before 2 days but i dont have backup of one day transactions. now i tried ...
1
vote
0answers
44 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
47 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
80 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
115 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
47 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
101 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
248 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
307 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
112 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
2k views

Why does CAST(1 AS SIGNED INTEGER) return a BIGINT on MySQL?

If I do CAST(1 AS SIGNED INTEGER) I always end up getting a BIGINT returned, for example: $ mysql -u root -p --column-type-info Enter password: --- Copyright and help message snipped for brevity --- ...
1
vote
0answers
108 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
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
29 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
177 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
2k 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
72 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
45 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
122 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
65 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
207 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
113 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
71 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
123 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
37 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
118 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
519 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
82 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
51 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 ...
1
vote
0answers
122 views

How to use variables in file paths in MySQL statements?

I have now a problem that I have to set paths through variables in queries like load data infile '/my/file/path/to/file.txt' into table database1.table2 fields terminated by '' lines terminated ...
1
vote
0answers
518 views

Fastest way to perform simple operations on 1 million rows

I have a simple table with more than 1 million rows in an InnoDB MySQL table. Here are the steps that I need to perform on each row: SELECT * FROM tbl where work_done=0 make some data processings ...
1
vote
0answers
76 views

MySQL: What criteria should I base my table column order/arrangement/grouping on?

Does the order, arrangement or grouping of columns with large (such as long text descriptions) and small (such as bit and tinyint) field types have any noticeable impact on MySQL (InnoDB engine) ...