sql_mode in phpmyadmin with mariadb

carlarogers

Member
Credits
243
mysql is one of the best things that ever happened to me. When I started out in this line of work, I wanted to get a copy of Oracle, which at that time was a word that meant "serious relational database, not a toy," on my computer so I could see it and someday get paid for being able to run it. The only barrier I encounted was the software licence fee, less than $20,000 as I recall, but anything over $1,000 was probably next to impossible for me. Some years went by, imagine my surprise to discover mysql FREE, doing everything I ever heard of wanting to do with Oracle, only it was FREE. Microsoft SQL SERVER was dirt cheap and thanks to having most of what it did developed by engineers not at micosoft, from whom Microsoft had bought it, and before Microsoft entombed it inside its layers of technology that simplified it by making it almost impossible to work with, for the low low low price of only a few hundred dollars provided you did not deploy it on too big a system but it wasn't FREE. Mysql was FREE, a serious DB, not a toy and not encumbered by any of those intricate iron boxes with weird connectors that Microsoft uses to make their systems interconnect and simultaneously, very difficult to integrate .

Then Oracle bought mysql. I knew that was bad, but all in all, time has proven the fears I had when oracle bought mysql have not materialized. Mysql stayed good to this day. Then some people with vision and skills I lack took it upon themselves to develop for the benefit of humanity and cost to themselves a fork from mysql, and I probably have it wrong, I think that is something like how we got MariaDB. I don't know why Debian and LAMP started showing up with Mariadb instead Mysql, but I do know a lot of agony was avoided thanks to MariaDB and MySQL both starting with the third letter in LAMP.


EXCEPT, freaking sql_mode
I love things that are put into my life against my will, that are mandatory because they help in ways I don't know I need. Mariadb helps me by defaulting to a sql_mode, something I did not know had options, yes I want sql mode...I have to say what kind of sql mode? Why? What kinds are there.... don't answer that.

As everyone knows, because everyone got the memo and everyone agreed on the night I went to sleep early, mariadb defaults sql_mode to strict, starting a few versions ago. Ok, it only took a few hours to figure out why I was getting messages instead of data.. it was because strict mode said it was not good enough to fail to provide explicit default values for every field whereas before that was fine. It happened the table I was trying to change, probably add a field... it didn't care that every field had a default value applied to it when the php application added records, I could not just add the field and say save.... some of this is out of sequence, because something else stopped working and there was no way I was going to have to go through all my code to make everything comply with strict sql_mode, and fortunately God loves me, which I know, because it took only a minute to add a line to the php-sql module I wrote and is used by ALL the sql in my system, which I was sure wouild make me world famous and rich someday, so that right after a connection is made to a database, a simple command sets the sql_mode to the non strict mode and life is good again, even though I have bypassed the protection so generously delivered by mariadb to my ignorant old fashioned and not rich world.

Sooooooooooooooooo back to phpmyadmin. Somewhere in phpmyadmin, it connects using mysqli, just like the php systems I made. I will be damed if I can figure out where it is in phpmyadmin it does this. I guess my next step is to start running some of those grep things that mean "search for a string in a bunch of files and let me know where you find them" -- why is that called grep?

Anyway, this might not be so bad afterall.

The reason I decided to start a thread is to ask if you can tell me the magic and to me, secret, method of telling maria db to please put itself into
TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION mode

instead of
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION mode

when mariadb starts......

Lest you suspect me of decadence, laziness, or the worst, abject ignorance, I have a defense. I have tried many times to add commands (maybe they are directives or settings) to various members of the my.cnf family of files and symbolic links mariadb disburses so copiously on my system. My latest attempt shows none of the fruits of research and study of the mariaedb configuration files I have diligently worked to master. I read the documenation here

and here

and synthesized the information to edit a file found on my Debian server at :
/etc/my.cnf

after verifying and considering options implied by the output of clues furnished by mariadb herself, in response to
/usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

where such output is
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

and so into /etc/my.cnf under [server], then when that did not work, under [mysqld] then when that did work work, under both, and this after trying other things the documentation suggested, I added
set sql_mode = "TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

and found upon restart of mariadb that restart failed but restart worked when I added this instead (no "set" at the start)
sql_mode = "TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

leading me to infer Mariadb sees me asking, begging, requesting, commanding

NOT STRICT MODE, turn that off, but not in my words, words and delimiters I am told by her creator are hers
sql_mode = "TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

And she restarts with no objection or error so i scramble into phpmyadmin and into the SQL command box I enter
SELECT @@sql_mode;

To which she responds, with a face so straight I know she mocking me:
STRICT_................................................

As soon as i see strict, I know phpmy admin is going to die when I try changing tables I and I know I had this working but something I don't know about has changed.


So, my question to you is, how do I either
A. make mariadb default sql_mode to not strict at startup

OR

B. Tell phpmyadmin we are running non strict sql_mode around here and I am the boss so get in line!

While you are reading this, I am going to grep-thrash into phpmyadmin to see if I can find where i can tell it to switch to non-strict mode before running my rquests to to add a field to a table so I don't have to gouge out my eyes to relieve the agony of setting defaults for al the fields in all the tables of all my databases

Any assistance you can provide will be appreciated immensely.

thankyou

p.s. I know I am wrong, but that doesn't mean I believe it anyway. This OP is really really funny. The fact you are not involuntary keeled over in laughter is not because this is not funny. Just remember that. Maybe someday you will sympathize even though you know sympathy is a disgusting habit no programmer has, proving you were right to suspect I have never programmed anything.... all i am saying right now is a moment where laughing is ok. beause what I wrote is hilarious.

Why does no one believe me when I tell them I am funny? Then people accuse me of being funny when I am being serious. This post is serious and funny.

Freaking sql_mode sucks. That is just common sense right there. and proven by science.

1603863305946.png


Fauci - the face of Science
 
Last edited:


wizardfromoz

Super Moderator
Staff member
Gold Supporter
Credits
7,568
1. First up, I found myself questioning my perception, in that it might be Carl A.Rogers over Carla Rogers, and you can enlighten us or maintain privacy as is your wish. :)

2. 2nd up, I am moving this to Database, where it is better suited for qualified answers, and won't "trickle off the board" so quickly. Viewers are notified and redirected, as you will see.

3. Third, you may be doing yourself a disservice with the nature of your Post. I find your writing interesting, but some who might otherwise help you, might find the above TL;DR (too long, don't read) in trying to establish the points where you seek help.

Buckle up your seatbelt, you're off to Database.

Good luck.

Chris Turner
wizardfromoz
 

f33dm3bits

Gold Member
Gold Supporter
Credits
7,600
I only read part of your topic so I have a basic idea of what it is about, I found it too long to read as @wizardfromoz already mentioned. You can try setting adding this to /etc/my.cnf -> sql_mode=NO_ENGINE_SUBSTITUTION and then restart mariadb. Also what version of mariadb are you running, this setting is default since mariadb-10.1.17.

If you can't get it to work in phpmyadmin I think it might be worth a try using adminer, it's a phpmyadmin alternative you may have better luck there.
 
Last edited:


Members online


Top