Selecting/casting output as integer in SQL
SELECT CAST(sum(number)/count(number) as UNSIGNED) as average, date FROM stats WHERE * GROUP BY date
mysql
How to check event scheduler status in MySQL?
Use SHOW VARIABLES SHOW VARIABLES WHERE VARIABLE_NAME = ‘event_scheduler’
ALTER TABLE ADD CONSTRAINT gets Error 1064
Omit the parenthesis: ALTER TABLE User ADD CONSTRAINT userProperties FOREIGN KEY(properties) REFERENCES Properties(ID)
ld: library not found for -lzstd while bundle install for mysql2 gem Ruby on macOS Big Sur 11.4 Apple M1
Step 1: confirm you have both openssl and MySQL installed via brew brew install mysql brew install openssl or if you have previous version of openssl try brew reinstall openssl@3 Step 2: Run this on your Rails app to make sure you can get through bundler: gem install mysql2 -v ‘0.5.3’ — –with-opt-dir=$(brew –prefix openssl) … Read more
How to change the MySQL root account password on CentOS 7?
What version of mySQL are you using? I”m using 5.7.10 and had the same problem with logging on as root There is 2 issues – why can’t I log in as root to start with, and why can I not use ‘mysqld_safe` to start mySQL to reset the root password. I have no answer to … Read more
Remove last two characters of a string column in MySQL
To select all characters except the last n from a string (or put another way, remove last n characters from a string); use the SUBSTRING and CHAR_LENGTH functions together: SELECT col , SUBSTRING(col FROM 1 FOR CHAR_LENGTH(col) – 2) AS col_trimmed — ANSI Syntax , SUBSTRING(col, 1, CHAR_LENGTH(col) – 2) AS col_trimmed — MySQL Syntax … Read more
Execute command denied to user ”@’localhost’ for routine error
It works….. I try to grant this priviledge in root. log in as root GRANT EXECUTE ON PROCEDURE TestMediaControl.monthavrage TO ‘jeinqa’@’localhost’ flush privileges;
Describe all tables in the database in a single statement?
There is no statement that describe all tables at once. Here’s some options: SELECT * FROM information_schema.columns WHERE table_schema=”db_name”; SELECT * FROM information_schema.columns WHERE table_schema=”db_name” ORDER BY TABLE_NAME, ORDINAL_POSITION; SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, COLUMN_COMMENT, ORDINAL_POSITION FROM information_schema.columns WHERE table_schema=”db_name” ORDER BY TABLE_NAME, ORDINAL_POSITION; SELECT * FROM information_schema.columns WHERE table_schema != ‘information_schema’;
Take perfect backup with mysqldump
If you want to take a full backup i.e., all databases, procedures, routines, and events without interrupting any connections: mysqldump -u [username] -p -A -R -E –triggers –single-transaction > full_backup.sql -A For all databases (you can also use –all-databases) -R For all routines (stored procedures & triggers) -E For all events –single-transaction Without locking the … Read more
Export MySQL database with triggers and procedures?
mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior: –routines – FALSE by default –triggers – TRUE by default so in mysqldump command , add –routines like : mysqldump <other mysqldump options> –routines > outputfile.sql See the MySQL documentation about mysqldump arguments.