SHOW COLUMNS FROM tablenamehttps://stackoverflow.com/questions/4165195/mysql-query-to-get-column-names
Category Archives: MySql
PostgreSQL remote connection
Problem: Cannot connect remotely mysql or postgresql
Solution:
Allow ports 3306 for mysql or 5432 for pgs.
Go to /etc/mysql/mariadb.conf.d/50-server.cnf and change bind-address = 0.0.0.0. Restart mysql pr maria actually.
Go to hestiacp postgresql configuration part. Go and change listen_addresses = ‘*’, remove comment. Restart psg.
Done.
MySQL for reference
When removing old files from server and then update mysql:
UPDATE`catalogues`SET`active`='0'WHERE validnost_end_date <='2021-02-01 00:00:00';Delete duplicate entries mysql
delete t1 FROM cataloguestext_2 t1
INNER JOIN cataloguestext_2 t2
WHERE
t1.id < t2.id AND
t1.page = t2.page AND
t1.catalogues_id = t2.catalogues_id;
ref https://phoenixnap.com/kb/mysql-remove-duplicate-rows
Adminer without password
Very important sometimes.
Download adminer .php and then change this on line 1152.
login($_e,$F){if($F==””)return to login($_e,$F){if($F==”abc”)return.
Save and you are good to go. Delete after everything has been setup and working ok.
Complete mysql backup from terminal
Leave a reply
I have tried it and it works. Make sure to delete history with password, if you used it in the terminal.
https://stackoverflow.com/questions/10867520/mysqldump-with-db-in-a-separate-file
MySQL import/export dump timestamp problem
I have just lost 4 hours today for a simple export import problem. Why it always has to be that way?!?!?
I had to transfer ip of the domain with the data and of course db. Like always everything was smooth but after trying domain (after dns change) I had to pictures online?!
I have tried everything. Problem was simple (after the battle everyone are generals) – it was mysql :
Dump TIMESTAMP columns in UTC (enables TIMESTAMP columns to be dumped and reloaded between servers in different time zones)
Mysql columns were using timestamps because of the start and end date (23:59:59) for local time BUT when exporting that option was clicked and made strange dates on the importing mysql server.
After unchecking this box time was still strange but at least dates were not going -1 day.
Note to myself – ODS and mysql
When importing data you have got from the datacenter (statistical files) use first openoffice .ods format and import it into already built table (copied table with all the structure) and then just import files to that table. Make sure that openoffice sheet is the same name as db table.
If you previously made table (copied table from table you were using before) then the data will just be imported automatically. You might have some problems with columns number but predefined table (copied) must be the same fields count as with .ods file you are importing.
End of note to myself
MySQL rand function
If you need to add some random number to some of the mysql columns then here it goes…
UPDATE tableSET column = FLOOR( 1 + RAND( ) *100 )
The last number (100) means you are about to add any value until 100. So 99, 1, 5, 33, 45…any between 1 and 100.
If you put 10 instead of 100 then you will get between 1 and 10 (2, 5, 6).
Returns a random floating-point value v in the range 0 <= v < 1.0.
RAND
That’s it.
MariaDB/MySql server – load data infile
When using php7.2 and onward make sure you are having:
mysqli.allow_local_infile = On
in php.ini setup or uncommented.
Otherwise this will not work. I’ve spent 2 days trying to make it working on mariaDB config file because few years ago I had the same problems and most of it were on DB end (optimization) but now this totally surprised me. It was on php end.
This is really nice stuff. Check it
https://stackoverflow.com/questions/13016797/load-data-local-infile-fails-from-php-to-mysql-on-amazon-rds?rq=1
Please not…ini_set is not always working so you might need to change allow_local_infile directly to the server’s php.ini for specific php version. I’m using directadmin almost every day. Have 3 php versions installed and testing everything there.