mysql

Using Aggregate Functions Inside a Trigger In MySQL

I have a 'People' table with several attributes including 'age'. Each time I insert a new tuple into this table, I would like to find out the average age of all the people listed in the table. If the average is above 50, I want to modify the age in the tuple being inserted. I'm using a 'BEFORE INSERT' trigger for this. Here is the test c...

Mysql get count of rows for each day

My Current query is: SELECT DISTINCT DATE(vote_timestamp) AS Date, COUNT(*) AS TotalVotes FROM `votes` WHERE vote_target_id='83031' GROUP BY DATE(vote_timestamp) ORDER BY DATE(vote_timestamp) DESC LIMIT 30 (line breaks separated for readability) Where vote_timestamp is a time for each "vote", Count(*) is the count for that day, and ...

ado.net entity data model and mysql - problem with UTF8

I use ADO.NET Entity Data Model in ASP.NET with MySql. I tried many collocation for my databases and tables but so far diacritics (polish) characters are changed.. for example, from ł to l. And this is at query level (I log all query to file on mysql server level). Any ideas? My connection string does not contain any infromation about ch...

Will partitions improve MySQL INSERT speed?

I'm doing a lot of INSERTs via LOAD DATA INFILE on MySQL 5.0. After many inserts, say a few hundred millions rows (InnoDB, PK + a non-unique index, 64 bit Linux 4GB RAM, RAID 1), the inserts slow down considerably and appear IO bound. Are partitions in MySQL 5.1 likely to improve performance if the data flows into separate partition ta...

log4j logging to DB (in a cluster)

Hi, I have a Java application wich runs on two machines in a cluster and they both log to one mysql database. Everything works fine, but I would like to have an additional field in the database wich represents the IP where the requests is coming from. I solved this by having two different log4j.properties files, but I guess there is a ni...

SELECT the newest record with a non null value in one column

Hi there I have table data which looks like this id | keyword | count | date 1 | ipod | 200 | 2009-08-02 2 | ipod | 250 | 2009-09-01 3 | ipod | 150 | 2009-09-04 4 | ipod | NULL | 2009-09-07 Now what I am after is getting the count of the row which has the newest date but has a not null count. In which case row 3 with count 150.) eg ...

SELECT all the newest record distinct keyword with a non null value in one column

Hello again... Following on from this question http://stackoverflow.com/questions/1740199/select-the-newest-record-with-a-non-null-value-in-one-column I know have a problem where I have this data id | keyword | count | date 1 | ipod | 200 | 2009-08-02 2 | ipod | 250 | 2009-09-01 3 | ipod | 150 | 2009-09-04 4 | ipod | NULL | 2009-09-07...

Does mysql have any limitation on number of databases

Hi All, I am creating mysql databases in mysql default data directory in "/Var/lib/mysql/data" But it is not allowing me to create more than 31999 databases in it. The similar sort of question I have asked before : http://stackoverflow.com/questions/1607793/mysql-create-database-with-new-database-location Where I said that mysql can...

Automatic Wordpress installation?

I've been wondering about making a blog hosting site like blogspot as a bit of a hobby. I figure Wordpress would be ideal to use but I'm not 100% on a lot of things related to this. Can I simply create mysql databases and wordpress installs with php functions and keep it secure from possible hacks? Or would the server as a whole need its...

How to change a column value in AFTER INSERT trigger ?

Hello, I have a trigger AFTER INSERT inside which I must update the value of a column of the row that has been just inserted. I obviously cannot use : SET new.column = value; I've tried to do a manual update in the trigger but it's also not allowed. Is there any simple way to work this out ? Many thanks ! ...

How do I add a unique key to a column in MYSQL?

If I want one column to be unique, or two, or three? ...

Java-Mysql Connection error

My stack trace: cbs.ui.OverallReportUI btnGenerateBillActionPerformed SEVERE: null java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql....

Strange mySQL Coldfusion Problem

Hey guys well I'm working on this system and it giving me sooo much trouble. I just want to quit at this point. Long story short I made a few changes to how a mySQL table works, I went from storing a comma separated list for a few different fields to storing the same data each in its own field. I then rewrote the code to work with the ne...

file manager with role based permission

Hi, I m a beginner to PHP and MySQL, I am having 3 user roles in my project: manager editor mtuser Managers account can upload files to the system. Editors and mtusers are able to download file uploaded by a manager, they can modify those and reupload back to managers (feedback). I am using a temp directory to store those files on...

rename table to 'NOW() + old_table_name'

is it possible somehow to use the NOW() function or something similar in MYSQL rename table()? the reason i need this is because instead of dropping old tables straight away we prefer to first rename then to old_ date-table-was-taken-ouf-of-use_ table_name so when we actualy delete it we know how long the table has been 'unavailable' ...

line count path problem

$f="../mts/sites/default/files/test.doc";//in this way i am able to find line count,but i am not getting how i can give folder path for line counting..if i give path as $safe_filename and $target_path.$safe_filename it's coming file content not opening. Please check the code and help me out thanks in advance <?php $nid = 1; $teaser = fa...

How to implement multi-level hierarchies in user management

Implement multi-level hierarchies so that super user have also all rights that sub user have means super user can also manage sub-users jobs. How design the database for this. ...

Using HTTP POST for login forms

I regularly use a standard form to send login information through the HTTP POST method and then validate it using php to check if the details are correct. I use an md5 hash on the passwords (and sometimes usernames) to give some degree of security, so I'm not storing a raw password in my code in case it's viewed by an unauthorised person...

watch requests between db server and client

Hi, Is there any tool which can monitor the requests and responses between a MySQL server and clients? Basically I want to know how many requests my web app makes to the mysql server and analyze the performance. Is there any similar thing available for MSSQL server also? ...

Select Query Blocks read/write/update operations on transactional table

I have a Select query which executes on a transactional table having more than 4 million records. Whenever I execute this query , I observe that all write and update operations on that particular transactional table become suspended and we start getting exceptions from java side that lock wait timeout exceeds , try restarting transaction...