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

imported an excel file (csv) into a database. IS there any way to cancel the data import. Someone accidentally imported a batch file into a mysql database. They imported client ids and choose the wrong client on the drop down when importing. The tables are modified as well. I am not familar with the setup of the tables as this is not my area. Would I have to go into the backups and take earlier copy ? In addition, the database was updated throughout the day so a backup would override updates. . I can view the front end of mysql how can I view the backend and get to the workbench if I need to query the database for these records? Thanks in advance Any ideas on how to resolve this erroneous excel import?
0
Hi!

I'm creating a Maria DB project (MySQL) using C#, and I have a need to get the primary key Id from a row I just insert into a table.

I've looked at several solutions on the internet and I'm not having any success.  They are either unreliable (documented errors) or do not work with MySql/Maria.

This is my last effort:


 MySqlCommand cmd = new MySqlCommand("INSERT INTO bbb.traveldocuments (CustomerID,TDNumber,TDDateCreated,TDMoveStart,TDMoveEnd,TDJobNumber,TDPermitType,TDNotes)" + "VALUES(@CustomerID,@TDNumber,@TDDateCreated,@TDMoveStart,@TDMoveEnd,@TDJobNumber,@TDPermitType,@TDNotes); SELECT SCOPE_IDENTITY() ", con);

.
.
.
cmd.Parameters.AddWithValue("@TDNotes", txtBox_TD_Notes.Text);

cmd.ExecuteNonQuery();
int insertedID = Convert.ToInt32(cmd.ExecuteScalar());


When I run the above code I get:

MySql.Data.MySqlClient.MySqlException: 'FUNCTION SCOPE_IDENTITY does not exist'

Are there any suggestions I can try?

Thank you in advance.

G
0
I am using this data set query that is generating error , The dated diff function requires 3 arguments(s). Incorrect syntax near Label 1. Incorrect syntax near 'Group Order'. The Query generates data for a DemographicsPar2(parameter. I am building a ssrs has board that uses this parameter.

SELECT DISTINCT 
                  'Age' DemographicGroup, 
CASE 
WHEN DATEDIFF(YY, DOB, @ReportDate) < 25 THEN '<=25'

 WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 25 AND 35 THEN '26-35' 
WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 36 AND   45 THEN '36-45' 
WHEN DATEDIFF(YY, DOB, @ReportDate) > 45 THEN '46+' END Label, CASE 
WHEN DATEDIFF(YY, DOB, @ReportDate) < 25 THEN 1 
WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 25 AND 35 THEN 2 
WHEN DATEDIFF(YY, DOB, @ReportDate) BETWEEN 36 AND 45 THEN 3 
WHEN DATEDIFF(YY, DOB, @ReportDate) > 45 THEN 4
END GroupOrder

INTO        #Demo2
FROM     [dbo].[Dim_Borrower]
UNION
SELECT DISTINCT 'Marital Status', MaritalStatus, 1
FROM     [dbo].[Dim_Borrower]
UNION
SELECT DISTINCT 'Race', Race, 1
FROM     [dbo].[Dim_Borrower]
UNION
SELECT DISTINCT 'Sex', Sex, 1
FROM     [dbo].[Dim_Borrower]
                      SELECT *, DemographicGroup + ' - ' + Label RealLabel
                      FROM     #Demo2
                      WHERE  DemographicGroup IN (@Demographics1)
                      ORDER BY DemographicGroup

Open in new window



When I run this query on the SSRS query Designer, the output shows Three columns. DemographicGroup / Label / GroupOrder/ Real Label ..
0
Hi Team,

I need to below information in MYSQL database , Iam new to this . I know how to drop an constraint in oracle and the data dictionary views , but  I need to know the commands
1. Drop check constraint , primary key constraint , NOT NULL , foreign key constraint in  MYSQL
2. data dictioanry view to get the constraints defined on a table .
0
I have a Database and this SQL:
select `sponsorship1`,`sponsorship2`,`sponsorship3`,`sponsorship4`,`sponsorship5`,`sponsorship6`,`sponsorship7`,`sponsorship8`,`sponsorship9`,`sponsorship10`,`sponsorship11`,`sponsorship12` from gala

Open in new window

Returns this data (as shown using JSON):
    [{"sponsorship1":"1","sponsorship2":"1","sponsorship3":"1","sponsorship4":"0","sponsorship5":"1","sponsorship6":"0","sponsorship7":"1","sponsorship8":"1","sponsorship9":"1","sponsorship10":"1","sponsorship11":"1","sponsorship12":"1"},
     {"sponsorship1":"0","sponsorship2":"0","sponsorship3":"0","sponsorship4":"0","sponsorship5":"0","sponsorship6":"0","sponsorship7":"0","sponsorship8":"0","sponsorship9":"0","sponsorship10":"0","sponsorship11":"0","sponsorship12":"1"},
     {"sponsorship1":"0","sponsorship2":"0","sponsorship3":"0","sponsorship4":"0","sponsorship5":"0","sponsorship6":"0","sponsorship7":"0","sponsorship8":"0","sponsorship9":"0","sponsorship10":"0","sponsorship11":"0","sponsorship12":"1"},
     {"sponsorship1":"0","sponsorship2":"0","sponsorship3":"0","sponsorship4":"0","sponsorship5":"0","sponsorship6":"0","sponsorship7":"0","sponsorship8":"0","sponsorship9":"0","sponsorship10":"0","sponsorship11":"0","sponsorship12":"1"},
     {"sponsorship1":"0","sponsorship2":"0","sponsorship3":"0","sponsorship4":"0","sponsorship5":"0","sponsorship6":"0","sponsorship7":"0","sponsorship8":"0","sponsorship9":"0","sponsorship10":"0","sponsorship11":"0","sponsorship12":"0"},
     

Open in new window

0
Hi,

I have a small method that pulls the largest number out of a Column that is in a table.  It then displays it in a couple of text boxes.

The code works good, but in testing I want a decimal number (xx.x) but only getting an integer right now.

Here is the complete Method:


using (MySqlCommand cmd = new MySqlCommand("SELECT MAX(TDNumber) FROM bbb.traveldocuments", con1))
            {

                con1.Open();

                object result = cmd.ExecuteScalar();
                result = (result == DBNull.Value) ? null : result;
                decimal blah = Convert.ToDecimal(result);
                MessageBox.Show(blah.ToString());   // something to show me what the value of blah is at this point
                txtBox_TD_MAX_NUMBER.Text = blah.ToString();
               
                con1.Close();

                decimal newtd = blah + 1;
                txtBox_TD_nextTDNumber.Text = newtd.ToString();


            }

It's a little complex since my original code was crashing if the value was null (empty database).  Now I get a zero, which isn't bad, just wondering if it's because my Database is empty and the 0 is actually a null?

The format of the Column is 10.1 decimal unsigned.

Thanks!

G
0
Hi,

I have a subform in Ms-Access, with the following information:

Product ID, Description,  Quantity,  Price, Total (Calculated field).

At the bottom of the form I have a Grand Total calculation.

However, when I change the Quantity, the Grand Total is not updating one time.  It is when I press enter(s) until a new record that the Grand Total is updated.

I know that "me.refresh" or "cmdruncommand accmdrefresh" after update (event) Quantity could be a solution.
However, if if use the "Refresh command", the form blinks and it does not look professional (at least for my point of view) when a user types information and the blinking blinking appears.

Is there any other way of getting the Grand Total updated, avoiding the blinking?

Thank you,
0
I am trying to import a .sql into a blank database in PHPMyAdmin
The Collation is utf8_unicode_ci

The database tables in the .sql file are:
ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I get a collation error telling me that the collation format is unrecognised

How do I proceed with this, please?
0
I am having trouble adding names to the database. The page that displays the contents of the database works fine. I set error reporting to ALL and still can't get the addition to the database to go through. Could someone point me in the right direction? Attached are the files. Thank you.
simpledb.zip
0
Hi,
How can I avoid ms-access form from Blinking?
I have an Invoice (FormInvoiceHead), with some related fields: Customer, date, address....
Everytime I select the Customer from a dropdown list, the form is updated and in the process it starts (the form and sub_form) blinking.  And it does not look professional.

Note:   I don't have any requery or refresh code after update.

Please advise,

Thank you,
0
Hi,

I am using MS-ACCESS as a front end.  MySql as Back end.

I have a DELETE query, to delete the content of a Table (No conditionals on the query at all, so delete all the records in the table).

But for some reason, the query is deleting all the records, except one record.   I executed the DEL query again an still not able to delete that specific row or record.

Also, the table shows #delete# in all the fields if I open the table, but if I refresh the table, the same record appears again.

Please advise.
Thank you.
0
I am googling a term but not finding it, let's say I cam Creating a table which has one column  and I want the next column to have the same or calulated value, let's say I have a date and in the second column I want it to show the name of the day (I know the functions just looking for teminology)

date  |day of the week
1/1/1 | sunday

Another example could be one table equals an intiger then the next column is some calucation let's column1 +1

col1|col2
1       |col1+1 (it should only show 2)
0
I have written an autosuggest function in Javascript (plain javascript).

This autosuggest is a single box that searches a database table of about 3.5 million cities (including towns and villages) - the database actually also details region, and country but this isn't important.

Asking AJAX requests to search such a database takes many seconds per request and this is obviously too slow.

My immediate thoughts on how to speed this up as as follows:

Create separate database tables corresponding to each letter of the alphabet, i.e. 26 tables.

If the first letter the use types is 'a', consult the table with places beginning with 'a' from that point on.

In theory this would make the request 26 times faster?

My first question is:

Is the a sensible approach or am I missing something?

Furthermore, if that is still not fast enough,

take the table, for example all places beginning with 'a', and split that into a further 26 tables; i.e.

All places beginning with aa, ab, ac, ad........ az

My second question is:

Is this a sensible and logical approach? Is there a limit to the number of tables on can have in mysql (there would be 26x26 = 676 tables in this case)?


I am aware of indexing tables to speed them up, so the approach above would be 'on top of' indexing the tables.

I look forward to hearing from you.

thank you
James
0
Using centos7, mysql 5.7

I restored a db in mysql and now I can't access the root login again

I tried to do this:
https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

and it didn't work
I added user=root in the my.cnf file under /etc/
any ideas?
0
elcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.29 MySQL Community Server (GPL)

I was able to login as mysql -u root -p
with the password at that prompt
I also created another user with a password
I restored a db dump with the second user... then i attempted to login to mysql as root but now I'm not able to ... what happened and how do I login back to mysql?
0
Hello,
Trying from the Mysql side get PHP session info. My PHP is ....ancient.  I'm writing a trigger and the trigger need to store some unique info about what PHP session that the trigger belongs to.

Regards Paer
0
Best way to import daily 2-3 fields data from one table to another linked table in access

We have a database with attendance. I have linked the required table to access. We need to import the data from a query to a linked table in the same access .
0
Refer to attached.

After setting the ssl_type to 'ANY' as per CIS hardening
benchmark for our mySql, access was denied.

What's missed?

We're running RHEL 7 OS
mySql_appmssl2.jpg
0
Hi, I am using 5.5.47-MariaDB.
I need to modify an INSERT query.
The original query is:

INSERT into ${acct_table1} (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctS
essionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtoco
l, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port-Id}',
'%{NAS-Port-Type}', '%S', '0', '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{NAS-Port-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%
{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')

Open in new window


A tipical entry is:

INSERT into radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('c1-huaw00304000500000f3def0AAADvm', '1e7a45zec33ffd2a', 'USERNAME@REALM', '', '10.9.1.3', '', 'Ethernet', '2020-01-28 12:16:10', '0', '0', 'RADIUS', '102400000', '', '0', '0', '0/4 vlanid 5 pppoe 57411', 'xx:xx:xx:xx:xx:xx', '', 'Framed-User', 'PPP', '202.44.111.22', '0', '0');

Open in new window


I need to match the "vlanid" number an put the string "service", for example:

0/4 vlanid 5 pppoe 57411

Open in new window


should be

service5

Open in new window


How can I do this? thankyou!
0
Trying to upgrade Mysql 8.0.16 to 8.0.19 (latest) but i cant find any instruktions anywhere, only how to do  it from 5.17.
0
I have 3 tables
users -> users.id - users.email - users.username - users.password
reports -> reports.id - report_types.id - reports.date - reports.url
report_types -> report_types.id - report_types.name
tokens -> tokens.id - users.id - reports.id - tokens.start - tokens.end

I have a select box that contains the reports, it must show all reports but I want to option text color to be black when a token exist and light gray when a token table is NULL

<option value="reports.id">report_types.name - reports.date</option>

It seems to me that there will be a nested select but not sure

a more challenging part is the Select for the tokens needs to be in a if statement that returns false.

If more clarification is needed let me know.
0
Hello, Experts,

I am writing a SQL query in MySQL (mainly a SQL Server user - getting feet wet in MySQL as of recent) where I am looking to rank total sales (calculated field) from highest to lowest. In SQL Server you were able to do this with the RANK function. Based on my research, I cannot seem to find a decent answer on how to properly accomplish this in MySQL.

Here is what I am after.

Output:

Column Names: StoreID, Rank, Total_Sales
                             TX001, 3, 15000.00
                             AZ005, 1, 60000.00
                             CO010, 2, 22000.00

Currently, here is my working SQL code that I have composed. My output is coming out with ranking being incorrect. I.E: Rank = 1 ===> 15000.00 and Rank 3 ====> 60000.00

set @rank := 0;

select

    s.nickname,
    @rank := @rank + 1 as rank,
    sum(ro.total_sales - ro.taxes) / 100 as sales



from rpt_repair_order ro
inner join shop s
on ro.shop_id = s.id

where  DATE(CONVERT_TZ(ro.posted_date, 'UTC', s.time_zone_id)) > '2020-01-19' and  DATE(CONVERT_TZ(ro.posted_date, 'UTC', s.time_zone_id)) <= '2020-01-25'

group by s.nickname

order by sales asc;

Open in new window

0
Hi Experts

Could you point a way to add a FK to an existent MySQL table?

Accordingly to:
img002
I first added a new column status_id

The above table must to receive a FK  column from this other:
img003
What I had tryed:


ALTER TABLE padronizacaorevendas
ADD FOREIGN KEY (status_id) REFERENCES statusparticipantes(id);

Cannot add or update a child row: a foreign key constraint fails (`local_cheiodegaz`.`#sql-15d8_16c`, CONSTRAINT `#sql-15d8_16c_ibfk_1` FOREIGN KEY (`status_id`) REFERENCES `statusparticipantes` (`id`))

Open in new window


Thanks in advance
0
Mysql VPN.
HI..how do I share or connect Mysql using a Internet private VPN?   NOTE: I ms access as a front end....also Work bench to manage
0
Hi..as you know ms-access saves the information automatically by default.   How do I SAVE the information with a button instead of save it automatically?   I mean...the user will save after changes.  Take into account that I am using a Head form and a detail form.   For instance....an Invoice...it has FormInvoiceHead...  and FormInvoiceDetail....both forms linked.....Father-child....   how do I SAVE the information using  both forms....taking into account that is related info??   Note:  I use mysql as a back end.
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.