MySQL Server

46K

Solutions

22K

Contributors

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

Share tech news, updates, or what's on your mind.

Sign up to Post

This works fine:
SELECT HD_QUEUE.NAME , count(HD_TICKET.ID) FROM ORG1.HD_TICKET
JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY HD_QUEUE_ID

But breaks when I add this in:
WHERE HD_QUEUE.ID = 5

SELECT HD_QUEUE.NAME , count(HD_TICKET.ID) FROM ORG1.HD_TICKET
JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY) AND WHERE HD_QUEUE.ID = 5
GROUP BY HD_QUEUE_ID

What am I doing wrong?
0
Get proactive database performance tuning online
LVL 3
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

hi,

I read that all MySQL database share the same transaction log, is that right?

it can be a big impact on performance, right ?
0
I am creating a simple messaging application using mysql and php.
 
This is my simple database and I have put some example data in it. You can see user 1 and 4 have been having a conversation, then there is a few other single messages.

example
I want a list of who has had a conversation with you... like it does on the iPhone…. Eg...

- user 4 should only see 1 entry for the conversation between himself and user 1.
- Then they would click that conversation to show the full messaging history between the 2 users.
 
From the db above for user 4 I would want to see…..
 
User 1 / Message 5 / 2017-07-23 10:13:00
User 2 / msg / 2017-07-23 10:20:00

Currently my simple query looks like this:

SELECT *
FROM message
WHERE userIDTo = $userID
OR userIDFrom = $userID

Open in new window


but this query shows a list of 8 messages, but i only want it to show the most recent messages... in this scenario it would be 2.

Hope my explanation makes sense and you can help.

Thanks

Steve
0
hi,

now setup the first MySQL 5.7. 19 and now created a user to access it from a remote machine.

how can I grant the full right on everything to this user by doing this:

GRANT usage  on *.* TO xyz@localhost;

what I got is :

ERROR 1133 (42000): Can't find any matching row in the user table


GRANT ALL PRIVILEGES on *.* TO xyz@localhost;

and I got :

ERROR 1133 (42000): Can't find any matching row in the user table


the same message,

how can I solve it?
0
Hi.

I have a table A with a number of records. In table B is  a number of relating records. Say there is 100 records in table A, then there is a number of matching records (matching ID) in table B, but it may be 0 or more records for each record in table A.
When I make a query with LEFT JOIN, then I get less than 100 records. I don't understand why. My syntax seems straight forward like:

SELECT A.id, A.var1, A.var2, B.id, B.var1 LEFT JOIN B ON A.id=B.id WHERE B.var1='test'

Open in new window


Am I missing something? I tried LEFT OUTER JOIN  as well, but no difference. I want  all 100 records and just a "NULL" or something if a related record in table  B is  missing.

Best

Ulrich
0
I am working with a third-party MySQL database.  I am trying to decode a field.  This field contains the IP address of a computer.  I have been told this field is in base64 format.  I am not able to figure this out.  Below I have an example of some methods that I have tried using, with no luck.  

select 
    ip,
    from_base64(ip),
    inet_ntoa(ip)
from datbasename.device;

Open in new window


I am running MySQL 5.6.32.

Does anyone have any suggestions on how I can decode these values?

Many Thanks!
0
MySql 5.7 / Coldfusion 11

I have a real estate property search form with multiple fields for instance  City, Price, Acres, etc... and a Keyword input that needs to be able to further filter these results.   Example: I need to be able to find only properties listed in a certain city by a certain agent the agents name goes in the "keyword" input.

I have 3 tables in my database that I need to query and do a fulltext search on; "listings",  "fagents", "fcoagents"  
so I added fulltext index for each.

ALTER TABLE listings ADD FULLTEXT(Class,Type,SubType,City,PropertyName,Highlights,Remarks,Agent,CoAgent,AddressNumber,AddressStreet,Zipcode);

ALTER TABLE fagents ADD FULLTEXT(AgentFName,AgentLName,AgentEmail);

ALTER TABLE fcoagents ADD FULLTEXT(AgentFName,AgentLName,AgentEmail);


SELECT * FROM listings
LEFT JOIN fagents ON listings.Agent = fagents.Agent
LEFT JOIN fcoagents ON listings.CoAgent = fcoagents.CoAgent

WHERE (Status = "Active" OR Status = "Pending")

AND

<!---Search by City--->
<cfif StructKeyExists(form,"City") and listLen(trim(form.City), ",")>
 AND City IN
        (
             <cfqueryparam value="#form.City#"
                   cfsqltype="cf_sql_varchar"
                   list="true"
                   separator=",">
     )
</cfif>

<!---Search by keywords--->
<cfif StructKeyExists(form,"Keyword") and len(trim(form.Keyword))>
AND
0
I am refactoring an older system written in Php/Mysql.  In this code I am seeing several instances of 'return -1' and I'm not clear why this could be.

function mysql_connection($database){
      if(!isset($database)){
            return -1;
        }

        more code ...
}

I'm a novice and have not found an answer in any of my research.

Can someone help me understand why this would be used?
0
Hi there.

I want to make a query based on a table with 542 unique individuals, and each of these 542 individuals has got up to 6 events each (time series). Right now there is up to 6 rows for each individual. I want to "drag" those events out in one row. Now I made some CASE WHEN obs=1 THEN date END as date1 etc. and grouped by individual and event. I get the desired columns, BUT they're still in separate rows. How do I merge them to only 1 row for each individual?

Best regards

Ulrich
0
I watched a video of MariaDB which stated  on a power point

data comparison and synchronization
-comparing and synchronizing data I'm databases of any size
-generation of data synchronization kit
- comparison reports in HTML and EXCEL

the last one is what stopped me.
I'm building a web app that will store numbers and other data daily over a large geographic region each month this data is manually entered into Excel via a CSV file.

is what Maria is proposing better or worse overall for us. approx 10,000 customers but and we're not expecting any probaleks from the MySQL.

it's nice to know I can port to it.

personally I love MySQL Object Oriented because it still lets you count rows so easy. been up coding going to do some CSS.
0
Will your db performance match your db growth?
LVL 3
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Hi Experts;
Using Workbench on my local system to a remote MySql Server -
I set up a couple events via event_scheduler. Tested and viewed using >>show events, which displayed my events as well as a few others set by other developers.

I logged out of the Workbench; and later launched to work on something else.
I ran >>show events; again to look at the events, and the results are blank (?!).

I expect this is a simple novice mistake. Can anyone help me verify my events are scheduled?
Thanks
0
I googled but i was only able to get top n rows or random rows is that possible to fetch it % of rows for every customers .
0
I have a MySQL database with a table called 'results'. It has race results from various years.

I have a query that returns a list of participants who appear 3 times in the table (the difference being the 'year' column)

Select COUNT(firstname) AS CountOfRuns,
firstname,
surname,
dob, email
from results
group by firstname,
surname,
dob
having CountOfRuns = 3

Open in new window


I have a column called 'pin' and for each of the rows returned by the above query (there will be 3 rows per person) I want to update the 'pin' column to have the number 3 in it.

I can't get my head around how to get the Update statement to make the necessary change.
0
HI am using stored procedure to convert row value to column header . but while trying to convert it works fine if the column header has less words and it fails if it has more than 5o

BEGIN

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(questions= ''',
questions,
''', yes_no, NULL)) AS ''',
questions,''''
)
) INTO @sql
FROM tbl_main where task=in_task;
SET @sql = CONCAT('SELECT case_id,audited_by,resolved_by, ', @sql, ' FROM  tbl_main WHERE task IN  (''',in_task,''') and audited_date   between(''',from_date,''') and (''',to_date,''') GROUP BY   case_id,audited_by,resolved_by');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END

Can anybody assist me to overcome this
0
This query in Access results in the error in screenshot... is it the = sign?

SELECT dsf.qm_sku AS sku, dfv.option_code AS OptionColor, df.fsp_title AS OptionTitle
FROM dynamic_sku_fsp dsf
INNER JOIN dynamic_fsp_values dfv ON dfv.fsp_value_id = dsf.fsp_value_id
INNER JOIN dynamic_fsp df ON df.fsp_id = dfv.fsp_id
WHERE option_code IS NOT NULL

image
0
Hi Experts

Could you point how can I avoid a full table scan on this mysql query?

Acordingly with this query profiler statistics:
img001

SELECT
		dt_output
	,	COALESCE(SUM(assistencias),0) AS assistencias
	,	COALESCE(SUM(services),0) AS services
	,	COALESCE(SUM(connectionsoffered),0) AS connectionsoffered
	,	COALESCE(SUM(acdconnections),0) AS acdconnections
	,	COALESCE(SUM(abnconnections),0) AS abnconnections
	,	COALESCE(SUM(connectionsoffered_fake),0) AS connectionsoffered_fake
	,	COALESCE(SUM(med_wait_time),0) AS med_wait_time
	,	COALESCE(SUM(med_acd_talk_time),0) AS med_acd_talk_time
	,	COALESCE(SUM(percent_serv_lvl_spl),0) AS percent_serv_lvl_spl
	,	COALESCE(SUM(percent_abnconnections),0) AS percent_abnconnections
	FROM
	(
		SELECT
			DATE_FORMAT(Initial_Date, "%Y-%m") AS dt_output
		,	NULL AS assistencias
		,	NULL AS services
		, 	SUM(_receivedconnections) AS connectionsoffered
		,	SUM(_acdconnections) AS acdconnections
		, 	SUM(_abdconnections) AS abnconnections
		,	SUM(_abdconnections + _acdconnections) AS connectionsoffered_fake
		, 	ROUND(SUM(_TMEFormula1)/SUM(_TMEFormula2)) AS med_wait_time
		, 	ROUND(SUM(_TMAFormula1)/SUM(_TMAFormula2)) AS med_acd_talk_time
		, 	ROUND((SUM(_SLAFormula1)/SUM(_SLAFormula2)*100),1) AS percent_serv_lvl_spl
		,	ROUND((SUM(_abdconnections)/SUM(_abdconnections + _acdconnections)*100),1) AS percent_abnconnections
		FROM project_reports.yearly_table
			 AS `csd`
		INNER JOIN `project_reports`.`list_skill` AS

Open in new window

0
Hi Experts


Could you point the better manner on extend a timeout in PHP?

My sessions timing out too quickly.

Thanks in advance.
0
hi,

If I want to setup mySQL active active/ multi master , which based on replication, which edition of MySQL should I use?

community or enterprise editon ?

what if I also need to use audit option for MysQL, can MysQL community offer that?

any technical feature is lack from community edition ? I just want to setup POC of MySQL to check if MySQL active active . multi master is working fine.
0
hi all,

I search from internet about MySQL cluster Auto installer:

https://www.youtube.com/watch?v=nYiIwgjcRWI

but I am install MySQL on redhat linux and I am not sure how the guy in the video execute the auto installer in windows? how it works ?
0
Get free NFR key for Veeam Availability Suite 9.5
LVL 1
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

I need help sorting a list of titles in an ascending order. As you can see in the screenshot below all looks good except that the titles with the value "10" in the string are grouped next to the titles which contain a "1" in them.

Using a natural sort almost gets me there, so does using a CAST() but, the results are still not correct. Can this be fixed?

Sort order is incorrect. All titles with a "10" should be at the bottom of the list.
I've pasted my natural sort query below:

SELECT * FROM pi.files WHERE project_id='121' AND step=0 AND box=0 ORDER BY file_title ASC
0
php date() returns the server time
even if the timezone is changed through script  the date() function returns the time set
database being on another server have timestamps stored as per gmt
m trying to extract data between a particular interval on the basis of epoch timestamp
0
Table definition:

DROP TABLE IF EXISTS category;
CREATE TABLE category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    parent_id INT,
    CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
    REFERENCES category (id)
) engine=innodb;

DROP TABLE IF EXISTS textitem
CREATE TABLE category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    parent_id INT
) engine=innodb;

Open in new window


Experimental rows table 1:

INSERT INTO category VALUES
(19, 'category1', NULL),
(20, 'category2', 19),
(21, 'category3', 20),
(22, 'category4', 21),
(23, 'categoryA', 19),
(24, 'categoryB', 23),
(25, 'categoryC', 23),
(26, 'categoryD', 24);

Open in new window


Experimental rows table 2:

INSERT INTO textitem VALUES
(1, 'mytext is mytext', 21);

Open in new window


Recursive Stored procedure:

DROP PROCEDURE IF EXISTS getpath;
DELIMITER $$
CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
BEGIN
    DECLARE catname VARCHAR(20);
    DECLARE temppath TEXT;
    DECLARE tempparent INT;
    SET max_sp_recursion_depth = 255;
    SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent;
    IF tempparent IS NULL
    THEN
        SET path = catname;
    ELSE
        CALL getpath(tempparent, temppath);
        SET path = CONCAT(temppath, '/', catname);
    END IF;
END$$
DELIMITER ;

Open in new window


Wrapper function for the stored procedure:

DROP FUNCTION IF EXISTS getpath;
DELIMITER $$
CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
    DECLARE res TEXT;
    CALL getpath(cat_id, res);
    RETURN res;
END$$
DELIMITER ;

Open in new window


Select example:

SELECT id, name, getpath(id) AS path FROM category;

Open in new window


Output:

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 19 | category1 | category1                               |
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
| 23 | categoryA | category1/categoryA                     |
| 24 | categoryB | category1/categoryA/categoryB           |
| 25 | categoryC | category1/categoryA/categoryC           |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |
+----+-----------+-----------------------------------------+

Open in new window


how to expand the SQL to get the text from textitem with last ID?

+----+-----------+-----------------------------------------+--------------------------------+
| id | name      | path                                    | textiten.name                  |       
+----+-----------+-----------------------------------------+--------------------------------+
| 19 | category1 | category1                               |                                |
| 20 | category2 | category1/category2                     |                                |
| 21 | category3 | category1/category2/category3           |                                |
| 22 | category4 | category1/category2/category3/category4 |mytext is mytext                |
| 23 | categoryA | category1/categoryA                     |                                |
| 24 | categoryB | category1/categoryA/categoryB           |                                |
| 25 | categoryC | category1/categoryA/categoryC           |                                |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |                                |
+----+-----------+-----------------------------------------+--------------------------------+

Open in new window

0
Say, I have a table named "articles", with only 2 fields:

ID, Title

I want to sort by posted date, but have that come from a local PHP variable, for example:

$posted_date = '2017-01-01'

Can I somehow do something like,

SELECT * FROM articles ORDER BY $posted_date ASC

Is it possible? Or maybe you have a better solution?

I run into this problem a lot. Where the client wants to sort by a certain field that is not a column in the table. Sometimes it's a column in another table -- in which case I would do a JOIN... but this time it's stored as a serialized value in one of the table fields, so hopefully you see why I'm asking to use php variable.

I'm trying to avoid creating the "posted_by" field because it will cause me to do a lot of reprogramming and database repair. But I guess that is my worst-case fix.

Thanks for your help.

Tony
0
I have a website I am working which is hosted on GoDaddy.

The website enables users to input information into a HTML form and attach a document (can be a pdf or image) - part of the form is a drop down which pulls the options from one of the database tables.

I am trying to upload the form data to two different tables and upload the document/image to a folder on godaddy and store the file location in the mysql database.

I have set up a piece of code which uploads to one of the tables however seem to be having issues with both the upload of the file/image and uploading parts of the data to another table.

Also I would like the user to be navigated to another page and a message showing whether or not the upload was successful.

I have been using mysqli for the website so far.

Any help would be much appreciated.
0
hi,

Now I am getting ready to install MariaDB (fork of MySQL ) Galera Cluster, what I want to install is the latest edition from Redhat linux repository.

from: https://mariadb.com/kb/en/mariadb/what-is-mariadb-galera-cluster/

the most stable one should be MariaDB 10.2.7 , is that right?

I'd like to list down the steps I will take and please suggest if there are anything missing for version 10.2.7 :

from :https://mariadb.com/kb/en/mariadb/yum/#installing-mariadb-galera-cluster-with-yum , it said:

"Galera Cluster is included in the default MariaDB packages from 10.1, so the instructions in this section are only required for MariaDB 10.0 and MariaDB 5.5."

so I just need to do:

1) sudo yum install MariaDB-server MariaDB-client

this one should not need :
sudo yum install MariaDB-Galera-server MariaDB-client galera

as this is for MariaDB 10.0.x or below, right?

2) when do I need this mariaDB.repo ? when the first time I install MariaDB package ? before  step 1 )  above ?

"# MariaDB 10.2 RedHat repository list - created 2017-08-07 04:53 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/rhel7-ppc64le
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1"

3)
after installation done by step 1) , do this to start the MariaDB cluster DB:

sudo systemctl start mariadb

4) after that, do I need to Importing the MariaDB Signing Key ?

sudo rpm --import …
0

MySQL Server

46K

Solutions

22K

Contributors

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.