MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
6 views

set sql_mode “blank” after upgrade Mysql to 5.6

after upgrade mySQL from 5.5 to 5.6 some our app face to problem and need set sql_mode to blank to solve this issue. I add sql_mode = '' to my.cnf but not affect mysql setting. Thanks to guide.
0
votes
0answers
5 views

MySQL InnoDB data restore using 3 files

I am able to restore MySQL table data along with structure with indexes (InnoDB Engine) using 3 files. MyTable.frm, MyTable.ibd and ibdata1. Can any body confirm, will these 3 files suffice to ...
0
votes
0answers
6 views

Checksum mis-match found on a table while restoring data using mysqlbackup

We are trying to restore MySQL data (which was backed up using a single image) using the mysqlbackup utility. But, all of a sudden, it is showing checksum mis-match for table t1 Our questions ...
0
votes
0answers
18 views

What is the best way to find out the sum of a field from a table?

I am making a accounting software. I have a table debit_credit that contains double entry accounting data.Table is: debit_credit_id | tran_id | ledger_head_id | debit | credit | dr_cr | commission | ...
0
votes
1answer
14 views

Can't enable MySQL 5.6 Query Cache

I'm trying to enable MySQL Query Cache on Ubuntu 15.04 with MySQL 5.6.25 I've added this to end end of /etc/mysql/my.cnf and /etc/mysql/conf.d/mysql.cnf: query_cache_type = 1 query_cache_size = ...
-3
votes
1answer
33 views

Finding uniqe values from one table and calulating total number from another table that have unique values from first table [on hold]

I stuck on calculating values Name of table: dabase_123 Demo of table: program | quantity windows xp--------30 windows xp--------30 windows xp--------30 linux ubuntu -------80 I can filter ...
0
votes
0answers
15 views

MySQL Connector C++ bad_alloc thrown? [migrated]

I'm trying to write to a database from C++, but every time I attempt to execute() the command, I get a bad_alloc error. I know that the string is correct, as I have used the same exact string with the ...
0
votes
3answers
53 views

Major performance difference from a small change in query

I have a query written out to search a WordPress database for any post ids that match the keyword search to post content, title, author, or associated company. The company portion of the query is ...
0
votes
0answers
26 views

mysql migration from 5.2 to 5.4

I am on a MySQL migration project not managed by me. We are migrating from 5.2 to 5.4. However, I have few questions: Firstly, I could not find a good post on the release notes of MySQL 5.2, 5.3 or ...
1
vote
1answer
26 views

Aliases in calculations for MySQL

I am in the process to move a really small database from Access to MySQL and I have some queries to translate. I have this really simple query (in Access db): SELECT InvoiceNumber, Field1 + ...
1
vote
1answer
24 views

MariaDB unicode text got truncated

I'm using 5.5.40-MariaDB in Centos 7. [root@test ~]# /usr/libexec/mysqld -V /usr/libexec/mysqld Ver 5.5.40-MariaDB for Linux on x86_64 (MariaDB Server) I'm trying to insert a unicode string into ...
0
votes
1answer
17 views

Remove particular table from being replicated in a Multi-Master replication

I have followed all the steps in this link to set-up MySQL Master-Master replication for two database servers. The master-master replication works fine. Here is my my.cnf file, which contains some ...
0
votes
0answers
14 views

How to generate “Too many connections” error using mysql cli over ssh

I have a script which queries for blocks of data, one after another, from a remote MySQL database via ssh and mysql cli and pipes the result output to stdin of a local executable. Loosely scripted, ...
0
votes
1answer
19 views

Database Script

Database contains <60-70 tables, procedures, views etc. Database A:- script 1: backup of script taken on 30th July. Database B:- script 2: backup of script taken on 31st July. What can the ...
0
votes
1answer
34 views

MySQL IN() statements: Avoiding duplicate lists

I need to check that two columns both appear in a list of IDs. So right now I have this: SELECT `main_table`.* FROM `activities` AS `main_table` WHERE (actor_id IN ('10027' , '11535')) ...
0
votes
1answer
29 views

How to verify the password generated by ENCRYPT?

I use the ENCRYPT function to generate password for my email users. SELECT ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))) This function will generate a long encrypted string which ...
0
votes
0answers
19 views

Why does this simple insert fail sometimes? [on hold]

I have the following insert which I suspected might be failing frequently. I just tested it manually by making a small series of http requests manually from my browser to execute it, making sure ...
0
votes
0answers
172 views

Inserting emoji - smartphones in MySQL database with PHP? [migrated]

Well with the the asks from is quite high explanatory will only strengthen the question. APP have a chat system which be late the HTML , send it to PHP with ajax , but the usurer try to insert some ...
0
votes
2answers
40 views

Total number of read/writes in a day in MySQL

Aside from using standard count queries with a date, is there a global way to get the number of writes (or reads for that matter) in a 24hr period on a table, or an entire schema?
1
vote
0answers
36 views

Tuning MySQL MyISAM for large joined updates

I have a database with two tables that I would consider small. I need to update values on the larger of the two tables (1,100,000 rows, 75M MYD, 120M MYI) from the join of the two tables. The smaller ...
1
vote
2answers
104 views

Select Between Date Range Mysql

I want to select records between 2 dates: select * from orders where orders.created <= '2015-07-31'; select * from orders where orders.created >= '2015-07-01' and orders.created <= ...
0
votes
0answers
27 views

Versioning the DB scripts (MySQL)

We are a development team of 4 programmers. In order to manage the DB scripts we created some sort of update script that looks like this: DROP PROCEDURE IF EXISTS our_db.Temp; DELIMITER $$ CREATE ...
0
votes
0answers
45 views

mysql deadlock issues

we are having starnge deadlock issue. We have a deadlock in mysql and it appears conitously when we run engine innodb command. the webserver it is comming from do not exist as we have manually killed ...
0
votes
1answer
32 views

what is the fastest way to restore MySQL database without using dump files [on hold]

what is the best procedure to quickly restore the MySQL database without using dump files. can any body help me in this regard. Thanks in advance.
0
votes
1answer
31 views

Mysql 5.5 to Mysql 5.6 upgrade Insert query is breaking

Assume column with the following definition product_id INT NULL NO_DEFAULT product_id_nn INT NOT NULL NO_DEFAULT and query insert into table (product_id,product_id_nn) values ...
1
vote
1answer
27 views

MySQL SELECT blocking an INSERT?

Is it possible for a SELECT query to block INSERT / ALTER queries? I have the following (taken from ENGINE INNODB STATUS) on my slave database: ---TRANSACTION 26DA7060, not started mysql tables in ...
3
votes
0answers
17 views

Mysql transaction READ REPEATABLE and READ COMMITTED read view

I've come across something strage using Amazon RDS (MySQL 5.6.23). I had issues when concurrent transactions were locking and then calculating a value from multiple tables. The following is a quick ...
0
votes
0answers
16 views

MySQL - Seconds shown for query run time is incorrect

I came into work this morning, and first thing I noticed is that one of my Slave databases was running behind. I took a look at the processlist and could see that I had the following running. SELECT ...
-2
votes
0answers
9 views

mysql incremental backup using percona [on hold]

Can anyone suggest me what are the pro's and cons of using percona xtrabackup to make incremental backups of mysql database..
-1
votes
0answers
35 views

How to check if all days of the month are in the database

This is my table wys_attendence: id studid adate amonth ayear acls_id attendence 1 28 02 07 2015 10 1 2 31 02 07 2015 10 0 4 ...
0
votes
1answer
16 views

Extract four digits from a column by regexp

I am sitting on a database where one column contains two data types ;) Example "1";"When the Wind Blows 0275";"James Patterson " "4";"The Reluctant Fundamentalist 1026";"Mohsin Hamid"; The ...
0
votes
1answer
25 views

Deleting near duplicates based on one varchar field

I'm getting around to cleaning a database but I need some help with deleting near duplicates in MySQL database but is only off by one character in a column. I've searched and found ways to remove ...
1
vote
0answers
18 views

Caching impression counts to avoid mysql inserts

We currently get a LOT of mySQL inserts/updates on duplicate daily (millions) and we're looking at speeding up the application by using some form of counter with memcached and then clearing it on ...
2
votes
0answers
9 views

Caching impression counts to avoid mysql inserts

We currently get a lot of mySQL inserts/updates on duplicate daily (millions) and we're looking at speeding up the application by using some form of counter with memcached and then clearing it on ...
2
votes
0answers
43 views

How to store data where various subcategories have specific columns?

I'm new to database designing. I'll use MySQL and PHP. I'm designing a database in which I have a lot of categories and subcategories like given below: Product Table p_id p_title category_id ...
0
votes
0answers
29 views

Tables not updating and deleting the entries

I am facing the below problem on updating the tables ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 3 read views open inside InnoDB Main thread process no. 2376, id ...
-1
votes
1answer
27 views

Mysql: How does a rollback operation be performed inside InnoDB

I would like to hear in simple words Please answer me in simple words to the question on the below scenario. ============== Engine: Innodb Trx1 : started and a DML is executed at 10:00 AM and rolled ...
2
votes
1answer
74 views

select n values per group

Lets say I have the following table ----------------------------- | user_id | comment | ----------------------------- | 2 | thats cool | | 2 | awesome | | 3 ...
0
votes
0answers
36 views

Need to make sure database is fully normalised and all is accessible using MySQL and Java EE

So as per the picture: I need to create the roster table with all the data from the connecting ones - Each Roster entry will have the country,state and city, tech and subtech and client. Then ...
1
vote
0answers
39 views

Get average of multiple rows with criteria and return those rows

I'm attempting to get any items that are the same type, but are ranked one higher that have an average equal or greater than the item ranked below. I have a table and query like ...
1
vote
1answer
37 views

Speed up COUNT(*) when using an index

I have a table similar to this (simplified): CREATE TABLE books ( id INT AUTO_INCREMENT, category INT NOT NULL, PRIMARY KEY (id), KEY (category) ); This table has over 10,000,000 ...
1
vote
1answer
52 views

Delete rows where matched value is twice as much

I'm trying to match two tables with two columns, table1 and table2. Each has id and price columns. I want the query to match each id and compare the price, then delete the row if the price in the ...
1
vote
0answers
18 views

How to sort new data for display? [migrated]

I am new to SQL. I have a table called Categories with 3 columns: id int(25) category varchar(25) subcategory varchar(25) I would like to add a new subcategory "Real Estate Agency" in the category ...
0
votes
2answers
21 views

What is happening when Seconds_Behind_Master oscillates between two very different sets ov values?

I have a Percona 5.6 slave, newly set up, that is replicating from a server that is itself a slave to a third. The middle server (dbS2, the master for dbS3) is currently ~260000 seconds behind the ...
1
vote
2answers
28 views

Generating a sequence of values

How can I change 2,2,2,1,2 into 201,202,203,101,204 in MySQL? I have a table with data in the format of: +---+---+---+ | U | C | R | +---+---+---+ | 1 | 2 | 1 | | 1 | 2 | 2 | | 1 | 2 | 3 | | 1 | 1 | ...
0
votes
1answer
14 views

Error 1241 When Calculating Percentages

I am trying to get my percentages to equal 100%. As of right now they far surpass 100%. I need to account for the percentages in two different tables, ua_money_allocation and ua_expenses_by_school. ...
0
votes
0answers
15 views

How can I associate different not done continuation blocks with two cursors in the same scope?

If I have two cursors declared in the same block, how can I associate different not done continuation handler actions that are independent of one another and only execute with the cursor they're ...
2
votes
1answer
24 views

How to write a query that counts rows by date?

I have a database named emp1. There is a table named tx_feeder. I want to get the count of rows from that table. I have used the following query to get the data of single day, select count (*) ...
0
votes
1answer
9 views

mysqlbinlog: unknown variable ssl_key or ssl_cert or ssl_ca

I am trying to recover binlog from a remote server using mysqlbinlog client program, however it appears that mysqlbinlog doesn't recognize the SSL options that's required to establish SSL connect to ...
1
vote
0answers
23 views

How do you output field changes in mysql the most efficiently?

I have a rather large employee feed coming in and each employee has about 50 fields. I take the employee data and add on a bunch of other stuff and this gets outputted to one of our vendors. Most ...