Technology Tales

Dealing with Error 1064 in MySQL queries

Recently, I was querying a MySQL database table and got a response like the following:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

The cause was that one of the data column columns was named using the MySQL reserved word key. While best practice is not to use reserved words like this at all, this was not something that I could alter. Therefore, I enclosed the offending keyword in backticks (`) to make the query work.

There is more information in the MySQL documentation on Schema Object Names and on Keywords and Reserved Words for dealing with or avoiding this kind of situation. While I realise that things change over time and that every implementation of SQL is a little different, it does look as if not using established keywords should be a minimum expectation when any database tables get created.

Disabling the SSL connection requirement in MySQL Workbench

A while ago, I found that MySQL Workbench would only use SSL connections and that was stopping it from connecting to local databases. Thus, I looked for a way to override this: the cure was to go to Database > Manage Connections... in the menus for the application's home tab. In the dialogue box that appeared, I chose the connection of interest and went to the Advanced panel under Connection and removed the line useSSL=1 from the Others field. The screenshot below shows you what things looked like before the change was made. Naturally, the best practice would be to secure a remote database connection using SSL, so this approach is best reserved for remote non-production databases. While it may be that this does not happen now, I thought I would share this in case the problem persists for anyone.

Controlling display of users on the logon screen in Linux Mint 20.3

Recently, I tried using Commento with a static website that I was developing and this needed PostgreSQL rather than MySQL or MariaDB, which many content management tools use. That meant a learning curve that made me buy a book, as well as the creation of a system account for administering PostgreSQL. These are not the kind of things that you want to be too visible, so I wanted to hide them.

Since Linux Mint uses AccountsService, you cannot use lightdm to do this (the comments in /etc/lightdm/users.conf suggest as much). Instead, you need to go to /var/lib/AccountsService/users and look for a file called after the username. If one exists, all that is needed is for you to add the following line under the [User] section:

SystemAccount=true

If there is no file present for the user in question, then you need to create one with the following lines in there:

[User]
SystemAccount=true

Once the configuration files are set up as needed, AccountsService needs to be restarted and the following command does that deed:

sudo systemctl restart accounts-daemon.service

Logging out should reveal that the user in question is not listed on the logon screen as required.

Setting up MySQL on Sabayon Linux

For quite a while now, I have offline web servers for doing a spot of tweaking and tinkering away from the gaze of web users that visit what I have on there. Therefore, one of the tests that I apply to any prospective main Linux distro is the ability to set up a web server on there. This is more straightforward for some than for others. For Ubuntu and Linux Mint, it is a matter of installing the required software and doing a small bit of configuration. My experience with Sabayon is that it needs a little more effort than this, so I am sharing it here for the installation of MySQL.

The first step is to install the software using the commands that you find below. The first pops the software onto the system while the second completes the set-up. The --basedir option is need with the latter because it won't find things without it. It specifies the base location on the system, and it's /usr in my case.

sudo equo install dev-db/mysql
sudo /usr/bin/mysql_install_db --basedir=/usr

With the above complete, it's time to start the database server and set the password for the root user. That's what the two following commands achieve. Once your root password is set, you can go about creating databases and adding other users using the MySQL command line

sudo /etc/init.d/mysql start
mysqladmin -u root password 'password'

The last step is to set the database server to start every time you start your Sabayon system. The first command adds an entry for MySQL to the default run level so that this happens. The purpose of the second command is to check that this happened before restarting your computer to discover if it really happens. This procedure also is necessary for having an Apache web server behave in the same way, so the commands are worth having and even may have a use for other services on your system. ProFTP is another that comes to mind, for instance.

sudo rc-update add mysql default
sudo rc-update show | grep mysql

ERROR: Ambiguous reference, column xx is in more than one table.

Sometimes, SAS messages are not all that they seem, and a number of them are issued from PROC SQL when something goes awry with your code. In fact, I got a message like the above when ordering the results of the join using a variable that didn't exist in either of the datasets that were joined. This type of thing has been around for a while (I have been using SAS since version 6.11, and it was there then) and it amazes me that we haven't seen a better message in more recent versions of SAS; it was SAS 9.2 where I saw it most recently.

proc sql noprint;
    select a.yy, a.yyy, b.zz
        from a left join b
            on a.yy=b.yy
                order by xx;
quit;

ERROR: Invalid value for width specified - width out of range

This could be the beginning of a series of error messages from PROC SQL that may appear unclear to a programmer more familiar with Data Step. The cause of my getting the message that heads this posting is that there was a numeric variable with a length less than the default of 8, not the best of situations. Sadly, the message doesn't pinpoint the affected variable, so it took some commenting out of pieces of code before I found the cause of the problem. That's never to say that PROC SQL does not have debugging functionality in the form of FEEDBACK, NOEXEC, _METHOD and _TREE options on the PROC SQL line itself or the validation statement, but neither of these seemed to help in this instance. Still, they're worth keeping in mind for the future, as is SAS Institute's own page on SQL query debugging. Of course, now that I know what might be the cause, a simple PROC SQL report using the dictionary tables should help. The following code should do the needful:

proc sql;
    select memname, name, type, length
        from dictionary.columns
            where libname="DATA" and type="num" and length ne 8;
quit;

Further securing MySQL in Fedora

Ubuntu users must be spoilt because any MySQL installation asks you for a root password, an excellent thing in my opinion. With Fedora, it just pops the thing on there with you needing to set up a service and setting the root password yourself; if I recall correctly, I think that openSUSE does the same thing. For the service management, I needed to grab system-config-services from the repositories because my Live CD installation left off a lot of stuff, OpenOffice and GIMP even. The following command line recipe addressed the service manager omission:

su - # Change to root, entering password when asked
yum -y install system-config-services # Installs the thing without a yes/no prompt
exit # Return to normal user shell

Thereafter, the Services item from the menus at System > Administration was pressed into service and the MySQL service enabled and started. The next step was to lock down the root user, so the following sequence was used:

mysql # Enter MySQL prompt; no need for user or password because it still is unsecured!
UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;
quit # Exit the mysql prompt, leaving the bare mysql command unusable

For those occasions when password problems keep you out of the MySQL shell, you'll find password resetting advice on the MySQL website, though I didn't need to go the whole hog here. MySQL Administrator might be another option for this type of thing. That thought never struck me while I was using it to set up less privileged users and allowing them access to the system. For a while, I was well stymied in my attempts to access the MySQL using any of those extra accounts until I got the idea of associating them with a host, another thing that is not needed on Ubuntu if my experience is any guide. All in all, Fedora may make you work a little extra to get things like thing done, yet I am not complaining if it makes you understand a little more about what is going on in the background, something that is never a disadvantage.

A second post today?

While I know what I said about a post every two days, something has entered my head that seems timely. Things seem to starting up for 2008 and my getting a swathe of post ideas is only one of them. Today, Sun has bought up MySQL, the database that stores these ruminations for posterity, and Oracle has finally got its hands on Bea, the people behind the Weblogic software with which I have had an indirect brush for a lot of 2007.

Running SQL scripts with MySQL

Here's another of those little things that you forget if you aren't using them every day: running MySQL scripts using the Windows command line. Yes, you can also run SQL commands interactively, but there's a certain convenience about scripts. I am putting an example here so that it can be found again easily:

mysql -u username -p databasename < script.sql

Though I wouldn't be at all surprised if the same line worked under Linux and UNIX, I haven't needed to give it a try.

SQL Developer Java error

Last night, I tried starting up Oracle's SQL Developer so that I could add a listing of my hillwalking blog posts to my website's site map with a spot of PHP scripting. However, all that I got was something like that which you see below:

I must confess that this one threw me. The solution, though challenging to find (they often are, even with the abilities of Google) was to use a batch file called sqldeveloper.bat that you can find in the [installation directory]\sqldeveloper\bin directory. It does start the thing when all else seems to fail and got me up and running again. I did get that blog post listing added to the site map after all; Having more visibility of the MySQL tables was a definite plus point.