I am using Zend_Db to insert some data inside a transaction. My function starts a transaction and then calls another method that also attempts to start a transaction and of course fails(I am using MySQL5). So, the question is - how do I detect that transaction has already been started? Here is a sample bit of code: try { ...
I have a table of the form CREATE TABLE data { pk INT PRIMARY KEY AUTO_INCREMENT, dt BLOB }; It has about 160,000 rows and about 2GB of data in the blob column (avg. 14kb per blob). Another table has foreign keys into this table. Something like 3000 of the blobs are identical. So what I want is a query that will give me a re m...
How do I connect to the database(MYSQL) in connection bean using JSF to retrieve its contents. Also please let me know how do I configure the web.xml file? ...
In Ruby on Rails, how to add foreign key constraint in migration? ...
I'm rewriting a PHP+Mysql site that averages 40-50 hits a day using Django. Is sqlite a suitable database to use here? Are there any advantages/disadvantages between them? I'm just using the db to store a blog and the users who can edit it. I am using fulltext search for the blog search, but no complex joins anywhere. ...
Microsoft SQL Server has a nice feature, which allows a database to be automatically expanded, when it becomes full. In MySQL I understand that a database is in fact a directory with a bunch of files corresponding to various objects. Does it mean that a concept of database size is not applicable and a mysql database can be as big as avai...
I seem to often find myself wanting to store data of more than one type (usually specifically integers and text) in the same column in a MySQL database. I know this is horrible, but the reason it happens is when I'm storing responses that people have made to questions in a questionnaire. Some questions need an integer response, some need...
I was reading about refactoring a large slow SQL Query over here, and the current highest response is from Mitch Wheat, who wants to make sure the query uses indexes for the major selects, and mentions: First thing I would do is check to make sure there is an active index maintenance job being run periodically. If not, get all existi...
I have a table with a timestamp field of type datetime. I need to aggregate the data between a defined start and end time into x groups representing time intervals of equal length, where x is supplied as function parameter. What would be the best way to do this with Hibernate? EDIT: some explanations mysql Table: data_ts: datetime pk...
I've created a UserObject and RoleObject to represent users in my application. I'm trying to use hibernate for CRUD instead of raw JDBC. I've successfully retrieved the information from the data base, but I can not create new users. I get the following error. org.springframework.web.util.NestedServletException: Request processing fa...
I am sharding my data into multiple mysql databases, but sometimes I will need to gather information from multiple shards. How can I query multiple mysql databases in parallel in php? I've seen some examples of asynchronous curl requests and shell_exec, but is there a more direct way? ...
According to the documentation, a prepared query provides a significant performance benefit if you're running a query multiple times because the overhead of the MySQL server parsing the query only happens once. I'm wondering what exactly they mean by "multiple times" there. I.e., say you have a web page that runs a query one time. Now s...
I run a small browser MMO, and I have a problem where a couple users are embedding scripts into their profile images, and using them to make attacks against said users, and my game in general. Is there a way to protect against this, or do I need to start blocking people from being able to use their own custom images? If it helps any, i...
How to query to get count of matching words in a field, specifically in MySQL. simply i need to get how many times a "search terms"appear in the field value. for example, the value is "one two one onetwo" so when i search for word "one" it should give me 3 is it possible? because currently i just extract the value out of database and d...
I have a website with members who message each other. There are getting to be a few members and they like to send messages - I'm sure you can see where this is going. Currently I have said messages stored in a nicely relational table cunningly titled "messages" with different status ids to denote, er, status (unread, saved, etc). I know...
I'm having a bit of a problem with converting the result of a MySQL query to a Java class when using SUM. When performing a simple SUM in MySQL SELECT SUM(price) FROM cakes WHERE ingredient = 'chocolate'; with price being an integer, it appears that the SUM sometimes returns a string and sometimes an integer, depending on the version...
Hi, I found a few articles online providing examples of how to model graphs of various kinds (DAGs, in particular) in SQL, but they all seemed enormously complex, given the relative simplicity of what they're modeling. Is there a best / standard way of doing this? My current thinking is something like this: create table node ( id in...
I'm looking at building a Rails application which will have some pretty large tables with upwards of 500 million rows. To keep things snappy I'm currently looking into how a large table can be split to more manageable chunks. I see that as of MySQL 5.1 there is a partitioning option and that's a possible option but I don't like the wa...
I have a trigger in which I want to have a variable which holds an INT i get from a (SELECT..). So I can use it in two IF statements; so I don't have to call the (SELECT...) twice. How do you declare/use variables in MySQL triggers? ...
I not a DB expert, so am looking for advice for a web-based system I'm thinking of setting up. The general set up of the system I have is that it will have a web-based interface (possibly in PHP) for logging in etc, and some C++ code running on the server doing some processing. Both the PHP and the C++ code will need read/write access t...