OBClient Usage Tips and Troubleshooting

This topic describes usage tips, common issues, and platform-specific notes for OBClient.

Usage tips

Vertical display of query results

Some query results are more readable when displayed vertically than in the usual horizontal table format. You can display a query vertically by terminating it with \G instead of a semicolon (;). For example, longer text values that contain line breaks are usually easier to read when output vertically:

obclient> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
*************************** 1. row ***************************
  msg_nro: 3068
    date: 2000-03-01 23:29:50
time_zone: +0200
mail_from: Monty
    reply: monty@no.spam.com
  mail_to: "Thimble Smith" <tim@no.spam.com>
      sbj: UTF-8
      txt: >>>>> "Thimble" == Thimble Smith writes:

Thimble> Hi.  I think this is a good idea.  Is anyone familiar
Thimble> with UTF-8 or Unicode? Otherwise, I´ll put this on my
Thimble> TODO list and see what happens.

Yes, please do that.

Regards,
Monty

    file: inbox-jani-1
    hash: 190402944
1 row in set (0.09 sec)

Use the --safe-updates option

For beginners, a useful startup option is --safe-updates (or --i-am-a-dummy, which has the same effect). This is helpful when you might have executed a DELETE FROM tbl_name statement but forgot the WHERE clause. Typically, such a statement would delete all rows from the table. With --safe-updates, you can only delete rows by specifying their key values. This helps prevent accidents.

When you use the --safe-updates option, OBClient sends the following statement when connecting to the OceanBase server:

SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;

The SET statement has the following effects:

• Unless you specify a key constraint in the WHERE clause or provide a LIMIT clause (or both), UPDATE or DELETE statements are not allowed. For example:

UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
UPDATE tbl_name SET not_key_column=val LIMIT 1;

• The server limits all large SELECT results to 1,000 rows, unless the statement contains a LIMIT clause.
• The server aborts multi-table SELECT statements that may require checking more than 1,000,000 row combinations.

To specify limits other than 1,000 and 1,000,000, you can override the default values using the --select_limit and --max_join_size options:

obclient --safe-updates --select_limit=500 --max_join_size=10000

Disable automatic reconnection in OBClient

If the OBClient loses connection to the server while sending a statement, it immediately attempts to reconnect automatically and send the statement again. However, even if OBClient reconnects successfully, your first connection is terminated, and all previous session objects and settings are lost: temporary tables, auto-commit mode, and user-defined and session variables. Additionally, any current transactions are rolled back. This can be dangerous, as shown in the following example, where the server shuts down and restarts between the first and second statements without your knowledge:

obclient> SET @a=1;
Query OK, 0 rows affected (0.05 sec)

obclient> INSERT INTO t VALUES(@a);
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test
Query OK, 1 row affected (1.30 sec)

obclient> SELECT * FROM t;
+------+
| a    |
+------+
| NULL |
+------+

The user variable @a is lost with the connection and is undefined after reconnection. If it is important for OBClient to terminate and report an error when the connection is lost, you can start the OBClient using the --skip-reconnect option.

Create a stored program using delimiters

When creating a stored program from the command line, you may need to distinguish between regular delimiters and delimiters within BEGIN END blocks. Consider the following example:

CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC
BEGIN
 DECLARE x TINYINT;
 SET x = 42;
 RETURN x;
END;

If you enter the above content line by line, OBClient will treat the first semicolon (at the end of the DECLARE x TINYINT line) as the end of the statement. Since this is only a partial definition, it will throw a syntax error.

The solution is to specify a different delimiter during the process duration using DELIMITER. The delimiter can be any character set you choose, but it must be a unique one that will not cause further confusion. // is a common choice:

DELIMITER //

CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC
BEGIN
  DECLARE x TINYINT;
  SET x = 42;
  RETURN x;
END 
//

DELIMITER ;

Finally, the delimiter reverts to the default semicolon. The \g and \G delimiters are always available, even if a custom delimiter is specified.

Support for macOS

OBClient supports running on macOS. The following describes considerations for using it on a Mac.

Permission issues

When running OBClient for the first time on a Mac, you may encounter a permission prompt. You need to allow its use in "System Preferences" > "Privacy & Security".

FAQ

Connection issues

Question: Unable to connect to the server

Possible causes:

• Incorrect host address or port number
• Network connection issues
• Firewall blocking the connection
• Server not started

Solution:

1. Check whether the host address and port number are correct.
2. Use the ping command to test network connectivity.
3. Check firewall settings.
4. Confirm the server status.

Question: Different connection behaviors for localhost and 127.0.0.1

On Unix systems, localhost and 127.0.0.1 have different connection behaviors:

• When using localhost, OBClient attempts to use a Unix socket connection.
• When using 127.0.0.1, OBClient uses a TCP/IP connection.

If your server listens only on a TCP/IP port, use 127.0.0.1 or the actual IP address.

Character set issues

Question: Incorrect output format

When the operating system uses UTF-8 or another multibyte character set, OBClient may output incorrectly because the client uses the Latin-1 character set by default.

Solution:

Use the --default-character-set option to force the client to use the system character set:

obclient --default-character-set=utf8

Or set it to auto to obtain the character set from the client environment:

obclient --default-character-set=auto

Historical file issues

Problem: Historical files contain sensitive information

The .mysql_history file may contain sensitive information (such as SQL statements containing passwords).

Solution:

1. Protect this file using restricted access mode:

chmod 600 ~/.mysql_history

2. If you do not want to maintain historical files, you can set the MYSQL_HISTFILE environment variable to /dev/null:

export MYSQL_HISTFILE=/dev/null

TNS configuration issues

Problem: The tnsnames.ora file is invalid

When using multi-IP connections, if the TNS configuration file format is incorrect, an "Invalid tnsnames.ora file" error occurs.

Solution:

1. Check whether the TNS_ADMIN environment variable is correctly set.
2. Check whether the format of the tnsnames.ora file is correct.
3. Ensure that the file path and permissions are correct.

References

• Overview of OBClient
• Reference for OBClient command-line options
• --error-sql (Script error context)
• OBClient configuration and environment variables
• Oracle-compatible mode support