Previous Thread
Next Thread
Print Thread
Hop To
Page 2 of 2 1 2
#173595 01/15/2007 6:59 AM
Joined: Jun 2006
Posts: 3,839
Likes: 1
I
Ian
Offline
Carpal Tunnel
Carpal Tunnel
I Offline
Joined: Jun 2006
Posts: 3,839
Likes: 1
We encountered a problem. The reason reported was
Script:
Line#:
SQL Error: 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 3
SQL Error #: 1064
Query: select count(*) from ubbt_POSTS WHERE POST_POSTED_TIME >

Please use your back button to return to the previous page

------------------

This was when trying to make a new post in a forum...

People seem to be psoting in other forums - this forum can only have posts started by admins (it is a site news forum)

The post was posted fine, despite the error.

I have posted in an admin forum as a test and all was fine.

Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
At what point did you get the error? The only place that query is used in is to show the total # of unread posts in a forum, the 7.1 feature.

Joined: Jun 2006
Posts: 3,839
Likes: 1
I
Ian
Offline
Carpal Tunnel
Carpal Tunnel
I Offline
Joined: Jun 2006
Posts: 3,839
Likes: 1
the error has now gone - mind you the database then corrupted LOL

not having a lot of luck frown

Joined: Jun 2006
Posts: 3,839
Likes: 1
I
Ian
Offline
Carpal Tunnel
Carpal Tunnel
I Offline
Joined: Jun 2006
Posts: 3,839
Likes: 1
Okay - just set up a new forum as a test - and am getting this error again.

This is a stock 7.1b4 - and I get the error on all items (posts, CP changes etc..)

I have not altered any templates yet - so this is out of the box.


Joined: Jun 2006
Posts: 3,839
Likes: 1
I
Ian
Offline
Carpal Tunnel
Carpal Tunnel
I Offline
Joined: Jun 2006
Posts: 3,839
Likes: 1
Even just showing 'who's online' brings up this error frown

Joined: Jun 2006
Posts: 3,839
Likes: 1
I
Ian
Offline
Carpal Tunnel
Carpal Tunnel
I Offline
Joined: Jun 2006
Posts: 3,839
Likes: 1
BTW - tried repairing ubbt_POSTS - no joy frown

Joined: Jun 2006
Posts: 3,839
Likes: 1
I
Ian
Offline
Carpal Tunnel
Carpal Tunnel
I Offline
Joined: Jun 2006
Posts: 3,839
Likes: 1
anyone?

any thoughts?

frown

Joined: Jun 2006
Posts: 16,366
Likes: 126
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,366
Likes: 126
Query: select count(*) from ubbt_POSTS WHERE POST_POSTED_TIME >

It looks like it failed when trying to count the number of posts before a specific time, such as for the "latest posts in thread" opion that shows at the thread listing; it looks like the time wasn't included or something?

is it reoccouring?


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Jun 2006
Posts: 3,839
Likes: 1
I
Ian
Offline
Carpal Tunnel
Carpal Tunnel
I Offline
Joined: Jun 2006
Posts: 3,839
Likes: 1
yes - but this is a brand new forum - it happened on the very first post frown

I have only made 3 so far - 1 global announcement, 1 sticky and now a test in an admin forum.

All had this error, as did various changes in the CP prior to even posting.

However on every ocasion the requested task is carried out, despite the error.

Joined: Nov 2006
Posts: 3,095
Likes: 1
Carpal Tunnel
Carpal Tunnel
Joined: Nov 2006
Posts: 3,095
Likes: 1
Is this on the same server as your production forum ?

Joined: Jun 2006
Posts: 3,839
Likes: 1
I
Ian
Offline
Carpal Tunnel
Carpal Tunnel
I Offline
Joined: Jun 2006
Posts: 3,839
Likes: 1
yes - which is running along just fine now smile

Joined: Jun 2006
Posts: 3,839
Likes: 1
I
Ian
Offline
Carpal Tunnel
Carpal Tunnel
I Offline
Joined: Jun 2006
Posts: 3,839
Likes: 1
.. touch wood wink

Joined: Jun 2006
Posts: 16,366
Likes: 126
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,366
Likes: 126
Another question would be is it on the same domain as another forum...


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Nov 2006
Posts: 3,095
Likes: 1
Carpal Tunnel
Carpal Tunnel
Joined: Nov 2006
Posts: 3,095
Likes: 1
LOL - Just mighty coincidental to have such issues on a NEW install.

Just FYI I installed my new site yesterday and did not run into any issues aside from some nuances that I suggested changes for.

Did a clean new install of UBBT 7.1b4 on Linux system with Apache 1.3

Joined: Jun 2006
Posts: 3,839
Likes: 1
I
Ian
Offline
Carpal Tunnel
Carpal Tunnel
I Offline
Joined: Jun 2006
Posts: 3,839
Likes: 1
Originally Posted by Gizmo
Another question would be is it on the same domain as another forum...


Different domain smile (using my other licence - need to buy more LOL )

Joined: Jun 2006
Posts: 16,366
Likes: 126
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,366
Likes: 126
well, that would avoid the possiblity of any cookie conflicts (hey i'm grasping at straws here lol)


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Nov 2006
Posts: 3,095
Likes: 1
Carpal Tunnel
Carpal Tunnel
Joined: Nov 2006
Posts: 3,095
Likes: 1
I was grasping at hardware/path/config issues of the main server for one or more of PHP or MySQL ????

Is this on Linux box?

Joined: Jun 2006
Posts: 3,839
Likes: 1
I
Ian
Offline
Carpal Tunnel
Carpal Tunnel
I Offline
Joined: Jun 2006
Posts: 3,839
Likes: 1
both on linux - both forums on the same server, but different domain names.

All the paths are the same (except the domain part obviously) smile

Very strange....

I will check all settings again today - but it is the morning school run time now.....

Joined: Jun 2006
Posts: 16,366
Likes: 126
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,366
Likes: 126
The hardware seems sane, with the exception of his backup script which just eats away at system resources lol


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Jun 2006
Posts: 3,839
Likes: 1
I
Ian
Offline
Carpal Tunnel
Carpal Tunnel
I Offline
Joined: Jun 2006
Posts: 3,839
Likes: 1
tell me about it frown

However that is not the cause of this error....

Joined: Jun 2006
Posts: 16,366
Likes: 126
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,366
Likes: 126
as long as it's not preformance/resource related lol


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Nov 2006
Posts: 3,095
Likes: 1
Carpal Tunnel
Carpal Tunnel
Joined: Nov 2006
Posts: 3,095
Likes: 1
Yep, but pretty strange that a NEW clean install has problems too right out the gate. whistle

Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
Looking at this again, the query seems to be a bit incomplete. That would give the total # of new posts in all forums older than a new date. I'm trying to locate where we even do a query like that.

Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
Ok, I've been searching through all of the scripts and I can't find any query like this. The scripts that check for unread posts all specify an individual forum and use a join in the process, so they look more like "and t1.POST_POSTED_TIME > "

There is no query in the default code that I can find anywhere that executes a query like this.

Joined: Jun 2006
Posts: 3,839
Likes: 1
I
Ian
Offline
Carpal Tunnel
Carpal Tunnel
I Offline
Joined: Jun 2006
Posts: 3,839
Likes: 1
Hi,

This was a stock install - nothing had altered - however it has now vanished.

All very strange....

As long as it remains that way, I will be happy.

Thanks for having a look and my apologies for any inconvenience smile

Joined: Jun 2006
Posts: 196
I
enthusiast
enthusiast
I Offline
Joined: Jun 2006
Posts: 196
Originally Posted by Rick
Ok, I've been searching through all of the scripts and I can't find any query like this. The scripts that check for unread posts all specify an individual forum and use a join in the process, so they look more like "and t1.POST_POSTED_TIME > "

There is no query in the default code that I can find anywhere that executes a query like this.


Don't have the code with me, but what about cache_builders/forum_stats.php

Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
Nope, that one doesn't query the POSTS table at all. It grabs the total posts from the FORUMS table, using the FORUM_TOPICS and FORUM_POSTS fields.

Joined: Jun 2006
Posts: 3,839
Likes: 1
I
Ian
Offline
Carpal Tunnel
Carpal Tunnel
I Offline
Joined: Jun 2006
Posts: 3,839
Likes: 1
Rick - when I first had this problem it was just after I had installed Ian's modification to forum_stats.php.

However this was a fresh install - I did various things to correct the issue, including re-uploading. Whether re-uploading this file did the trick I do not know.


Joined: Jun 2006
Posts: 196
I
enthusiast
enthusiast
I Offline
Joined: Jun 2006
Posts: 196
Well yeah, that's my query, but if that was causing it it'd imply either an issue with $html->get_date(), php's ability to subtract, or $dbh->do_placeholder_query(). What bothers me is that I'm anal about using the __LINE__ and __FILE__ parameters in queries, so those for some reason weren't getting added into the error

Joined: Jun 2006
Posts: 626
Addict
Addict
Joined: Jun 2006
Posts: 626
I am now receiving the following error:

Quote
We encountered a problem. The reason reported was
Script: /home/virtual/site5/fst/var/www/html/forums/scripts/showflat.inc.php
Line#: 226
SQL Error: Can't open file: 'ubbt_POSTS.MYD'. (errno: 145)
SQL Error #: 1016
Query: SELECT t1.TOPIC_ID,t1.FORUM_ID,t1.TOPIC_LAST_REPLY_TIME,t1.TOPIC_IS_STICKY,t1.POST_ID FROM ubbt_TOPICS as t1, ubbt_POSTS as t2 WHERE t2.POST_ID = 85993 AND t1.TOPIC_ID = t2.TOPIC_ID

Please use your back button to return to the previous page.
Will doing a repair on the ubbt_posts.myd table solve the problem? It isn't showing any rows with only 1B in both data_length and index_length columns.

Joined: Jun 2006
Posts: 626
Addict
Addict
Joined: Jun 2006
Posts: 626
Looking at the ubbt_posts table itself, it says "in use" which is what is obviously causing the problem.

Joined: Jun 2006
Posts: 626
Addict
Addict
Joined: Jun 2006
Posts: 626
I am ready to use the repair table ubbt_posts command, however, I want to make sure this is ok to do before I do it as I can't afford to mess up on such a critical table as I don't want to lose all the posts data.

Joined: Jun 2006
Posts: 16,366
Likes: 126
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,366
Likes: 126
It's saying it cannot read ubbt_POSTS, so I believe that a repair should fix the issue


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Jun 2006
Posts: 16,366
Likes: 126
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,366
Likes: 126
this error code is associated with a crash of the MySQL database, where the table (in this case, the ubbt_POSTS table) is marked as needing repair.

MySQL.com provides:
"4.4.6.7 Using myisamchk for Crash Recovery" http://www.mysql.com/doc/en/Crash_recovery.html


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Jun 2006
Posts: 626
Addict
Addict
Joined: Jun 2006
Posts: 626
I did the REPAIR TABLE ubbt_POSTS command about twenty or so minutes ago.

The data base has been growing ever since, and is still growing.

If it doesn't stop growing soon, I may run out of server space.

Joined: Jun 2006
Posts: 626
Addict
Addict
Joined: Jun 2006
Posts: 626
Does it create a temporary file or table while doing the repair?

Joined: Jun 2006
Posts: 626
Addict
Addict
Joined: Jun 2006
Posts: 626
While this is going on, it won't let me access the database via MySQL Admin.

Joined: Jun 2006
Posts: 626
Addict
Addict
Joined: Jun 2006
Posts: 626
It has increased by about 100MB since I executed the repair command via the UBBT Control Panel after closing down the board.

Joined: Jun 2006
Posts: 626
Addict
Addict
Joined: Jun 2006
Posts: 626
The repair didn't work via the SQL Command in the Control Panel.

It increased the database size up to the max. space and then reverted back to normal size again.

Any ideas on what to do next?

Joined: Jun 2006
Posts: 626
Addict
Addict
Joined: Jun 2006
Posts: 626
Their server support is now trying to repair it for me.

They said they are doing a "real repair" whatever that means. smile

Well, as long as it works, that is fine with me. smile

Joined: Jun 2006
Posts: 626
Addict
Addict
Joined: Jun 2006
Posts: 626
Three hours later and the repair isn't done yet.

Joined: Jun 2006
Posts: 16,366
Likes: 126
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,366
Likes: 126
I had linked you to a MySQL.com topic on the matter this afternoon before i went out which explained alternative repair methods.


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Jun 2006
Posts: 626
Addict
Addict
Joined: Jun 2006
Posts: 626
After about 6 hours, the repair was successfully completed by our server support.

As this is the 2nd time that table became corrupted, the question is why did it become corrupted?

Joined: Jun 2006
Posts: 16,366
Likes: 126
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,366
Likes: 126
MySQL tables/databases can become currupted for any number of reasons; server crashes, insufficient space, etc; it all honestly varies case by case.

For most cases you can repair the database with built in command tools, such as the "myisamchk" tool I linked to in my previous post.

In a lot of cases, a simple "repair" via the MySQL command can fix a lot of issues; however, running this or any SQL maintenance from a web browser is highly not advised by me, it will take forever, doing such tasks from the command line will highly improve wait times here as you'll not be bogging down (or be bogged down by) the webserver to execute MySQL commands and recieve feedback.


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Mar 2007
Posts: 26
A
journeyman
journeyman
A Offline
Joined: Mar 2007
Posts: 26
Not to do a thread hijack here, but is there a resource for preventative measures that a n00b MySQL admin should take for the UBB data? I back it up (via the web interface) daily. Are there consistency checks or other things that I should be doing?

Joined: Jun 2006
Posts: 626
Addict
Addict
Joined: Jun 2006
Posts: 626
After what I went through twice, I would also be interested in the preventative measures aspect.

Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
Table corruption can happen from a variety of things. It's more prone to happen on a shared server. Some of the reasons would include:

  • Hardware issues, motherboard/cpu/memory or a slow/bad hard drive.
  • The mysql process being killed while the database is being written to.
  • The computer/server being shutdown unexpectedly.
  • Certain versions of MySQL have bugs in them that are more prone to corruption. So, it's important for the host to be on top of things and upgrade accordingly.


In short, the more stable and up to date the server is the less frequently you should have table crashes. For example, here, we haven't had a single table crash.

There are no real preventative steps that can be taken. If you have frequent corruption issues, then it's more than likely related to something above.

Having a properly tuned mysql server can help. If you're on a hosting company that just throws up a mysql server and uses the default configuration, that certainly isn't going to help, especially if you're using shared hosting.

Joined: Jun 2006
Posts: 626
Addict
Addict
Joined: Jun 2006
Posts: 626
And I am using shared hosting.

Joined: Jun 2006
Posts: 626
Addict
Addict
Joined: Jun 2006
Posts: 626
The same problem happened again on the same day, Saturday, as it did the previous week.

I also discovered that they do the site back-ups on that day, which makes me wonder, if the site back-up may be triggering the problem?

Joined: Jun 2006
Posts: 626
Addict
Addict
Joined: Jun 2006
Posts: 626
A related question:

If I do a back-up of that table, and this happens, again, how would I replace the corrupted table with the back-up table?

Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
When they are backing up the table it very well could be taxing MySQL heavily and contributing to the problem.

As for restoring a backup table. You could use something like phpmyadmin to drop the old table (not the entire database) and then import the backup table.

Joined: Jun 2006
Posts: 626
Addict
Addict
Joined: Jun 2006
Posts: 626
As this happened the last two Saturdays, I will do a backup of that table on Friday evening, or first thing Saturday morning, and, if it happens again, I will drop that table and import the backup one via phpmyadmin.

Page 2 of 2 1 2

Link Copied to Clipboard
ShoutChat
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
Not allowing attachment over 2m
by ehill - 12/03/2024 3:16 PM
New Admin Here
by SenecaFlyer - 12/02/2024 4:14 PM
Post Counts zeroed out
by Baldeagle - 11/03/2024 3:05 PM
Who's Online Now
0 members (), 1,448 guests, and 60 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 8.0.1
(Snapshot build 20240918)