MySQL Server

47K

Solutions

23K

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

hi,

I am planning a architecture and that includes a cross site and HA design with MariaDB.

I will use GALERA ARBITRATOR + maxscale + mariaDB, how it fix into the diagram below.

MariaDB HA and DR
0
Exploring SQL Server 2016: Fundamentals
LVL 13
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

How to connect ManageEngine ServiceDesk PLus database to Access?
I tried creating an ODBC but I get an error
EE1.JPGAppreciate if anyone can help me connect/link SDP tables
0
I am thinking of implementing HA solution for MySQL. I found that InnoDB Clustering is one of the option but require 3 DB nodes. What is the advantage of InnoDB Clustering compare with other solution which require just 2 DB nodes such as MHH ?
0
I am using MySQL and require to have High Availability (HA) solution. I found there is a solution call InnoDB cluster but it require 3 DB nodes. Is there any other HA solution which just require 2 DB nodes ? as I know, MS SQL is able to setup this with 2 DB nodes
0
I had this question after viewing Kannel compilation.
am having issue on this step about mysql
 
trying install kannel 1.5.0 in centos 7   kannel at the end installed but due to mysql issue then i cant install sqlbox

[root@send-sms gateway-1.5.0]# ./configure --prefix=/usr/local/kannel  --with-mysql-dir=/usr/bin/mysql/ --enable-debug --enable-assertions --with-defaults=speed --disable-localtime --enable-start-stop-daemon --enable-pam

Configuring for Kannel gateway version 1.5.0 ...

Running system checks ...
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... yes
checking whether we are cross compiling... no
checking for suffix of executables...
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for gcc option to accept ISO C99... -std=gnu99
checking for a BSD-compatible install... /usr/bin/install -c
checking for ranlib... ranlib
checking for bison... bison -y
checking for flex... flex
checking lex output file root... lex.yy
checking lex library... none needed
checking whether yytext is a pointer... no
checking for ar... ar
checking for convert... 

Open in new window

0
hi,

any procedure to upgrade MariaDB and maxscale ?

do you know if latest edition of mariaDB and maxscale can do transaction level failover so that when DB is failover and slave DB becoming primary, executing queries is not going to drop off ?
0
hi,

how to configure connection string for read-only round robin scale out operation for MariaDB ?


tks.
0
hi,

I read this :https://www.red-gate.com/simple-talk/cloud/cloud-data/designing-highly-scalable-database-architectures/

Other than MS SQL always on which is the MS version of Horizontal scale out solution, how can I implement shading with SQL always on read only routing ?

do any you all disagree that shading is the horizontal scale out for write operation?
0
Hi All,

Are there any major caveats to using .net entity framework with mysql? Ive found myself in an interesting scenario working on a project that uses .netframework+mssql as backend/api for a cross platform mobile app (native andoird and native ios).

App is hosted on azure hosted sql server but DB costs are getting very high. We received an offer to move the DB to another cloud service with generous credits, but of course they only have hosted mysql (or we would need to use VMs, licensing for mssql alone would eat benefits).

Our entire app runs super sleek everything through visual studios (no stored procs or views or triggers on db etc), so i was thinking we can change the VS db driver to MySQL and run our migrations on the new cloud service to recreate the database on mysql... then we can migrate the data over as well.

I eventually would like to convert the app to .netcore as well but dont think we will be able to do that right now, immediate concern is reducing the DB costs.

Is this a crazy idea or something that is viable?
0
So unfortunetly my data is not updating even though I get a status request of 200 ok below are my front end and back end code
and below I have a screen shot and my front end and back end code I do not know why it is not updating in the front end when I do it on postman it does update and the querry works

data-not-updating.png
app.put('/movielist/updateMovie/:id',(req,res) =>{
  let update = req.body;
  mysqlConnection.query("UPDATE movielist SET name = '', thumnail_path = ?, description = ?, year_released = ?, language_released = ? WHERE idmovielist = ?",
 [update.name,update.thumnail_path,update.description,update.year_released,update.language_released,req.params.id],function (err, results) {
    if (!err) {
      res.send("Movie list is updated");
    } else {
      console.log(err);
    }
  });
});

$(function() {
  //Fill the list when the page loaded
  renderMovieList('movies');

  $("#movieAdded").click(function(a) {
    a.preventDefault();

    let mydata = {
      idmovielist: $($("#newForm")[0].intNum).val(),
      name: $($("#newForm")[0].name).val(),
      thumnail_path: $($("#newForm")[0].thumnail_path).val(),
      description: $($("#newForm")[0].description).val(),
      year_released: $($("#newForm")[0].year_released).val(),
      language_released: $($("#newForm")[0].language_released).val(),
    }

    displayMovie(mydata);

    $("#newForm").trigger("reset");
    $("#newForm").toggle();
  });

  $("#updateMovie").on("click", function(a) {
    

Open in new window

0
OWASP: Threats Fundamentals
LVL 13
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

I need a sql command which shows me all active transactions
0
Magento 2
/app/code/<owner>/<module>/view/adminhtml/ui_component/<module>_index_listing.xml
How do I retrieve a string from a string from table\column linked to the main table?

i.e. I am able to retrieve data from table1. but table1 is linked to table2 using entity_id. I need app_status from table2 using table1.
0
select
	DATE(quotes.create_date) as create_date,
	quotes.year,
    quotes.make,
    quotes.model,
    quotes.type,
    quotes.city_org,
    quotes.prov_org,
    quotes.city_dest,
    quotes.prov_dest,
	quotes.quote_amount,
	quotes.origin_shipping_type,
    quotes.dest_shipping_type,
    quotes.order_note,
    notes.note_text
from notes
join quotes on notes.quote_no=quotes.quote_no
where 
(
	quotes.quote_type = "EN"
	OR notes.note_text like "%non runner%"
	OR notes.note_text like "%nr%"
    )
AND notes.note_text like "(by:%"
AND notes.note_text not like "(by: Roger) T%"
AND notes.note_text not like "(by: Doug) T%"
AND notes.note_text not like "(by: Larry) T%"
AND notes.note_text not like "%Quote Source%"
AND DATE(quotes.create_date) >= '2017-01-01'

Open in new window


How can I concatinate the note_text so there is only a single row with concatinated notes instead of duplicate rows with different note text?

Sample output attached, I'm trying to get the notes (last column in attached image)  concatinated and only 1 row returned instead of 2 and the notes column should show "(by: Steve) non runner, (by: Steve) 3300" all in the same string.

I tried using concat and group_concate and it didn't work so well... need help
sample.JPG
0
I've recovery my windows OS then i found mysql folder in Windows.old folder.  and MySQL unistalled or windows restored to previous version before MySQL Installed.
I try to make Solution as follows:
1- I've used New PC with Mysql .
2- stop Mysql Services.
3- replace Data folder.
4- When try to start MySQL again error 1067 is appear.

how can i recovery data from ibdata1 file and *.frm
0
I have a MySql question that is out of my league. I have a self reference hierarchical table. Every row is a node in the tree. The parent column points at the id of its parent, the master.

TABLE MyTable1
  id int(11) NOT NULL AUTO_INCREMENT,
  parent int(20) DEFAULT NULL,
  aData varchar(50) DEFAULT NULL

I also have a secondary table:

TABLE MyTable2
  id int(11) NOT NULL AUTO_INCREMENT,
  id_table1 int(20) DEFAULT NULL,
  aData varchar(50) DEFAULT NULL

The id_table1 points at the id column in table1 it belongs to.


Now i ned to make two select that from any node (identified by id column in MyTable1)

(1) get all of its branches (or children).
(2) make another select that gives me all the rows of the MyTable2 that is owned by any of the just selected rows in MyTable1.

Regards, Paer.
0
I have two tables in a MySQL database with almost identical structure.

I want to copy the "from" table to the "to table" without having to mention every column by name.

The "to table", purposely, has one more column at the end that is auto increment.

Is there a way to "copy corresponding", so to speak? Either in phpMysql or in php code.

Thanks
0
In mysql database i created "leave" table:

CREATE TABLE `leave` ( 
`ID_LEAVE` int(11) NOT NULL,
`ID_WORKER` int(11) NOT NULL,
`BEGIN_DATE` datetime DEFAULT NULL,
`END_DATE` datetime DEFAULT NULL
)

INSERT INTO `leave` 
(`ID_LEAVE`, `ID_WORKER`, `BEGIN_DATE`, `END_DATE`) VALUES
(3, 26, '2019-03-20 07:00:00', '2019-03-21 15:00:00'),
(4, 22, '2019-03-20 07:00:00', '2019-03-20 15:00:00');

Open in new window


"Workers" table:

CREATE TABLE `workers` (
`ID_WORKER` int(11) NOT NULL,
`FNAME` varchar(20) NOT NULL,
`LNAME` varchar(20) NOT NULL
)

INSERT INTO `workers` (`ID_WORKER`, `FNAME`, `LNAME`) VALUES
(22, 'ALAN', 'FAST'),
(23, 'LEON', 'SPEED'),
(24, 'ADAM', 'GREEN'),
(25, 'DAVID', 'BUCS'),
(26, 'JACK', 'FAR'),
(27, 'ADAM', 'GAX'),
(28, 'ANDREW', 'WORM');

Open in new window


"Orders" table:

CREATE TABLE `orders` (
`ID_ORDER` int(11) NOT NULL,
`DESC_ORDER` varchar(50) NOT NULL,
`NUMBER_ORDER` varchar(30) NOT NULL
)

INSERT INTO `orders` (`ID_ORDER`, `DESC_ORDER`, `NUMBER_ORDER`) VALUES
(19, 'TEST', 'TEST'),
(20, 'TEST2', 'TEST2'),
(21, 'TEST3', 'TEST3'),
(22, 'TEST4', 'TEST4');

Open in new window


"Order_status" table (I'm sorry for that's not in order):

CREATE TABLE `order_status` (
`ID_STATUS` int(11) NOT NULL,
`ID_ORDER` int(11) NOT NULL,
`ID_WORKER` int(11) NOT NULL,
`BEGIN_DATE` datetime NOT NULL,
`END_DATE` datetime NOT NULL,
`ORDER_DONE` tinyint(1) DEFAULT NULL
) 

INSERT INTO `order_status` (`ID_STATUS`, `ID_ORDER`, `ID_WORKER`, `BEGIN_DATE`, `END_DATE`, `ORDER_DONE`) 

Open in new window

0
Environment:
New laptop:  Win 10
Old laptop:  MacOS High Sierra + Bootcamp (Win 10) on dead MacBook Pro

How to transfer entire XAMPP directory from a Bootcamp partition of a Mac to a Windows PC?

I can attach old laptop's hard drive to the new Windows PC via a USB3 cable and read the bootcamp partition on it (using HFS+ installed on the new laptop).

If I try to simply copy the XAMPP directory over, the databases are partly useable by Joomla on the Windows PC but I'm unable to log in as administrator on the sites which were in htdocs on the bootcamp xampp install (I'm guessing there are a number of issues with a direct copy approach such as path references in the xampp/php files on the bootcamp xampp which are invalid once they're on the Windows PC).

I can no longer bootup the mac (motherboard fried) to access windows on it in bootcamp and export the databases (which would be the normal way to transfer a db from one xampp install to another).

I've tried installing virtualization software on the new laptop but so far haven't been able to create a bootable MacOS in VMWare or Virtual Box.

There should be a way for me to create a fresh xampp install on the new windows laptop and to copy over just the database and tables I need from the old laptop's bootcamp xampp directory but I'm not clear on which files to copy over.  Presumably the Joomla sites in directories in htdocs could all be copied over without an issue (guessing) but the databases which those …
0
I am using the following code to create league tables which works great.
select 
    team, 
    count(*) played, 
    count(case when Home > Away then 1 end) wins, 
    count(case when Away> Home then 1 end) lost, 
    count(case when Home = Away then 1 end) draws, 
    sum(Home) Home, 
    sum(Away) Away, 
    sum(Home) - sum(Away) goal_diff,
    sum(
          case when Home > Away then 3 else 0 end 
        + case when Home = Away then 1 else 0 end
    ) score 
from (
    select hometeam team, Home, Away from footballtest  INNER JOIN Leagues ON CASE WHEN LEFT(footballtest.code, 4) LIKE '0%'  THEN right(LEFT(footballtest.code, 4), LENGTH(LEFT(footballtest.code, 4))-1) ELSE LEFT(footballtest.code, 4)END = Leagues.code where Leagues.Code = '102' and   footballtest.season = '19' and thedate < CURDATE() 
  union all 
    select awayteam, Away, Home from footballtest  INNER JOIN Leagues ON CASE WHEN LEFT(footballtest.code, 4) LIKE '0%'  THEN right(LEFT(footballtest.code, 4), LENGTH(LEFT(footballtest.code, 4))-1) ELSE LEFT(footballtest.code, 4)END = Leagues.code where Leagues.Code = '102' and   footballtest.season = '19' and thedate < CURDATE() 
) a
group by team
order by score desc, goal_diff

Open in new window


What I would like to do is only pull each teams last 5 Games and build the table from that to show current form.I am not sure how I could use Limit 5 here?

I am using MYSQL version 5.8 (With a host).

The code that would need changing is the following:

select hometeam team, Home, Away from footballtest  INNER JOIN Leagues ON CASE WHEN LEFT(footballtest.code, 4) LIKE '0%'  THEN right(LEFT(footballtest.code, 4), LENGTH(LEFT(footballtest.code, 4))-1) ELSE LEFT(footballtest.code, 4)END = Leagues.code where Leagues.Code = '102' and   footballtest.season = '19' and thedate < CURDATE()

Open in new window



As this currently pulls all results from footballtest.season = '19' for each team when I need the last 5 games played by each team in the season.

I have tried the following code but I get the error "Syntax error at WITH table_a"


Open in new window

0
Become a Microsoft Certified Solutions Expert
LVL 13
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

hi,

I am studying MariaDB trigger:

this is  a trigger:

CREATE DEFINER=`root`@`%` TRIGGER `HR`.`UPDATE_JOB_HISTORY` AFTER UPDATE ON HR.EMPLOYEES FOR EACH ROW 
Begin 
   IF (NEW.job_id <> OLD.job_id) AND(NEW.department_id <> OLD.department_id) then 
      CALL HR.ADD_JOB_HISTORY(OLD.EMPLOYEE_ID,OLD.HIRE_DATE,CURRENT_TIMESTAMP,OLD.JOB_ID,OLD.DEPARTMENT_ID); 
   end if; 
end; 

Open in new window


it will call a SP called ADD_JOB_HISTORY and what this SP does is:

DROP PROCEDURE IF EXISTS HR.ADD_JOB_HISTORY; 
CREATE PROCEDURE HR.`ADD_JOB_HISTORY`(P_EMP_ID          MEDIUMINT 
   , P_START_DATE      DATETIME 
   , P_END_DATE        DATETIME 
   , P_JOB_ID          VARCHAR(10) 
   , P_DEPARTMENT_ID   SMALLINT) 
BEGIN 


   INSERT INTO JOB_HISTORY(EMPLOYEE_ID, START_DATE, END_DATE, 
                           JOB_ID, DEPARTMENT_ID) 
    VALUES(P_EMP_ID, P_START_DATE, P_END_DATE, P_JOB_ID, P_DEPARTMENT_ID); 
END; 

Open in new window


when when I insert a record in EMPLOYEES  tables nothing created in JOB_HISTORY table, what is going on ?
0
I'm trying to convert the following query that worked in MySQL 5.5 to MySQL 8.0. I am getting the following error: "Error Code: 1109. Unknown table 'GLOBAL_STATUS' in information_schema"

If this table no longer exists in version 8.0, is there another way to get the slave status?

DELIMITER ;;
drop procedure if exists aspDoCustDbCheck;;
CREATE  PROCEDURE `aspDoCustDbCheck`()
begin
 declare vSlaveSts varchar(12);
 
 SELECT variable_value into vSlaveSts
 FROM information_schema.global_status 
 WHERE variable_name='SLAVE_RUNNING';
 
 Select vSlaveSts as SlaveSts
  ,Now() as Time
 ; 
end ;;
DELIMITER ;

call aspDoCustDbCheck();

Open in new window

0
hi,

As I am testing Oracle to MariaDB converison and when I migrate to MariaDB some table may fail to create when there are foreign key prevent me from creating tables .

from your point of view, how can I check quickly which MySQL constraint I have to remove before dropping that table and recreate again .

it is a very time consuming task if I have to check tables by tables .. ( 3xxx tables!)

or I should look at Oracle constraint instead to give hints  ?
0
hi,

as I am tried to migrate from Oracle to MariaDB/MySQL and we are try to tools Ispirer, once the tools convert the following object for us how can we verify the converted objects can works without any problem:

1) sequence,

by running Select NEXTVAL(<sequence name>); ?

what should be the output?

2) Function.

3) trigger

4) package,

5) synonyms.

any idea?
0
I had my 10.2.15-MariaDB server crashed after a select count(*) from a 18.6 Gb table.

 It's not a HeidiSQL error because it also happens if i do it using MariaDB's command prompt.

After this crash, i can't do a select count(*) over 60 seconds that i get the same error again.

It doesn't seem like a configuration issue as this weren't happening before the first crash.

I used to run several hours querys without a problem.

My interactive_timeout variable is set to 28800:

"Variable_name";"Value" "connect_timeout";"36000" "delayed_insert_timeout";"300" "innodb_flush_log_at_timeout";"1" "innodb_lock_wait_timeout";"50" "innodb_rollback_on_timeout";"OFF" "interactive_timeout";"28800" "lock_wait_timeout";"86400" "net_read_timeout";"30" "net_write_timeout";"60" "slave_net_timeout";"60" "thread_pool_idle_timeout";"60" "wait_timeout";"28800"

I still have 62 Gb free in my hard drive (SSD) and my PC has 32 Gb. of RAM memory.

Thanks!
0
hello

i'm working on a mariadb cluster with high performance requirements in terms of number of queries.

i can work with read-uncommited. actually, most of the app would not require more than eventual consistency. i'm well aware that app should probably be using cassandra or a similar tool, but moving from mysql is simply not currently an option. mariadb using a cassandra store neither. i'll be probably moving part of the load to memcache/redis for parts that do not need to be in an SQL store. little by little, though.

i've worked quite extensively with mysql tuning in the old days, but not that much in the past 5 to 10 years and i feel i might be quite outdated and additionally working with both an environment and a workload i'm not used to on mysql.

i can obviously add more NDB members but i'd rather switch to maria if possible.

the overall quantity of data is rather small : around 10Go but we expect about *10 growth quite shortly. actually more, but i'll probably manage to optimise many things by then.

--

workload

the current NDB cluster has 2 members

it performs about 10k select queries per second on each member, and about 200 inserts/s. the rest is comparatively neglectible.

most of the select and inserts will be a single row.

there are a few bigger queries, but performance on those queries is not critical as long as running them does not produce contention.

--

hardware and OS requirements. i have no control over most of these.

0

MySQL Server

47K

Solutions

23K

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.