mysql

MySQL truncation command - unicode characters

Hi all, I am currently trying to adjust the values stored in a table in MySQL. The values stored contain a series of Unicode characters. I need to truncate to 40 bytes worth of storage, but when I try: UPDATE `MYTABLE` SET `MYCOLUMN` = LEFT(`MYCOLUMN`, 40); MySQL is overly helpful and retains 40 characters, rather than 40 bytes. Is t...

parsing text for contents of database table of limited size

I have a MySQL table of "people" as part of a web site, for example: | people_id | firstname | lastname | ----------------------------------------- | 1 | John | Lennon | | 2 | Paul | McCartney | | 3 | George | Harrison | | 4 | Ringo | Starr | | . | ....

cache mysql results and requery only if something changes

Is it possible to query the database only once while the page loads in PHP. Somehow store these results and display it to user on subsequent reports. Is there any way to determine if something is changed in the database so that we can connect to DB only then to fetch new/changed results? Please give me some idea. Thanks. ...

How to overcome "java.sql.SQLException: Too many connections" exception?

I have a simple web application written in java which has servlets accessing the Mysql Database to generate reports. The report generation happens very frequently. I am using the apache commons DBCP to get connections to the DB. I also close the connection explicity in the finally block always. But i do not explicitly close the Stateme...

Jython & Mysql - what is the current practice for connection?

Just trying jython for the first time, because something came up at work that would fit for this perfect for it. I assume jython can make mysql jdbc connection using the mysql jdbc driver. After googling and reading, however I am having problems and it seems jython specific and probably simple. so i do jython: Jython 2.5.1 (Release_2_...

MySQL Bug? "SHOW TABLE STATUS" Reports Fluxuating Number of Rows During Import

Hi all, I'm importing 4.1mil records into an offline system to do some analysis on a subset of our database. As I'm running the import, I'm trying to check its progress by using: SHOW TABLE STATUS LIKE 'MailIssueElement' What's odd...at different times, I'm seeing higher AND lower values for Rows. I would expect it to only go up. ...

Insert into MySQL tables with JOIN?

I have these tables: http://stackoverflow.com/questions/2104063/my-final-mysql-db-could-someone-check-if-the-tables-are-correctly-made How can I make an Insertion of an ad here? Is join used on insert also? Thanks Clarification: I need to insert values into multiple tables here, and don't know how to do it else than using multiple IN...

How to migrate a MySql 4.x database to MySQL 5.0?

I have a zip file for a Windows MySQL 4.x installation database which includes the MySQL files and the databases files (.myi, .myd, frm). I have a Windows MySQL 5.0 running installation. I need to bring one of the databases from the zip file to life in the current active MySQL 5.0. How can this be done? ...

Getting limited amount of records from hierarchical data

Let's say I have 3 tables (significant columns only) Category (catId key, parentCatId) Category_Hierarchy (catId key, parentTrail, catLevel) Product (prodId key, catId, createdOn) There's a reason for having a separate Category_Hierarchy table, because I'm using triggers on Category table that populate it, because MySql triggers work...

Generate unique id from unique string input

Hi, I have a table with a column of unique string values. The max length of the string value is 255 char. I want to generate a unique id with the string value as input. In other words I am looking for a compact representation for a string. The unique id generated can be alpha-numeric. A useful feature to have would be to be able to reg...

MySQL InnoDB insertion is very slow

We use MySQL server 5.1.43 64-bit edition. InnoDB is used as engine. We have a sql script which we execute every time we build the application. On ubuntu machine with MySQL server and InnoDB engine it takes about 55 seconds to complete the execution. If I run the same script on OSX, it takes close to 3 minutes! Any ideas why OSX is s...

Should I use AI on these fields in MySQL?

I have this db below. I wonder how I should use the ID to identify each record. Everything is connected from the classified_table! Two Questions: Should I use AI on every PK in this case? Could somebody give me the FULL code for selecting an entire classified from only an ad_id ("bmw_330ci_8939483" for example)? I am new to normalize...

Why is MySQL InnoDB so much slower at full table scans than MyISAM?

EDIT OP has acknowledged a mistake when profiling PostgreSQL in his answer below. I am updating this question to reflect the comparison between MyISAM & InnoDB. Hello, I ran a test against MySQL InnoDB, MyISAM, and PostgreSQL to see how well each of these engines performed doing full table scans to understand what the response ti...

JDBC MySQL; is supplying the DB name & username/password enough?

Hi, When setting up a JDBC connection in a J2EE application, do you need to specify the schema name in addition to the database name? I have followed this tutorial and have setup a database, and username/password, but I'm coming up against this error when I startup my application. Is it possible that DBUnit is trying to insert the data...

ranking entries in mysql table

I have a MySQL table with many rows. The table has a popularity column. If I sort by popularity, I can get the rank of each item. Is it possible to retrieve the rank of a particular item without sorting the entire table? I don't think so. Is that correct? An alternative would be to create a new column for storing rank, sort the entire t...

Unduplicatable "Illegal mix of collations" error

I was going through the error log of my Rails application and found that someone had run into the following error: "Mysql::Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=': SELECT * FROM `tags` WHERE (name = LOWER('?')) LIMIT 1" I understand the reasoning for this error, h...

remove information from mysql table when user close his Browser

i m creating two table(in mysql) named table_temp_guest table_temp_order now if a guest enters then we save his personal information in this first table and if he purchase something from any stall ,it saved as a temporary order in table_temp_order. now my question is : i m using session id, so when user goes to logout( without ...

how to compare two tables fields name with another value in mysql?

I have two tables table_school school_open_time|school_close_time|school_day 8:00 AM | 9:00PM | Monday 10:00 AM | 7:00PM | Wednesday table_college college_open_time|college_close_time|college_day 10:00 AM | 8:00PM | Monday 10:00 AM | 9:00PM | Tuesday 10:00 ...

How do I register an mysql database?

Sorry for a noob question regarding MySQL. I downloaded FlightStats to learn about mysql but I can't figure out how to register it with my localhost mysql db. I know in MS SQL you can simply register any sql db using sql studio. I tried to google but come up with no result. Perhaps, my search phrase is wrong. I'm searching with "how to r...

can we insert into two tables with single sql statement?

Will it be possible to insert into two tables with same insert command? ...