mysql

Rails/MySQL lazy writing on .save(updates/writes)

I'm working on a pretty large data migration project which involves moving data between servers and different active record schemas. The whole migration literally takes days. How can I implement a caching solution where my Ruby migration code when it calls the Active Record save method writes to a cache (which then updates the MySQL da...

php - storing encrypted string in mysql

I need to store the customers' driver license # (most of the time) (or personal ID) so when they come to pick their product they can identified themselves based on their id. This is usually the way it is handled everywhere in my country (unfortunately). However, I would like to store this #'s encrypted in mysql. I now I can make use of A...

MySQL table structure question

How should my mysql table look like when I allow a user to enter there post in multiple categories. Should it look like the below mysql table in example 1 or in example 2 or something different? EXAMPLE 1 CREATE TABLE ac ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, categoryID INT UNSIGNED NOT NULL, articleID INT UNSIGNED NOT NUL...

Mysql Recursive Stored Procedure...Limit 0 reached...can't change the max_sp_recursion_depth variable

UPDATE: Ok, I was able to debug this and found that the by doing this select @@max_sp_recursion_depth I can see that the variable is set at 15, however, when I run the call back to itself with CALL single_limb_portfolio_list(xaccount_id, xparent_portfolio_id); I get the error stating the Recursive Limit 0 was reached, but it never iterat...

MySQL comments table structure question

How should my comments table look like when I allow a user to enter comments for a post. Below is my MySQL table as of right now. The uniqueID is to stop users from hitting the submit button multiple times. CREATE TABLE acomments ( commentID INT UNSIGNED NOT NULL AUTO_INCREMENT, parent_commentID INT UNSIGNED NOT NULL, userID IN...

Help with a seemingly trivial Mysql query with if statements

I've got two fields called T1 and T2 defined as double. I'd like to set T3 with the following conditions: If T1 & T2 >0 then T3=(T1+T2)/2 If T1==0 then T3=T2 if T2==0 then T3=T1 if T1==0 & T2==0, T3=-9999 I have no idea how to include multiple if statements in the query. ...

Inner join - two values that are using the same join!

Hello guys. I have had a good think and just can't get my head around this. I have a table (transfers) like the following id | playerid | old | new | amount They are all integers. I am joining this table (teams) to the following id | name | founded The join is going to be on old and new. old and new are both ints and i need the ...

How can I set the default for a column as a GUID?

How can I set the RoR model's property (database column) to default for a column do generate a GUID in mysql? Does the column have to be a unique identitifier or can I have it be a string of length 36 also? ...

Can "auto_increment" on "sub_groups" be enforced at a database level?

In Rails, I have the following class Token < ActiveRecord belongs_to :grid attr_accessible :turn_order end When you insert a new token, turn_order should auto-increment. HOWEVER, it should only auto-increment for tokens belonging to the same grid. So, take 4 tokens for example: Token_1 belongs to Grid_1, turn_order should be 1 up...

How to deal with Apostrophe while writing into Mysql database

I am getting this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's','portal','','offering','MSNBC','News','','sports','','MSN','Money','','games'' at line 3 The only problem is that this error shows up when inserting data that contains a...

default value of GUID in for a column in mysql

I want a column to default to a GUID, so if I am doing an insert and I don't explicitly set the value, I want it to default to a new GUID value. how can I do this? ...

Update and select in one query

I found similar questions with correct answers. But they're a bit complicated for me. I just want a simple basic statement. I have: string sql = "UPDATE tblPopUp SET PopUp = 'False' WHERE DisplayNo = 1" ...and: string sql1 = "SELECT Period FROM tblPopUp WHERE Dis...

Effcient method for ajax autocomplete query on big db table and busy site

We're all familiar with the autocomplete tagging setup here on SO. I'm wondering what is the most efficient method for querying the tag table in this scenario. A busy site with many users tagging away with the autocomplete feature requires a lot of querying. Say at any one time there are 100 users typing an average of 10 characters to ...

MySQL "remembers" deleted InnoDB table

MySQL is giving me grief when I try to recreate a particular table in a particular database; I believe the problem is that somewhere MySQL has some records of the table's foreign keys and keeps trying to check the constraint when I try to recreate the table. The database in question is for the test environment and is periodically refres...

Mysql Stored procedures

How stored procedures are fast in Mysql . Where it is suitable to use stored procedures (insert,update,select) How can we make stored procedure of a complex query returning 100000 rows and how can we get these records on php form. any example of such stored procedure using select query returning multiple rows ? ...

make a file directory in php & mySQL

I am currently making a file manager system I store all the folders names and files in the database (MySQL)I am trying to add folders in the database and then add sub folders to them,I wanted it to show all the folders to show in there correct position, Here is a eg: Folder1 Folder1-Sub1 Folder1-Sub1-Sub1 Folder1-Sub...

Mapping a byte[] that can grow up to 400KB on MySql

Hi, I have the following: @Entity public class ExamplePhoto { ... @Column(nullable= false) private byte[] photo; ... } I am using Hibernate and a MySql database. I tried putting a mediumblob but got a "tinyblob expected error" message. Given the fact that the photo could be of up to 400KB the tinyblob won't do the job. How can I m...

MySQL data types: int versus enum

I have a number of columns that only need to store a few values (e.g. 0, 1, 2, 3). What datatype should I pick for such a case? I feel like I should pick something like ENUM('0', '1', '2'). Would int be better (despite it being less restrictive)? Should I consider something else (like tinyint)? EDIT: Actually, what general advice shoul...

Multi-tiered Comment Replies: Display and Storage

So I'm trying to create a comment system in which you can reply to comments that are already replies (allowing you to create theoretically infinite threads of replies). I want them to display in chronological order (newest on top), but of course the replies should be directly underneath the original comment. If there are multiple comme...

Need to select from multiple tables based upon records in one table

Hi guys, heres the situation. I have a table called as maps coordinates the schema is like below: map_coordinates: item | item_id | latitude | longitude The idea is that this table contains a list of coordinates of different items, they could be a note, a classifieds post, an event etc. I want to be able to set it up so I have a resul...