MySQL - Read For Learn

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

MySQL Server on MAMP-Windows Will Not Start

I had the same problem. I contacted mamp support, and their answer was the following; it worked for me. Hope this helps you too. Stop all servers in MAMP. The below assumes your MAMP installation directory is C:\MAMP\. For MAMP: With Windows Explorer go to folder C:\MAMP\db\mysql\ First backup all files that begin with mysql-bin.*, then delete them … Read more

Create a temporary table in a SELECT statement without a separate CREATE TABLE

From the manual found at http://dev.mysql.com/doc/refman/5.7/en/create-table.html You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with … Read more

How to install Python MySQLdb module using pip?

It’s easy to do, but hard to remember the correct spelling: If you need 1.2.x versions (legacy Python only), use pip install MySQL-python Note: Some dependencies might have to be in place when running the above command. Some hints on how to install these on various platforms: Ubuntu 14, Ubuntu 16, Debian 8.6 (jessie) Fedora … Read more

MySQL date formats – difficulty Inserting a date

Put the date in single quotes and move the parenthesis (after the ‘yes’) to the end: But you can always use dates without STR_TO_DATE() function, just use the (Y-m-d) ‘20120101’ or ‘2012-01-01’ format. Check the MySQL docs: Date and Time Literals

ERROR 1044 (42000): Access denied for user ”@’localhost’ to database ‘db’

No, you should run mysql -u root -p in bash, not at the MySQL command-line. If you are in mysql, you can exit by typing exit.

How do I find which transaction is causing a “Waiting for table metadata lock” state?

Works for MySql version < 5.7.3 Look for the Section – We can use INFORMATION_SCHEMA Tables. Useful Queries To check about all the locks transactions are waiting for: A list of blocking transactions: OR A List of locks on particular table: A list of transactions waiting for locks: Reference – MySQL Troubleshooting: What To Do When Queries Don’t Work, … Read more

MySQL date format DD/MM/YYYY select query?

You can use STR_TO_DATE() to convert your strings to MySQL date values and ORDER BY the result: However, you would be wise to convert the column to the DATE data type instead of using strings.

MySQL IF NOT NULL, then display 1, else display 0

Instead of COALESCE(a.addressid,0) AS addressexists, use CASE: or the simpler: This works because TRUE is displayed as 1 in MySQL and FALSE as 0.

+ More