MySQL : Table does not exists

[Dinath]

Bonjour à tous,

J’ai voulu activer InnoDB sur mon MySQL, j’ai donc suivi un tutoriel, suggérant de rajouter quelques lignes dans le fichier /etc/mysql/my.cnf sur le site officiel de MysqL.

J’ai également stoppé la db, supprimé les fichiers ibdata1, ib_logfile0, ib_logfile1, puis redémarré.

Bien entendu, je n’ai pas fait de backup comme je fais d’habitude, et je me suis rendu compte que mon script de sauvegarde hebdo ne fonctionnait pas sur mon sql… Il suffit d’une fois…

Enfin, depuis c’est le bronx complet ! Je ne peux pas créer de table dans mes databases, mysqlcheck ne fonctionne pas, mysqldump non plus, j’ai perdu des tables entières sur des db, d’autres n’ont pas été touché…

Sur ce site par exemple : ziweo.com/ Il ne reste que trois table avec trois fois rien dedans, et le site ne semble pas affecté…

Je suis resté toute la nuit et toute la matinée dessus, je craque… des idées ?

Merci

[Kristy]

T’as supprimer les fichiers ibdata & iblog?

Si oui, bah sachant que tes sauvegardes marchent pas A+ les datas(à ma connaissance du moins, peut etre quelqu’un me dira “mais non il y a ca qui existe” et j’aurais appris quelquechose )
Si tu les a juste déplacer, tu devrais pouvoir stop mysql, remettre ton ancien fichier de config (sans les spécifications faites sur les fichiers ibdata/iblog) remettre les fichiers ibdata & iblog dans le répertoire /var/mysql/ relancer mysql et test.

Sinon, t’as pas d’erreur au démarrage de mysql ou dans les logs ?
Du genre des erreurs de l’utilisateur debian-sys-main?

Quels sont les messages d’erreurs retourné par mysql quand tu test les commandes qui “ne marchent pas” ?

[Dinath]

Bonjour,

Pas de backup… la seule fois où je n’en fais pas… il suffit d’une fois :-/

root@alexandre:/home/dinath# mysqlcheck -u root -pxxx --auto-repair --all-databases
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
phpmyadmin.pma_bookmark                            OK
phpmyadmin.pma_column_info                         OK
phpmyadmin.pma_designer_coords                     OK
phpmyadmin.pma_history                             OK
phpmyadmin.pma_pdf_pages                           OK
phpmyadmin.pma_relation                            OK
phpmyadmin.pma_table_coords                        OK
phpmyadmin.pma_table_info                          OK
phpmyadmin.pma_tracking                            OK
phpmyadmin.pma_userconfig                          OK
piwik.piwik_access                                 OK
piwik.piwik_archive_blob_2013_01                   OK
piwik.piwik_archive_blob_2013_04                   OK
piwik.piwik_archive_blob_2013_05                   OK
piwik.piwik_archive_blob_2013_06                   OK
piwik.piwik_archive_blob_2013_07                   OK
piwik.piwik_archive_blob_2013_08
Error    : Table 'piwik.piwik_archive_blob_2013_08' doesn't exist
status   : Operation failed
piwik.piwik_archive_blob_2013_09
Error    : Table 'piwik.piwik_archive_blob_2013_09' doesn't exist
status   : Operation failed
piwik.piwik_archive_blob_2013_10
Error    : Table 'piwik.piwik_archive_blob_2013_10' doesn't exist
status   : Operation failed
piwik.piwik_archive_blob_2013_11
Error    : Table 'piwik.piwik_archive_blob_2013_11' doesn't exist
status   : Operation failed
piwik.piwik_archive_blob_2013_12
Error    : Table 'piwik.piwik_archive_blob_2013_12' doesn't exist
status   : Operation failed
piwik.piwik_archive_numeric_2013_01                OK
piwik.piwik_archive_numeric_2013_04                OK
piwik.piwik_archive_numeric_2013_05                OK
piwik.piwik_archive_numeric_2013_06                OK
piwik.piwik_archive_numeric_2013_07                OK
piwik.piwik_archive_numeric_2013_08
Error    : Table 'piwik.piwik_archive_numeric_2013_08' doesn't exist
status   : Operation failed
piwik.piwik_archive_numeric_2013_09
Error    : Table 'piwik.piwik_archive_numeric_2013_09' doesn't exist
status   : Operation failed
piwik.piwik_archive_numeric_2013_10
Error    : Table 'piwik.piwik_archive_numeric_2013_10' doesn't exist
status   : Operation failed
piwik.piwik_archive_numeric_2013_11
Error    : Table 'piwik.piwik_archive_numeric_2013_11' doesn't exist
status   : Operation failed
piwik.piwik_archive_numeric_2013_12
Error    : Table 'piwik.piwik_archive_numeric_2013_12' doesn't exist
status   : Operation failed
piwik.piwik_goal                                   OK
piwik.piwik_log_action                             OK
piwik.piwik_log_conversion                         OK
piwik.piwik_log_conversion_item                    OK
piwik.piwik_log_link_visit_action                  OK
piwik.piwik_log_profiling                          OK
piwik.piwik_log_visit                              OK
piwik.piwik_logger_api_call                        OK
piwik.piwik_logger_error                           OK
piwik.piwik_logger_exception                       OK
piwik.piwik_logger_message                         OK
piwik.piwik_option                                 OK
piwik.piwik_report                                 OK
piwik.piwik_session                                OK
piwik.piwik_site                                   OK
piwik.piwik_site_url                               OK
piwik.piwik_user                                   OK
piwik.piwik_user_dashboard                         OK
piwik.piwik_user_language                          OK
security-helpzon.mybb_adminlog                     OK
security-helpzon.mybb_adminoptions                 OK
security-helpzon.mybb_adminsessions                OK
security-helpzon.mybb_adminviews                   OK
security-helpzon.mybb_announcements                OK
security-helpzon.mybb_attachments                  OK
security-helpzon.mybb_attachtypes                  OK
security-helpzon.mybb_awaitingactivation           OK
security-helpzon.mybb_backup_attachments           OK
security-helpzon.mybb_backup_polls                 OK
security-helpzon.mybb_backup_pollvotes             OK
security-helpzon.mybb_backup_posts                 OK
security-helpzon.mybb_backup_threads               OK
security-helpzon.mybb_backup_threadsubscriptions   OK
security-helpzon.mybb_badwords                     OK
security-helpzon.mybb_banfilters                   OK
security-helpzon.mybb_banned                       OK
security-helpzon.mybb_calendarpermissions          OK
security-helpzon.mybb_calendars                    OK
security-helpzon.mybb_captcha                      OK
security-helpzon.mybb_datacache                    OK
security-helpzon.mybb_delayedmoderation            OK
security-helpzon.mybb_events                       OK
security-helpzon.mybb_forumpermissions             OK
security-helpzon.mybb_forums                       OK
security-helpzon.mybb_forumsread                   OK
security-helpzon.mybb_forumsubscriptions           OK
security-helpzon.mybb_g33k_regsecureq_questions    OK
security-helpzon.mybb_gallery_cat                  OK
security-helpzon.mybb_gallery_comment              OK
security-helpzon.mybb_gallery_permissions          OK
security-helpzon.mybb_gallery_pic                  OK
security-helpzon.mybb_gallery_report               OK
security-helpzon.mybb_gallery_settings             OK
security-helpzon.mybb_google_seo                   OK
security-helpzon.mybb_groupleaders                 OK
security-helpzon.mybb_helpdocs                     OK
security-helpzon.mybb_helpsections                 OK
security-helpzon.mybb_icons                        OK
security-helpzon.mybb_joinrequests                 OK
security-helpzon.mybb_mailerrors                   OK
security-helpzon.mybb_maillogs                     OK
security-helpzon.mybb_mailqueue                    OK
security-helpzon.mybb_massemails                   OK
security-helpzon.mybb_moderatorlog                 OK
security-helpzon.mybb_moderators                   OK
security-helpzon.mybb_modtools                     OK
security-helpzon.mybb_mycode                       OK
security-helpzon.mybb_mysb_reports                 OK
security-helpzon.mybb_mysb_shouts                  OK
security-helpzon.mybb_pages                        OK
security-helpzon.mybb_polls                        OK
security-helpzon.mybb_pollvotes                    OK
security-helpzon.mybb_posts                        OK
security-helpzon.mybb_privatemessages              OK
security-helpzon.mybb_profilefields                OK
security-helpzon.mybb_promotionlogs                OK
security-helpzon.mybb_promotions                   OK
security-helpzon.mybb_reportedposts                OK
security-helpzon.mybb_reputation                   OK
security-helpzon.mybb_searchlog                    OK
security-helpzon.mybb_sessions                     OK
security-helpzon.mybb_settinggroups                OK
security-helpzon.mybb_settings                     OK
security-helpzon.mybb_sharebuttons                 OK
security-helpzon.mybb_smilies                      OK
security-helpzon.mybb_spiders                      OK
security-helpzon.mybb_stats                        OK
security-helpzon.mybb_tasklog                      OK
security-helpzon.mybb_tasks                        OK
security-helpzon.mybb_templategroups               OK
security-helpzon.mybb_templates                    OK
security-helpzon.mybb_templatesets                 OK
security-helpzon.mybb_themes                       OK
security-helpzon.mybb_themestylesheets             OK
security-helpzon.mybb_threadprefixes               OK
security-helpzon.mybb_threadratings                OK
security-helpzon.mybb_threads                      OK
security-helpzon.mybb_threadsread                  OK
security-helpzon.mybb_threadsubscriptions          OK
security-helpzon.mybb_threadviews                  OK
security-helpzon.mybb_thx                          OK
security-helpzon.mybb_upgrade_data
Error    : Table 'security-helpzon.mybb_upgrade_data' doesn't exist
status   : Operation failed
security-helpzon.mybb_userfields                   OK
security-helpzon.mybb_usergroups                   OK
security-helpzon.mybb_users                        OK
security-helpzon.mybb_usertitles                   OK
security-helpzon.mybb_warninglevels                OK
security-helpzon.mybb_warnings                     OK
security-helpzon.mybb_warningtypes                 OK
site.ecms__banip                                   OK
site.ecms__banners                                 OK
site.ecms__category
warning  : Found row where the auto_increment column has the value 0
status   : OK
site.ecms__comment                                 OK
site.ecms__fields                                  OK
site.ecms__inc                                     OK
site.ecms__menu                                    OK
site.ecms__models                                  OK
site.ecms__news                                    OK
site.ecms__newsinstances
Error    : Table 'site.ecms__newsinstances' doesn't exist
status   : Operation failed
site.ecms__newsletter                              OK
site.ecms__site                                    OK
site.ecms__static                                  OK
site.ecms__themevars                               OK
site.ecms__userfields                              OK
site.ecms__usergroups
warning  : Found row where the auto_increment column has the value 0
status   : OK
site.ecms__users                                   OK
wordpress.wp_commentmeta
Error    : Table 'wordpress.wp_commentmeta' doesn't exist
status   : Operation failed
wordpress.wp_comments
Error    : Table 'wordpress.wp_comments' doesn't exist
status   : Operation failed
wordpress.wp_links
Error    : Table 'wordpress.wp_links' doesn't exist
status   : Operation failed
wordpress.wp_options
Error    : Table 'wordpress.wp_options' doesn't exist
status   : Operation failed
wordpress.wp_postmeta
Error    : Table 'wordpress.wp_postmeta' doesn't exist
status   : Operation failed
wordpress.wp_posts
Error    : Table 'wordpress.wp_posts' doesn't exist
status   : Operation failed
wordpress.wp_term_relationships
Error    : Table 'wordpress.wp_term_relationships' doesn't exist
status   : Operation failed
wordpress.wp_term_taxonomy
Error    : Table 'wordpress.wp_term_taxonomy' doesn't exist
status   : Operation failed
wordpress.wp_terms
Error    : Table 'wordpress.wp_terms' doesn't exist
status   : Operation failed
wordpress.wp_usermeta
Error    : Table 'wordpress.wp_usermeta' doesn't exist
status   : Operation failed
wordpress.wp_users
Error    : Table 'wordpress.wp_users' doesn't exist
status   : Operation failed
ziweo.wp_WPPortfolio_debuglog                      OK
ziweo.wp_WPPortfolio_groups                        OK
ziweo.wp_WPPortfolio_websites                      OK
ziweo.wp_commentmeta
Error    : Table 'ziweo.wp_commentmeta' doesn't exist
status   : Operation failed
ziweo.wp_comments
Error    : Table 'ziweo.wp_comments' doesn't exist
status   : Operation failed
ziweo.wp_links
Error    : Table 'ziweo.wp_links' doesn't exist
status   : Operation failed
ziweo.wp_options
Error    : Table 'ziweo.wp_options' doesn't exist
status   : Operation failed
ziweo.wp_postmeta
Error    : Table 'ziweo.wp_postmeta' doesn't exist
status   : Operation failed
ziweo.wp_posts
Error    : Table 'ziweo.wp_posts' doesn't exist
status   : Operation failed
ziweo.wp_term_relationships
Error    : Table 'ziweo.wp_term_relationships' doesn't exist
status   : Operation failed
ziweo.wp_term_taxonomy
Error    : Table 'ziweo.wp_term_taxonomy' doesn't exist
status   : Operation failed
ziweo.wp_terms
Error    : Table 'ziweo.wp_terms' doesn't exist
status   : Operation failed
ziweo.wp_usermeta
Error    : Table 'ziweo.wp_usermeta' doesn't exist
status   : Operation failed
ziweo.wp_users
Error    : Table 'ziweo.wp_users' doesn't exist
status   : Operation failed

Repairing tables
piwik.piwik_archive_blob_2013_08
Error    : Table 'piwik.piwik_archive_blob_2013_08' doesn't exist
status   : Operation failed
piwik.piwik_archive_blob_2013_09
Error    : Table 'piwik.piwik_archive_blob_2013_09' doesn't exist
status   : Operation failed
piwik.piwik_archive_blob_2013_10
Error    : Table 'piwik.piwik_archive_blob_2013_10' doesn't exist
status   : Operation failed
piwik.piwik_archive_blob_2013_11
Error    : Table 'piwik.piwik_archive_blob_2013_11' doesn't exist
status   : Operation failed
piwik.piwik_archive_blob_2013_12
Error    : Table 'piwik.piwik_archive_blob_2013_12' doesn't exist
status   : Operation failed
piwik.piwik_archive_numeric_2013_08
Error    : Table 'piwik.piwik_archive_numeric_2013_08' doesn't exist
status   : Operation failed
piwik.piwik_archive_numeric_2013_09
Error    : Table 'piwik.piwik_archive_numeric_2013_09' doesn't exist
status   : Operation failed
piwik.piwik_archive_numeric_2013_10
Error    : Table 'piwik.piwik_archive_numeric_2013_10' doesn't exist
status   : Operation failed
piwik.piwik_archive_numeric_2013_11
Error    : Table 'piwik.piwik_archive_numeric_2013_11' doesn't exist
status   : Operation failed
piwik.piwik_archive_numeric_2013_12
Error    : Table 'piwik.piwik_archive_numeric_2013_12' doesn't exist
status   : Operation failed
security-helpzon.mybb_upgrade_data
Error    : Table 'security-helpzon.mybb_upgrade_data' doesn't exist
status   : Operation failed
site.ecms__newsinstances
Error    : Table 'site.ecms__newsinstances' doesn't exist
status   : Operation failed
wordpress.wp_commentmeta
Error    : Table 'wordpress.wp_commentmeta' doesn't exist
status   : Operation failed
wordpress.wp_comments
Error    : Table 'wordpress.wp_comments' doesn't exist
status   : Operation failed
wordpress.wp_links
Error    : Table 'wordpress.wp_links' doesn't exist
status   : Operation failed
wordpress.wp_options
Error    : Table 'wordpress.wp_options' doesn't exist
status   : Operation failed
wordpress.wp_postmeta
Error    : Table 'wordpress.wp_postmeta' doesn't exist
status   : Operation failed
wordpress.wp_posts
Error    : Table 'wordpress.wp_posts' doesn't exist
status   : Operation failed
wordpress.wp_term_relationships
Error    : Table 'wordpress.wp_term_relationships' doesn't exist
status   : Operation failed
wordpress.wp_term_taxonomy
Error    : Table 'wordpress.wp_term_taxonomy' doesn't exist
status   : Operation failed
wordpress.wp_terms
Error    : Table 'wordpress.wp_terms' doesn't exist
status   : Operation failed
wordpress.wp_usermeta
Error    : Table 'wordpress.wp_usermeta' doesn't exist
status   : Operation failed
wordpress.wp_users
Error    : Table 'wordpress.wp_users' doesn't exist
status   : Operation failed
ziweo.wp_commentmeta
Error    : Table 'ziweo.wp_commentmeta' doesn't exist
status   : Operation failed
ziweo.wp_comments
Error    : Table 'ziweo.wp_comments' doesn't exist
status   : Operation failed
ziweo.wp_links
Error    : Table 'ziweo.wp_links' doesn't exist
status   : Operation failed
ziweo.wp_options
Error    : Table 'ziweo.wp_options' doesn't exist
status   : Operation failed
ziweo.wp_postmeta
Error    : Table 'ziweo.wp_postmeta' doesn't exist
status   : Operation failed
ziweo.wp_posts
Error    : Table 'ziweo.wp_posts' doesn't exist
status   : Operation failed
ziweo.wp_term_relationships
Error    : Table 'ziweo.wp_term_relationships' doesn't exist
status   : Operation failed
ziweo.wp_term_taxonomy
Error    : Table 'ziweo.wp_term_taxonomy' doesn't exist
status   : Operation failed
ziweo.wp_terms
Error    : Table 'ziweo.wp_terms' doesn't exist
status   : Operation failed
ziweo.wp_usermeta
Error    : Table 'ziweo.wp_usermeta' doesn't exist
status   : Operation failed
ziweo.wp_users
Error    : Table 'ziweo.wp_users' doesn't exist
status   : Operation failed

Et le pire… c’est que je ne peux pas créer de nouvelles tables ! Il me dit : “xxx doesn’t exist”.

dinath.net/data/images/sql_t … _exist.png

Pour MySQLDump c’est pareil… Ce qui me laisse penser que mes tables sont toujours quelque part… je l’espère du moins.

Surtout que comme je l’ai dit plus haut, y’a un site qui tourne sans base de données…

Je n’ai rien du tout dans les logs !

Merci

[Dinath]

mysql> use wordpress;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------------+
| Tables_in_wordpress   |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
11 rows in set (0.00 sec)

C’est… désolant, et que je fais un select dessus il me dit qu’elle n’existe pas…

[kripteks]

Vu que tu utilises phpmyadmin (seul méthode que j’utilises pour mysql comme un fainéant).
Fait un “export” de la base de donnée (pas table(s).

Et garde bien cet sauvegarde, avant de faire des dégâts (en plus ou pas).

Fait une copie de cet sauvegarde, et ouvre le, pour voir le contenu dans quel état il ait.
Si nécessaire, faire directement des modifications dedans et importer la base (en supprimant avant celui dans le serveur).

Je connais pas tes bases/tables supprimer, mais si c’était des données que tu utilisais pour le bon fonctionnement, ces tables on du être supprimer avec l’ordre que tu as donné, c’est l’humain et la machine.
Si c’est vraiment important.
Tu peux peut-être les retrouver avec une des méthodes qui trouve les fichiers effacer.
Mais pour cela, backup déjà tous avant. Je sais pas si ces méthodes n’affecte pas les données actuel et donc les risques sur les données actuels + config.

En tous cas, essaye de voir ce qui en est de l’état des base de donnes en forme de fichier dans le disque, en root: /var/lib/mysql/ les bases de données et tables sont en forme de répertoire et fichier, tu pourras voir si les tables manquante sont absents.

[Kristy]

Alors est ce que t’as supprimé les répertoires aussi ou pas ?
Si tu les as pas supprimé ya peut etre moyen de récupérer quelque chose en essayant les commandes de réparation base/table, de régénération d’index etc…

La suite ici : dj-j.net/waka/Linux:Administ … nn.C3.A9es

[Dinath]

Bonjour,

Certains fichiers manquent là où il n’y a plus de table.

@Kristy, ai été voir ton lien, rien n’a fonctionné… :-/

find /opt/backup/mysql/ -type f -name '*.MYI' -exec myisamchk -r -q {} \;

ça ou un “REPAIR TABLE” c’est pareil…

Merci pour vos réponses.