All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

1
vote
1answer
265 views

Handling SQL updates for production deployments

I'm researching for quite a long time now (about two months) and haven't found a good approach for this, so I'll ask you experts out there in the hope of some enlightenment. I run a pretty ...
0
votes
1answer
36 views

MySQL insert with sub select slow

We are using Python and LOAD DATA INFILE to load data from CSVs into our staging database. From staging we have sql scripts moving the data into our actual production database. LOAD DATA INFILE is ...
0
votes
1answer
336 views

Update query in a trigger

I have a configuration table: Table: refdatecol - id int(11) startdatecol varchar(100) enddatecol varchar(100) datecolTable varchar(100) This table holds the user configured column names ...
0
votes
1answer
49 views

Mysql: slow down with order by, global database problem

i create nasty query for testing reasons. Only for speed testing. EXPLAIN SELECT m.post_id FROM wp_postmeta m LEFT JOIN wp_postmeta mi ON m.`post_id` = mi.`post_id` LEFT JOIN wp_postmeta md ON (md.`...
0
votes
2answers
49 views

how to deal with mysql 500 million data per month?

this is a interview question a telecom billing system, there is a internet flow table (phone_number,start_time,stop_time,time_lasting,fare),500 million records per month(2G),every record size is 300K,...
0
votes
1answer
7 views

Mysql will no longer start after DB import

I'm running Mysql5.6 on RHEL 7. I had managed to get the mysql service up and running, and imported a database into it with no issues. However, after attempting to reset a user's password, the ...
0
votes
0answers
18 views

Determining whether a MySQL column is ever used

How can I determine whether a particular column in a MySQL table is ever SELECTed or explicitly UPDATEd or INSERTed or referenced in a WHERE clause? It'd be nice if I could determine the db user who ...
0
votes
1answer
124 views

How to recover performance_schema after restoring from partial innobackupex backup?

I am taking a partial backup from a Master server, which needs to be restored on a to-be slave server. I only need one of the databases, and the mysql database. I am following instructions on https:/...
0
votes
1answer
32 views

How do I remove first line from text and only the first?

I have a tables "Table1" in this table I have a Text NOT null field - "value". the value field is populated like so: value: 1.2.3.4 [cookie] : butter - 1 cup. 1.2.3.4 [cookie] : flour - 2 ...
0
votes
0answers
19 views

Top Recommended Books/Courses For Learning Mid-Advanced Database Programming Beyond Basic CRUD Operations [on hold]

I have been a SQL DBA for the past two years and am looking to learn more indepth database programming to up my skill set and make me more attractive to potential employers. Currently with my day to ...
3
votes
1answer
130 views

Which is better MySQL search strategy, varchar x 2 or text column?

I have a MySQL database for an SMS application. For the sms_msg column, I initially setup varchar(255) expecting SMS to be short anyway. Now I realize that's not enough and some messages are getting ...
0
votes
1answer
135 views

MySQL PHPMyAdmin Autocommit Off

I have global autocommits off but I was wondering if it were possible to set autocommit on for PHPMyAdmin during login without doing so manually. Turning off autocommit makes the PHPMyAdmin GUI not ...
1
vote
2answers
31 views

MySQL to use all cores 20 cores I have

I am currently setting up a website that uses a MySQL database having more than 40 million rows of data. I am using one server for files and separate mysql server. Two servers are having 20 core, 64G ...
0
votes
2answers
125 views

MySql: how to remove special characters from column in query

What i get value in php $subject = preg_replace('/[^A-Za-z0-9]/', '', 'abc - 123'); // abc123 Is it possible to use preg_replace in my mysql query? I want to something like this. want to ...
0
votes
1answer
21 views

MySQL .ibd file is too big

I got a memory issue while in linux. When i checked memory allocation by using df -h i found a path /dev/sda1 and it is used 100%. When i dig more i found that /var/lib/mysql file ate almost all ...
0
votes
0answers
15 views

Error creating foreign key on (check data types)

Very weird problem. I need help, I can't figure out and I don't know how to trace the specific errors in phpmyadmin. I have 2 tables and I want to create FK between them. The first table is keywords:...
0
votes
1answer
16 views

Can i Make Index for all columns(No write operations)?

Few days I just learned indexes.I my New app(running on node js) There will no write operation.And my server has 500mb of ram.Can I set Index to all of My column.I almost have 500K rows.My query is ...
3
votes
1answer
178 views

How to run OPTIMIZE TABLE on a 3-node cluster?

I've my hands on a 3-node Percona XtraDB Cluster where, according to mysqlcheck, some tables are corrupted (some indexes contain the wrong number of entries): mydb.mytable Warning : InnoDB: Index '...
0
votes
0answers
19 views

Replication after Master down in Master-Master schema in MySql

I have set up a Master to Master replication scheme. I have 2 Master server: server A and server B. Server A is configured as follow. my.ini server-id = 1 binlog_do_db = example log-bin=mysql-...
0
votes
1answer
218 views

MySQL - When Command column in Processlist shows “Prepare” or “execute”

Can someone please highlight when a "Command" shows "Prepare" or "Execute" for "Show Full Processlist" Any example which shows this would be helpful.
0
votes
0answers
23 views

Adding where clause in LEFT OUTER JOIN giving unexpected result

SELECT xarbo_discussions.id as id, xarbo_discussions.subject as subject, xarbo_todo_lists.title as todo_list_title, xarbo_todos.title as todo_title, xarbo_files.title as ...
0
votes
0answers
27 views

Update on a table on MySQL Innodb seems to hang / lock forerever

When performing an update on a MySQL database for an InnoDB table, the update sentence never finishes. This is the update sentence from the MySQL query log: UPDATE `bb_plan` SET `creation_date` = '...
0
votes
1answer
37 views

Matching Custom Exported CSV File with Main MySQL Database

I recently selected certain columns from a table from a huge MySQL db >10GB into a csv file and then filtered through the rows. I would now like to retrieve the rest of the data matching the filtered ...
0
votes
1answer
36 views

I Need Help With My Database Design for Product Management

I'm designing a MySQL database and the front end for my work. The database will store all of the products that we sell on our marketplaces and their relevant data. We will be able to lookup products ...
0
votes
1answer
160 views

Replication Issue - Changing the replication-do-db filter

I have a server that someone has setup which are acting as a master on both database. Since they have been configured as both master on the same database, changes one on end has caused all sorts of ...
0
votes
0answers
8 views

How do display the vertical format like MYSQL in the console, but for H2 Database?

I am administering a H2 database and I'm coming from the mysql world. I usually use the console to do quick queries in the mysql console. When there is alot of data returned, I use the \G option for ...
0
votes
1answer
38 views

Really slow join, limit and order_by query

I've got these two tables: books (about 2 million records in total) Containing a unique ID (isbn) aswell as an integer value representing popularity (index, but not unique). category_pointers (also ...
0
votes
1answer
117 views

MySQL CONVERT_TZ returns null

MySQL CONVERT_TZ returns null, if timezone value is in plus. example: select CONVERT_TZ(now(),SUBSTRING(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')),1,5),'+00:00'); it returns ...
0
votes
1answer
216 views

How to recover a dropped mysql database with its procedures?

I had some databases on a server and MySqlserver on this server has been uninstalled accidentally by someone. I had some backups on this server and could recover all tables and their data successfully ...
8
votes
1answer
766 views

Issues with MySQL installation

First of all, excuse the length, this post is very long, and needs some background, second; it has been hard for me discerning the signal from the noise, please bear if some of this is not relevant: ...
2
votes
1answer
215 views

Mysql high cpu usage - cpanel

After an update from whm, I see a high CPU usage from MySQL (5.6). my.cfg: [mysqld] slow_query_log=1 slow_query_log_file="/var/lib/mysql/slow-query.log" long_query_time=1 skip_name_resolve ...
4
votes
1answer
554 views

Partialy Solved - Problems Deleting Data

I did a workaround that solved the problem for today but I want to know what I did wrong for the next project. I'm stuck on trying to delete some information on my MySQL database. I've never used a ...
3
votes
0answers
96 views

Inputting auto-incrementing, grouped, unique hashes?

Alright, here's what I've come up with so far: SET @id=(SELECT IFNULL(MAX(id) + 1, 0) FROM leads); UPDATE leads SET id=@id WHERE id IN ( SELECT id FROM (SELECT * FROM leads) AS something WHERE ...
1
vote
1answer
18 views

Temporary file write failure on MySQL

When running an ALTER TABLE ... ADD COLUMN ... I get: Error Code : 1878 Message : Temporary file write failure. And on log_error (/DATA/mysql/.err) 2016-06-29 17:27:01 7f11f08a7700 InnoDB: ...
1
vote
0answers
24 views

Question about MySQL triggers performance [duplicate]

I currently have a database that stores some transactions for several users, for example purchasing or selling products is stored as 1 entry in the transactions table. It can also store profits made ...
0
votes
0answers
14 views

With regards to an ms access to sql migration

I am aware there is lot of information on the web about how to migrate from an access database to a SQL one. However most of the tutorials are about moving from MS Access to MS SQL Server, what If I ...
1
vote
0answers
14 views

Mysql to MS SQL Migration and synchronization

Database migration from MySQL to MS SQL using SSMA tool is very nifty, however, I have one question around this. I have 2 MySQL Db's want to migrate to MS SQL, one sizes around 850 GB and another one ...
0
votes
0answers
12 views

mysql needs a start after turning system off

Hii I am facing severe installation issues after installing Ubuntu 16.04. Mysql needs a start everytime system is turned ON. I have removed every available package of mysql 5.7 but after installation ...
0
votes
1answer
698 views

inno_db_buffer_pool_size and key efficiency

I have the following metrics from mysql workbench After I have set innodb-buffer-pool-size to 6G, according to this answer's query on how to determine the recommended size. While the used buffer ...
3
votes
0answers
19 views

FULLTEXT Search (InnoDB) - Am I doing it right?

Goal: I wish to do autocomplete using ~130K global city names. Setup: I have created a 'cities' table as follows: CREATE TABLE IF NOT EXISTS cities ( id INT(11) NOT NULL AUTO_INCREMENT, ...
-1
votes
0answers
6 views

What is recent mysql version supported for RHEL 6 [on hold]

I am trying to find what is the recent MySQL version for RHEL 6. Thanks in advance.
0
votes
0answers
14 views

Is there a UI tool for moving data from two different tables?

The schemas in the two databases are different. Eg. Database 1 / users - id - name - phone Database 2 / users - id - first_name - mobile Is there a tool that lets me visually create the statements ...
1
vote
2answers
7k views

How does ORDER BY FIELD() in MySQL work internally

I understand how ORDER BY clause works and how the FIELD() function works. What i want to understand is how the both of them work together to sort. How are the rows retrieved and how is the sort ...
0
votes
1answer
25 views

How do I prevent multiple row insert?

I set up a playground MySQL database for users and I'd like to prevent multiple row (large) inserts. That is, if there will be over N rows after INSERT operation, then raise an error and do not ...
1
vote
0answers
32 views

Do not want to allow Queries to take more than x minutes?

Do not want to allow Queries to take more than x minutes ! What System variable can I set to automatically terminate it.
15
votes
3answers
35k views

How to make MySQL table name case insensitive in Ubuntu?

I am using Ubuntu 13.10 and MySQL 5.6 and I know database name and table name are case sensitive in Ubuntu (and some other *nix environments) by default. Now, I want to make MySQL work as case ...
0
votes
0answers
19 views

Backup MySQL to different destinations

I'm working with MySQL databases on Windows and I need a solution how to backup them to Amazon S3 and DropBox. I have 5 databases 600-900 MB each and I need to backup 3 of them to DropBox and another ...
0
votes
1answer
27 views

Default privileges to execute function

I have a user that has select only access but was granted execute on a single function in our mysql db. The issues I'm running into is other more privileged users will drop and create this function ...
1
vote
0answers
16 views

Retrieve data from OLTP MySQL to SQL Server DWH using SSIS

I'm running a BI solution currently reading data from an OLTP SQL Server 2008 to a SQL Server 2012 data warehouse. The ETL phase is implemented with SSIS and runs twice a day. This phase relies on a ...
0
votes
1answer
113 views

MySQL: How to decrease sleep process's time out?

When I run 'show processlist;', then I get so many sleep processes. I set wait_timeout and interactive_timeout to 60 in my.cnf. But, sleep process is not died when time 60 on processlist. I found ...