MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
607 views

Need advice for table design for multi-user access

I have an inventory application that needs to support multi-user. As of right now, only one user can access and manage their items. I've gotten a lot of requests to support multiple users so they can ...
1
vote
1answer
93 views

Replication Master/Master, both master stop by themself

I did a master/master replication with MySQL on a Gentoo OVH Release 2, all work fine in my test phase, i put it in production, the replication works fine in 1 or 2 days but this morning, i don't know ...
1
vote
2answers
1k views

Remove InnoDB files (ibdata1 and iblog files) after migration to MyISAM

I changed the only two InnoDB tables on a replication slave to MyISAM, so the slave now only has MyISAM tables. Can I now remove the ibdata1 file completely?
1
vote
2answers
80 views

Strange issue regarding my.cnf

I'm getting a weird message from phpmyadmin saying: #2006 - MySQL server has gone away What I found online is that I should increase the size of max_allowed_packet witch should be in the my.conf ...
1
vote
1answer
494 views

Would adding indexes to my foreign keys improve performance on this MySQL query?

Consider the following query: SELECT `locations`.`id` AS `location_id`, `locations`.`address`, `locations`.`lat`, `locations`.`lng`, `tickets`.`status_id`, `customers`.`name`, ...
1
vote
2answers
239 views

Security: writing to a file from MySQL CLI

I was testing writes to a file from MySQL CLI (locally & remotely) and I wonder what settings are preventing writes for remote users, basically I would like to make sure that it's set on all ...
1
vote
3answers
435 views

index mysql concatenated columns

I have a table for author names with two fields, first_name & last_name, both varchar(55), both nullable (for crazy business logic reasons) although in reality last_name is unlikely to ever be ...
1
vote
2answers
681 views

boolean purpose column char(0) or tinyint(1)

For Boolean purpose column which one is better: nullable char(0) or tinyint(1). I know bool is an alias for tinyint(1) , but in "High Performance MySQL" book that published by O'reilly said: "If ...
1
vote
1answer
191 views

I have accidently deleted ibdata1 on an ubuntu server

I have accidently deleted the 'ibdata1' file on the server having innodb, Is there any way I can recover it? the mysql service has restarted since the deletion.
1
vote
2answers
116 views

Which MySQL engine should i use where most operations are read operations

I want to develop an application for which i will need a database to store data. I am using MySQL as backend. My question is Which MySQL engine should i use? Well, i have mentioned some important ...
1
vote
2answers
72 views

adding ORDER BY decreases speed by 15x

I have a simply query: SELECT DISTINCT images.* FROM `images` WHERE `images`.`user_id` IN (SELECT following_id FROM `follows` ...
1
vote
1answer
1k views

Optimize command issuing “'View is not base table” and “Corrupt” error messages

I have no experience with MySQL. My boss ran an optimize against a MySQL database. Against a bunch of views we got the message "tablexyz" is not BASE TABLE and the next has message is ...
1
vote
2answers
1k views

Is there any way to force MySQL use Hash Join instead of Nested Loop Join?

According to the document MySQL Explain Output format, MySQL resolves all joins using a nested-loop join method. Is there any way to force MySQL to use a hash join?
1
vote
1answer
565 views

Like with the results of a subquery (multiple values)

What I'm trying to do is the following concept: SELECT field1,field2,field3 FROM table1 where field3 like '%'+(SELECT distinct field from table2)+'%' But this is not allowed because like does not ...
1
vote
1answer
1k views

ERROR 13 (HY000) When Creating Database

I'm trying to create a database and when I type in CREATE DATABASE epik; I get the error ERROR 13 (HY000): Can't get stat of './epik' (Errcode: 13) I am running kubuntu 13.04 with mysql 5.5.31.
1
vote
1answer
2k views

passing --single-transaction in mysqldump command line tool

I'm using MySQL GUI tool to backup my innodb database and I use the option '--single-transaction' to get a consistent copy as backup. I have however, noticed that if I backup using command line ...
1
vote
2answers
281 views

How to avoid lagging when enabling log-slave-updates?

Master: 48GB RAM 16-core my.cnf: https://clbin.com/hlkUo Slave: 64GB RAM 24-core my.cnf: https://clbin.com/viLm0 Both are running 5.5.28. For the incremental backup purpose, I need to enable ...
1
vote
2answers
243 views

What is the size of currently-existing tmp tables in innodb?

This is yet another "how to add a column to a gigantic table" question. The table on disk takes 2.5GB, and I have around 10GB of ram I can use. I have a feeling I can crank up the tmptablesize option ...
1
vote
1answer
214 views

Master updates Slave but not Master itself

I have a setup master/slave in which applications are pointed only to master. Yesterday Master had got crashed due to "multi bit error on dimm detected" in Front indication panel in orange color. ...
1
vote
1answer
701 views

Will OPTIMIZE TABLE have any impact on my data?

I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables. But before doing so, I would (of course) like to know if ...
1
vote
2answers
2k views

High Mysql Load , over 700% CPU

I had high mysql load on server linux 64 bit , 24 G.B ram , Intel(R) Core(TM) i7 CPU 950 @ 3.07GHz , Alot of quiers in sending data mode Here is mysql status ...
1
vote
1answer
960 views

MySQL: logging queries which would execute without using indexes

I am trying to use log_queries_not_using_indexes = 1 to find queries which are not executing optimally on a MySQL server. However, I find the resulting log file of rather limited value. Apparently, ...
1
vote
2answers
40 views

Order by on an alphanumerical column

I have a column that has strings of the following type: Miller 10 Allen 20 King 10 .... Jones 100 I try to sort the column based on the numerical part of the data. I tried the following: SELECT ...
1
vote
1answer
60 views

mySql Relationships / Index - Checking for duplicates

I have this setup on innoDB: TblUser user details TblConnection User Connection TblConnection also has these indexes: User, Connection where User is the foreign key of TblUser.id and another ...
1
vote
1answer
56 views

After adding a slave to Master - Master configuration, slave syncs from only one master

Futher to my previous question, Here is what we have +<------+ | ^ V | S1<---M1 M2 | ^ V | +-------+ what happens is Upon ...
1
vote
2answers
567 views

How do I find an invalid utf8 character “somewhere” in my MySQL/trac database

I have an installation of trac, which uses MySQL. It has been upgraded so many times, moved servers etc. and chances are that the MySQL character sets were not always set correctly or consistently ...
1
vote
3answers
11k views

How to migrate SQL Server to MySQL

I'm trying to migrate a SQL Server db to MySQL, and I'm having a tough time. I've tried: MySQLWorkbench -- migration tool fails because my login only shows views in SQL Server, not the tables ...
1
vote
1answer
886 views

SELECT every second row without auto_increment

How can I select every second row in MySQL, without using any data of the table or some auto_increment? Something like SELECT EVERY SECOND ROW FROM `table`; Thank you!
1
vote
2answers
293 views

Why would splitting up a query make it faster, and can I/should I fix this?

The following query is for a web-based game. It sets the isTiedForHighScore column to true for all solutions that tied the score for a given "map." UPDATE solutions INNER JOIN ( SELECT mapID, ...
1
vote
2answers
5k views

MySQL 5.6 Failure

I have the following in RPM_UPGRADE_HISTORY in /var/lib/mysql MySQL RPM upgrade to version 5.6.10-1.linux_glibc2.5 'pre' step running at Fri Mar 15 15:41:26 EDT 2013 ERR file(s): -rw-r----- 1 mysql ...
1
vote
1answer
2k views

MySQL: Create multiple tables with one query

Can you list multiple table names in a MySQL CREATE TABLE query? CREATE TABLE table1, table2, table3 LIKE table_template; If so, can you do it with the IF NOT EXISTS clause? CREATE TABLE IF NOT ...
1
vote
2answers
2k views

MySQL Replication without stopping master

I am currently setting up a replication for my database one of the ways i am thinking of synchronizing the master and slave without write locking the master, is by first taking a log position then ...
1
vote
2answers
4k views

Multiple parents and multiple children in product categories

I am making a ecommerce site. In this site I want to categorise the items into three different layers primary category sub category sub category >>electronic ...
1
vote
1answer
300 views

Does MySQL's MyISAM use cache before insert?

I want to test two different Disks, one of them is HDD and another is SSD I use exactly same information and app for both servers. They are exactly same, just one of them is SSD and another is HARD. ...
1
vote
1answer
1k views

MYSQL Triggers syntax

I need some help with my trigger statement, as I would like to only insert the new row if a column value does not already exist, or if it does exist update a corresponding column. Currently I have a ...
1
vote
1answer
631 views

Query is not using indexes on third table in left join

My query is not using indexes on third table(pci promotion_coupon_images) . i was tried with using index and force index in query but there is no change in result. any body please suggest me how ...
1
vote
1answer
43 views

simple query is not using indexes

My query is not using indexes. it was taking around 5 seconds. output is 500 rows only. it is a simple query Please find the explain plan and table structure here. Explain Plan mysql> explain ...
1
vote
1answer
51 views

Column order for better index

let say I have two tables like tbl_parent and tbl_children (just as an example), in the first table I have parent_id, and in the second one I (will) have something like child_id plus the parent_id, ...
1
vote
3answers
180 views

Anyone want to try a script bash or python?

I need to automate master binary log purging to occur so that purging never goes back further than the last slave has the updated info for. These slaves are mobile and there are 3 of them, and sync ...
1
vote
1answer
111 views

Issue with Storage Engine

Just got the below error in one of the slave. I just Googled it and changed the variable myisam_data_pointer_size from 4 to 6. Somehow, my ALTER TABLE was successful. Can you please let me know ...
1
vote
2answers
1k views

Using MySQL EXPLAIN to analyze the performance of a function

You can precede a SELECT statement in MySQL with EXPLAIN, but how would you explain a function? I have a function that does a lot of work and I would like to analyze the performance of the function as ...
1
vote
1answer
295 views

Which Database to choose: MySQL or Oracle

I have an application generating time series data (32 channels, 22KHz, 6 secs) once a minute. These have to be stored in a database. At the moment I'm using MySQL with InnoDB tables ...
1
vote
1answer
1k views

Changing InnoDB data directory prevents MySQL service from starting

I have a MySQL instance installed on Windows 7 and I wanted to change the directory of InnoDB tables data. I modified the "my.ini" adding the following line after "[mysqld]": ...
1
vote
2answers
3k views

Getting repeated value in a group_concat only if ids are different

I have two tables, first the table Product: id|category_id --+----------- 1 | 12345 2 | 12345 3 | 12465 And then a table activity: id|prod_id|activity_type |description ...
1
vote
1answer
94 views

mysql allow access for localhost and named server

Hope we're enjoying this Fine Friday afternoon and getting ready for the weekend. I've got a quick question. I'm trying to set up a user to allow them access from localhost and from a specific named ...
1
vote
1answer
95 views

Phantom Table in MySQL

Programmer here, be gentle. I'm working in a MySQL database with only a single table, as shown by show tables: mysql> show tables; +----------------------+ | Tables_in_widget_web | ...
1
vote
1answer
91 views

After piping a file with SQL statements to mysql, how can I keep the command line session open?

A file, in.sql, contains a CREATE TEMPORARY TABLE statement followed by some INSERTs that populate the above table. Temporary tables are purged after the session closes, so 'mysql -u user -p pass < ...
1
vote
2answers
354 views

mysql_select_db() very slow on a busy server

I have a MySQL Server with about 30 Databases and 1500 Tables. Overall size of the data is 2 GB. The Server has 16 GB RAM and an AMD FX-6100 CPU. Most tables are myisam. On average the server answers ...
1
vote
1answer
40 views

Is it possible to CREATE EVENT to another server?

I have serverA and ServerB. I need ServerA to copy a database to ServerB and UPDATE/INSERT/DELETE as it changes. I think this can be done with replication but I don't want to continually open a ...
1
vote
1answer
675 views

Splitting a column to normalized table

I have a de-normalized table as Original De-Normalized Table id text 23 first,second|third,fourth,fifth|sixth I want to normalize the database to create the following tables Table 1: (Split by ...