View All Products
Compute
Storage
Databases
Networking
Developer Tools
Delivery
Security
Services
Industries
Pricing
Community
Engage With Us
Community ▶ Questions ▶ Have an error in my SQL syntax
Log in to Ask a Question

Have an error in my SQL syntax

none

Hi, I was following this guides to set up mail.
As I use this comman 

GRANT SELECT ON mailserver.* TO 'mailuser'@'127.0.0.1' IDENTIFIED BY 'mypassword';

it shows

ERROR 1064 (42000): 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 '‘mypassword’' at line 1

My MySQL version is 15.1 Distrib 10.3.27-MariaDB
(the guide is using ver. 14.14)

Now I don't know how and where to find the correct syntax.
Can anyone help me out? THANKS A LOT!

Update: Everything's fine now! I delete all the 'and it worked.

8 Replies

I'm a little rusty on this but https://www.mysqltutorial.org/mysql-grant.aspx says it should be like this:

CREATE USER mailuser@127.0.0.1 IDENTIFIED BY 'mypassword';
GRANT SELECT ON mailserver.* TO mailuser@127.0.0.1;

…plus or minus a few quote marks.

If your mail server is not going to have a lot of users or volume, try using SQLite for this. Your pain will be a lot less! If you're not an SQL wizard…or you don't play one on tee-vee, SQLite is a great learning vehicle. https://sqlite.org

Also, don't forget that you have to set up SPF, DKIM & DMARC so that your server plays nice with the big boyz (Comcast, Gmail, et.al.).

-- sw

Hi, thank you for replying:)

I want to ask another question here.
As I was following this guide
I can not get my OpenDKIM worked.
I also try follow this guide because I saw one previous request from others said it worked.
But it didn't… I stock at the testing step by using this command

sudo opendkim-testkey -d mydomain.com -s default -vvv

it shows

opendkim-testkey: using default configfile /etc/opendkim.conf
opendkim-testkey: checking key 'default._domainkey.mydomain.com'
opendkim-testkey: 'default._domainkey.mydomain.com' query failed

Here's the status of OpenDKIM

 opendkim.service - OpenDKIM DomainKeys Identified Mail (DKIM) Milter
   Loaded: loaded (/lib/systemd/system/opendkim.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Thu 2021-02-04 04:36:35 CST; 38s ago
     Docs: man:opendkim(8)
           man:opendkim.conf(5)
           man:opendkim-genkey(8)
           man:opendkim-genzone(8)
           man:opendkim-testadsp(8)
           man:opendkim-testkey
           http://www.opendkim.org/docs.html
  Process: 4335 ExecStart=/usr/sbin/opendkim -x /etc/opendkim.conf (code=exited, status=78)

Feb 04 04:36:35 myhostname systemd[1]: opendkim.service: Control process exited, code=exited, status=78/CONFIG
Feb 04 04:36:35 myhostname systemd[1]: opendkim.service: Failed with result 'exit-code'.
Feb 04 04:36:35 myhostname systemd[1]: Failed to start OpenDKIM DomainKeys Identified Mail (DKIM) Milter.
Feb 04 04:36:35 myhostname systemd[1]: opendkim.service: Service RestartSec=100ms expired, scheduling restart.
Feb 04 04:36:35 myhostname systemd[1]: opendkim.service: Scheduled restart job, restart counter is at 5.
Feb 04 04:36:35 myhostname systemd[1]: Stopped OpenDKIM DomainKeys Identified Mail (DKIM) Milter.
Feb 04 04:36:35 myhostname systemd[1]: opendkim.service: Start request repeated too quickly.
Feb 04 04:36:35 myhostname systemd[1]: opendkim.service: Failed with result 'exit-code'.
Feb 04 04:36:35 myhostname systemd[1]: Failed to start OpenDKIM DomainKeys Identified Mail (DKIM) Milter.

Anyone on Debian10 have a solution for this?
or where should I go ask for help?
THANKS A LOT!

You write:

opendkim-testkey: using default configfile /etc/opendkim.conf
opendkim-testkey: checking key 'default._domainkey.allmyloveyogurt.com'
opendkim-testkey: 'default._domainkey.allmyloveyogurt.com' query failed

default._domainkey.allmyloveyogurt.com is a reference to the DNS TXT record you have to create holding the public signing key. Here's mine (domain name redacted):

stevewi:~ $ dig -t txt mailkey._domainkey.mydomain.com @ns1.linode.com

; <<>> DiG 9.10.6 <<>> -t txt mailkey._domainkey.mydomain.com @ns1.linode.com
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 8874
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 512
;; QUESTION SECTION:
;mailkey._domainkey.mydomain.com. IN    TXT

;; ANSWER SECTION:
mailkey._domainkey.mydomain.com. 86400    IN TXT  "v=DKIM1; h=sha256; k=rsa; c=relaxed/simple; p=MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAubf3LHUtzWmCXP7CsKsmzi0Q5nTd4UQr81Rre4aenNnJ9vzeejpMh6/Y1UOLvojYbEbSANEoDrEsidkUDkCaOWl1MI9Sit3hfaN1BYMylDGqx1R9Q7GniYqxsCGzlM1nGNTnaU6Z7UlvNTEZ9ojDjc32yEi69NWuUH5fc" "Gms5yQ4Lx2R2zy8mKqmGwkCZh4vLwnqeS9+Nw4bt3Nc29dx9CcXMYlrvnjinQROsgo2VDmL0JjrFzctgPvAkyXXf7oTM2SaUwQQGG+DgyI4OvV6M4FjGaDpLDnGZmIKADC7h/waAQIIWo77cCUwlWbRvgpgYtEB0Qr36EtxmT51aZ2i2wIDAQAB"

;; Query time: 95 msec
;; SERVER: <DNS Server IPv6 address redacted>)
;; WHEN: Wed Feb 03 13:44:34 PST 2021
;; MSG SIZE  rcvd: 545

As you can see, mine is called mailkey._domainkey.mydomain.com. I believe you need another TXT record that looks something like this:

stevewi:~ $ dig -t txt _adsp._domainkey.mydomain.com @ns1.linode.com

; <<>> DiG 9.10.6 <<>> -t txt _adsp._domainkey.mydomain.com @ns1.linode.com
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 42485
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 512
;; QUESTION SECTION:
;_adsp._domainkey.mydomain.com.    IN  TXT

;; ANSWER SECTION:
_adsp._domainkey.mydomain.com.    86400 IN TXT    "dkim=all"

;; Query time: 75 msec
;; SERVER: <DNS Server IPv6 address redacted>)
;; WHEN: Wed Feb 03 13:49:03 PST 2021
;; MSG SIZE  rcvd: 110

This TXT record tells OpenDKIM which emails to sign. I sign everything ("dkim=all").

-- sw

P.S. My public signing key is SHA-256. This is technically unsupported but seems to work. You may want to stick with SHA-128. Once you get this working, I can send you a shell script that you can run monthly or so to change the public signing key regularly (for better security).

Hi,
so I added a TXT record at Linode DNS manager.
Is there anything I should do on Linux?
And these are what I got.

Max:~ $ dig _adsp._domainkey.mydomain.com TXT

; <<>> DiG 9.10.6 <<>> _adsp._domainkey.mydomain.com TXT
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 59343
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 1232
;; QUESTION SECTION:
;_adsp._domainkey.mydomain.com. IN TXT

;; ANSWER SECTION:
_adsp._domainkey.mydomain.com. 86400 IN TXT "dkim=all"

;; Query time: 231 msec
;; SERVER: 192.168.1.1#53(192.168.1.1)
;; WHEN: Thu Feb 04 15:41:05 CST 2021
;; MSG SIZE  rcvd: 86

Also this

Max:~ $ dig default._domainkey.mydomain.com TXT

; <<>> DiG 9.10.6 <<>> default._domainkey.mydomain.com TXT
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 57573
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 1232
;; QUESTION SECTION:
;default._domainkey.mydomain.com.    IN TXT

;; ANSWER SECTION:
default._domainkey.mydomain.com.    300 IN TXT "v=DKIM1; h=sha256; k=rsa; \010\009  p=MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAlH18DK1rMPVn+RheQoGhf0TYFHAMhwortrFWwFeha9dJTSoC9HHNOG6OM7QvxuKpBjFGB3TyZ3LWtJ3ffFYYKb+gqHlHLmnmZ9CKN4JGOxIdhvteQtNjUG108kiPdmB4wKhadiKx7GPvaOP26jWKplYlyYKV0JAYLVog5v/Crc8r+0l6jFx" "d1GvoKjainz1E8jKXjztMAYXRbQ\010\009  4fNUhTDq2YOjhEJtOvk+igzHDRyEhkxaMrxFClQzmMALcyomI5aFGfv2ZSjWRwbvJzZasliPjhoOOEmboVLpVJojSQjkm0j+CifJ2eiKI9UXJaGj00jS+UMrT4IbEyicXpCluI5wIDAQAB"

;; Query time: 246 msec
;; SERVER: 192.168.1.1#53(192.168.1.1)
;; WHEN: Thu Feb 04 15:39:48 CST 2021
;; MSG SIZE  rcvd: 509

`

I wonder why mine don't have the @ns1.linode.com at the end of line 1 like yours.
And c=relaxed/simple in the TXT value.

Thanks again.

You write:

I wonder why mine don't have the @ns1.linode.com at the end of line 1 like yours.

This was part of the dig command line…telling dig which DNS server to query. I did those from my Mac and dig queried the DNS server(s) for my ISP (a large fibre operator in the Pacific Northwest). I had to qualify my request.

And c=relaxed/simple in the TXT value.

You have to SPECIFICALLY put c=relaxed/simple in the TXT value. Since it's an optional value, you have to specify it explicitly if you don't want the default. See:

https://help.returnpath.com/hc/en-us/articles/222438487-DKIM-signature-header-detail

This page describes what all that gobbledegook means and does for you. Skip down to the Optional tags section for an explanation of canonicalization. It has to do with which parts of the message are allowed to be modified in transit and how much modification you're willing to accept and still find the message to be valid. If you don't specify c=, I believe it defaults to c=relaxed/relaxed.

Sorry for the late response… I've been working on my taxes :-(

-- sw

Hi @stevewi :)
it's okay! I'm also trying to figure out why it won't work.
Anyway, it's quit embarrassed that I found out it's because of the copy-paste error…

Now the key-testing shows OK!

But somehow I can't send or receive email now…
And the OpenDKIM still won't work…
(ahhh so stressful but I really want to learn and make this to be done.)

Should I post a new question or would you give me some advice?
Thank you so much!

Here's what it shows.

 opendkim.service - OpenDKIM DomainKeys Identified Mail (DKIM) Milter
   Loaded: loaded (/lib/systemd/system/opendkim.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Sun 2021-02-07 03:38:35 CST; 4s ago
     Docs: man:opendkim(8)
           man:opendkim.conf(5)
           man:opendkim-genkey(8)
           man:opendkim-genzone(8)
           man:opendkim-testadsp(8)
           man:opendkim-testkey
           http://www.opendkim.org/docs.html
  Process: 23469 ExecStart=/usr/sbin/opendkim -x /etc/opendkim.conf (code=exited, status=0/SUCCESS)
 Main PID: 23470 (code=exited, status=69)

Feb 07 03:38:35 myhostname systemd[1]: opendkim.service: Main process exited, code=exited, status=69/UNAVAILABLE
Feb 07 03:38:35 myhostname systemd[1]: opendkim.service: Failed with result 'exit-code'.
Feb 07 03:38:35 myhostname systemd[1]: opendkim.service: Service RestartSec=100ms expired, scheduling restart.
Feb 07 03:38:35 myhostname systemd[1]: opendkim.service: Scheduled restart job, restart counter is at 5.
Feb 07 03:38:35 myhostname systemd[1]: Stopped OpenDKIM DomainKeys Identified Mail (DKIM) Milter.
Feb 07 03:38:35 myhostname systemd[1]: opendkim.service: Start request repeated too quickly.
Feb 07 03:38:35 myhostname systemd[1]: opendkim.service: Failed with result 'exit-code'.
Feb 07 03:38:35 myhostname systemd[1]: Failed to start OpenDKIM DomainKeys Identified Mail (DKIM) Milter.

You write:

But somehow I can't send or receive email now…

This is because postfix(1) is trying to communicate with something that's not there (opendkim).

Here's what it shows.

This looks like a permissions/ownership problem to me. Make sure the runtime directories and configuration files have the correct ownership/permissions:

drwxr-x---  2 opendkim opendkim    60 Feb  6 08:48 /var/run/opendkim

and

stevewi@dave:~$ ll /etc/opendkim
total 32
-rw-r--r-- 1 opendkim opendkim   86 Jan 10  2020 KeyTable
-rw------- 1 opendkim opendkim   20 Apr  2  2020 mailkey.private
-rw-r--r-- 1 opendkim opendkim  199 Jan 10  2020 SigningTable
-rw-r--r-- 1 opendkim opendkim  122 Jan  8  2020 TrustedHosts

n.b., mailkey.private doesn't have to have this name as long as the name you use matches what you entered in KeyTable.

Can you copy/paste the filter configuration lines from /etc/postfix/main.cf (this is mine…yours may look different):

# milter configuraton
#
milter_default_action = accept
milter_protocol = 6
smtpd_milters = 
        unix:var/run/opendkim/opendkim.sock,
    unix:var/run/opendmarc/opendmarc.sock
non_smtpd_milters = $smtpd_milters

Should I post a new question or would you give me some advice?

Lets move this to a new thread…and gain some visibility with others. Your OP was about SQL syntax…

-- sw

Okay!Thanks:)

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Log in to Reply
Community Code of Conduct