WHAT DO YOU MEAN YOU WERE ONLY JOKING WHEN YOU SAID “DROP DATABASE *” WAS A REPAIR COMMAND – lol
Emulate Oracle's ROWNUM :
DROP TABLE scores ; CREATE TABLE scores ( id int(8) NOT NULL AUTO_INCREMENT, value int(8) DEFAULT 0, rank int(8) DEFAULT 0, PRIMARY KEY (id) ); INSERT INTO scores (id, value, rank) VALUES ('1', '10', '0'), ('2', '20', '0'); UPDATE scores SET value = value+1; SET @rownum =0; UPDATE scores t, ( SELECT @rownum := @rownum +1 rownum, scores. * FROM scores ORDER BY value DESC ) r SET t.rank = r.rownum WHERE ( t.id = r.id); SELECT * from scores ORDER BY rank ASC; SELECT *,@bonus:=CONCAT(rank,value) FROM scores ORDER BY @bonus;
Misc:
mysqldump --add-drop-table --extended-insert -u ${USER} -p ${PASSWORD} ${USER}
Howto :
sudo aptitude install mysql-server mysql> create database tmp
DROP TABLE lines ; CREATE TABLE lines ( id int(8) PRIMARY KEY, line TEXT ); /* SQL error: lines.id may not be NULL */ INSERT INTO lines (id,line) VALUES ( 1, 'one') ; INSERT INTO lines (id,line) VALUES ( 2, 'two') ; INSERT INTO lines VALUES ( NULL, 'three') ; INSERT INTO lines (id,line) VALUES ( null, 'four') ; INSERT INTO lines (line) VALUES ( 'five' ) ; SELECT COUNT(*) FROM lines; /* #| 5 */ # sqlite3 "${file}" ".dump"
cd $HOME/.config/f-spot file=photos.db sqlite3 "${file}" ".dump" | tee $file.sql grep -o "file://[^\']*" $file.sql | sort | uniq | tee $file.txt RAGMA foreign_keys=OFF; BEGIN TRANSACTION; /**** ERROR: (10) disk I/O error *****/
“mysqldump: Got error: 1045: Access denied for user”
Checking for corrupt, not cleanly closed and upgrade needing tables..
sudo aptitude reinstall --purge mysql-server-5.0 sudo dpkg-reconfigure --force --priority=low mysql-server-5.0 Stopping MySQL database server: mysqld. Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables.. mysqlcheck --repair --all-databases mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
mysql --user=root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 32 Server version: 5.0.51a-17 (Debian) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database tmp; http://dev.mysql.com/doc/refman/5.0/en/adding-users.html
How to replace paths in fspot datatabase :
sudo apt-get install sqlite3
sqlite3 -list ~/.config/f-spot/photos.db ".dump" | less
How to export / merge tags :
sudo apt-get install freetds-bin tsql -S "$host" -U "$user" -P "$password" -D "$database" SELECT * FROM information_schema.tables go