MYSQL error: Unknown column 'guid' in 'NEW' mysql

Question

MYSQL error: Unknown column 'guid' in 'NEW' mysql

vj78 21

I have this sql statement and it gives me error: Unknown column 'guid' in 'NEW' mysql

SQL Statement in python: INSERT INTO user (username, userpassword , admin) VALUES (%s, %s, %s )

Table structure is:
userid
username
userpassword
userguid
admin

userid is auto increment
userguid is uuid() function

Anurag Sharma 17,636 Reputation points

2021-04-11T20:08:41.923+00:00

Hi @vj78 , welcome to Microsoft QnA forum.

Could you please provide table script as well as python code to replicate it exactly?
vj78 21 Reputation points

2021-04-11T21:41:37.117+00:00

I realized it created a trigger in mysql by default when trying to create this guid column. I deleted that it does not error out. But I still need to figure out how to code the guid funtion. I am just working with MYSQL at this point to get it to work in MYSQL.

Table script:

CREATE TABLE users (
username varchar(50) NOT NULL,
userpassword varchar(50) NOT NULL,
admin tinyint(1) DEFAULT NULL,
userguid varchar(100) NOT NULL DEFAULT uuid(),
userguid1 varchar(100) NOT NULL DEFAULT 'select uuid();'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
vj78 21 Reputation points

2021-04-11T21:43:24.867+00:00

Python Code:

def adduser(self, username, password, admin):
mydb = mysqldb.connect(host="xxxxxxxxxx", user="xxxxxxx",password="xxxxxxxxxxx", db="xxxxxxxxx",
use_unicode=True,charset="utf8")
mycursor = mydb.cursor()
myparams = (username, password, )
print(myparams)
mysql = mycursor.execute("INSERT INTO user (username, userpassword ) VALUES ('xxxxxxxxxxxx', 'xxxxxxxxxx' )")
myresult = mycursor.execute(mysql, myparams)
mydb.commit()
return (myresult)
vj78 21 Reputation points

2021-04-11T21:56:36.47+00:00

I tried using and including the userguid UUID function in the python code instead of MYSQL function and it works nicely.

Now sure, what is best way.

Doing it in python is working.
vj78 21 Reputation points

2021-04-11T22:37:45.887+00:00
I updated my python code (as below):
Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s, %s, uuid())' at line 1

def adduser(self, username, password, admin):
mydb = mysqldb.connect(host="xxxxxx", user="xxxxxx",password="xxxxx", db="xxxxx",
use_unicode=True,charset="utf8")
mycursor = mydb.cursor()
myparams = (username, password, )
print(myparams)

mysql = mycursor.execute("INSERT INTO user (username, userpassword, userguid ) VALUES (%s, %s, uuid())") myresult = mycursor.execute(mysql, myparams) mydb.commit() return (myresult)

Answer accepted by question author

0 additional answers

Your answer

Anurag Sharma 17,636 Reputation points

2021-04-11T20:08:41.923+00:00

Hi @vj78 , welcome to Microsoft QnA forum.

Could you please provide table script as well as python code to replicate it exactly?
vj78 21 Reputation points

2021-04-11T21:41:37.117+00:00

I realized it created a trigger in mysql by default when trying to create this guid column. I deleted that it does not error out. But I still need to figure out how to code the guid funtion. I am just working with MYSQL at this point to get it to work in MYSQL.

Table script:

CREATE TABLE users (
username varchar(50) NOT NULL,
userpassword varchar(50) NOT NULL,
admin tinyint(1) DEFAULT NULL,
userguid varchar(100) NOT NULL DEFAULT uuid(),
userguid1 varchar(100) NOT NULL DEFAULT 'select uuid();'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
vj78 21 Reputation points

2021-04-11T21:43:24.867+00:00

Python Code:

def adduser(self, username, password, admin):
mydb = mysqldb.connect(host="xxxxxxxxxx", user="xxxxxxx",password="xxxxxxxxxxx", db="xxxxxxxxx",
use_unicode=True,charset="utf8")
mycursor = mydb.cursor()
myparams = (username, password, )
print(myparams)
mysql = mycursor.execute("INSERT INTO user (username, userpassword ) VALUES ('xxxxxxxxxxxx', 'xxxxxxxxxx' )")
myresult = mycursor.execute(mysql, myparams)
mydb.commit()
return (myresult)
vj78 21 Reputation points

2021-04-11T21:56:36.47+00:00

I tried using and including the userguid UUID function in the python code instead of MYSQL function and it works nicely.

Now sure, what is best way.

Doing it in python is working.
vj78 21 Reputation points

2021-04-11T22:37:45.887+00:00

I updated my python code (as below):
Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s, %s, uuid())' at line 1

def adduser(self, username, password, admin):
mydb = mysqldb.connect(host="xxxxxx", user="xxxxxx",password="xxxxx", db="xxxxx",
use_unicode=True,charset="utf8")
mycursor = mydb.cursor()
myparams = (username, password, )
print(myparams)

mysql = mycursor.execute("INSERT INTO user (username, userpassword, userguid ) VALUES (%s, %s, uuid())") myresult = mycursor.execute(mysql, myparams) mydb.commit() return (myresult)

Answer 1

Anurag Sharma 17,636

Hi @vj78 , thanks for your patience.

Can you please try below code once? Here I passing 'myparams' in the same execute statement instead of new line:

myresult = cursor.execute("INSERT INTO users (username, userpassword, userguid ) VALUES (%s, %s,  uuid())",myparams)  
return (myresult)

Please let me know if it works.

----------

If answer helps, please mark it 'Accept Answer'

Anurag Sharma 17,636 Reputation points

2021-04-15T07:42:11.253+00:00

Hi @vj78 , just checking if answer helped.
vj78 21 Reputation points

2021-04-15T13:39:28.81+00:00

Yes, Thank you

Share via

MYSQL error: Unknown column 'guid' in 'NEW' mysql

0 additional answers

Your answer