Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x

MySQL Server

46K

Solutions

22K

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

from MySQLdb import *
from Tkinter import *
#Standard Imports (IMO)
import sys, random, math

class dataStoreTest:

    def __init__(self):

        self.root = Tk()
        self.root.title("Database Test")
        self.inputFrame = Frame(self.root)

        #TextVariables
        self.fName = StringVar()
        self.lName = StringVar()
        self.DOB = StringVar()
        self.email = StringVar()

        #MySQLdb Variables
        self.connection = connect("localhost", "root", "", "test")

        self.setupGUI()

        self.root.mainloop()

    def setupGUI(self):

        #Title/Header
        title = Label(self.root, text="DataBase Test", fg="Black")
        title.pack()

        #Input Fields
        fNameLab = Label(self.root, text="First Name: ", width=14, fg="steelblue")
        fNameLab.pack()
        fNameEnt = Entry(self.root, textvariable=self.fName, width=14, bg="gray")
        fNameEnt.pack()

        lNameLab = Label(self.root, text="Last Name: ", width=14, fg="steelblue")
        lNameLab.pack()
        lNameEnt = Entry(self.root, textvariable=self.lName, width=14, bg="gray")
        lNameEnt.pack()

        dobLab = Label(self.root, text="DOB (YYYY-DD-MM):", width=18, fg="steelblue")
        dobLab.pack()
        dobEnt = Entry(self.root, textvariable=self.DOB, width=14, bg="gray")
        dobEnt.pack()

        emailLab = Label(self.root, text="E-Mail Address:", width=20, fg="steelblue")
        …
0
Veeam Task Manager for Hyper-V
LVL 1
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

I want a query where I get all rows in table 'products' where the column 'model' has no match with column 'model' in table 'freight'.

I tried this: SELECT * FROM products WHERE model <> (SELECT model FROM freight)

I get this MySQL message: #1242 - Subquery returns more than 1 row

Yeah, I know that, I want to know which SPECIFIC rows that applies to.

How do I modify this to get what I want?
0
Hi,

I would like to offer users to be able to toggle modules on/off as well as various options for each module.

I'm not sure what the best method to accommodate this?

i have considered
  1. creating a separate table for each module. Each table would have some similarities but I could add/remove columns for each specific module setting. There would be some redundancy
  2. create a table which would include all modules and its related options. create a second table which would list specific options and use an option id and player id to tie the two together. If there is no database value the default setting would be used

I have included a copy of a proposed options, they are limited as I haven't developed the ability to toggle them.

there are approximately 26 modules and they are all unique(of the 26 only about 10 would include options)
modules.png
0
Hi,

Given the sample data attached How do I combine the following 2 select queries so I return 1 row

The two tables are from the back end of Wordpress

SELECT id p,title p,meta_value  m as wanted_code  
FROM wp_post p, wp_postmeta m
WHERE p.status ='published'
AND m.meta_key = 'wanted_code' AND m.meta_value REGEXP 'AA|XX'
AND m.post_id = p.id 

Open in new window


SELECT id p,title p,meta_value  m as docid
FROM wp_post p, wp_postmeta m
WHERE p.status ='published'
AND m.meta_key = 'docid' 
AND m.post_id = p.id 

Open in new window


The result would look something like this
id | title | docid | wanted_code  
123 | Title 123 | xyz123 | AAA, BBB  ,XXX

Open in new window


EE_data.xlsx
0
I have the following code that  the Elements on line 4 assigns results to all rows not just the matching resource_id.  How do I limit that column to only records that match resource.id=Elements.resource_id?  

SELECT CONCAT(p.key_tag,"-",r.resource_num) AS KeyTag, 
CONCAT(p.address_number," ",p.address_street) AS address, 
CASE WHEN p.suite IS NULL THEN p.address_number ELSE CONCAT(p.address_number,p.suite) END AS PropCode,
(SELECT GROUP_CONCAT(element_type_id) FROM elements GROUP BY resource_id) AS Elements, 
r.assignedto,
r.assigneddate
FROM property p
LEFT JOIN resource r ON r.property_id=p.id
ORDER BY p.key_tag,r.resource_num

Open in new window

0
MySQL Hot backup for 60GB database.

First I've read that mysql dump is can be slower than another enterprise app so that is where I'm exploring now.  
Trying to find a solution for a hot backup of a 60GB database.  It needs to be encrypted because of sensitive data so looking for the software to use, commands, and tips.

MySQL Enterprise Backup - Is what I'm looking at 30 day free trial but cannot find how much it cost?
If I wanted this recoverable up to the closet time like 15 minutes/1hour how can that be acomplished?  Which command?

I've also seen Percona XtraBackup 2.4.8 but seems to be Linux based and I would have to spin up and pay for another Linux server.  I know the db is small but the nightly backups we take would be bad if I could recover 23 hours ago.  I need a smaller window.  

Also since the DB is so small is it faster to backup to same server and then have an ftp script send or copy it somewhere else.
The fear is with this application it says there are reasons for a table to become corrupt.  So  I also won't know the time it does become corrupt is backing up every hour or every change just going to overwrite that?  How do I achoomplish the simple goal of Daister Recovery up to the minute of incident.  If it is a corrupt table or an entire database that needs to be restored for some reasons.
0
I am creating a database in Mysql, but I read somewhere that it is best to define my tables using the latin1 character set and then use the utf-8 character set only on the columns where needed. (That is since the utf-8 character can take up more bites). The problem, is that I don't know what is the difference between the two character sets.
1. Could someone please point me to a resource which displays the difference between the two character sets?
2. Could you explain for which types of data will one typically have to use the UFT-8 character set? e.g. Persons names or address information or HTML/XML/XHTML data?
0
we are using third party application connected to MYSQL database.This application is deployed in tomcat server.I want to see which query is exactly executed when I access some link in the application.I am using my sql workbench.
0
Hi Experts

Could you explain if PHP Magento use is always interactive?

After a quick view over its features - even it has an MVC architecture, the configurations looks to be only by use of interactive menus.
Any programation is needed, so? or optionally it could be done?

Thanks in advance.
0
I have a stored procedure in MySQL 5.0.95 (Upgrading is sadly not an option).

The procedure, when executed under the linux command line (and the mysql interface - MySQL Monitor), is returning data properly - 4 fields.  But when executed in the MySQL Workbench 6.2, the result is BLOB for each and every one.

I thought perhaps is a compatibility issue (MySQL 5 is not supported under Workbench 6.2) so I ignored that and for various reasons, tried to run it in a vbscript.

The vbScript displays a really tiny question mark in a box as the output for each field, which I assume is a representation of a BLOB.

So, bottom line, how do I get the actual readable data in the vbscript when executing the stored procedure? (I still don't care about the Workbench tool, I need the data properly returned in the vbscript).

(For reference, the field names have been changed to protect the innocent :-) )
DELIMITER $$
CREATE DEFINER=`client`@`%` PROCEDURE `GetItemServiceSummary`(IN StartDate DATE, IN EndDate DATE)
BEGIN

SET @newItem =
(SELECT COUNT(*) AS 'New Item' FROM mytable
WHERE vendor IN (175,176)
AND entered_date BETWEEN StartDate AND EndDate
AND erstatus != 'cancelled'
AND covertype != 5
AND product = 4);

SET @newService =
(SELECT COUNT(*) AS 'New Service' FROM mytable
WHERE vendor IN (175,176)
AND entered_date BETWEEN StartDate AND EndDate
AND erstatus != 'cancelled'
AND covertype != 5
AND product != 4);

SET @closedItem =
(SELECT COUNT(*) AS 'Closed Item' FROM mytable

Open in new window

0
Get your Disaster Recovery as a Service basics
LVL 1
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

My jQuery autocomplete works for the IP Address listed here http://45.33.62.115/rates/index2.php

However it does not work for the server address here https://consolidatedutilities.com/rates/index2.php
0
Is it possible to use MYSQL Workbench GUI to backup and restore a database ?

Thx
0
I plan a simple Angular website with little user data needing to be persisted, at first. Username, Email, personal preferences, etc.

What of the value of using Firebase versus me configuring a MySQL database? Clearly, there seems to be no back-end service needing to be written, and that is pretty awesome.

Is this NoSQL?

What other advantages are there? What other free alternatives are there?

Thanks
0
    Private Sub Work1()
        If TextBox1.Text = Label8.Text Then
            conn.Open()
            Dim cmd As New MySqlCommand
            Dim myDA As New MySqlDataAdapter(cmd)
            Dim myDT As New DataTable
            cmd.Connection = conn
            cmd.CommandText = "Update() rsv.tbl_inventory SET item_quantity=''" & Val(TextBox3.Text) + Val(Label10.Text) & "'' WHERE item_code=''" & TextBox1.Text & "'';"
            cmd.ExecuteNonQuery()
            conn.Close()
        End If
    End Sub



Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Work1()
        TextBox1.Select()
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        ComboBox1.Text = ""
    End Sub

Open in new window


I got this error??

MySql.Data.MySqlClient.MySqlException: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') rsv.tbl_inventory SET item_quantity=''1'' WHERE item_code=''213349-001''' at line 1'

Any help thanks
0
I have a win10 system that updates a mysql db with mysql stored procedures, executed from vba in an access app on this system. This has worked fine for several years. with this update we also updated the mysql ODBC drivers from mysql 5.1 to mysql 5.3 (64bit windows, 32bit access). The connection to the mysql tables are fine, but the SP execution doesn't work and there are no error msgs.
here is the code for one SP

Dim cnnSQL As New ADODB.Connection
Dim cmdSQL As ADODB.command
 
Set cnnSQL = New ADODB.Connection
  'cnnSQL.Open "DRIVER=MySQL ODBC 5.1 Driver; SERVER=192.168.1.162; DATABASE=Wonder; UID=adminstation02;PASSWORD=****; OPTION=3"
  cnnSQL.Open "DRIVER=MySQL ODBC 5.3 ANSI Driver; SERVER=192.168.1.162; DATABASE=Wonder; UID=adminstation02;PASSWORD=****; OPTION=3"

    Set cmdSQL = New ADODB.command
    With cmdSQL
        .ActiveConnection = cnnSQL
        .CommandType = adCmdStoredProc
        .CommandText = "emptyc3"  ' name of mysql stored procedure
       ' .Parameters.Append .CreateParameter("ReturnValue", adInteger, adParamReturnValue)
      '  .Parameters.Append .CreateParameter("@ContactID", adInteger, adParamInput, , ContactID)
        .Execute
     '   If .Parameters(0).Value = -1 Then
     '       MsgBox "Cannot delete record"
     '   Else
     '       MsgBox "Record deleted."
     '   End If
    End With
   
    Set cmdSQL = Nothing
    cnnSQL.Close
    Set cnnSQL = Nothing
Any help greatly appreciated
0
Hi,

I am trying to update my database with a small icon image.

The column I try to update is set to type LONGBLOB.

I make this query in MySequel: UPDATE stednavne_copy SET `Kort`= (LOAD_FILE('Peter Kromans MacBook Pro (2)/PK MacBook Pro 2017/Brugere/pkr/Skrivebord/places-icon-5_18x18.png'))

And it returns the message: "No errors, 2180 rows affected"

But nothing goes into the database - and the entire column is set to value NULL.

What am I doing wrong?
0
H,

Im curious what is the best way to implement an options table.

I am currently adding more and more user options to an app. I am currently using a mysql table to organize the options.

Im curious if it is best practice to create an increasing larger table or something else?

Each option does have a default and if the user has not opted to change from the default the table will remain empty for that user.

what is the best practices? and or any ideas

thanks
0
Should we still run MySQLi? Does this mean no further security or any other types of developments would be done on it?

I think this is being done because of Oracle supposedly doing something to MySQL performance. I don't know that seemed to blow over.

Should I start using MariaDB? It seems to be what all the young trendy guys are doing.

I'll admit prepared statements are easier to write in PDO, but PDO doesn't support $mysqli->num_rows(); which makes it worth it's weight in gold for me.

Please provide assistance on this subject.
0
Hello! This is my first post here so please don't hesitate to let me know if my etiquette is lacking in any way.

In a large mysql table, I have 3 fields that store information pertaining to a timeline, and I would like to apply custom ordering to all of them.

The "Date" column holds either a year OR an era name. There are 3 options for the era name in this example: Early Era, Mid Era, and Late Era. If a year is present in the "Date" column, in the same row the "Era" and/or "Era2" columns may optionally have an era name. There will never be a value in the "Era2" column without there also being a value in the "Era" column.

I would like to perform the following sort:

- I would like to put rows with all three columns empty at the very bottom.

- I would then like all the "Date" columns that have only a year -- with "Era" and "Era2" columns in that row empty -- toward the bottom. The year may be sorted in ascending order.

- Then, I would like to order any column with an era name ("Date", "Era", or "Era2" column) with a predefined custom order (see below). Rows with a year defined in the "Date" and a value in the "Era" or "Era2" columns will sort before "Date" columns which hold only an era name.


Example of Predefined Order:

     ("Early Era","Mid Era","Late Era")

Open in new window



Current Unsorted Table:


Open in new window

0
Enroll in October's Free Course of the Month
LVL 10
Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Hi experts, I'm trying to get the next availalbe free IP address from a database, using a GoLang Function. This is what I have so far:

func GetNextFreeIP() string{
	db := dbopen()
	addr, err := db.Query("SELECT `IpAddress` FROM `FrontFacingIPs` WHERE `Avlilable` = 1 LIMIT 1")
	if err !=nil {
		fmt.Println("Couldn't find free IP, Error message is", err)
	}
	return addr
}

Open in new window


However, my IDE throes the following error:
"Cannot use addr (type *Rows) as type string"

**You can assume that the query is correct (I tested it) and that there are free IP Addresses (The function that calls this functions checks it beforehand)
0
I have a task to write config file for MySQL to tune it to work on computer with 32 GB RAM. Which settings should I tune in SQL config file?
0
I have two mysql databases on the same mysql server - with identical structures - differing only  in data content.  They each have customer information -  one for year 2016 and the other for year 2017. Objective is to
- list the customers who were active in 2016, but are inactive in 2017...... Lost Customers
- list the customers who are active in 2017 but inactive  in 2016........  New Customers

There are no database  technical problems - permissions etc..   I need help with

     How to manipulate two record sets (2016 and 2017) to create tables of lost and new customers. I am trying a 'lists' approach as you will see from the code attached - idea being to create and  manipulate  lists  from each recordset. I able to create the lists, but not to subtract  them. Printout attached shows lists for 2016 and 2017)

    If the LISTS approach is not the best,  I would ask for advice on alternative

    Many thanks for your help

    James Kane
    =======================================================================================

    PRINTOUT OF  TWO LISTS PLUS  TABLE
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    
    <cfparam name="nom" default="0">
    <cfparam name="ListCommon" default="0">
    <head>
    
    
    <!---record sets for year 18 and 17--->
    <cfquery name="activemembers_18" datasource="office_18">
    SELECT civilites, nom, 
    

    Open in new window

    0
    I'm converting from Microsoft SQL Server to Maria DB and trying to get the syntax right for a basic stored procedure that takes one parameter.  I'm getting an error however shown in the attached image.

    DELIMITER $$
    
    CREATE PROCEDURE p_GetDistanceRangeById (
    
      IN p_distanceRangeId tinyint unsigned
    )
    			
    BEGIN
    	
        
    	SELECT
    		distance_range_id,
        distance,
        distance_text,
        distance_unit,
        locale
      FROM Distance_Range
      
      WHERE distance_range_id = p_distanceRangeId;
    
     END $$
    
    DELIMITER;
    

    Open in new window

    0
    I want to connect Android and iOS apps to a MySQL database.

    Is it possible to secure the API with cryptography?

    Wouldn't it be more secure to use a static IP address and only accept remote connections from that address?

    Are there providers that do this to where we could have 10 connections on one app or would it be easier to deploy a virtual server and connect the phones to it?
    0
    I have this query which works fine on my mac running mysql, but as soon as i run it on my lunix server it fails.
    SELECT G.slideGroupID, G.slideGroupTitle, G.slideGroupDirectory, G.slideGroupPresenter, MIN(S.slideNumber), S.slideLocation
    FROM slidegroup G
    LEFT JOIN slide S ON G.slideGroupID = S.slideGroupID
    GROUP BY G.slideGroupID
    ORDER BY G.slideGroupOrder
    

    Open in new window


    #1055 - Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'stinteractive_3.S.slideLocation' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

    If I remove S.slideLocation from the select it works.... but I need it.

    Can you help?

    Steve
    0

    MySQL Server

    46K

    Solutions

    22K

    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.