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

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
Fundamentals of JavaScript
LVL 13
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

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
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
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
Exploring ASP.NET Core: Fundamentals
LVL 13
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

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
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
I'm trying to retrieve data from my remote MySQL database from C++ under Ubuntu.

#include <mysql++.h>
#include <stdlib.h>
 
using namespace std;
using namespace mysqlpp;
 
int main() {
    
    cout << "Trying to connect..\n";

    try {
        Connection conn(false);
         conn.connect("Sql737674_5", "62.149.xxx.xxx", "Sql737674", "xxxxxxxxx");
        Query query = conn.query();
        
        if (conn.connected()){
        cout << "OK\n";
        } 
        else {
        cout << "NOT CONNECTED\n";
        }

        cout << "Eseguo Select\n";
        /* Now SELECT */
        query << "SELECT * FROM Utenti LIMIT 10";
        StoreQueryResult ares = query.store();
	cout << "Ciclo FOR\n";
       // printf("numero: %d", ares.num_rows());
        for (size_t i = 0; i < ares.num_rows(); i++){
	cout << "Name: " << ares[i]["Nome"] << " - Cognome: " << ares[i]["Cognome"] << endl;
         }
        
    } catch (BadQuery er) { // handle any connection or
        // query errors that may come up
        cerr << "Error: " << er.what() << endl;
        return -1;
    } catch (const BadConversion& er) {
        // Handle bad conversions
        cerr << "Conversion error: " << er.what() << endl <<
                "\tretrieved data size: " << er.retrieved <<
                ", actual size: " << er.actual_size << endl;
        return -1;
    } catch (const Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        cerr << "Error: " << er.what() << endl;

Open in new window

0
Hi SQL Experts,

As part of a MySQL (MyISAM) project of mine, I'm trying to convert an SQL SELECT on 2 tables to a LEFT JOIN query, and it seems to be giving the right output, but I'm wondering if I've got the optimal syntax.
SELECT
        table1.security_id,
        table1.item
FROM table1
LEFT JOIN table2 ON
        table2.security_id = table1.security_id AND
        table2.org_id = 123 AND
        table2.person_id = 100000
WHERE
        table1.org_id = 123 AND
        table1.security_id IN (0, table2.security_id)

Open in new window

Note the additional test for 0 in the last line, which is why I think I needed to convert this query from a traditional SELECT on 2 tables to a LEFT JOIN in the first place, because otherwise I was getting double-ups of output.

Questions:

Q1. Do I really need to have this line like this in the LEFT JOIN ON:
        table2.security_id = table1.security_id AND
And this link like this in the WHERE:
        table1.security_id IN (0, table2.security_id)
Looks a bit duplicated to me, but if I simply remove one it fails.

Q2. And should this:
        table2.org_id = 123 AND
be in the LEFT JOIN ON
and this:
        table1.org_id = 123 AND
be in the WHERE?
Note: The "123" could be any number, but it will be the same in both places.

Q3. Any other comments, suggestions or questions?

If you need what I'm trying to achieve, let me know, but I'm hoping it's clear from the fact that the above code does seem to be doing the job.

Thanks.
tel2
0
Why Diversity in Tech Matters
LVL 13
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Hello,

My mysql database will not allow a NULL value in my date field since I upgraded my server and php version to 7.3 (it worked fine in 5.6 server)

it keeps inserting 0000-00-00

the field view from phpMyAdmin..

column - type - null- default
tps_shows_end_date - date - Yes - NULL      

My code.

if (isset($_POST['show_date_end'])) { 
			
			if (($_POST['show_date_end'] == "0000-00-00") 
			 || ($_POST['show_date_end'] == '0000-00-00')
			 || ($_POST['show_date_end'] == 0000-00-00) 
			 || ($_POST['show_date_end'] === NULL)) {
				$show_date_end = NULL;
			} else {
				$show_date_end = $_POST['show_date_end'];
			}
			
			
		} else {
			$show_date_end = NULL;
		} 



 $insertSQL = "INSERT INTO tps_shows (tps_shows_end_date) VALUES ('".$show_date_end."')";

Open in new window

0
hi,

when I try to setup up MySQL for any hosts do not have internet access, must I setup local repository ?

before when I install MariaDB on top of Redhat linux I do this:

mount the RHEL 7 installation ISO to /mnt folder :

# mount -o loop rhel-server-7.5-x86_64-dvd.iso /mnt

Open in new window


Setup local repository:

copy the media.repo file from /mnt to /etc/yum.repos.d/ and set the permissions to 644 :

# cp /mnt/media.repo /etc/yum.repos.d/rhel7dvd.repo
# chmod 644 /etc/yum.repos.d/rhel7dvd.repo
Edit the rhel7dvd.repo, changing the gpgcheck=0 setting to 1 and adding the following 3 lines
enabled=1
baseurl=file:///mnt/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release
clear the related caches by yum clean all and subscription-manager clean once
# yum clean all
# subscription-manager clean

Open in new window


should I do it this time for MySQL standalone installation using community edition 8.0.17?

and I am reading this:
https://dev.mysql.com/doc/refman/8.0/en/linux-installation-yum-repo.html
https://dinfratechsource.com/2019/06/25/install-mysql-8-0-in-linux-and-windows/
https://www.tecmint.com/install-latest-mysql-on-rhel-centos-and-fedora/
https://www.itsmearunchandel.co.in/linux/install-mysql-8-0-version-on-centos.html

seems ALL need me to do this: add the MySQL to the local repository, must I do that:

https://www.itsmearunchandel.co.in/linux/install-mysql-8-0-version-on-centos.html
rpm -ivh mysql80-community-release-el7-3.noarch.rpm

Open in new window


or local repository procedure I post above?

then I:

yum -y localinstall <MySQL 8.0.17 rpm package one by one>

Open in new window


?

after that must I secure configure it and initialize it ?
0
hi,

any easy to follow procedure to setup CentOS VM on windows 10 and install MySQL standalone on top of CentOS VM ?
0
I have a MySQL stored procedure that pivots the data from a view with dynamic columns.  This is working fine, but just temporarily returns the output on the screen in phpMyAdmin and I can't then use the data for anything, such as to display the query results.

I suppose I need to output the results to a temporary table or subquery so that I can use the results.  Any advice on how I can do this?

The code that works that returns the pivot data is:

===================================
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(p.FieldName = ''',
      FieldName,
      ''', p.FieldValue, NULL)) AS ',
      FieldName
    )
  ) INTO @sql
FROM Store_Items;

SET @sql = CONCAT('SELECT p.SubmissionId
                    , p.FormId                  
                    , p.DateSubmitted
                    , p.UserId
                    , p.confirmed, ', @sql, '
                   FROM Store_Items p
                   GROUP BY p.SubmissionId');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
===================================

Would a temp table be what I need, or possibly some other subquery before I DEALLOCATE PREPARE?

What code would I need to add/change to do this?

Any help would be greatly appreciated!
0
I am using  Joi (Happy) module in node.js to validate entries into a form to a mysql database.  However, the validation does not work. I am receiving a "TypeError: Cannot read property 'path' of undefined" every time I leave the file field empty.  Nothing occurs when I leave any other field empty.  In other words, the validation simply does not work and no field message is being sent to the user based on the entry criteria. Please see the backend and the frontend code using sequelize. Without validation, the form works fine.

FRONTEND CODE WITH FORM:

<!DOCTYPE html>
<html lang="en">
<head>
  <!--JQuery UI: default css (smoothness)(always first)-->
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/smoothness/jquery-ui.css"> 


  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <meta http-equiv="X-UA-Compatible" content="ie=edge">
  <title>Document</title>

  <!--JQuery (always first)-->
<script src="https://code.jquery.com/jquery-3.4.1.js" integrity="sha256-WpOohJOqMqqyKL9FccASB9O0KwACQJpFTUBLTYOVvVU="
crossorigin="anonymous"></script>

<!--JQuery UI core (always first)-->
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>

<!--JQuery UI(after JQuery UI core) - Controls Dialog Modal -->
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script> 

</head>
<body>
  <!---(8)-->
  <h3>Inserting Data into the database</h3>
  <h3>Data List</h3>
  <div id="storesF">
  <div 

Open in new window

0
I am using sequelize to upload to a mysql database.  I am able to upload the image but I am not able to load the other fields which are all null. I keep getting a "TypeError: Cannot read property 'path' of undefined" error.

Please see the sequelize log below as it is only capturing the "Id" and the "Image."


Executing (default): INSERT INTO `stores` (`ID`,`Image`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?);
Station record was successfully created: [object SequelizeInstance:stores]

router.post("/station", (req, res) => {
upload(req,res,(err)=>{
if(err){
        res.send(err);
        }else{
  let newStation = req.body.Station;
  let newImage = req.file.path.replace(/\\/g, "/");
  let newAddress = req.body.Address;
  let newMonthlycstoresales = req.body['Monthly C-Store Sales'];
  let newOperator = req.body.Operator;
  let newTopsku = req.body['Top SKU'];

  let data = {Station: newStation, Image: newImage, Address: newAddress, ['Monthly C-Store Sales']: newMonthlycstoresales, Operator: newOperator, ['Top SKU']:newTopsku };

Store.create(data)
              .then(stores => {
                 console.log('Station record was successfully created: ' + stores);
                 res.send(req.file);
             })
              .catch(err => {
                 res.send('error: ' + err)
             
                })
               }
              })
            })

I am using multer to upload the files.

const 

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.