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

I created a User Defined function that uses an ID, in this case StateProvinceID (INT)  to the return the States TAXRATE, STATEPROVINCECODE and COUNTRYREGIONCODE,
having Syntax error at the very start of the query,

Msg 156, Level 15, State 1, Procedure StateProvinceLogin, Line 16 [Batch Start Line 217]
Incorrect syntax near the keyword 'SELECT'.


                        CREATE FUNCTION      StateProvinceLogin
                                                ( @StateProvinceID int )
                        
                        RETURNS TABLE AS RETURN

                                    (      SELECT                  a.TaxRate, b.StateProvinceCode, b.CountryRegionCode
                                          FROM                  AdventureWorks2016.Sales.SalesTaxRate a
                                          INNER JOIN            AdventureWorks2016.Person.StateProvince b
                                          ON                        a.StateProvinceID = b.StateProvinceID
                                          WHERE                  @StateProvinceID = TaxRate AND  @StateProvinceID = StateProvinceCode
                                          AND                        @StateProvinceID = CountryRegionCode
                                     )
                                     -- To Verify

                                    SELECT * FROM StateProvinceLogin('5')
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.

Hello. I create a Mysql database and a table on a web hosting company server. but I do not have an idea, How to verify connection and  bring a record or value from Mysql database. Can someone write a simple php code for me? I have following information. for example, Server IP address- 1.1.1.1, Database name-tempdataserver, Database user ID- tempuser, Password- temppaass.
Primary key column- stock, Primary key example- aapl, Column to display- CLOSE.

I  can connect  use Mysql for Excel and make a new table.
Thank you.
0
Hi

I have a SQL Server table with a varbinary-field in it. I want to insert that record in a MySQL-Database. How can I access this field-data?
myTable is local on a SQL Server
mySqlTable1 is on a MySQL Server in the web

Query1.SQL.Text := 'SELECT myString1, myVarBinary1 FROM myTable';
Query1.Execute;
mySqlQuery1.SQL.Text := 'INSERT INTO mySqlTable1 (str1, blob1) VALUES('+QuotedStr(Query1.FieldByName('myString1').AsString)+','+??myVarBinary1??)'

Open in new window

How can I access the varbinary-field? Something like Query1.FieldByName('myVarBinary1').As???
Thanks for any help
Peter
0
Hi,

I am not very familiar with mysql but I see that the .err files are taking up alot of memory on my server (27 GB worth). What is a safe way to deal with these .err files. Delete truncate, etc. And how would I go about doing it. I have mysql 5.7.

Thank you.
0
I'm having trouble with a MariaDB nested correlated subquery.  I need to reference a returned rowcount as a column value that is dependent on a customer id from the parent query.  The problem I'm having is MariaDB / MySql has a limitation that only allow correlated queries 1 level deep.  The COUNT portion of the following query violates that rule.  I'm guessing this could be accomplished using JOINs instead, but I'm really at a mental roadblock and could use some fresh eyes.  HELP PLEASE!

SELECT t.*,

#This is the count that I need to have returned as a value
(SELECT COUNT(MERGEDVAL)
FROM (SELECT
CONCAT(sos_deliverydate,
               sos_trip_id,
               sos_route_id,
               sos_routestop) AS MERGEDVAL
        FROM   salesordersummary s2
        WHERE  0 = 0
               AND ( sos_deliverydate BETWEEN '2019-08-18' AND '2019-09-14' )
               AND sos_shipvia <> 'SLS DEL'
               AND ( sos_shipvia <> 'T2'
                      OR Dayofweek(sos_deliverydate) = 1 )
               #this is the referenced value that I need to use.  This works well if I manually fill in a value for 't.sos_customer_id', but fails otherwise
               AND sos_customer_id = 't.sos_customer_id'
        GROUP  BY sos_deliverydate,
                  sos_trip_id,
                  sos_route_id,
                  sos_routestop) AS DROPS) AS DROPCOUNT

FROM (SELECT sos_salesperson_id,
       sos_customer_id,
       …
0
I have installed VtigerCRM and started working.
I changed from http to https but site not opening after that.
Capture43.JPGI have a certificate ready but dont know where is the certificate location.
I followed as per this article
https://www.fastcomet.com/tutorials/vtiger/enable-ssl
0
hi my xampp not starting in browser
xampp
0
I have a database running on CentOS 7, MySQL 5.1.  

My question is that the general log file is like 10 - 20 GB a day and is full of simple select statements.  Is this normal?  That seems to be a huge daily file to create.  I am pretty new to MySQL on CentOS so was hoping someone could tell me if that seems to be a bit much or if it is pretty normal?

Thanks!

Jim
0
hi am geting this error trying to start mysql database in ubuntu

mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
user@myubuntu:~$ sudo etc/init.d/mysql -root -p start
[sudo] password for user:
Sorry, try again.
[sudo] password for user:
Sorry, try again.
[sudo] password for user:
sudo: 2 incorrect password attempts
user@myubuntu:~$ sudo start mysql
[sudo] password for user:
start: Unable to connect to Upstart: Failed to connect to socket /com/ubuntu/upstart: Connection refused
user@myubuntu:~$ Unable to connect to Upstart: Failed to connect to socket /com/ubuntu/upstart: Connection refused^C
user@myubuntu:~$ ^C
user@myubuntu:~$ sudo etc/init.d/mysql - root -p start
sudo: etc/init.d/mysql: command not found
user@myubuntu:~$ sudo etc/init.d/mysql -root -p start
sudo: etc/init.d/mysql: command not found
user@myubuntu:~$ sudo /etc/init.d/mysql - root -p start
Usage: /etc/init.d/mysql start|stop|restart|reload|force-reload|status
user@myubuntu:~$ pswd
123456: command not found
user@myubuntu:~$ sudo /etc/init.d/mysql - root -p start
Usage: /etc/init.d/mysql start|stop|restart|reload|force-reload|status
user@myubuntu:~$ sudo /etc/init.d/mysql start
[....] Starting mysql (via systemctl): mysql.serviceJob for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.
 failed!
0
hi,

Now I have convert Oracle to MariaDB/MySQL and we have a convern, how can we know the number of table is the same! but the main idea should be what tables is missed from MariaDB/MySQL side?

any easy way to compare what table is missed on MySQL/mariaDB side ?
0
Become a Certified Penetration Testing Engineer
LVL 13
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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

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
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
Microsoft Azure 2017
LVL 13
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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

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.