[Ask] Doesn't exist (1146) / Doesn't exist when using LOCK TABLES


Status
Not open for further replies.
haha. skali2 buat jantungan org dong. hal sperti ini lah yg membuat schedule backup di perlukan :)
 
Makin pesimis saya Om, tetap doesn't exist katanya

mysql> use DatabaseName;
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> ALTER TABLE DatabaseName.yuf6e_assets ENGINE=InnoDB;
ERROR 1146 (42S02): Table 'DatabaseName.yuf6e_assets' doesn't exist
mysql> show tables;
+-----------------------------------------+
| Tables_in_DatabaseName |
+-----------------------------------------+
| yuf6e_assets |
| yuf6e_associations |
| yuf6e_banner_clients |
| yuf6e_banner_tracks |
 
coba lakukan query select pada table yuf6e_assets
misal :
select * from yuf6e_assets
select (*) from yuf6e_assets

apakah data bisa tampil semua... atau ada record ke Berapa yang Corrupt?
kalo bisa tampil semua,, lakukan dump per table aja

coba pake scrip .sh ini Tuan

misal simpan backup.sh
chmod 777 backup.sh
./backup.sh

=======================================

#!/bin/bash
### change the values below where needed.....
DBNAMES="Nama Database Anda"
HOST="--host=localhost"
USER="--user=root"
PASSWORD="--password=tempe"
BACKUP_DIR="/data/backup"

#### you can change these values but they are optional....
OPTIONS="--default-character-set=latin1 --complete-insert --no-create-info --compact -q"
RESTORESCRIPT="$BACKUP_DIR/__restoreData.sql"
#DATE=`/bin/date '+%y%m%d_%H%M%S'`
DATE=`/bin/date '+%d%m%Y_%H%M%S'`


#### make no changes after this....
#### start script ####
echo removing old temporary files if they exists...
rm -f ${BACKUP_DIR}/*.sql > /dev/null 2>&1
rm -f ${BACKUP_DIR}/*.tar > /dev/null 2>&1
cd ${BACKUP_DIR}

for DB in $DBNAMES
do
echo "=========================================="
echo ${DB}
echo "=========================================="
echo 'SET FOREIGN_KEY_CHECKS=0;' > $RESTORESCRIPT

mysqldump --no-data $HOST $USER $PASSWORD $DB > ${BACKUP_DIR}/__createTables.sql
echo 'source __createTables.sql;' >> $RESTORESCRIPT

for TABLE in `mysql $HOST $USER $PASSWORD $DB -e 'show tables' | egrep -v 'Tables_in_' `; do
TABLENAME=$(echo $TABLE|awk '{ printf "%s", $0 }')
FILENAME="${TABLENAME}.sql"
echo Dumping $TABLENAME
echo 'source' $FILENAME';' >> $RESTORESCRIPT
mysqldump $OPTIONS $HOST $USER $PASSWORD $DB $TABLENAME > ${BACKUP_DIR}/${FILENAME}
done

echo 'SET FOREIGN_KEY_CHECKS=1;' >> $RESTORESCRIPT

echo making tar...
tar -cf ${DB}_${DATE}.tar *.sql > /dev/null 2>&1

echo compressing...
gzip -9 ${DB}_${DATE}.tar > /dev/null 2>&1

echo removing temporary files...
rm -f ${BACKUP_DIR}/*.sql > /dev/null 2>&1
rm -f ${BACKUP_DIR}/*.tar > /dev/null 2>&1

echo "done with " $DB
done

echo "=========================================="
echo " done with all database! "
echo "=========================================="
 
Thanks Om
antochoy said:
coba lakukan query select pada table yuf6e_assets
misal :
select * from yuf6e_assets
select (*) from yuf6e_assets
Click to expand...
mysql> select * from yuf6e_assets;
ERROR 1146 (42S02): Table 'DatabaseName.yuf6e_assets' doesn't exist
mysql>
Katanya tetap doesn't exist Om, namun pada saat di show tabel muncul,

antochoy said:
apakah data bisa tampil semua... atau ada record ke Berapa yang Corrupt?
kalo bisa tampil semua,, lakukan dump per table aja

coba pake scrip .sh ini Tuan

misal simpan backup.sh
chmod 777 backup.sh
./backup.sh

=======================================

#!/bin/bash
### change the values below where needed.....
DBNAMES="Nama Database Anda"
HOST="--host=localhost"
USER="--user=root"
PASSWORD="--password=tempe"
BACKUP_DIR="/data/backup"

#### you can change these values but they are optional....
OPTIONS="--default-character-set=latin1 --complete-insert --no-create-info --compact -q"
RESTORESCRIPT="$BACKUP_DIR/__restoreData.sql"
#DATE=`/bin/date '+%y%m%d_%H%M%S'`
DATE=`/bin/date '+%d%m%Y_%H%M%S'`


#### make no changes after this....
#### start script ####
echo removing old temporary files if they exists...
rm -f ${BACKUP_DIR}/*.sql > /dev/null 2>&1
rm -f ${BACKUP_DIR}/*.tar > /dev/null 2>&1
cd ${BACKUP_DIR}

for DB in $DBNAMES
do
echo "=========================================="
echo ${DB}
echo "=========================================="
echo 'SET FOREIGN_KEY_CHECKS=0;' > $RESTORESCRIPT

mysqldump --no-data $HOST $USER $PASSWORD $DB > ${BACKUP_DIR}/__createTables.sql
echo 'source __createTables.sql;' >> $RESTORESCRIPT

for TABLE in `mysql $HOST $USER $PASSWORD $DB -e 'show tables' | egrep -v 'Tables_in_' `; do
TABLENAME=$(echo $TABLE|awk '{ printf "%s", $0 }')
FILENAME="${TABLENAME}.sql"
echo Dumping $TABLENAME
echo 'source' $FILENAME';' >> $RESTORESCRIPT
mysqldump $OPTIONS $HOST $USER $PASSWORD $DB $TABLENAME > ${BACKUP_DIR}/${FILENAME}
done

echo 'SET FOREIGN_KEY_CHECKS=1;' >> $RESTORESCRIPT

echo making tar...
tar -cf ${DB}_${DATE}.tar *.sql > /dev/null 2>&1

echo compressing...
gzip -9 ${DB}_${DATE}.tar > /dev/null 2>&1

echo removing temporary files...
rm -f ${BACKUP_DIR}/*.sql > /dev/null 2>&1
rm -f ${BACKUP_DIR}/*.tar > /dev/null 2>&1

echo "done with " $DB
done

echo "=========================================="
echo " done with all database! "
echo "=========================================="
Click to expand...
Ok Om akan dicoba jika sdh bs diselect tabelnya
 
waduh, sudah di ingetin :D

udah kelar kah?
 
@ mas ini file ib_data file nya di hapus ya ? , setahu saya di file ib_data ini file2 mysql dan struktur2 table nya disana , apabila mas hapus maka mas menghapus data2 mysql nya
dan bs direstore dr backup apabila ada, untuk ib_log file ini yg aman untuk dihapus.
kl saya biasanya daripada dihapus lebih baik di rename misal ib_data1.bak dsb buat mencegah hal2 yg ga di inginkan


The file ibdata1 is the system tablespace for the InnoDB infrastructure.

It contains several classes for information vital for InnoDB

  • Table Data Pages
  • Table Index Pages
  • Data Dictionary
  • MVCC Control Data
    • Undo Space
    • Rollback Segments
  • Double Write Buffer (Pages Written in the Background to avoid OS caching)
  • Insert Buffer (Changes to Secondary Indexes)
apabila file ini dihapus maka mysql ga bs direpair karena missing data diatas , mohon koreksi jika saya salah memberikan penjelasan.
 
vishualhost said:
@ mas ini file ib_data file nya di hapus ya ? , setahu saya di file ib_data ini file2 mysql dan struktur2 table nya disana , apabila mas hapus maka mas menghapus data2 mysql nya
dan bs direstore dr backup apabila ada, untuk ib_log file ini yg aman untuk dihapus.
kl saya biasanya daripada dihapus lebih baik di rename misal ib_data1.bak dsb buat mencegah hal2 yg ga di inginkan


The file ibdata1 is the system tablespace for the InnoDB infrastructure.

It contains several classes for information vital for InnoDB

  • Table Data Pages
  • Table Index Pages
  • Data Dictionary
  • MVCC Control Data
    • Undo Space
    • Rollback Segments
  • Double Write Buffer (Pages Written in the Background to avoid OS caching)
  • Insert Buffer (Changes to Secondary Indexes)
apabila file ini dihapus maka mysql ga bs direpair karena missing data diatas , mohon koreksi jika saya salah memberikan penjelasan.
Click to expand...

Nah itu dia Om, sangkin semangatnya saya, langsung saya rm -rf ib* :D
Namun sebelum itu sudah saya cp ib* /opt/backupfile
Setelah itu saya restart service mysqlnya eh running, baru saya nydar setelah database lamanya saya tdk bisa akses (jika yg baru bisa),
Kemudian file ibdata1 yang baru terbentuk saya pindahin lalu saya kembalikan file ibdata1 yg tadinya saya simpan, eh malah ga bs running lg mysqlnya katanya file ibdatanya crash :20:

Ada saran kira2 Om, jika kedepan saya alami hal ini lg :D?
 
Status
Not open for further replies.
Back
Top