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

Storing UNION information  to a table variable. I was able to create a union  to determine the SalesRate for Texas, I want to store the information as a variable.
Union Query that run  is results Tax Rate --7.50      StateProvince-- 73      CountryRegion --US
SELECT      TaxRate, StateProvinceID,CountryRegionCode
            FROM            AdventureWorks2016.Sales.SalesTaxRate a
            JOIN            AdventureWorks2016.Sales.SalesTerritory b
            ON                  a.ModifiedDate =  b.ModifiedDate
            WHERE            a.[Name] lIKE  '%Texas%'
            AND                  b.[Name] = 'NorthWest'

variable
DECLARE                  @TexasSalesTax      INT
                        
                              SELECT            @TexasSalesTax  =  a.TaxRate
                              FROM            AdventureWorks2016.Sales.SalesTaxRate a
                              JOIN            AdventureWorks2016.Sales.SalesTerritory b
                              ON                  a.ModifiedDate =  b.ModifiedDate
                              WHERE            a.[Name] lIKE  '%Texas%'
                              AND                  b.[Name] = 'NorthWest'                                    
                        
                        SELECT * FROM @TexasSalesTax
                        
How can I pull the information from the union table to variable?
0
Learn SQL Server Core 2016
LVL 13
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

The Stg_Emp table contains all new & current employee   `1, John Doe      2, Jane Doe 3, Sally Mae
The Emp_List table contains some current & all former employees   1, John Doe      2, Jane Doe 5, Peggy Sue
ASSIGNMENT: Write two scripts using JOIN to:
Show the Company’s new employee John Doe and Jane doe ?
SELECT *
from dbo.Stg_Emp
LEFT JOIN dbo.Emp_List  
on dbo.Stg_Emp.EmpName = dbo.Emp_List.EmpName
---John Doe and Jane Doe
Show the Company’s former employee      Peggy Sue ?
SELECT *
from dbo.Stg_Emp
RIGHT JOIN dbo.Emp_List  
on dbo.Stg_Emp.EmpName = dbo.Emp_List.EmpName

Not sure if my Results are Correct John Doe and Jane Doe are Both New Employees and Peggy Sue is a Former Employee ? Then where does Sally Mae fall into ? How can I use  a join to categorize where she falls into
0
Our site was attacked by a bot last night.  The bot found our 'Contact Request" page and the attack started.   Tons of emails started hitting the mail boxes in sales.  I have been using a honey pot and input filters up to now, but this is not enough.    

I am working with mySQL/php.  The disabled page is https://www.glassdivider.com/W3-Contact_us.php

Looking at the emails I can see what the bot has been trying.    I don't understand how the bot is setting up the inputs.    I'm seeing all characters in fields I restrict the inputs.  Like the zip code field is restricted to numbers only,  but the bot is putting everything into the field.    I am check the field content on "onKeyUp" and "onKeyDown" which means the bot must not fill in the form.  The bot must be assigning the fields a value and then submitting it.

I need some help understanding what's happening,  and how to prevent this problem.
0
hi,

I have my MySQL 8.0.12 router, shell and community server installed and by some reason once MySQL shelled to the router it will prompt for login and never can login as it prompt for the following message:

 I have bootstrap mysql router and start mysqlrouter accordingly.

first messagge
I connection using MySQL shell and it has error:

 

Any reason why is that?

IP address of DB instance can be ping from that MySQL router node so connectivity is not a problem.

any reason why ?

this is the mysqlrouter.conf:

MysQL-router-3.jpg
0
Trying to make a LAMP on AWS.

Typed in sudo apt-get install mysql-server and "it" did it's thing ending at a prompt.

I then typed in sudo service mysql start... again it did it's thing.

I'm assuming that MySQL started (no errors were thrown) and thus was installed. During the install I was never prompted to create a password.

Is there a default password?

Typing in mysqladmin -u root -p status prompts for a password.
0
hi,

I use WinSCP to transfer files between Windows server and linux, and I accidentally delete a MySQL file in the bin directory using winSCP, any way to recover it except backup ?

any undelete?

accidentally delete a files in MySQL bin folder using WinSCP
0
Query that MUST use both where and order by clause
I am getting this ERROR
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
            WITH  UKCustomers3       (      CustID, CustName,CustEmail,Gender,CustHireDate,CustSales)      

            AS
            --We define the CTE QUERY

            (      SELECT      CustID,CustName,CustEmail,Gender,CustHireDate,CustSales
                  FROM      dbo.UKCustomers3
            )
                                          
            ,CTE_UKCustomers3

            AS

            --Define the Outer Query Referencing CTE_UKCustomers3
            --To Find out the TOP CustSales by Gender = Becky

            (SELECT       *
                  FROM      UKCustomers3
                  WHERE      Gender = 'F'
                  ORDER BY CustSales
            )

            -- To verify

            SELECT * FROM @UKCustomers3
0
Am creating a CTE table RatingInfo, I am getting this error invalid error

(2 rows affected)
Msg 208, Level 16, State 1, Line 100
Invalid object name 'RatingInfo'.

            ---                  CREATING A CTE STRUCTURE

            WITH            RatingInfo      (ReviewerName,EmailAddress,Rating,ReviewDate)
            AS
            --We Define the CTE query
            (
                        SELECT            ReviewerName,
                                          EmailAddress,
                                          Rating,
                                          ReviewDate
                        FROM            Production.ProductReview
                        
            )
            --Define the Outer query referencing CTE_RatingInfo
            
                  (      SELECT            ReviewerName,
                                          EmailAddress,
                                          Rating,
                                          ReviewDate
                        FROM            RatingInfo

                        WHERE            Rating  BETWEEN 2 AND 5
                        AND                  EmailAddress like 'J%'
                  )

            

            SELECT *      FROM      RatingInfo
0
How can I use both LIKE AND BETWEEN OPERATORS in this query ? Like operator returns a wild card character right ?  '%'

            SELECT            ReviewerName
                              EmailAddress,
                              Rating,
                              ReviewDate,
                              Comments
            FROM            Production.ProductReview
            WHERE            Rating BETWEEN 2 AND 5

sample output
______________________________________________________________
Email address/      ReviewDate                                 Comment
John Smith/        5/2013-09-18 00:00:00.000/   I can't believe I'm singing the praises of a pair of socks
0
I have over 60 stores that use cash registers for sales.  I have a method of collecting every transaction from a cash register by capturing the cash registers electronic journal through the serial port.  The data is all text based, but very unstructured.  (I am including a sample file of this data capture to this post.)

The question I have is this:  IF you were tasked with storing, then retrieving and analyzing these data; what type of database storage system would you use?  And why that one?  

I am waffling on whether to use a SQL or NoSQL solution.

Thank you,
Jeff

Here is a sample of the raw data capture.  (Note: about half of the way down there is something called a "Z report"; which is run when a cash register is closed out.  That zero's out all of the balances and totals during that period of time it was operating.)

DATE  10/09/2019 WED  TIME 02:17
                               
2X             @ 4.29          
Purses/Backpack T1         $8.58
Childrens Clothing T1      $1.99
TAX1                       $0.63
TOTAL                     $11.20
CASH                      $12.00
CHANGE                     $0.80
CLERK 1        No.000072   00000
                               
                               
                               
                               
                               
                               
                               
DATE  10/09/2019 WED  TIME 02:18
                               
5X   …
0
C++ 11 Fundamentals
LVL 13
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

hi,

with referring to:

https://www.experts-exchange.com/questions/29153130/how-to-upgrade-MySQL-InnodB-cluster.html?anchor=a42953346¬ificationFollowed=238198440&headerLink=account_loggout#a42953346

how can i get the generate binary of MySQL 8.0.x ?

I am now using MySQL 8.0.12 generaic binary to setup the InnodB cluster and I am not sure how to upgrade it easily, any idea?

ANY node i instlal MySQL and MySQL router will not have internet connection and I want to upgrade it to MySQL 8.0.17 InnoDB cluster


any idea on how to upgrade it ?
0
Hello Experts
Is there any way to export a particular mysql table with out using mysqldump?

For example I need the same sql format just with a command from within mysql ?

something like oposite to LOAD DATA INFILE <SQL FILE>  INTO TABLE <table>
but not using mysqldump because if forces me to work from cmd line.

Regards,
1
Hello Experts,
I have a  Mysql database with big tables above 500 hundred thousand records.

there is this table with ID primary key auto  increment and a date field.
I need to select all rows in a certain period. Usually a month.
When I do the select it checks the full table taking too long.

Is there any way to tell the select to start at a certain point and then just continue until the date field is greater than ...?
So basically it check just teh portion of the table I need to?

Regards,
0
hi,

my MySQL server 8 installed using generic binary and we do not use RPM to install it, I am not sure how can I only shutdown mysql instance in safest way.

any idea ? I start mysql this way:

bin/mysqld --default-file=/POC/my.cnf --user=xxx &

Open in new window

0
hi,

I have some MySQL/MariaDB tables I want to delete but it always say FK constraint but we all know what it means . but the problem is I can't find which table is referencing it and delete that one first.

e.g., I have a table I can't delete:

CREATE TABLE `AREA` (
  `CD` varchar(3) NOT NULL ,
  `ENG_DESCRIP` varchar(20) DEFAULT NULL ,
  `CHI_DESCRIP` varchar(10) DEFAULT NULL ,
  `ACT` varchar(1) DEFAULT NULL COMMENT 'Active Active  ',
  `CREATE_DT` datetime DEFAULT NULL ,
  `CREATED_BY_USR` varchar(25) DEFAULT NULL,
  `CREATED_BY_WPASS` decimal(10,0) DEFAULT NULL ,
  `LST_UPD_DT` datetime DEFAULT NULL ,
  `LST_UPD_BY_USR` varchar(25) DEFAULT NULL ,
  `LST_UPD_BY_WPASS` decimal(10,0) DEFAULT NULL ,
  `EFF_DT` datetime DEFAULT NULL,
  `ORD` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`CD`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Area';

Open in new window


I can't delete this one without knowing which table referencing this , any idea on how to find out quickly ?
0
Hi We are using Visual Studio and we want to start storage PDF or some other files in DB but we would like to know which is the best DB to do it?

we have SQL, Mysql, and MongoDB    if you know other, please advice
0
Hi,
I've selected details like
45e.png
but I encounter issue below. Why?
45f.png
0
Experts,

Much like my last post, I am trying to obtain several counts when certain conditions are met. I am attempting to utilize a cross-tab query so that all results appear in one table.

In this example, I'd like to get a total count of each shift type. A shift type or "need" is determined by when the employee starts and stops working. The counts would then be ordered by Site, Department, and employee classification.

I'm not sure if my SUM() counts are off because I am comparing TIMEs incorrectly or, maybe I am grouping incorrectly? Can anyone offer any suggestions?

Both "Job_Start_Time" and the "Job_End_Time" fields are stored as varchar(8) in my database. Both fields store time values in military time: HH:MM:SS.

SELECT
jobs.Job_Site, 
jobs.Job_Department, 
employees.User_Field_13, 
SUM(CASE WHEN CAST(schedule.Job_Start_Time AS TIME) < CAST('09:59:00' AS TIME) THEN 1 ELSE 0 END) AS 'AM Needs', 
SUM(CASE WHEN (CAST(schedule.Job_Start_Time AS TIME) < CAST('14:59:00' AS TIME)) && (CAST(schedule.Job_End_Time AS TIME) > CAST('14:59:00' AS TIME)) THEN 1 ELSE 0 END) AS 'PM Needs', 
SUM(CASE WHEN CAST(schedule.Job_Start_Time AS TIME) >= CAST('14:59:00' AS TIME) THEN 1 ELSE 0 END) AS 'Eve Needs' 
FROM 
mydb.employees 
LEFT JOIN mydb.schedule ON employees.Emp_Id=schedule.Emp_Id 
LEFT JOIN mydb.jobs ON schedule.JobAssignment_Id=jobs.JobAssignment_Id 
LEFT JOIN mydb.status ON status.Emp_Id=employees.Emp_Id 
WHERE 
schedule.DeleteFlag!='1' 
AND 

Open in new window

0
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
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 am trying to send the results for two(2) tables using sequelize, however only the results on either one table is printed. I would like both tables to print using promises. Here is the route code:

router.get("/alldataa", (req, res) => 

Promise.all([boilerHouse.findAll(), compressorHouse.findAll()])
.then((data) => {
  res.render('gigs', {
    gigs:data[0],
    gigs:data[1],
   
   //data[0] is response from tableA find
   // data[1] is from tableB
})   
  })
  .catch(err => console.log(err)));

Open in new window

0
Thank you for looking at my question,

The IT department have installed Webmin on a MySQL server. I have written a bit of php code and saved it to the correct place on the server.

The code is a simple table and begins like this:
<?php # Script 1.0 - Section1.php
      require_once $_SERVER['DOCUMENT_ROOT'] . '/CESTrack/Includes/configinc.php';
      //require_once $_SERVER['DOCUMENT_ROOT'] . '/CESTrack/Includes/mysql_connect.php';
      
      $Page_Title = "KLTD E-D1";
      include($_SERVER['DOCUMENT_ROOT'] . '/CESTrack/Includes/CESTrack_Header.html');
      
      echo '<h2>CES Tracker</h2>';
      
      echo '<table width="100%">';
            echo '<tr>';
                  echo '<td align="center">';
                        echo '<table width="80%" border="2">';
                              echo '<tr>';
                                    echo '<td colspan="16">';
                                          echo '<h2>HONKER LTD CES DASHBOARD</h2>';
                                    echo '</td>';
                              echo '</tr>';
                              
                              echo '<tr>';
                                    echo '<td width="13%" align="left">';
                                          echo'<h2>Activity</h2>';
                                    echo '</td>';
                                    
                                    echo '<td width="13%" align="center" colspan="3">';
                                          echo'<h2>Queries</h2>';
                                    echo '</td>';
                                    
                                    echo '<td width="13%" align="center" colspan="3">';
                                          echo'<h2>SENQ Enq/Quotations</h2>';
                                    echo '</td>';
                                    
                                    echo '<td width="13%" align="center" colspan="3">';
                                          echo'<h2>SENQ Orders</h2>';
                                    echo '</td>';
                                    
                                    echo '<td width="13%" align="center" colspan="3">';
                                          echo'<h2>Projects</h2>';
                                    echo '</td>';
                                    
                                    echo '<td …
0
hi,

any query can quickly find out which table has BIGINT type defined in MySQL 8.0.17 or MariaDB 10.4.6 ?
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
Hello Experts,
One of our remaining legacy apps uses mysql server
and mysql-connector-odbc-5.3.10-win32

It works just fine and we do not plan to develop a new app until other more
important projects are finished.

The fact is our customer replaced his server for some reason and
it works with vmware.

And now our app has lost a lot of speed since last friday.

I understand the new installation is a mirror of the older server.
But then our app has lost speed around 30% only when making queries.

Question1: mysql-connector-odbc-5.3.10-win32 is this a good ODBC or is it too old?
Question2: What configuration should I request in the VM so the company in charge of hardware can configure properly.
What else should I check given the fact that this app is still very useful and we do not plan to replace i until 2020.

Regards,
0
hi,

our MysQL InnoDB cluster 8.0.12 has 2 nodes out of 3 shut down to save resource for other VM and now I turn them both on, but I suspect that the InnoDB cluster is not working any more:

InnoDB cluster
1) how to justify the cluster status if everthing is ok ?
2) how to make the cluster work again?
3) or how to cluster resync well again?
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.