The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements.
Questions tagged [sql-mode]
102 questions
5
votes
1 answer
How to check the SQL_MODE in mysql for a single process?
How can I check what SQL_MODE is a process is using?
so I see all processed that are currently running using SHOW PROCESSLIST
What I need to do is to know the SQL_MODE that a process is using?
I want to make sure the client that I am using did not…
Mike
- 2,735
- 11
- 44
- 68
5
votes
2 answers
Font-locking for SQL-MODE inside of ORG-MODE not font-locking
Related to question: org-mode: fontify code blocks natively
I've got the latest org-mode and emacs versions as of November 1, 2012 (org stored in org-20121105).
I've also got the sql-mode that comes with emacs-24.
I've got fontification turned…
justingordon
- 12,553
- 12
- 72
- 116
4
votes
4 answers
How can set SQL_MODE permanently on my mac
I'm install MySql on my OsX 10.11 El Capitan. If I try to start my query I have a strange error if my query have Group By.
So if I try to execute this query from Terminal:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
I…
bircastri
- 2,169
- 13
- 50
- 119
4
votes
1 answer
How to set sql_mode hibernate
Is there a way to set the sql_mode (for MySql database) in Hibernate properties or in connection string?
Thanks,
Stefano
Stefano Cazzola
- 1,597
- 1
- 20
- 36
3
votes
2 answers
MYSQL: how to return all distinct row in mysql GROUP BY query
I have table T with some column and values
T
========================
id | name | g1 | g2
=========================
10 | abc | 1 | 1
14 | abc | 1 | 0
33 | abc | 1 | 0
42 | def | 1 | 0
52 | def | 1 | 1
63 |…
novanxyz
- 31
- 1
- 4
3
votes
3 answers
How to make Emacs sql-mode recognize MySQL #-style comments?
I'm reading a bunch of MySQL files that use # (to end-of-line) comments, but my sql-mode doesn't support them. I found the syntax-table part of sql.el that defines /**/ and -- comments, but according to this, Emacs syntax tables support only 2…
Ken
- 5,074
- 6
- 30
- 26
3
votes
1 answer
MySQL Can you override a server SQL mode at the database level?
We have a single MySQL 5.7 server that we need to have in a non-strict mode because some legacy databases require it. However, in our newest app, we'd like the database to enforce strictness (especially NO_ZERO_DATE). Is there a way to override the…
abeger
- 6,766
- 7
- 41
- 58
3
votes
4 answers
Why can't I change sql_mode permanently?
I an currently using mysql server 5.6 in windows 10. while using mysql, an error keeps appearing that goes as something like this "the following column does not have a default value" i read on the internet that i can solve this problem if i change…
mohamed
- 31
- 1
- 6
2
votes
4 answers
Understanding non standrad GROUP BY with ONLY_FULL_GROUP_BY sql mode set in MySQL
I understand that ANSI SQL requires non-GROUP BY attributes to be SELECTed with aggregate function.
For example, below is invalid in ANSI SQL:
SELECT name, MAX(salary), dept
FROM `employee_gb`
GROUP BY dept;
as name is not passed to any aggregate…
MsA
- 2,599
- 3
- 22
- 47
2
votes
1 answer
Not able to use GROUP BY with join query in MySQL
I need to execute the below Query but not able to do it.
SELECT *
FROM (SELECT *
FROM rider_status
order by created_date DESC) as riderActice
INNER JOIN rider_user ON rider_userId = rider_user.id
Where rider_user.isActive = 1
AND…
sofquestion 9
- 91
- 1
- 8
2
votes
1 answer
Can I avoid sql_mode on my query?
I see that I have this code at the end of a table that I want to insert on my db :
/*!40000 ALTER TABLE `to_import` ENABLE KEYS */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET…
markzzz
- 47,390
- 120
- 299
- 507
2
votes
2 answers
Emacs: How to visit all sql-mode buffers and set the appropriate sql-buffer
My typical usage of sql-mode in emacs is to:
a. open a foo.sql file and begin editing
b. decide I want to run it using the key bindings for sql-send-region
c. fire up my custom (db-connect) function to connect to an appropriate db and create a *SQL*…
Setjmp
- 27,279
- 27
- 74
- 92
2
votes
2 answers
SQL_MODE and TIME_ZONE when creating DATABASE
What do these options mean, and what are the possible choices when creating the database?
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
#------------------------------------------------------------
# Script…
L.G
- 1,458
- 4
- 14
- 24
2
votes
0 answers
ONLY_FULL_GROUP_BY disabled but still getting error 1055
I'm testing mysql server 5.7.17 and I have defined sql-mode as
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
Running each of these gives me the sql-mode defined:
SELECT @@sql_mode;
SELECT @@GLOBAL.sql_mode;
SELECT…
Alexander Criollo
- 21
- 3
2
votes
3 answers
MySQL won't start after changin my.cf
I'm working on a website and I'm getting this MySQL error:
"(...) this is incompatible with sql_mode=only_full_group_by (...)"
I've looked for an answer and I realized I had to change the sql_mode of my MySQL. So I've added the following line to…
Alexandre Justino
- 1,716
- 1
- 19
- 28