We help IT Professionals succeed at work.

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.

Hello, i'm trying to learn the basics of VB.
I have installed Visual Studio 2019
I have installed MySQL server and created a test db with a table and column.
I have connected the studio to MySQL successfully
I'd like to master creating a form and inserting, updating and displaying the contents of the db table.

I'm struggling to find a decent video tutorial of this. like how to program the save button and insert it into the db.

All the videos i find are either in very bad English or they describe very different methods from each other so i'm not sure which one i should be following.

can someone point me towards some good material please?
thanks
zac
0
I'm trying to call a MySQL stored procedure from an ASP classic script. The stored procedure simply returns an integer value 99 and outputs the result. Unfortunately the result returned toggles between 0 and 67 every time you refresh the page and it should simply show 99.

The stored procedure is defined here:

DELIMITER //
CREATE PROCEDURE Proc_Test (IN  Lang_param  VARCHAR(10),
                            OUT ID_param    int)
BEGIN
   SET ID_param = 99;
END //
DELIMITER ;

Open in new window


The database is MariaDB, v10.3.17.

Here is the ASP classic script calling the stored procedure. As value is passed in which does not get read but more importantly the return value is read and displayed.

<%@ Language=VBScript %>
<%
    Option Explicit
    Response.Buffer = True
    Dim Hostname, Username, Password, Database
    Hostname = "localhost"
    Username = "XXXXXXX"
    Password = "YYYYYYY"
    Database = "baseline"
    
%>
<html>

<head>
<title>Baseline Connection</title>
<style type="text/css">body {font: normal 11px Arial}</style>
</head>

<body>

<%
   Const adCmdStoredProc = 4
   Const adParamInput = 1
   Const adParamOutput = 2
   Const adInteger = 3
   Const adVarChar = 200

   Dim Command
   Dim Connection
   Dim ConnectionString

   Dim myLang
   Dim myID

   Set Connection = Server.CreateObject("ADODB.Connection")
   ConnectionString = _
     "DRIVER={MySQL ODBC 8.0 ANSI Driver};" & _
     "SERVER=" & Hostname & ";" & _
     "Database=" & Database & ";" & _

Open in new window

0
I'm trying to call a MySQL stored procedure and read its out parameter using ASP classic. Unfortunately I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[MySQL][ODBC 3.51 Driver][mysqld-5.5.5-10.3.17-MariaDB]OUT or INOUT argument 2 for routine baseline.Proc_Test is not a variable or NEW pseudo-variable in BEFORE trigger
/functions/command_procedure.asp, line 54


The stored procedure is defined here:

DELIMITER //
CREATE PROCEDURE Proc_Test (IN  Lang_param  VARCHAR(10),
                            OUT ID_param    int)
BEGIN
   SET ID_param = 99;
END //
DELIMITER ;

CALL Proc_Test ('eS', @myID);
SELECT @myID;

Open in new window


Here is the ASP classic script calling the stored procedure:

<%@ Language=VBScript %>
<%
    Option Explicit
    Response.Buffer = True
    Dim Hostname, Username, Password, Database
    Hostname = "localhost"
    Username = "baseline-user"
    Password = "xxxxxx"
    Database = "baseline"
    
%>
<html>

<head>
<title>Baseline Connection</title>
<style type="text/css">body {font: normal 11px Arial}</style>
</head>

<body>

<%
   Const adCmdStoredProc = 4
   Const adParamInput = 1
   Const adParamOutput = 2
   Const adInteger = 3
   Const adVarChar = 200

   Dim Command
   Dim Connection
   Dim ConnectionString

   Dim myLang
   Dim myID

   Set Connection = Server.CreateObject("ADODB.Connection")
   ConnectionString = _
     "DRIVER={MySQL ODBC 3.51 Driver};" & _
     "SERVER=" & Hostname & ";" & _

Open in new window

0
mysql 8 function always returns utf8mb4 no matter what I try
How do I get this function to return a latin1 result character set?

DELIMITER $$
drop function if exists afProgramName $$
Create function afProgramName(inSysLogTag varchar(60) CHARSET latin1)
-- Create function afProgramName(inSysLogTag varchar(60) )
RETURNS varchar(60) CHARSET latin1 collate latin1_bin
DETERMINISTIC
begin
declare Result varchar(60) CHARSET latin1 collate Latin1_bin  default _latin1 ''  ;
declare vpc integer;
 Set Result=inSysLogTag;
 Set vpc=Position('[' in inSysLogTag);
 if vpc>0 then 
  Set Result=Trim(Left(Result,vpc-1)) collate latin1_bin;
 end if;
 Set vpc=Position(':' in Result);
 if vpc>0 then 
  Set Result=Trim(Left(Result,vpc-1)) collate latin1_bin;
 end if;
 
             
  return Result ;
end$$

Select afProgramName(_latin1 'postfix/master[744]:') 
;

Open in new window

0
I'm having a problem using OUT parameters in a stored procedure call, IN parameters work just fine but OUT parameters cause a server '500 Internal Server Error message'

My setup is below, any advice appreciated

   Set Conn=Server.CreateObject("ADODB.CONNECTION")
   Set RS = Server.CreateObject("ADODB.RecordSet")
   Conn.Open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & Hostname & "; Database=" & Database & "; UID=" & Username & ";PASSWORD=" & Password & "; OPTION=3;"
   strSQL = "Create_Dummy ('eS', @mySuccess, @myStatusCode, @myID)"
   Set RS = Conn.Execute(strSQL)
   If RS.BOF And RS.EOF Then
      Response.Write "The connection to the Baseline is successful but unfortunately no record found in the database."
   Else
      Response.Write(RS.Fields("Success_param").value)
      Response.Write(RS.Fields("Status_Code_param").value)
      Response.Write(RS.Fields("ID_param").value)
   End If
   
   Response.Write(mySuccess)
   Response.Write(myStatusCode)
   Response.Write(myID)
0
hi, I'm trying to run some mySQL queries from php, to select records (from table e) which do not exist in another table (exg)

I would imagine any of these methods might work, but none are so I must be missing something fundamental, any suggestions?

SELECT e.ID FROM e WHERE NOT EXISTS (SELECT exg.ID FROM exg WHERE exg.ID=e.ID)
SELECT e.ID FROM e WHERE ID NOT IN (SELECT exg.ID FROM exg)
SELECT e.ID FROM e LEFT JOIN exg ON e.ID = exg.ID WHERE exg.ID IS NOT NULL

Open in new window


Thanks
0
I have a csv file which contains NULL values in fields that are not populated. The CSV file is fed into a mysql database. The user would like to remove all null values on the sheet with blanks - nothing in the cell. I am not sure why. The file when then be uploaded into the mysql database. Will this have an impact? Maybe they want them removed because they see them on a report from the data upload. Just want to make sure this wont affect anything
0
I have created a function that takes a List of codes and some other information and attempts to insert the data into a MySQL table.  I'm trying to use a prepared statement, but for some reason the records do not actually record the code value.  Can you tell me what I am doing wrong with my approach, or is there a better way of going about this?  Code is as follows:
public static bool WriteToDatabase(IList Codes, string PONumber, string ValidFrom, string ValidTo)
{
  if (Codes.Count < 1 || String.IsNullOrEmpty(PONumber))
  {
    return false;
  }
  string sql = "INSERT INTO gift_codes (code, customerPO, validFrom, validTo) values (@code, @po, @from, @to)";
  string code = "";
  MySqlCommand cmd = new MySqlCommand(sql, Conn);
  cmd.Parameters.AddWithValue("@po", PONumber);
  cmd.Parameters.AddWithValue("@from", ValidFrom);
  cmd.Parameters.AddWithValue("@to", ValidTo);
  cmd.Parameters.AddWithValue("@code", code);
  cmd.Prepare();
  for (int i = 0; i < Codes.Count; i++)
  {
    code = Codes[i].ToString();
    cmd.ExecuteNonQuery();
  }
  return true;
}

Open in new window

0
Up to now in my Mysql searches, I've only performed searches for BIGINTs and INTs which behave as I expect. (with the minor quibble I raised a few days ago about why BIGINTs appear as string data not numeric)

Now I'm trying to perform a search for a binary string and I cannot get the syntax right

Sample Code

TARGHASH=HASH_SALTED
&& An SHA256 32 Byte value stored, locally, in a 32 byte VFP CHAR(BINARY) Field and remotely  in a 32 byte Mysql Varbinary column

I prepare a string to encode the SQSTRING I will use in the select statement thus:
success=SQLPREPARE(HND,SQSTRING,'ROWFOUND')

IF NOT CHECK_EXECUTE(SUCCESS,HND)  && a function which checks the syntax and displays any errors or goes on to execute it
      CANCEL
ENDIF
SUSPEND

with and without
CURSORSETPROP("MapBinary",.t.,0)

I have tried the following versions of SQSTRING :

SQSTRING="SELECT HASH_SALTED,ROWNUM FROM HASH_CHAIN WHERE HASH_SALTED="+TARGHASH
*anticipated this would work because the VFP field is CHAR(BINARY) and to the human eye the raw data in the vfp table looks identical to the raw data visible in the Mysql column

SQSTRING="SELECT HASH_SALTED,ROWNUM FROM HASH_CHAIN WHERE HASH_SALTED="+ENCODE(TARGHASH,'HEX')
*ENCODE IS A CALL TO A CHILCRYPT LIBRARY WHICH CONVERTS THE STRING TO HEX

SQSTRING="SELECT HASH_SALTED,ROWNUM FROM HASH_CHAIN WHERE HASH_SALTED=BINARY"+TARGHASH

SQSTRING="SELECT HASH_SALTED,ROWNUM FROM HASH_CHAIN WHERE HASH_SALTED=CONVERT("+TARGHASH+",BINARY)"

all …
0
Hello, Experts,

I have a MySQL question. I am a DB admin for all things SQL server and recently started doing full-stack development and using MySQL more.

I have a very basic table that contains three columns (to make the example simple):

------------------------------
| id | ro_number | sales| date |
------------------------------
1 | 2355 | 2000 | 2020-02-15
2 | 2366 | 3500 | 2020-02-16
...

id is int and current PK, set to Not Null, ro_number is also int allow for nulls, sales is dec(13,2).

I am developing a solution that will pull data from an api in a json response, I parse through the data, and prep for loading data in (new records).

What I am after is in the event I need to update historical data, specifically for a date or given dates and I want to update the data if it exists. Should I just do a delete statement for the given date and then pull down the data and insert in again with the new values? or should I do a conditional check?

What say you?

I appreciate your help and excuse my noviceness with all things MySQL.

-Isaiah
0
I am creating a program that will generate X number of codes that must not already exist in a MySQL table.I am trying to read all of the codes in the current table, and store it within an array.  However, I can't seem to get that to work, either.  Below is the code that I have, but I get an error within the while block indicating that ExistingCodes has not been initialized, which puzzles me.  Use of unassigned local variable 'ExistingCodes' is the precise error.  What am I doing wrong here?  Is there a better way of doing this?  Keep in mind that I am going to use this array to check against when generating the new codes, and adding the new codes as they are generated.

    public static string[] GetExistingCodes
    {
      get
      {
        int index = 0;
        string[] ExistingCodes;
        string sql = "SELECT code from `gift_codes`";
        MySqlCommand cmd = new MySqlCommand(sql, Conn);
        MySqlDataReader rdr = cmd.ExecuteReader();
        while (rdr.HasRows)
        {
          ExistingCodes[index] = rdr.GetString(0);
          index++;
        }
        return ExistingCodes;
      }
    }

Open in new window

0
I have a set of files that came with a book that I am reading. I am not able to login using the files. I have
the correct name for the username, password, server, and database name. After a successful login, the
user should be redirected to the members-page.php. Here is the url for the site.

https://www.domgarofalo.com/mptc/152-118/lp3/login.php

The practice username is johndoe@email.com and the password is 12345678. Also, I have attached
the files with the database file included. I added the following three lines to display the errors on
the page and no errors appear are an unsuccessful login.

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

Can someone point me in the right direction? Thank you.

Archive.zip
0
I have an excel file with 1238 records. When I download into the mysql database it keeps only 912 records. I dont have any keys or table restraints defined. Why is it not pulling in all the records?
0
Finding MySQL on my MacBook and remote DB access

I think I used to access MySQL from the browser (Chrome) on localhost, but I do not recall the exact URL.

Can someone explain how to find MySQL?

Also, I have a developer who will be hosting my website, not yet at AWS, but on an internal server. Can I view that database from my version of MySQL? I would of course need to be given access. I will access the website from an IP address I will be given.

Thanks.
0
I am working on a mySQL/PHP web server.   I currently have the following statement:

"SELECT * FROM Products WHERE Part_no LIKE %s ORDER BY Finish ASC", GetSQLValueString($colname_RS_group . "%", "text")

It is sorting data with Part_no similar to:

SDCD38CH
SDCD38CHK
SDCD38PB
SDCD12CH

The search term pulled from the URL is SDCD38.   The result I currently get is:

SDCD38CH
SDCD38CHK
SDCD38PB

I want to filter out any Part_no ending in K so that I get

SDCD38CH
SDCD38PB

How can I modify the LIKE term to block any Part_no ending in K?
0
Working with a mysql backend database. I am not sure what they are using for the frontend. I am using the frontend for my job now. I have to run reports with a command driven front end menu. I would like to utilize the workbench in mysql to run queries to simplify the process. Is there a way for me to get into the workbench? I did not see anything on the menus that would allow me to access the backend? Thanks in advance
0
Not sure why I keep getting errors on this query using mysql
select `Lead Client Ref`, `Amount Remaining`, balance
from kaos_followup, paramount_payment
where kaos_followup.Lead Client Ref = paramount_payment.customer_no
0
This is an enterprise question asking how to deal with global time zones.

To be very clear, this question is not about how to deal with data or databases. It is a logic question in dealing with different time zones and how to show/represent data to users.
The reason being because there are processes that do calculations at the data center time which is not the UTC time that the data was generated at.
Also, the user looks at his data and wants to see it in his own time zone or perhaps in the time zone where the data was collected.

I need this to be clear otherwise, you will go down a rabbit hole and waste my question.

Question

We are storing historical raw data for a certain amount of events happening in our environment.
Our environment is covering almost every time zone so the decision to record that info in UTC format was a must.

With such amount of data recorded in the last 5 year, it becomes a must to start consolidate (or summarize if you prefer) all that data into a finite number of time-frames such like:
- Last 24hr
- Last week
- Last month
- Last year

What would be the best way to do this considering that each object is physically installed in different time-zones?
And users looking at the data are also in different time zones of the data itself also.


Just an example to clarify the doubt we are facing for instance with some data that is averaged out.
How should we store the information of last 24 hours or of last month average speed?…
0
Getting on pretty well with VFP uploading stuff into Mysql.

Got a problem coming the other way. Specifically, when I download the records from Mysql, all the fields defined as VARBINARY are landing in the VFP cursor as Memos instead of the VARCHAR(BINARY) I need.

I can write code that will convert them or suck the fields into a table with the right field types but given that this is an operation that may need to be performed millions of times a day, that's a bit of latency I would rather avoid. Is there a way to ensure that the downloaded data adopts the data type I require?
0
Hi!

I wrote a Database program using c# and MariaDB.  That is working great on my development system, Windows 10 Pro with LocalHost Database.


When it came time to move the program into production (Server 2016 Server with Domain Controller) I changed the source to the Server's internal IP but I get the following fatal error;


Unable to connect to any  of the specified MySQL hosts


However, with some testing I noticed the program works great, when I VPN in using a system not connected to the Domain.  Very odd.

Here is the MySQL statement (one combination of many throughout):


MySqlConnection con = new MySqlConnection("server=192.168.12.9;port=3306;database=bbb;uid=root;pwd=;");


So, with the client user logged in, and the program on the desktop, I did some trouble shooting:

1) Configured incoming and outgoing TCP and UDP ports of 3306, and eventually disabled the whole Windows Firewall

2) Disabled and eventually removed Kaspersky AV Business

3) Moved the code to another Server, the Domain Controller, and did the same test and had the same result

4) I've installed MySQL and MariaDB ODBC adapters, and they connect just fine during the install test, but I don't think these are required since my computers I've got this working don't even have those ODBC adapters installed, only defaults.

5) Some internet searching indicates a DNS issue, but i'm using the server's literal local IP in the string, so it has no need to do DNS …
0
h


how I want to backup the MySQL InnodB cluster , MySQL router, MariaDB cluster and maxscale configuration file to other linux  VM so that can I setup the same cluster again , how can I know where is the updated location of the my.cnf, myrouter.cnf, server.cnf and maxscale.cnf?
0
I have a mysql query that returns the next number:

SELECT dispOrder+1 FROM (SELECT MAX(dispOrder) AS dispOrder FROM question) AS x

Open in new window


The problem is if all the numbers are NULL it returns NULL... How can I set it so that if all are NULL it returns 0 (+1)

?
0
Hi,
So I need to pull a few values out of one row in a table, but the code I have skips a crucial part of the program (where the values are added together with other variables) if the table is empty.
Here is the code I'm working with:


 string newIDtemp;

            newIDtemp = txtBox_TD_Current_ID.Text;

            string constring = "datasource=localhost;port=3306;username=root;pwd=;";

            string Query = "SELECT * FROM bbb.approvaltotal WHERE TDId= '" + newIDtemp + "' ;";

            MySqlConnection conDataBase3 = new MySqlConnection(constring);
            MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase3);
            MySqlDataReader myReader;


            try
            {

                MessageBox.Show(tdapprovalcurrentjurfee.ToString());
                MessageBox.Show(tdapprovalcurrentpnfee.ToString());
                MessageBox.Show(tdapprovalcurrenttotal.ToString());



                conDataBase3.Open();
                myReader = cmdDataBase.ExecuteReader();

                while (myReader.Read())
                {
                   


                    string approvaltotalid = myReader.GetString("Id");
                    string approvaltotaltdid = myReader.GetString("TDId");
                    string approvalpermitnowtotal = myReader.GetString("PermitNowTotal");
                    string approvaljurisdictiontotal = myReader.GetString("JurisdictionTotal");


.
.
.
.

0
Hi,

I'm running MariaDB with HeidiSQL management software, and I want to move my databases to another system, using the same version of MariaDB and Heidi.

I'm using mysqldump to copy the database, and using this command:


C:\Program Files\MariaDB 10.4\bin>mysqldump -u root -p bbb > d:\database\bds2.sql





However when I want to restore, it seems to work okay, the command runs, but when I check Heidi, the tables are not there.  I use this command to restore:


C:\Program Files\MariaDB 10.4\bin>mysqldump -u root -p bbb < d:\database\bds2.sql


The database on the target system has a database named bbb, though a different pw than the original host.
I've restarted the mariadb service and the Heidi software, just in case there was a glitch but there is nothing.

My development system was Windows 10 and the target system is Windows 7.

The size of the backup file is about 27Kb, and in Heidi the empty tables are about 450Kb.

The Mysqldump restore does have an output when run, like a Header with information like the Host and DB server and Maria version number, then it has a couple dozen lines about 'SET'.  It doesn't say anything other than that.

Am I missing something on the backup and restore?  Should I use something different?

Thank you in advance!
0
PHPbb forum site hosted on a cloud server. I am extracting user data into excel through MySql in cPanel.
The datetime entries are stored numeric values (hash?) not sure how to get these in a usable format

For example value 1549909426 shows as Feb 11, 2019 1:23pm in the application
how can I either extract it as a usable date in MYSql or convert it once in excel
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.