Multiple database errors

Using 1.0.4, relatively new install, am getting fairly constant database parse errors. Most recent is this:
MODx encountered the following error while attempting to parse the requested resource:

« Execution of a query to the database failed - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’’ at line 1 »
SQL: SELECT setting_name, setting_value FROM `cvda_org_db`.`modx_cvdauser_settings` WHERE setting_name=’manager_language’ AND user=

I am no mySql jockey - could someone point me to the fix for this error msg?

I’d also be interested to know what might be causing this so I can correct that, too.

Thanks!

Is this your actual table prefix?

The entries in the table are all prefaced by modx_cvda: "modx_cvdawhatever_settings" except for one at the very end: modx_site_content

** Fix, sort of:

I went into the db and found the usersettings entry had gotten mixed up somehow, so deleted the other user I’d set up, leaving only me as Admin. File permissions may have been an issue as well, but seems functional again.

So: what messes databases up (haven’t had this problem until this version, 1.0.4) and how can I prevent this happening again?

Thanks!

Did you specify that table prefix when creating the database during installation? That’s not quite normal; it’s usually just modx_ if you left it at the default. The database prefix is something else; that’s done by your server when the database is created. Most shared hosting will add some part of your domain name (or something unique) to the name of the database and you cannot prevent or change that. But the table prefix is of no concern to the server, so you can have what you want. But that just doesn’t look right; especially since you have the one modx_site_content table.

Hm. Would it be prudent to change the name of that entry to match the others, or...what? What’s the best way to make this all a little more robust?

I would be highly suspicious of all that. Back it up first, then check your manager/includes/config.inc.php file. I would edit it if necessary to have

and then go change the name of the tables (tedious, but this whole thing would really bother me, unless I knew I specified that prefix on installation...but then why the site_content table with just modx_ ? And what is in that modx_site_content table? Do you also have a modx_cvdasite_content table? What is in that one?

Well, interesting...

There are these:
modx_cvdasite_content
(these look like current content)
modx_cvdasite_content-orig
(these look like pages created when just starting site, and there are only a few)
modx_cvdasite_content_metatags (this is empty)
(that’s it for the _cvdasite_content category)

and then at the bottom there is:
modx_site_content
(included in this are all pages, but some without aliases)

Frankly, I don’t remember exactly (afflicted with foggy brain), but it’s not unlikely that I specified that table prefix be modx_cvda.

Delete the tables "modx_site_content" and "modx_cvdasite_content-orig", do you think? Any further action?

Thanks for helping with this

It’s hard to say... not being the one who set this up, but something is not right; there should only be one site_content table, with whatever prefix your config.inc.php file says.

Here’s what config.inc.php says, so I must have specified it this way (why I had to get so complicated I don’t know):

$table_prefix = ’modx_cvda’;

I think I’ll take a chance and backup db and config file, then delete oddly named table plus the content-orig table.

If you would recommend changing modx_cvda to modx_ I can dig right in and do that. I am not knowledgeable enough in this area to know if it would make things more conventional, or create more weird problems

Well, it actually shouldn’t make any difference as long as it’s consistent. What bothers me is the database problems you’ve been having, and the extraneous tables you have floating around in your database. That sort of thing makes me nervous; I’m very much of the opinion that the database is the heart of the website, and if the heart starts behaving in an unexpected manner it’s a major concern.

I can’t tell you directly what you should do with your site, but for what it’s worth, just for the sake of simplicity and consistency, I would change the config.inc.php to ’modx_’, and rename all the tables. I would save the odd tables just in case they have stuff you want, then remove them.

Then I would do a check on all of the tables (if you’re using phpMyAdmin it’s down in the Table Maintenance section of the Operations menu), then Optimize them.