zmysql subsystem mysqlERROR 1146 (42S02):

General discussion on Zend Core for IBM System i
Post Reply
garrethdk
Posts: 4
Joined: Tue Dec 15, 2009 9:54 am

zmysql subsystem mysqlERROR 1146 (42S02):

Post by garrethdk » Tue Mar 01, 2011 7:09 pm

our iseries machine hosting mysql using zendcore failed to shut down correctly ,
when i go into mysql i can see the database but get the following
Table 'sckb.protected_titles' doesn't exist
when i query a table ,
is there any way of correcting the database ?
i have tried
bin/mysqlcheck --user=root --password=xxxx -A -e
bin/mysqlcheck --user=root --password=xxxx sckb -e
have tried with the --exteneded and with --repair ,
anyone ever come across this ?
regards,
Garreth.

timclark2000
Posts: 68
Joined: Fri May 01, 2009 8:19 pm

Re: zmysql subsystem mysqlERROR 1146 (42S02):

Post by timclark2000 » Wed Mar 02, 2011 10:32 pm

If you do the following:
use sckb;
show tables;
does protected_titles show up in the list of tables?

If so, I suspect that the IBM i user profile that MySQL is being started under does not match the profile that MySQL was running under when the table was created. As a result, while MySQL can see the table, it does not have permission to read the data from the table. Depending on the engine that was used to create the table, you will see one or more files with the name of your table in the IFS under /usr/local/mysqldata/sckb. What user profile owns those files? Does it match the user profile that the mysqld process is running under?

garrethdk
Posts: 4
Joined: Tue Dec 15, 2009 9:54 am

Re: zmysql subsystem mysqlERROR 1146 (42S02):

Post by garrethdk » Thu Mar 03, 2011 12:32 pm

Hi Tim,
Thanks for the response , the user is mysql in both cases and protected_titles does show up on the list

Code: Select all

                    Current                                         
Opt  Subsystem/Job  User        Type  CPU %  Function        Status 
     ZMYSQL         QSYS        SBS      .0                   DEQW  
       ZMYSQLD      MYSQL       BCI      .0  PGM-mysqld       SELW  
and the object owner for the data is

Code: Select all

                              Display Attributes 
                                                 
Object . . . . . . :   /usr/local/mysqldata/sckb 
                                                 
Type . . . . . . . . . . . . . . . . . :   DIR   
                                                 
Owner  . . . . . . . . . . . . . . . . :   MYSQL 
System object is on  . . . . . . . . . :   Local 
the user mysql also has access to RWX on everything in the subfolders ,
Strange one , this happend me before an i had to drop the database and rebuild it , can't believe this should be the case ,
Kind regards,
Garreth.

timclark2000
Posts: 68
Joined: Fri May 01, 2009 8:19 pm

Re: zmysql subsystem mysqlERROR 1146 (42S02):

Post by timclark2000 » Thu Mar 03, 2011 10:14 pm

Hmm...

Are you using MyISAM or InnoDB for this table?

Is there anything interesting in the error log (/usr/local/mysqldata/*.err) at the time that the error occurs?

Is it just one of the tables that can't be found? Or does it occur with all of the tables in the database?

Does user MYSQL have *X authority to /usr/local/mysqldata/sckb? Even though it is the owner of the directory, it still may not have sufficient authority.

garrethdk
Posts: 4
Joined: Tue Dec 15, 2009 9:54 am

Re: zmysql subsystem mysqlERROR 1146 (42S02):

Post by garrethdk » Fri Mar 04, 2011 10:33 am

Thanks again for replying Tim ,
1. From looking at the database ,one of the tables seems to be MyISAM and the rest InnoDB
2. Could not find any files with .Err or .Log in either the /usr/local/mysql or /usr/local/mysqldata directories
3. See the mysqlcheck results all the tables are messed up bar searchindex which seems to be MyISAM

Code: Select all

bin/mysqlcheck --user=root --password=xxxx sckb
sckb.archive                                                     
error    : Table 'sckb.archive' doesn't exist                    
sckb.category                                                    
error    : Table 'sckb.category' doesn't exist                   
sckb.categorylinks                                               
error    : Table 'sckb.categorylinks' doesn't exist              
sckb.change_tag                                                  
error    : Table 'sckb.change_tag' doesn't exist                 
sckb.externallinks                                               
error    : Table 'sckb.externallinks' doesn't exist              
sckb.filearchive                                                 
error    : Table 'sckb.filearchive' doesn't exist                
sckb.hitcounter                                                  
note     : The storage engine for the table doesn't support check
sckb.image                                       
error    : Table 'sckb.image' doesn't exist      
sckb.imagelinks                                  
error    : Table 'sckb.imagelinks' doesn't exist 
sckb.interwiki                                   
error    : Table 'sckb.interwiki' doesn't exist  
sckb.ipblocks                                    
error    : Table 'sckb.ipblocks' doesn't exist   
sckb.job                                         
error    : Table 'sckb.job' doesn't exist        
sckb.langlinks                                   
error    : Table 'sckb.langlinks' doesn't exist  
sckb.logging                                     
error    : Table 'sckb.logging' doesn't exist    
sckb.math                                        
error    : Table 'sckb.math' doesn't exist       
sckb.objectcache
error    : Table 'sckb.objectcache' doesn't exist       
sckb.oldimage                                           
error    : Table 'sckb.oldimage' doesn't exist          
sckb.page                                               
error    : Table 'sckb.page' doesn't exist              
sckb.page_props                                         
error    : Table 'sckb.page_props' doesn't exist        
sckb.page_restrictions                                  
error    : Table 'sckb.page_restrictions' doesn't exist 
sckb.pagelinks                                          
error    : Table 'sckb.pagelinks' doesn't exist         
sckb.protected_titles                                   
error    : Table 'sckb.protected_titles' doesn't exist  
sckb.querycache                                         
error    : Table 'sckb.querycache' doesn't exist        
sckb.querycache_info                                    
error    : Table 'sckb.querycache_info' doesn't exist   
sckb.querycachetwo                                      
error    : Table 'sckb.querycachetwo' doesn't exist     
sckb.recentchanges                                      
error    : Table 'sckb.recentchanges' doesn't exist     
sckb.redirect                                           
error    : Table 'sckb.redirect' doesn't exist          
sckb.revision                                           
error    : Table 'sckb.revision' doesn't exist          
sckb.searchindex                                   OK   
sckb.site_stats                                         
error    : Table 'sckb.site_stats' doesn't exist        
sckb.tag_summary                                        
error    : Table 'sckb.tag_summary' doesn't exist       
sckb.templatelinks                                      
error    : Table 'sckb.templatelinks' doesn't exist     
sckb.text                                               
error    : Table 'sckb.text' doesn't exist              
sckb.trackbacks                                    
error    : Table 'sckb.trackbacks' doesn't exist   
sckb.transcache                                    
error    : Table 'sckb.transcache' doesn't exist   
sckb.updatelog                                     
error    : Table 'sckb.updatelog' doesn't exist    
sckb.user                                          
error    : Table 'sckb.user' doesn't exist         
sckb.user_groups                                   
error    : Table 'sckb.user_groups' doesn't exist  
sckb.user_newtalk                                  
error    : Table 'sckb.user_newtalk' doesn't exist 
sckb.valid_tag                                     
error    : Table 'sckb.valid_tag' doesn't exist    
sckb.watchlist                                     
error    : Table 'sckb.watchlist' doesn't exist    
4. The mysql user has access to everything ! i checked each individual file and it has *RWX

Code: Select all

Object . . . . . . . . . . . . :   /usr/local/mysqldata/sckb             
Type . . . . . . . . . . . . . :   DIR                                   
Owner  . . . . . . . . . . . . :   MYSQL                                 
Primary group  . . . . . . . . :   *NONE                                 
Authorization list . . . . . . :   *NONE                                 
                                                                         
Type options, press Enter.                                               
  1=Add user   2=Change user authority   4=Remove user                   
                                                                         
                   Data     -------------Data Authorities-------------   
Opt  User        Authority  Objopr  Read  Add  Update  Delete  Execute   
                                                                         
     *PUBLIC     *EXCLUDE                                                
     MYSQL       *RWX         X      X     X     X       X        X     
I've tried copying the data off the iseries and using it on a linux box and a windows box with no joy either ,
some of the repair tools i've downloaded don't seem to recover any data either !
Will keep look :)
Kind regards,
Garreth.

timclark2000
Posts: 68
Joined: Fri May 01, 2009 8:19 pm

Re: zmysql subsystem mysqlERROR 1146 (42S02):

Post by timclark2000 » Fri Mar 04, 2011 4:18 pm

Hi Garreth,

It sounds like you've checked out everything I can think of to suggest. I'm guessing that this is a corruption issue with the InnoDB data store. A quick web search on 1146 and InnoDB indicates that this isn't an isolated issue, unfortunately. Wish you the best.

Tim

Post Reply