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

We use SQL server 2017 as our main data store RDMS

We have a vehicle tracking app with one massive log table (all positions for vehicles)  and a lot of other small tables (like car number plate, users etc), pretty much every query involves a join to this main log table
We are just getting going, and the main table is over a billion rows and over 1TB with a relatively small number of cars in relation to the growth plans for the next year
I know this is small, but we estimate we will grow between 10x to 100x in the next year, which has brought up some architecture questions for long term.

Currently we use SQL server which runs nicely, we will also be adding redis to address reduce load on DB,
Currently the DB grows by 10GB every day
Considering we maybe growing at least by 100GB a day, over time this puts us into the BIG DATA category, and I need to start looking at some options for scaling
Every record that gets inserted to the log table gets an ID, and we use this ID for logging events, alerts etc for fast performance (this works well at the current sizing)

Currently my biggest headache is servers with disk storage, I can find server options upto 10TB quite easily, but after this options are limited and prices sky rocket.
Pricing is a massive issue, we are not Google/Microsoft and cannot afford huge server costs.
Performance is also a huge issue, for example, people expect to run reports and expect very small loading times.
We were also planning to move …
0
OWASP Proactive Controls
LVL 13
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

I am trying to search the database column called "equipment" for multiple table models using sequelize. However, I am not receiving any data even though the fields are full. I am attempting to use the "Promise".  

However, if I simply call the model ( //boilerHouse.findAll({ where: { equipment_type: { [Op.in]: [req.params.name] } } }).then((data) => { ), I can search one table but not all.

router.get("/equipmentInfo/:name", (req, res) =>

 Promise.all([boilerHouse.findAll({ where: { equipment_area: { [Op.in]: [req.params.name] } } }), compressorHouse.findAll({ where: { equipment_area: { [Op.in]: [req.params.name] } } }), engineHouse33.findAll({ where: { equipment_area: { [Op.in]: [req.params.name] } } }), fireHouse.findAll({ where: { equipment_area: { [Op.in]: [req.params.name] } } }), hfoSeparator.findAll({ where: { equipment_area: { [Op.in]: [req.params.name] } } }), reverseOsmosis.findAll({ where: { equipment_area: { [Op.in]: [req.params.name] } } }), tankFarm33.findAll({ where: { equipment_area: { [Op.in]: [req.params.name] } } }) ])
.then((data) => {

  //boilerHouse.findAll({ where: { equipment_type: { [Op.in]: [req.params.name] } } }).then((data) => {
   
    res.render('gigs', {
      gigs:data,
         
     //data[0] is response from tableA find
     // data[1] is from tableB
      })   
    }).catch(err => console.log(err)));
    

Open in new window

0
Hi Experts,
I would like to extract the pay_interval field value from the JSON array into MYSQL syntax :
[
  {
    "111_id": 238777,
    "222_no": "I-86605",
    "333_item": [
      {
        "444": "Contract Rate",
        "555": "Hours",
        "666": "210.00",
        "777": "12.00",
        "888": "17.50",
        "999": "42.00",
        "2222": "252.00",
        "3456": "1234invoice_no": "INV-1",
        "54345": "90"
      }
    ],
    "pay_interval": 1,
    "888": "Unpaid",
    "t999": 210,
    "000_id": 29669,
    "234_id": 4393
  }
]

Open in new window

Any help would be appreciated
Thanks
0
hi,

anyone use MySQL workbench to migrate data from Oracle 12c to MySQL 8.0.12 ?

it seems migration wizard can't read the oracle schema even connection test is ok, any idea?

when loading schema from Oraclewhen selecting schema
0
hi,

right now I tried to do a logical backup from MariaDB 10.4.7 to MySQL 8.0.17 and I use mySQLdump:

Mysqldump  --databases <my database>  --single-transaction --user=root --password > <my database>.sql

Open in new window


dump is ok but I keep finding table schema dtail is missing, like constraint is not in the sql,  view, trigger and function also missing, how can I use mysqldump to dump all logical information to the output so that the script will create everything of that database for me?
0
To enable binlog in percona cluster we need to add variables to my.cnf, where we need to add server-id and log-bin

As per percona documentation they recommend PXC nodes share the same server-id.

Does this mean if we have 3 nodes in the cluster should we keep as server-id=1 for all three nodes?
0
   $insertEve1 ="insert into productionplan_night(line,lineName,prdline)values('$lineID','".$_POST['lineName1']."','".$_POST['dailyEPlnName1']."')";
    mysqli_query($con, $insertEve1);
   $insertEve2 ="insert into productionplan_night(line,lineName,prdline)values('$lineID','".$_POST['lineName2']."','".$_POST['dailyEPlnName2']."')";
    mysqli_query($con, $insertEve2);

Open in new window

Sample Output is:

line lineName prdline
A       ONE           AM-1
A       ONE           AM-1
B       TWO          AM-2
B       TWO          AM-2

If this query execution into twice times insertion of $insertEve1 and $insertEve2; This issues happen only in Chrome browser;
In firebox execution of only one time insertion; why it happen chrome browser...
0
Hello,

I posted this before and got a good answer, which ended up not being the answer I needed, so i am re-posting.. it was suggested to use phpcs + phpcbf but they will not upgrade your mysql to mysqli, it will only fix minor syntax and spacing issues..

I am looking to upgrade a website. My main goal is to remove all mysql connections and change them to mysqli. I have 100s of pages and 1000s of queries. Many of my queries look like so. Is there any easy ways to find and replace large amounts of queries at once?

$query_Recordset_getclientrep = "SELECT * FROM client_rep WHERE client_rep_id = '$the_client_rep'";
$Recordset_getclientrep = mysql_query($query_Recordset_getclientrep, $mine) or die(mysql_error());
$row_Recordset_getclientrep = mysql_fetch_assoc($Recordset_getclientrep);
$totalRows_Recordset_getclientrep = mysql_num_rows($Recordset_getclientrep); 

Select all
 
Open in new window

Open in new window


(I know it looks like old dreamweaver code, but it isn't, I just got comfortable with the format and used it long after I stopped using dreamweaver).

If there any ways I can do massive search and replaces to help save time.

I also have this code, any way to swap this out easy as removing it with breaking 100s of queries

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue)

Open in new window

0
Hello all!

I was wondering if someone could show me how to display my mysql query grouped by "nodeid"?  For example, I have some code (attached here) that pulls data from my database and shows me "nodes" that have "alerted" in the past 24 hours (pic attached).  What I'd like to do is display a separate table for each node and show each nodes alerts sorted by datetime.  I hope that makes sense.

Example:
TABLE 1
     Node 1
          All alerts from node 1.

TABLE 2
     Node 2
          All alerts from node 2.

Etc...

I'm hoping, since I'm still learning my coding, if you could show a working example.

Thanks all!
Eric :)



Current code output (Everything grouped together)


<?php

$conn=mysqli_connect("localhost","REMOVED","REMOVED","REMOVED");
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$sql = "SELECT config.nodeid, config.loc_city, config.loc_state, config.latitude, config.longitude, event_data.compass, event_data.heading, event_data.pressure, event_data.datetime, event_data.event_type FROM config INNER JOIN event_data ON config.nodeid = event_data.nodeid AND event_data.event_type = 'alertStart' AND event_data.datetime > DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY `config`.`nodeid` ASC;";

$result = mysqli_query($conn, $sql);
$num_rows = mysqli_num_rows($result);

    $header ="From: REMOVED@gmail.com" . "\r\n";
    $email    = 'REMOVED@mac.com';
    $header .= "MIME-Version: 1.0\r\n";
    

Open in new window

0
Alrighty, I've done a ton of googling but I need some thoughts/ideas.  I'm pulling records out of my database, these records do include a latitude column, longitude column, and a datetime column.  I need to somehow convert that datetime string, that's in UTC, to the local time based on the lat/long.  The idea would to add another column such as Local Time to this email.  I've included the code I'm using as a reference.  The code does work (I just double checked it).  I have a lot to learn and I'm not an expert so please excuse my code hacking.

Thank you in advance!
Eric :)

<?php

$conn=mysqli_connect("localhost","REMOVED","REMOVED","REMOVED");
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$sql = "SELECT config.nodeid, config.loc_city, config.loc_state, config.latitude, config.longitude, event_data.compass, event_data.heading, event_data.pressure, event_data.datetime, event_data.event_type FROM config INNER JOIN event_data ON config.nodeid = event_data.nodeid AND event_data.event_type = 'alertStart' AND event_data.datetime > DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY `config`.`nodeid` ASC;";

$result = mysqli_query($conn, $sql);
$num_rows = mysqli_num_rows($result);

    $header ="From: example@example.com" . "\r\n";
    $email    = 'example@example.com';
    $header .= "MIME-Version: 1.0\r\n";
    $header .= "Content-Type: text/html; charset=ISO-8859-1\r\n";
    $subject  = 'ALERT: ' . $num_rows . ' Nodes 

Open in new window

0
Rowby Goren Makes an Impact on Screen and Online
LVL 13
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

hi,

any detail procedure on how to upgrade MySQL innodB cluster of 3 nodes with 1 x management node which has MySQL router installed to latest InnoDB cluster ?

MySQL InnodB cluster do not have version 8 yet, right? but standalone version only ?
0
I want to group by invoice number and sum the cost for the attached MYSQL query and resulting report but when I add group by `INV`.`INVOICE_NUMBER`  I get no records found when I try to Select * from (complex query) group by `INV`.`INVOICE_NUMBER`

report
complexquery.txt
0
We are looking to bring on a consultant who can work with us on creating our database so that it will be scalable for anywhere from 10 simultaneous users to 100,000 and beyond. Our basic approach is that we want to create a round-robin paradigm for all of our read-only access, and then have one master server be the recipient of all the writes. We are going to be rolling out a website and we don't know how popular it's going to be, but obviously we want to have the thing structured correctly at the beginning so that we aren't re-factoring midstream.

Current db is MySQL. We want to stay with that not go with something else like PostGres.
0
I'm trying to join this two tables:

select first_name, last_name, order_date, order_amount
from customers c
inner join orders o
on c.customer_id = o.customer_id
SQL Error [1142] [42000]: SELECT command denied to user my_user or table 'customers'

Open in new window


But if I try:

select *  
from customers

Open in new window


I works just fine
0
I'm trying to run a query to remove duplicate data from the table IMG_TSK and there is no ID or unique id column.
the parameters for deletion would be per D_ID, i.e. *if* there are any duplicate T_ID entries [in each D_ID dataset], then the duplicate entries would be deleted, leaving the first such occurrence only.
It would also 're-order' the items, instead of leaving 'out of order' or skip a number in the sequence.
for example:

in this data set:

D_ID    T_ID   ORDER
"196"      "96"              "1"
"196"      "195"      "2"
"196"      "98"       "3"
"196"      "181"      "4"
"196"      "137"      "5"
"196"      "194"      "6"
"196"      "167"      "7"
"196"      "132"      "8"
"196"      "126"      "9"
"196"      "189"      "10"
"196"      "131"      "11"
"196"      "130"      "12"
"196"      "125"      "13"
"196"      "193"      "14"
"196"      "110"      "15"
"196"      "154"      "16"
"196"      "165"      "17"
"196"      "186"      "18"
"196"      "198"      "19"
"196"      "198"      "20"
"196"      "127"      "21"
"196"      "192"      "22"
"196"      "198"      "23"
"196"      "98"       "24"

I want to scrub duplicates so it returns:

D_ID    T_ID   ORDER
"196"      "96"       "1"
"196"      "195"      "2"
"196"      "98"              "3"
"196"      "181"      "4"
"196"      "137"      "5"
"196"      "194"      "6"
"196"      "167"      "7"
"196"      "132"      "8"
"196"      "126"      "9"
"196"      "189"      "10"
"196"      "131"      "11"
"196"      "130"      "12"
"196"      "125"      "13"
"196"      "193"      "14"
"196"      "110"      "15"
"196"      "154"      "16"
"196"      "165"      "17"
"196"      "186"      "18"
"196"      "198"      "19"
"196"      "127"      "20"
"196"      "192"      "21"

not:

D_ID    T_ID   ORDER
"196"      "96"              "1"
"196"      "195"      "2"
"196"      "98"       "3"
"196"      "181"      "4"
"196"      "137"      "5"
"196"      "194"      "6"
0
Hi;

How can I improve performance issue ? I created below stored procedure but if I check costly stored procedure by using sql query to detect them. I realized that below query needs improvement to fix performance issue.Can you help me please?

  

CREATE PROCEDURE [WMS].[GetSalesOrderSearch]  
 /*  
  EXEC WMS.[GetSalesOrderSearch] @CustomerId=1003,@PageNumber=1,@PageSize=1000,@SearchText='copy230',@SortDescending=0  
  */  
 (  
 @CustomerId INT  
 ,@PageNumber INT  
 ,@PageSize INT  
 ,@SearchText NVARCHAR(MAX) = NULL  
 ,@SortColumn NVARCHAR(255) = 'Id'  
 ,@SortDescending BIT = 0  
 ,@FilterListXml XML = NULL  
 ,@RowCount INT = NULL OUTPUT  
 )  
AS  
BEGIN  
 DECLARE @Query NVARCHAR(MAX) = '';  
 DECLARE @WhereClause NVARCHAR(MAX) = '';  
 DECLARE @SearchJoin NVARCHAR(MAX) = '';  
 DECLARE @FilterQuery NVARCHAR(MAX) = '(SO.CustomerId = @CustomerId AND SO.IsDeleted = 0) ';  
 DROP TABLE  
  
 IF EXISTS #FilterList  
  SELECT CAST(ROW_NUMBER() OVER (  
     ORDER BY n.value('(./AttributeId/text())[1]', 'INT')  
     ) AS VARCHAR(50)) Row  
   ,n.value('(./AttributeId/text())[1]', 'INT') AttributeId  
   ,n.value('(./PropertyName/text())[1]', 'VARCHAR(100)') PropertyName  
   ,n.value('(./AttributeType/text())[1]', 'VARCHAR(100)') AttributeType  
   ,n.value('(./AttributeEntityType/text())[1]', 'VARCHAR(100)') AttributeEntityType  
   ,n.value('(./BeginDate/text())[1]', 'DATETIME') BeginDate  
   ,n.value('(./BeginNumber/text())[1]', 'FLOAT') BeginNumber  
   

Open in new window

0
hi,

I am reading

https://mariadb.com/kb/en/mariadb-maxscale-23-readwritesplit/#transaction_replay
https://mariadb.com/kb/en/mariadb-maxscale-23-readwritesplit/#master_reconnection
https://jira.mariadb.org/browse/MXS-2139

how to turn on transaction_replay and  master_failure_mode=fail_on_write in maxscale so that when DML transaction is executing, failover is started, the executing transaction is not interrupted?


when I add this:

[readwriteservice]
transaction_replay=true
master_failure_mode=fail_on_write

maxscale can't start up any more and remove it can !

this is the error:

ob for maxscale.service failed because the control process exited with error code. See "systemctl status maxscale.service" and "journalctl -xe" for details

Open in new window


the log said:

Unknown module type for object 'readwriteservice':
0
I'm having a newby problem with MySQL.  Neither condition of IF statement seem to be hitting or executing.  I certainly don't get a Select printed.  Actually nothing past the SELECT DOIT prints.  I'm a MS SQL Server guy and this stuff is driving me nuts.  Any kind suggestions?  Thanks.
myexample.sql
0
Howcome i am getting this error when trying to reverse engineer my schema with mysql workbench?

I cant find this table anywhere in my database -- informatin_schema.triggers -- its not a permission issue because ive tried with root.

I have to use FreeTDS driver and not native mssql driver because it wasnt even loading the schemas for me. this driver worked but im not sure if its the driver or this mysterious table?


ERROR: Reverse engineer selected schemas: ProgrammingError("('42S02', "[42S02] [FreeTDS][SQL Server]Invalid object name 'INFORMATION_SCHEMA.TRIGGERS'. (208) (SQLExecDirectW)")"): error calling Python module function DbSql92RE.reverseEngineer
Failed

Open in new window

0
Announcing the Winners!
LVL 13
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Migrating from MSSQL to mysql using migration wizard (specifically from MSSQL Azure hosted DB).

WHen i connect with mysql workbench migration wizard, the schema is blank, and it cant find the related database and or tables to be imported to mysql.

 I used an alternative software called Data Loader -- https://www.dbload.com/
and it worked perfectly, found the schema on my first try.

I have to purchase this software -- 99 bucks, but i would prefer not too. Does anyone know why MySQL doesnt work finding the schema but this software that looks like its from 1990s works?

If the data loader app works it cant be permissions or user account because im using all the same.
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,

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

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.