Having issues with no transact strict mode in mysql

Databases
1

Please am facing issues with NO STRICT TRANS in my database, this is found in the Sql mode settings. And if it is in this mode it forces all the columns to be filled if not the insert function will not work.

This forces me to include all columns in my insert sql and then use empty data on them just to make sure my insert work.
The worst is that i will edit the sql mode and remove the no strict transac yet after sometimes or if i restart my Apache and mysql it comes again.
Please whats the permanent solution to this and what is really the work of that strict trans in mysql sql mode edit?

2

could you please show the query before and after you made those changes

3

@r937
It does happen in all my queries but i first started noticing it a time i tried using group as my column name it showed an error saying group is a mysql keyword.
Then i after i changed the column name, the problem has been there even on another table.

The sql mostly are

INSERT INTO tablename (email, password, family) VALUES ('example@yahoo.com', 'fhgfggggg', 'orchild')

The problem is not usually the code because after i have edited the sql mode and clean up the no strict trans line and run the insert code it works but after some time it goes back to the strict trans again

4

If email, password and family are the only columns i have in the table it will insert perfectly but i i have other columns and didn’t include it in the insert then it will not insert and will not throw error

5

there must be something else going on, because that’s not what’s supposed to happen

please do a SHOW CREATE TABLE so that i can see the column definitions for these other columns

also, what’s your mysql version number?

6

All other columns are VARCHAR data types with 120 value length only nothing else.
Secondly i don’t know how to generate create table code from a table, i only use mysql in creating my tables from scratch, select the number of columns and there data types thats all.
Perhaps you can show me how to print or generate a create table code from existing table. I would really love to know it

7

Mysql version is 4.8

8

run this –

SHOW CREATE TABLE tablename
9

whoa, that’s ancient!!

1 Like
10

Please tell us you’re not storing passwords in plain text.

11

Lol, could it be the cause? Am using it on local host

12

Nop am not Sir, just an illustrations, am using php password_hash() i learnt is stronger than MD5 but sad enough is not in mysql yet

1 Like
13

I did but is not showing full code, i was thinking the full code should appear on the screen so i can copy and paste

14

any reason you can’t upgrade? might make all your problems go away

15

let me guess, you’re using phpmyadmin

the full text is all there, i assure you

16

Your SQL query has been executed successfully.

SHOW CREATE TABLE myusers

myusers CREATE TABLE myusers (
id int(20) NOT NULL A…

17

You guessed right!

18

The dot … shows an incomplete code, besides am only seeing id but not other columns in the table, i know how a create table codes looks like and thats what i expect to see but this one is a half code, so what do we do now?

19

This is exactly what the SQL Mode edit looks like

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

So it took me roughly 3days to discover that the problem was actually an effect of STRICT_TRANS_TABLES which i still do not know what it means or its function, even after deleting it from the SQL mode edit and then save it everything works fine but later am back with that settings as if is a default Mysql SQL mode settings

20

First thing is to get your local dev up to current standards. If you are on windows, install Laragon and do your Dev from there.

https://laragon.org/

1 Like