Description:
when mysql server is configured with safe update mode
connector net ignore safe update mode
How to repeat:
CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
use db;
CREATE TABLE `products` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`BASE_PRICE` double(10,4) NOT NULL DEFAULT '0.0000',
`BASE_DISCOUNT` decimal(10,4) NOT NULL DEFAULT '0.0000',
`BASE_COST` double(10,4) NOT NULL DEFAULT '0.0000',
`CreatedBy` bigint(20) NOT NULL,
`ModifiedBy` bigint(20) DEFAULT NULL,
`ModIn` int(6) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
then run
on workbench
UPDATE PRODUCTS SET ModifiedBy=1,ModIn=9,BASE_PRICE =28,BASE_DISCOUNT=0,BASE_COST=22 and ID=10001;
the result will be
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
then
create c# winform project and add mysqldata.dll as reference
then try to update with previous statament
private string MyCurrentConnString()
{
return "server=127.0.0.1;port=3306;user id=root;password=yousef142;database=db;Convert Zero Datetime=True;SslMode=None";
}
private void button1_Click(object sender, EventArgs e)
{
decimal prc = 28.50m;
decimal disc = 0m;
decimal cst = 22.50m;
int usrid = 1;
int modin = 9;
long proid = 10001;
string sql = "UPDATE db.PRODUCTS SET ModifiedBy=@p1,ModIn=@p2,BASE_PRICE =@p3,BASE_DISCOUNT=@p4,BASE_COST=@p5 and ID=@p6;";
try
{
List<MySqlParameter> par = new List<MySqlParameter>();
par.Clear();
MySqlParameter p1 = new MySqlParameter("p1", MySqlDbType.Int64);
p1.Value = usrid;
MySqlParameter p2 = new MySqlParameter("p2", MySqlDbType.Int32);
p2.Value = modin;
MySqlParameter p3 = new MySqlParameter("p3", MySqlDbType.Double);
p3.Value = prc;
MySqlParameter p4 = new MySqlParameter("p4", MySqlDbType.Decimal);
p4.Value = disc;
MySqlParameter p5 = new MySqlParameter("p5", MySqlDbType.Double);
p5.Value = cst;
MySqlParameter p6 = new MySqlParameter("p6", MySqlDbType.Int64);
p6.Value = proid;
par.AddRange(new MySqlParameter[] { p1, p2, p3, p4, p5, p6 });
int m = MySqlHelper.ExecuteNonQuery(MyCurrentConnString(), sql, par.ToArray());
textBox1.Text = "Updated Record " + m.ToString();
}
catch(MySqlException ex)
{
MessageBox.Show(ex.Message, "update", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
Suggested fix:
i don`t know
Description: when mysql server is configured with safe update mode connector net ignore safe update mode How to repeat: CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */; use db; CREATE TABLE `products` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `BASE_PRICE` double(10,4) NOT NULL DEFAULT '0.0000', `BASE_DISCOUNT` decimal(10,4) NOT NULL DEFAULT '0.0000', `BASE_COST` double(10,4) NOT NULL DEFAULT '0.0000', `CreatedBy` bigint(20) NOT NULL, `ModifiedBy` bigint(20) DEFAULT NULL, `ModIn` int(6) unsigned NOT NULL DEFAULT '1', PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; then run on workbench UPDATE PRODUCTS SET ModifiedBy=1,ModIn=9,BASE_PRICE =28,BASE_DISCOUNT=0,BASE_COST=22 and ID=10001; the result will be Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. then create c# winform project and add mysqldata.dll as reference then try to update with previous statament private string MyCurrentConnString() { return "server=127.0.0.1;port=3306;user id=root;password=yousef142;database=db;Convert Zero Datetime=True;SslMode=None"; } private void button1_Click(object sender, EventArgs e) { decimal prc = 28.50m; decimal disc = 0m; decimal cst = 22.50m; int usrid = 1; int modin = 9; long proid = 10001; string sql = "UPDATE db.PRODUCTS SET ModifiedBy=@p1,ModIn=@p2,BASE_PRICE =@p3,BASE_DISCOUNT=@p4,BASE_COST=@p5 and ID=@p6;"; try { List<MySqlParameter> par = new List<MySqlParameter>(); par.Clear(); MySqlParameter p1 = new MySqlParameter("p1", MySqlDbType.Int64); p1.Value = usrid; MySqlParameter p2 = new MySqlParameter("p2", MySqlDbType.Int32); p2.Value = modin; MySqlParameter p3 = new MySqlParameter("p3", MySqlDbType.Double); p3.Value = prc; MySqlParameter p4 = new MySqlParameter("p4", MySqlDbType.Decimal); p4.Value = disc; MySqlParameter p5 = new MySqlParameter("p5", MySqlDbType.Double); p5.Value = cst; MySqlParameter p6 = new MySqlParameter("p6", MySqlDbType.Int64); p6.Value = proid; par.AddRange(new MySqlParameter[] { p1, p2, p3, p4, p5, p6 }); int m = MySqlHelper.ExecuteNonQuery(MyCurrentConnString(), sql, par.ToArray()); textBox1.Text = "Updated Record " + m.ToString(); } catch(MySqlException ex) { MessageBox.Show(ex.Message, "update", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } } Suggested fix: i don`t know