Tag Info

Hot answers tagged

5

You can also use BIN() function. You still have to LPAD() though, the higher order 0s are not displayed: LPAD(BIN(hash), 64, '0')


4

You can use the pager (see docs) command in MySQL console or use the --pager startup option (docs again) to redirect the output of your commands to an external executable. To use the pager command and list the tables try this: mysql> pager less mysql> show tables You can also start MySQL to send all your output to an external executable like this ...


3

You might also find the tee command to be of use. Log all output to an operating system file mytabs.txt. mysql> tee mytabs.txt; Logging to file 'mytabs.txt' Issue your show tables command as below. mysql> show tables; +-----------------+ | Tables_in_sport | +-----------------+ | billy | | player | | seasons | | team ...


2

After a little more messing around I've found that I can skip the CAST() and just use CONV(), but it still seems as though there should be a better way: select conv(hash, 2, 2) hash from `table`; I also am using LPAD() to get the full 64-characters, which would be nice to avoid if possible (I'm hoping there's a function that infer the length from the ...


2

The query will be slow because cardinality of category index is low. There are 12 categories, so in average the query will read 1/12 part of the index. You can't improve this query. Your original approach can improve overall performance. Just instead of manually updating book_count create a trigger on INSERT and DELETE event. UPDATE: To prove the query ...


1

You will notice in the errors this message InnoDB: innodb_force_recovery is on: we do not allow InnoDB: database modifications by the user. Shut down So please remove the innodb_force_recovery option from my.cnf


1

Get rid of UNIX_TIMESTAMP() everywhere. DATETIME fields can be compared directly. Once you have done that, you will probably notice the missing @ that is causing the error. I think it can be simplified down to only: CREATE TRIGGER ... IF COALESCE(OLD.updateDate, OLD.insertDate) < CURRENT_DATE() - interval 3 month INSERT INTO ...


1

MySQL doesn't reduce/reclaim the size of previously deleted data. If the previous .ibd file grew significantly but the actual data only accounted for a portion of its size, running OPTIMIZE TABLE can reclaim the unused space. Source: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html To verify MySQL replication integrity, you may want ...


1

With the changing of the clock times on the Master there might be loss of data for the slave a slave may replay a query it already processed Rather than risk either of these extremes, you should schedule a brief downtime. Here is what you should do: STEP 01: Stop all writes to the Master SET GLOBAL read_only = 1; FLUSH TABLES WITH READ LOCK; STEP ...


1

The ".frm" file holds table definition. You might want to check mysql file-types info. Usually when you move data-base physically do following: Shutdown mysql Copy datafiles to datadir make sure mysql has full permissions on it: chown mysql:mysql datadir/ -R start mysql So, make sure that the tables which are not "visible" have proper permissions for ...


1

It sounds like you lack the SUPER privilege (global permission). When you connect to MySQL, run SHOW GRANTS; If you see more than one line, you do not have SUPER. What does having SUPER give you ? The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill ...


1

PROLOGUE Someone asked the same thing of me in my organization because everyone was using MySQL 5.5. All DB servers was upgraded over the past 8 months to MySQL 5.6. Some client applications were being affected by sql_mode change as well. ROOT CAUSE I just found out why what you did does not work and the workaround is very simple. According to MySQL 5.5 ...


1

The output is only showing the current query in a transaction. What else is in the BEGIN...COMMIT? Or are you running autocommit=0? Or =1? SELECT grabs shared locks on the rows it is looking at, for the duration of the transaction. INSERT grabs exclusive lock(s). It sounds like the SELECT already has a lock that the INSERT desires. Perhaps the SELECT ...



Only top voted, non community-wiki answers of a minimum length are eligible