question

vj78-8539 avatar image
0 Votes"
vj78-8539 Suspended asked AnuragSharma-MSFT answered

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

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

azure-database-mysql
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @vj78-8539, welcome to Microsoft QnA forum.

Could you please provide table script as well as python code to replicate it exactly?

0 Votes 0 ·

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;

0 Votes 0 ·

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)

0 Votes 0 ·

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.

0 Votes 0 ·

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)
0 Votes 0 ·

1 Answer

AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered

Hi @vj78-8539, 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'


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @vj78-8539, just checking if answer helped.

0 Votes 0 ·

Yes, Thank you

0 Votes 0 ·