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

I have inherited a program where the date field in a MySQL table is really a VARCHAR field.  The date looks like this string:  1260777185.  I can't change the format of the field because of various reasons.  How can I change/convert this field to a date format in my query?  It needs to be done at the query level.  The field is called timestamp.  

I'm using this query, but the field is blank once the query has been run.

SELECT amount, description, STR_TO_DATE('timestamp', '%m/%d/%Y') AS transdate FROM transactions

Thank you.
0
Get your Conversational Ransomware Defense e‑book
LVL 1
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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
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
How do you block an IP from visiting your site?

Can You block an IP At/with Google so Google does not show your site to an IP?

How do I do this?
0
hi,

now installing the MySQL 5.7.19 on redhat linux 7.2 and I do this:

/bin/mysqld --defaults-file=my.cnf --user=mysql --initialize

and see this:

installation error
any reason why and how to solve it?
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
hi all,

I watch this :https://www.youtube.com/watch?v=JWy7ZLXxtZ4

and I want to know if MySQL InnoDB Cluster and MySQL multi master replication is the same thing?

how about MySQL InnoDB Cluster and MySQL NDB cluster, must not the same, right?
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
Hi Experts,

I have setup DB cluster using Mariadb galera cluster. And I use Maxscale as DB Proxy and config read/write splitting to avoid DEADLOCK.

The problem is when I INSERT/UPDATE & SELECT at the same time. It returns an old value, not new value (As attachment below).

Do I config wrong something, please explain for me and help me to resolve it!

Thanks!!!
0
Get real performance insights from real users
LVL 1
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

I inherited some code.

FROM_UNIXTIME(ARRAY_MAX(tasks.task_status['closed'])) > 
DATE_ADD('MONTH', -6, DATE_ADD('DAY', -MOD(DAY_OF_YEAR(CURRENT_DATE), 365/2), CURRENT_DATE))

Open in new window


A couple things I don't get.

First, on the  right of the > operator it looks like
MOD(DAY_OF_YEAR(CURRENT_DATE), 365/2)

Open in new window

returns days into current half of year.  Correct?

And this
DATE_ADD('DAY', -MOD(DAY_OF_YEAR(CURRENT_DATE), 365/2), CURRENT_DATE)

Open in new window

then subtracts that many days from the current date.  So this would be either January 1 or (apps) July 1.  Correct?

So this
DATE_ADD('MONTH', -6, DATE_ADD('DAY', -MOD(DAY_OF_YEAR(CURRENT_DATE), 365/2), CURRENT_DATE))

Open in new window

would be 6 months before then.  So if today is Aug 10, then this would result in January 1 of the current year.

If I'm wrong please let me know.
In any case, this seems to return a DATE.

Now the left side of the > operator.
A couple questions.
The first, I'm embarrassed, because I can't recall what this does.  Does it return some index, or ???
fieldname['somevalue']

Open in new window


Second, ARRAY_MAX( selected field value(s) ) ¿should? return the highest value found?

Third, FROM_UNIXTIME() returns a DATE.

Therefore tasks.task_status['closed'] must be a UNIX timestamp?

— — —

Thanks!
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, im having a hard time removing these, these files are huge

#sql-df3_13ba706.frm  
#sql-ib500.ibd  

I've tried drop table '#mysql50##sql-df3_13ba706';

and mysql pretty much says I dont know what Im doing...

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''#mysql50##sql-df3_13ba706'' at line 1
0
first of all, i mistakenly typed "sc delete mysql" in the command prompt because my Xampp mysql is not responding.
Later, i uninstall Xampp and reinstall it again and MySQL started working. so i uninstall Mariadb and reinstall it again. While installing Mariadb, it stopped and displayed a message saying "service "MySQL" (MySQL) failed to start. Verify that you have sufficient privileges to start system services." i would like you to help me solve this problem. Thank you.
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
My Access database will not allow me to open a new record (you can't go to specified record). My setup is as such I have an Access front-end with a MYSQL back-end using php MyAdmin as administration tool that's hosted by godaddy. Everything seems to be working fine outside of losing a few lines of vba on some forms for calculations and not being able to open new records. The latter being most important right now.
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,

what is the diff between MysQL active active (multi master) and NDB cluster?

from application client connectivity point of view, which one involve client application modification to make sure that client application can know which is the next DB to write to if the primary one failed ?

any automatically round robin method to find out the next DB to write automatically ?

any docu/URL on how to make change to the client application to take advantage/ make use of this MySQL dB architecture.
0
Get 15 Days FREE Full-Featured Trial
LVL 1
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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
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
hi,

got a project on testing MySQL active active /multi master installation to POC if it works or not working well (e.g. if it is so good everyone use it but not oracle anymore), what version of MySQL can do active active / multi master well ?

Any concern on stability of this of MySQL active active cluster? still based on replication technology and therefore MySQL active active = multi master cluster? I think so ....

Any known issue of MySQL multi master for 2 x site  ?

any workable step by steps procedure to set up  MySQL active active quickly and what is the way to test it ?
0
my table looks like this

dt        ticker         open
1            A                1.2
1            B                 6
2            A                 1.4
2            B                 6.5

I would like it to turn out to be

dt           A            B
1            1.2         1.4
2            6            6.5

I tried using mysql query in below format

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(ticker= ''',
ticker,
''', open, NULL)) AS ',
ticker
)
) INTO @sql
FROM properties;
SET @sql = CONCAT('SELECT dt, ', @sql, ' FROM properties GROUP BY dt');

while executing the query in phpmyadmin I am getting below error .

Warning in .\libraries\dbi\DBIMysqli.php#436
mysqli_fetch_fields() expects parameter 1 to be mysqli_result, boolean given.


      
below are additional error messages :-

 the query is executing ,but looking into rows i am getting errors Backtrace .\libraries\dbi\DBIMysqli.php#436: mysqli_fetch_fields(boolean true) .\libraries\DatabaseInterface.php#2641: PMA\libraries\dbi\DBIMysqli->getFieldsMeta(boolean true) .\libraries\sql.lib.php#1848: PMA\libraries\DatabaseInterface->getFieldsMeta(boolean true) .\libraries\sql.lib.php#2199: PMA_getQueryResponseForResultsReturned( boolean true, array, string 'qc', string 'tbl_main', NULL, NULL,

I am stuck with this , can anyone guide me with the solution
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.