mysql

XA vs. Non-XA JDBC Driver Performance?

We are using an XA JDBC driver in a case where it is not required (read-only work that doesn't participate in a distributed transaction). Just wondering if there are any known performance gains to be had to switch to the Non-XA JDBC driver - if not it's probably not worth switching? FWIW we are using MySQL 5.1 ...

Mysql NULL datetime field in stored proc

I have a stored procedure which takes in a datetime field along with some other fields. The datetime field is allowed to be empty or NULL but when it is passed in it gets passed as an empty string, ''. This causes a problem as my sql admin says that the datetime field is not proper format. How do I convert the '' to a NULL value. I have ...

MYSQL: COUNT with GROUP BY, LEFT JOIN and WHERE clause doesn't return zero values

Hi guys, thanks in advance for any help on this topic! I'm sure this has a very simple answer, but I can't seem to find it (not sure what to search on!). A standard count / group by query may look like this: SELECT COUNT(`t2`.`name`) FROM `table_1` `t1` LEFT JOIN `table_2` `t2` ON `t1`.`key_id` = `t2`.`key_id` WHERE `t1`.`anothe...

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Unknown command

Hi .. I'm trying to create datasource for connection pooling mysql database in Websphere Application Server. I've set the JDBC Provider, I set the Implementation class name : com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource my mysql driver version is 5.1.6. but I got this error while I try to connect the datasource...

MySQL student database

Background School Corp. has two databases with a common table, employees. These two databases are located at the company's offices in New York and Atlanta. Client computers connect to the databases to make changes. If employees join at the New York office, the information pertaining to them should be available at the Atlanta office as we...

Can I force MySql table name case sensitivity on file systems that aren't case sensitive

So our target environment is linux, making mysql case-sensitive by default. I am aware that we can make our linux environment not case sensitive with the lower_case_table_names variable, but we would rather not. We have a few times been bitten with a case mismatch because our dev rigs are OSX, and mysql is not case sensitive there. Is...

"Did you mean" feature on a dictionary database

I have a ~300.000 row table; which includes technical terms; queried using PHP and MySQL + FULLTEXT indexes. But when I searching a wrong typed term; for example "hyperpext"; naturally giving no results. I need to "compansate" little writing errors and getting nearest record from database. How I can accomplish such feaure? I know (actua...

Delete all but 5 newest entries in MySQL table

I currently have PHP code that handles the logic for this because I do not know how to handle it in SQL. I want to create a stored procedure that will delete all the rows except for the 5 newest for a given config_id. IE config_id = 5 gets passed to the SP so it knows which config_id it is looking to clean up. CREATE TABLE `TAA`.`RunHi...

cast a String to sql time

How we cast a string into time type with mysql in java So String------->java.sql.time thanks. ...

Curve-fitting in PHP

Hi, I have a MySql table called today_stats. It has got Id, date and clicks. I'm trying to create a script to get the values and try to predict the next 7 days clicks. How I can predict it in PHP? ...

MySQL Student database beginner SQL employment expectations

Background, Student pursuing BSIT degree, employer expectations, entry level. I would like to solicit opinions from this forum as to what your professional expectations are as regards an entry level position in working in the database realm... I see many job opportunities that require a minimum of 2 or more years experience, how does one...

MYSQL Query using subquery

Cant get this to work can any one help. List the part number, part description, and on_hand value of each part whose number of units on hand is more than the average number of units onhand for all parts use a subquery? SELECT PART_NUM, DESCRIPTION, SUM(ON_HAND * PRICE) ON_HAND_VALUE FROM PART; WHERE MAX(ON_HAND); ...

Automatic logout in python web app

I have a web application in python wherein the user submits their email and password. These values are compared to values stored in a mysql database. If successful, the script generates a session id, stores it next to the email in the database and sets a cookie with the session id, with allows the user to interact with other parts of the...

What happens if you kill a long-running alter query?

What happens if you kill a long-running alter query? Will the alter query simply revert? How long could that take (as a proportion of the time it has already been running)? What if that query is being replicated onto another server? Will killing the process on the other server revert the original server's alter query? We're running mys...

Concatinating Multiple Rows in SQL

Hello, I have a table structure that looks like this: ID String ----------- 1 A 1 Test 1 String 2 Dear 2 Person I need the final output to look like this: ID FullString -------------------- 1 A, Test, String 2 Dear, Person I am really lost on how to approach this... I looked on a couple examples online b...

Timed email reminder in python

I have written up a python script that allows a user to input a message, his email and the time and they would like the email sent. This is all stored in a mysql database. However, how do I get the script to execute on the said time and date? will it require a cron job? I mean say at 2:15 on april 20th, the script will search the datab...

MySQL performance

Hi, I have this LAMP application with about 900k rows in MySQL and I am having some performance issues. Background - Apart from the LAMP stack , there's also a Java process (multi-threaded) that runs in its own JVM. So together with LAMP & java, they form the complete solution. The java process is responsible for inserts/updates and fe...

Would server traffic software (something like Piwik or Google) make a good case for using No SQL?

We are trying to develop a company specific tracking software but not interested in Google or Piwik. Essentially we would have a JavaScript tracking code also. The data that it would capture, would that be best suited for traditional RDMS or can we get a NO SQL solution ? Any thoughts or ideas welcome. ...

Data truncation when retrieving data from MySQL database with prepared statements

I have a script that retrieves multiple products using prepared statements. Like putting loops into loops, I have prepared statements in prepared statements - so there is a prepared statement for retrieving all products, a prepared statement to retrieve all images for that product, a prepared statement to get all attributes for that prod...

Populate a jTable Using MySQL

I have a project with a jTable called AchTable, that is like this: +-------+------+ | File | Type | +-------+------+ | | | | | | | | | +--------------+ And I have a mySQL table that is like the same, then I want to know how could I populate the jTable. ...