MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
28 views

How to detect MySQL slave writes?

In a MySQL master-slave replication architecture, how do you detect if there have been (erroneously, since this should never happen!) direct writes on the slave? For now, I'm running this command on ...
0
votes
1answer
26 views

Can I split mysql the search results into two tables in php?

I have index tables with multiple columns: s | s1 | s2 | s3 | s4 | s5 | s6 | s7 | s8 | s9 | s10 | s11 | s12 | ------------------------------------------------------------------- 0 | 1 | 0 | 1 | 1 ...
1
vote
2answers
33 views

Mysql query to retrieve the latest updates based on DATETIME for each parameter

I have a station_data table where sensors from different weather stations report their data. Sample data: id(AI),station_code,parameter_code,value,date_time 1,1,1,1.234,2015-01-01 00:00 ...
0
votes
2answers
57 views

simple mysql query design help

Simple query design help. Below is my query: SELECT tkt.ticket_id, tkt.`number`, val.field_id, FF.name, val.value FROM os_TICKET tkt, os_form_entry_values val, os_form_field FF where ...
2
votes
1answer
44 views

MySQL stuck in EXPLAIN statement

I am trying to debug the following query. # Projects with at least 50 members with at least 240 yearly commits each select project_id, count(*) as active_members from ( select ...
0
votes
1answer
18 views

Updating inverse side bidirectional association

I understand (more or less) the concept of unidirectional and bidirectional association in a relationship, but I don't see the correct practical application of it. For example, I want an contact ...
0
votes
1answer
29 views

MySQL transaction and replication between two datacenters

I'm using a master MySQL in an east coast data center and want to setup a replication server on a west coast data center. The app server on the west coast may at times need to write to the master. ...
1
vote
1answer
63 views

MYSQL - Timeclock Count Hours In/Out for each day of current week

Below is my table for my database timeclock that records each record of an employee clocking in/out. I am wanting to create a function that returns hours for each day Monday-Sunday of current week. ...
0
votes
1answer
23 views

What could cause a record to appear in the MySQL binary log but not the original table?

I have a web application (CiviCRM) that has the option to enable binary logging, which I've done. We use this software to record donations, including automatic recurring donations. Our payment ...
0
votes
1answer
33 views

MySQL Replication: How to restore master FROM slave?

TL;DR: given a master/slave setup between two MySQL databases, where the connection from the master to the slave is read only, how can you restore the master from the slave in the event of a failure ...
1
vote
1answer
27 views

Update a unique column with an unknown number of values

I have a table with three columns, ID, a foreign key field and a text field (unique). The user should be able to update all text fields where the FK has a certain value. The problem is that the user ...
3
votes
2answers
271 views

Does an empty column value occupy same storage space as a filled column value?

I have a table with 2 columns. The type of both columns is set to varchar(38). If I create a row with an empty value for one of the columns, will it take same storage space as if the value was not ...
0
votes
0answers
24 views

Got an error reading communication packets

Facing error in MySQL Log which is "Got an error reading communication packets" for multiple IP's. Current MySQL version is 5.0.67 running in Linux Things looked into so far are as follows ...
0
votes
0answers
26 views

Set particular database connection temporarily to read-only

Set particular database temporarily to read-only through the command line while connecting db ? Note : Want to prevent UPDATE/DELETE operations Like - < --i-am-a-dummy >, it prevents to ...
0
votes
1answer
77 views

MySQL query very slow for one object, very fast for others

I have a table for Items and then a table for Parameters where each Item can have multiple Parameters. Each Parameter object has a foreign key back to its Item. I also have a Parameter Type table that ...
1
vote
2answers
52 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 ...
1
vote
1answer
25 views

convert 1 row as column and concatenate the results to the corresponding column

I have 2 tables table 1 - apps ----------- id | name ----------- 1 | abc ----------- 2 | sdf ----------- 3 | dfg ----------- table 2 - contacts ------------------------ id | cnt_type | ...
1
vote
2answers
44 views

Weird Syntax Error [closed]

I'm working with MySQL 5.6 on a Ubuntu 14.04 server. Checking in this online syntax checker: https://sql.treasuredata.com/ The syntax checker tool throws the exception: mismatched input 'NOT' ...
0
votes
0answers
48 views

MySQL deadlock in simple table

I'm having a deadlock issue that I don't get. Possibly it's because of the composite index (idSite,timestamp). Maybe it's range locking on it? I have this table: CREATE TABLE `logs` ( `idLog` ...
4
votes
4answers
341 views

Why index is not used with order by?

I trying to get info about why MySQL does not using my index when I create a inner join and trying to ORDER BY on the end. I have my SQL query here: SELECT * FROM product p INNER ...
2
votes
2answers
40 views

How can I count all children & grandchildren until the 7th level?

I have 3 tables right now 1. members 2. members_parents 3. members_status How can i count the children & grandchildren until to the depth level and separate the count() for the status 0 = ...
1
vote
1answer
112 views

Your PHP MySQL library version 5.5.44 differs from your MySQL server version 10.0.20. This may cause unpredictable behavior

How can I fix this "error" in phpmyadmin ? Is this a serious problem, I should care about? Server Version: 10.0.20-MariaDB-1~trusty-log - mariadb.org binary distribution Apache/2.4.7 (Ubuntu) ...
1
vote
1answer
34 views

Set sql_mode “blank” after upgrading to MySQL 5.6

After upgrading 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 added sql_mode = '' to my.cnf but there was no effect on the mysql setting. ...
0
votes
2answers
31 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
21 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
2answers
108 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
2answers
47 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 = ...
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
60 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 ...
1
vote
1answer
37 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
48 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
24 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
27 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
22 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
42 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
38 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
173 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
48 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
46 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
154 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
32 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
51 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
46 views

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

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
43 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
52 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 ...
4
votes
1answer
35 views

Mysql transaction READ REPEATABLE and READ COMMITTED read view [closed]

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
27 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 ...
-1
votes
0answers
47 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
27 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 ...
1
vote
1answer
29 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 ...