Warning on SQL Check

[mike2]

I was checking my DB with mysqlcheck to make sure there was nothing wrong with it, because of an error not getting emails when importing to list 1, (I posted a seperate topic on this).

And got a warning on the lm_hits table, here it is:

Code: [Select]

maoptin.lm_hits
warning  : Datafile is almost full, 1067906 of 1114110 used
status   : OK


Let me know if this is serious and what I should do, thanks.

[DW]

This is a peculiar error... Googling for "warning  : Datafile is almost full" yielded some results

Is your system running FreeBSD?

The first result indicates you should be able to have data files up to 3.2GB or even 4GB, whereas yours seems to allow up to just over 1MB!

I can't seem to find any results showing such a low value... How is the disk space on the partition/drive?

Regards

[mike2]

Yea it's odd, my system is a Fedora Core 1...

Even after trying all the MYisamchk's on it to repair it, no luck.  As stated in my other post, there was some errors on my Disk, I fixed them all with FSCK, but it still says the same thing.

Here's what the myisamchk continues to say, even after I do a -r (repair) on it...

Code: [Select]

Checking MyISAM file: lm_hits.MYI
Data records:   63168   Deleted blocks:       0
- check file-size
myisamchk: warning: Datafile is almost full,    1073856 of    1114110 used
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
MyISAM-table 'lm_hits.MYI' is usable but should be fixed

Disk space is fine:  
/dev/hda5              4814936    305872   4264476   7% /var

Maybe an old version of mysql causing this?  Mine Is 3.23.58-4

But from what I'm reading v3.23 tables can be huge.
Here's a link:  
http://dev.mysql.com/doc/refman/5.0/en/table-size.html

Here's another link that is interesting talking about this, maybe the max rows isn't big enough or something?
http://bugs.mysql.com/bug.php?id=1551

According to my operating system, my files can be big... here's ulimit -a command:

Code: [Select]

ulimit -a
core file size        (blocks, -c) 0
data seg size         (kbytes, -d) unlimited
file size             (blocks, -f) unlimited
max locked memory     (kbytes, -l) unlimited
max memory size       (kbytes, -m) unlimited
open files                    (-n) 1024
pipe size          (512 bytes, -p) 8
stack size            (kbytes, -s) 10240
cpu time             (seconds, -t) unlimited
max user processes            (-u) 7168
virtual memory        (kbytes, -v) unlimited


[DW]

Hi Mike,

How's it going?  I think having you replace edituser.php with the most recent was the wrong move.  This should definitely work with the latest stable release (v1.87d).  I really don't like the looks of the 'datafile almost full' error - do you get it with any other tables?  This could be the reason for what you report, where it -seems- to import the users but doesn't actually - perhaps a datafile is full somewhere so these "insert" statements are lost.  I think when we figure this part out it will solve the problem.  I wish I had more experience with the error...  I haven't come up with a solution yet but can keep searching...

I have an idea.  We might be able to see a MySQL error during the import process if we enable it.  In the v1.87d version of edituser.php around line 792, change:

Code: [Select]

    $cmd = "insert into $utable values ('null','$uniq_str','$lis','$fname','$lname','$email','$user1','$user2','$user3','$user4','$user5',
'$user6','$user7','$user8','$user9','$user10','$setseq','$setdel','$xcnf','$today','$ipadd','$refu','1','0')";
     if($demo<>'yes'){
      @mysql_query($cmd,$link);
to

Code: [Select]

    $cmd = "insert into $utable values ('null','$uniq_str','$lis','$fname','$lname','$email','$user1','$user2','$user3','$user4','$user5',
'$user6','$user7','$user8','$user9','$user10','$setseq','$setdel','$xcnf','$today','$ipadd','$refu','1','0')";
     if($demo<>'yes'){
      @mysql_query($cmd,$link) or die(mysql_error($link));

Try another import - if the insert statements fail we could be given more information.

Regards

[mike2]

Ok here's an update...  I continued to get that error on the lm_hits table no matter what.

I tried dropping the table and rebuilding it per your instructions.  The table seemed ok for a minute, then when running a check it produced another different error about table size being wrong or something, very wierd.

Ran repair, fixed it, was ok for a bit, then later same kind of error...  So I was thinking disc corruption, but not sure yet.

Shortly after, I was trying to manually search for my email in the lm_user table, got this error "ERROR 1030: Got error 127 from table handler"

Did some searching, optimized all of the TABLES and after optimizing them could search for my email again, no problems.  The lm_hits table hasn't produced an error via mysqlcheck since I optimized them all.

So I'm not sure if that's a coincidence or not..

EITHER WAY, I then deleted my email manually via mysql statement "delete from lm_users where email = "michaeljamesrogers@yahoo.com";

I then tried importing my CSV file with only that email and another email of mine in the file.  While importing it says importing 1 user 2 duplicates... still not right.  And of course NO email got to me  (BTW, this was also after going back to your 1.87d edituser.php with the code tweak you suggest above).

So the insert is working, but no email is getting sent, can anyone duplicate this error with list "1"

EVEN NEWER UPDATE:  While an email was sending to list 1 (about 150K users), I ran myisamchk *.MYI, here's some errors, dunno if they are because mail is running or could this version of MYSQL be bugged or my disc going bad...

Code: [Select]

myisamchk *.MYI | more
myisamchk: warning: 1 clients is using or hasn't closed the table properly
MyISAM-table 'lm_hits.MYI' is usable but should be fixed
myisamchk: warning: 1 clients is using or hasn't closed the table properly
MyISAM-table 'lm_links.MYI' is usable but should be fixed
myisamchk: warning: 1 clients is using or hasn't closed the table properly
MyISAM-table 'lm_sendp.MYI' is usable but should be fixed
myisamchk: warning: 1 clients is using or hasn't closed the table properly

... snip ...

Checking MyISAM file: lm_config.MYI
--More--myisamchk: error: Found 90690 keys of 90692
myisamchk: error: Record-count is not ok; is 90688        Should be: 90692
myisamchk: warning: Found         38 deleted blocks       Should be: 34
MyISAM-table 'lm_sendq.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: warning: 1 clients is using or hasn't closed the table properly
Data records:       1   Deleted blocks:       0

... snip ...

Checking MyISAM file: lm_sendq.MYI
Data records:   90692   Deleted blocks:      34
- check file-size
--More--myisamchk: warning: Found 15646964 record-data and 103628 unused data and 212 deleted-data
myisamchk: warning: Total 15750804, Should be: 15750840
MyISAM-table 'lm_users.MYI' is usable but should be fixed

... snip ...



Update this morning:  Woke up, ran mysqlcheck, all tables are fine, ran myisamchk and it finds no errors either.....?

[mike2]

Update:  I brought the machine down, ran FSCK on /var to make sure the disc didn't have any errors on it and it was clean, NO errors.

Upgraded the box to Fedora Core 4 and then ran YUM to upgrade all packages, so I now have:
Mysql 4.1.20-1.FC4.1
PHP 5.0.4-10.5

Deleted my email from list 1, imported the file and same problem....

BUMP/HELP

[DW]

This one is tricky!  Did you try enabling the additional error message on the import insert command as mentioned in my last post?  If so when it fails we should be given more information.

Also, is there any other way you can recreate the error consistently?  I can try creating a dummy table on your server and filling it up with data to see if that causes it.

Regards

[mike2]

This one is tricky! Did you try enabling the additional error message on the import insert command as mentioned in my last post? If so when it fails we should be given more information.

Yes I did enable what you stated, but it made no difference at all, the insert command apparently isn't failing as the user is actually getting added to the list just fine, correct?

Also, is there any other way you can recreate the error consistently? I can try creating a dummy table on your server and filling it up with data to see if that causes it.

I'm not sure what you mean by other way... this is the only time this is happening, it's only to list 1 and i've tried it 10+ times.  If I try doing the same to the other lists, they get the email just fine.

I made sure the welcome/confirm messages were ok by doing the preview on them and they went to my admin email just fine.

I don't think making a dummy table and filling it with data would help do you?  Like I said, the user is added no problem, it's that no email is sent.

One thing I was thinking about was that my list 1 is old, created way back in 2003, when I was first working with you to get sending speeds up, dunno if you remember that or not, but I was thinking maybe my list structure/indexes were different. But even if so, that wouldn't explain emails working from the other lists just fine would it?

I could give you phpmyadmin access to a "Test" DB if you think that would help?  Let me know or if you have ANY other IDEAS?

[mike2]

Ok for the heck of it, I installed a new copy of Listmail into a temporary directory and created a temporary test database for it...

On this List 1 I have NO problems with the import/email feature, it worked flawlessly right off the bat?

[mike2]

Ok so I thought I'd check out the structure of all the tables in my old original installation of listmail and the new temporary test one i setup...

There were a few things I apparently didn't get updated over the time:

Code: [Select]

lm_users table:  cseq and cdel were smallint(5), unsigned, changed to smallint(6)

lm_follow:  same as above

lm_links:  Removed Index:  refcode_2  INDEX None refcode  


Didn't think any of those would make a difference though and I was right, still no help, same problem.  

Here's a thought to what might fix this for me.  Copy the List to a new list, move the users to that list, delete original list....   Then move this list back to list#1 (only for convenience of it being first on the list, because I work with it most).

Or better yet maybe... completely move all lists/settings over to the new temporary installation to make sure all DB settings are right???

The thing is I'm not 100% sure the easiest / safest way to do this?

Thoughts/suggestions???

[DW]

Regarding the "1 or more users may be using the table" you should be shutting down MySQL before doing any repairs, of course.

service mysqld stop

..might work.

After that you could make sure all filehandles are closed:

lsof | grep mysql

To kill anything still open:

for pid in `lsof | grep /mysql | awk {'print $2'}`; do kill -9 $pid; done;

I am not sure if it is related to the installation itself and suspect a system problem, but don't know for sure...

I am not sure if the command-line check/repair utilities are the same as running repair MySQL queries.  I think you should try these first.  While MySQLd is running login to MySQL from the commandline:

mysql -u listmailuser -p

Change to the ListMail database...

use listmaildatabase

Repair the tables...

REPAIR table table1, table2

I don't have many more ideas - this is a tough one to troubleshoot from afar.  If you want me to try some hands-on testing submit your info here (Have we done this already?):

http://listmailpro.com/support?t=other

With FTP access to the ListMail files and access to ListMail I could do some debugging and hopefully narrow it right down.

Regards

[mike2]

Yea the repair shows all tables are ok... Since upgrading there are no problems like that anymore, the ones I was having I believe is because I was checking them while a mailing was running.

If you see my post above, this may be the simplest way of fixing this for me:

Code: [Select]

Here's a thought to what might fix this for me. Copy the List to a new list, move the users to that list, delete original list.... Then move this list back to list#1 (only for convenience of it being first on the list, because I work with it most).

Or better yet maybe... completely move all lists/settings over to the new temporary installation to make sure all DB settings are right???

The thing is I'm not 100% sure the easiest / safest way to do this?

Just let me know the best way to do this and I'll try it maybe...

[DW]

So the conclusion is the datafiles are somehow corrupted and we need to re-insert the data?  Anything less than copying the original tables will result in loss of users' unique IDs.  Why not perform a quick mysqldump then re-insertion:

Code: [Select]

mysqldump -p -u admin --add-drop-table listmaildb > listmail.sql
then

Code: [Select]

mysql -p -u admin -D listmaildb < listmail.sql
You could easily copy the tables to a new database for testing this way, too.

[mike2]

Gave this a shot, no luck...

Now also, when I did this and copied the listmail.sql file to the test DB, I get the same results while importing into list 1 of the test db

What next?

[DW]

Mike,

From here, I don't know... I'd like to be able to recreate the error on a set of ListMail files I can edit.  That way I can troubleshoot directly.  Are you averse to submitting your info?

Regards