Tag Archives: mysql

python 3.5 – install oracle mysql driver on ubuntu [solved]

Installing this mysql driver is surprisingly quite a painful experience. Steps Download package relevant for your distribution and python version from oracle website: https://dev.mysql.com/downloads/connector/python/ Install downloaded package: sudo dpkg -i mysql-connector-python-py3 _2.1.3-1ubuntu15.04_all.deb Go into python and run “import mysql” For all virtualenvs, simply symlink: ln -s /usr/lib/python3/dist-packages/mysql <path_to_virtual_env>/lib/pythonX.X/site-packages It should be good by now.

PhpMyAdmin: change default collation / charset

To change default collation proprosed in phpmyadmin, simply define the following entry in you config.inc.php file: $cfg[‘DefaultConnectionCollation’] = ‘utf8_general_ci’; That’s it. sources http://wiki.phpmyadmin.net/pma/Config  

Mysql: change default encoding

Let’s say you want to change default encoding for mysql from latin1 to utf8, simply add the following lines to /etc/mysql/my.cnf under [mysqld] section: #default-character-set = “utf8” #comment: this one is not working collation-server = “utf8_general_ci” character-set-server = “utf8” Don’t forget to reload mysql server (/etc/init.d/mysql restart) That’s it. source http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

Mysql: how to change default storage engine

If you want to change default storage engine for mysql (currently set to MyISAM on my version), simply set the following option into /etc/mysql/my.cnf: default-storage-engine = INNODB Don’t forget to reload mysql server (/etc/init.d/mysql restart) sources http://dev.mysql.com/doc/refman/5.1/en/storage-engine-setting.html

Mysql: how to enable strict-mode to reject non-valid ENUM values

To have mysql reject invalid enum-values such as empty fields, you can enable strict mode. You can do this in multiple ways, including real-time by setting a global var, unfortunately running an app with full rights on mysql table is really risky, as such we will focus on activating strict mode at configuration file level. edit /etc/mysql/my.cnf add|edit the following entry (new line): sql-mode = STRICT_ALL_TABLES save your changes & restart mysql server (/etc/init.d/mysql restart) That’s it! If you try inserting an empty value in an ENUM field, it should now be rejected by mysql. Sources http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_sql_mode http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

How to restore mysql databases using raw files?

My server was recently hacked (damn it!), I tried to restore mysql dbs using my regular backup procedure to find out that mysql-dump backup was not working properly => unique solution remaining, restore mysql db from raw files (located into /var/lib/mysql). Copying & pasting /var/lib/mysql folder did not make it, neither a copy & paste of only my own db folders. What I had to do was the following: reinstall mysql as usual (apt-get install mysql) and changing root password (along with password for all user-apps as well) <= the server was hacked! note: update your app code to reflect […]

How to retrieve list of mysql users?

Mysql users are stored into ‘mysql’ db and ‘user’ table, therefore to retrieve the list of all registered users, simply execute the following query: select * from mysql.user; or, for a less detailed list: select User, Host, Password from mysql.user; sources http://forums.digitalpoint.com/showthread.php?t=280484

control mysql & apache logging level

If you want to avoid ‘too many connections’ error due to a full-disk error, you should consider modifying your mysql/apache log settings. MySQL First we will check our running log settings, then we will see how to change them permanently. To do this, we will need the help of ‘mysqladmin’ available within mysql-client package sudo apt-get install mysql-client-5.1 update this command to match your mysql version mysqladmin variables -u mysqlusername -p  | grep log then enter your mysql user password when requested In my case: | log | OFF | | log_bin | OFF | | log_bin_trust_function_creators | OFF | | […]

unable to stop / start / restart mysql server

Each time I tried to stop / start /restart mysql server, it ended in a ‘failed!’ message: Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed! I killed it through its pid, updated mysql package and it was unable to start again!!!!! After looking at /var/log/syslog, I had multiple error messages such as: error: ‘Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ mysql invoke-rc.d: initscript mysql, action “start” failed After a couple of hours, I tried quick and dirty fix advised here (http://www.debianhelp.org/node/1962), I simply remove my mysql config […]

move rows from one table into another

Let’s say you want to move rows from one table into another table within the same db (for instance, you implement an ‘undo’-like operation), you can do it pretty easily with a single sql statement: INSERT INTO table2 SELECT * FROM table1 where row = row id Of course, feel free to adapt it to your own requirements. sources http://www.dbforums.com/ansi-sql/952421-move-row-one-table-another.html