[MDEV-18211] stray "\0"s in mysql client output on macosx querying Linux server Created: 2019-01-11  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5
Fix Version/s: 10.4

Type: Bug Priority: Minor
Reporter: Jason Vas Dias Assignee: Vicențiu Ciorbaru
Resolution: Unresolved Votes: 0
Labels: None
Environment:

5.5.5-10.3.8-MariaDB-1:10.3.8+maria~jessie mariadb.org binary distribution running on Ubuntu 16.04.3 LTS (Xenial Xerus) x86_64 queried with remote MacOSX 10.14.2 mojave x86_64 with EITHER :
mysql Ver 15.1 Distrib 5.5.57-MariaDB, for osx10.14 (x86_64) using readline 5.1
(from MacPorts) OR
Oracle mysql Ver 14.14 Distrib 5.7.17, for osx10.14 (x86_64) using EditLine wrapper
(from MacPorts)



 Description   

When querying a join on a table that contains a varchar(255) column,
from a remote mariadb server of the above version, with either of the
above clients, stray "\0" characters sequences sometimes appear at
the end of the varchar(255) column values, which breaks the number
of columns in the mysql output .
When I run the same query on the server, these characters do not appear
in the output , so it appears to be a MacOSX client issue.
Has anyone seen this before / any ideas how to workaround ?
I thought I should report this since it makes mysql output useless for parsing
(the constant number of columns on each line is broken) .

Example:
On MacOSX host:

\$ mysql -D D -h $h -u $u -P $P <<'EOF'
 
SELECT
 
  c_t
 
, e_t
 
, u_t
 
, r_id
 
, ur_id
 
, u_id
 
, U.n as n
 
, e_id
 
, E.n as e
 
, E.t as t
 
, E.r as r
 
, E.d as d
 
FROM
 
  a
 
  INNER JOIN  E ON ( a.e_id = E.e_id)
 
  INNER JOIN U ON ( a.u_id = U.u_id )
 
WHERE ( (c_t >= ''2019/01/11 10:00:00'')
 
    AND  (u_id IN ( "3da396d5397e3991","1e9bffc880e1621","c76829bc7a3524e5","ed13b5be0fa917ca" )))
 
ORDER BY e_t ASC
 
LIMIT 60;
 
EOF
 
;
 
...
 
\# A normal output line:
 
2019-01-11 16:03:55	2019-01-11 16:03:37	2019-01-11 16:03:39	3713	3713	c76829bc7a3524e5	T1	9 \
 
HH_BC		42   0   255
 
\# A bad output line:
 
2019-01-11 16:04:08	2019-01-11 16:03:43	2019-01-11 16:03:53	4745	4745	c76829bc7a3524e5	T1    7 \
 
HH_DC	\0	43   0   255
 
... \#_____^- this is bad



 Comments   
Comment by Jason Vas Dias [ 2019-01-11 ]

It appears the \0's occur at the position a column
separator character would appear at (with spaces
before & after it, this breaking the constant number
of columns on an output line. None of the strings
in the table are allowed to contain spaces , so I
don't want to have to use a column separator char.
This is on my company workstation at work, it is
a database colleagues maintain, so I don't have
any choice , I need to access the Linux hosted DB
from the MacOSX client , but these stray '\0's really
mess things up - they are not on every line, just
where the strings are greater than @ 20 chars or so.

The stray '\0's happen in either MacOSX Terminal or under xterm
running under XQuartz or with no terminal output to a file .

It does not happen if I ssh into the server and run the query.

But really, I can't be tied to having to run all queries on the server under SSH .

Why these extra disastrous '\0's ?

Comment by Jason Vas Dias [ 2019-01-12 ]

Oops, sorry ! It took doing a

$ mysqldump -d D -u $u -p -h $H E

to see that there are some values of the E.r 'r' string that are represented as:
''
(two single quotes)
while others are represented in the dump output as
'\0'
so actually MacOSX is being more correct here.
But I'm not sure there should be any difference between '' and '\0', since '\0' is
not valid in the default UTF-8 locale , and
/*!40101 SET character_set_client = utf8 */;
is in effect, and LC_ALL is :
LANG=en_gb.UTF-8; LC_ALL=POSIX
So this is not a bug, but I am curious what the
difference between '\0' and '' is in mysql, and
why the invalid UTF-8 string "\0" is displayed at all.

Generated at Thu Feb 08 08:42:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.