Insert into a MySQL table or update if exists
Use INSERT … ON DUPLICATE KEY UPDATE QUERY:
mysql
Convert partially non-numeric text into number in MySQL query
This should work:
ERROR 1148: The used command is not allowed with this MySQL version
You can specify that as an additional option when setting up your client connection: This is because that feature opens a security hole. So you have to enable it in an explicit manner in case you really want to use it. Both client and server should enable the local-file option. Otherwise it doesn’t work.To enable … Read more
ImportError: No module named mysql.connector using Python2
Depending on your python version and how you installed it, it is possible that mysql connector isn’t installed, you can install it with pip To install mysql connector:
How to create a MySQL hierarchical recursive query?
For MySQL 8+: use the recursive with syntax.For MySQL 5.x: use inline variables, path IDs, or self-joins. MySQL 8+ The value specified in parent_id = 19 should be set to the id of the parent you want to select all the descendants of. MySQL 5.x For MySQL versions that do not support Common Table Expressions (up to version 5.7), you would achieve this with the … Read more
SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 102
You didn’t bind all your bindings here You’ve declared a binding called :numRows but you never actually bind anything to it. UPDATE 2019: I keep getting upvotes on this and that reminded me of another suggestion Double quotes are string interpolation in PHP, so if you’re going to use variables in a double quotes string, … Read more
Cannot connect to Database server (mysql workbench)
The issue is likely due to socket authentication being enabled for the root user by default when no password is set, during the upgrade to ubuntu 16.04. The solution is to revert back to native password authentication. You can do this by logging in to MySQL using socket authentication by doing: Once logged in: which … Read more
Unable to connect to any of the specified mysql hosts. C# MySQL
Sometimes spacing and Order of parameters in connection string matters (based on personal experience and a long night :S) So stick to the standard format here Server=myServerAddress; Port=1234; Database=myDataBase; Uid=myUsername; Pwd=myPassword;
Getting java.sql.SQLException: Operation not allowed after ResultSet closed
The problem is with the way you fetch data in getStuff(). Each time you visit getStuff() you obtain a fresh ResultSet but you don’t close it. This violates the expectation of the Statement class (see here – http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html): By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is … Read more
SQL statement not working – “Operand type clash: date is incompatible with int’
Your date format is incorrect, it needs to be in quotes and rearranged slightly. or rather