mysql

Convert time for use in mysql

I am trying to load timestamps into mysql. All my times are UTCTime objects. The HDBC mysql implementation does not seem to like UTCTime objects although internally the documentation says that it treats all times as if they were UTC times. I believe that I need to convert UTCTime to EpochTime since it looks like the HDBC mysql implementa...

Copying a MySQL database to another machine

I'm trying make a copy of a MySQL database on another server. I stop the server, tar up the mysql directory, copy it to the other server and untar it. I set all the permissions to match to the working server, and copied the my.cnf file so everything is the same except for server specific changes. However, when I try to startup the serve...

Is there a way to get the first active bit through SQL (MySQL)?

I have a column named a, and a column named b. I want to fill with an UPDATE ... SET b = ... query the b column, so that it contains the first bit that a has set to 1. Okay, you probably didn't understand, it's much easier to understand with an example: a = 2508 = 0x9CC = 0100111001100 b = 4 = 0x4 = 0000000000100 a = 2080 = 0x8...

MySQL : How to get records are have non null values ?

I want to get all records in case the result_1 is not null like below : SELECT ID, Code, NULLIF(CompareWithField,2.25) as result_1 FROM `data` WHERE Indexed = 0 and code = 142 and 'result_1' is not null But instead, every time I run the query, I receive a result even if result_1 reports NULL. Any solution ?...

Do I need to use mysql_real_escape_string on database data for re-insert?

Hello and thanks in advance. I am retrieving data from the db. The data already went through mysql_real_escape_string when it was added to the db. Once retrieved I am comparing it to a raw variable and depending upon the result I may be re-inserting the original db data back into the db into another, different, field. My question is, ...

JDBC (mysql) saves queries in heap memory

Hi, I am trying to run Hibernate Search on database on a table with 12,500,000 records. On startup, Hibernate Search queries the database to obtain the schema information. The problem is that I get OutOfMemory: heap size exception. I read around, and found out that JDBC connector of MySQL puts queries on JAVA heap memory and it's a b...

CSV to MYSQL parse issue

I am trying to parse a .csv file into a mysql database, and it's not fun. Some rows look like this: "Value", Value, "Value3", ,"Value,Value" And some look like this: Value, Value, , Value, , Value This preg_split worked well except for fields that were empty: foreach ($row as $item) { $item = preg_split( "/[,]*\\\"([^\\\"]+)\\...

Spring 3 MVC + MySQL: cannot store € character

Hi there, I have Spring 3 MVC set up with Hibernate and MySQL 5. In a web form, I enter a single character into a field, € (i.e. just the one character). When I then attempt to save the data, I get the following exception: java.sql.BatchUpdateException: Data truncation: Data truncated for column 'name' at row 1 'name' is a String on m...

database (mysql) data type choice: Text vs Binary

What are the tradeoffs in choosing a TEXT datatype as compared to a BLOB or BINARY datatype? I don't intend to index on the column or use it in a WHERE clause, it's just data in the database, that happens to be textual. If there's a performance or storage advantage to my choice of datatype though, that would be good to know... Thanks! ...

Get locked tables in mysql query.

Hi, Is there any way via mysql sql query to get the locked tables, i have an app running and there are bunch of tables getting locked in the app accessing processing (c# threading app) and mysql DB. I need to see the locked tables based on sql query and analyze the code for that particular section. Thanks. ...

Using java System.currentTimeMillis() with a php script and mysql table?

Hi, I've got a webapp (using php), my clients are java-based and are submitting dates to me using: System.currentTimeMillis(); so my web app gets it via a post, so it's a string, like: $submittedTime = "9734367890508"; I want to insert it into a mysql table field, I think either a DATETIME or TIMESTAMP field can store it. What t...

How to add per day and total visitors counter

I am developing a website using php, mysql, javascript, html, css. I want to add a counter in this website. I already added a counter in this website, that counter counts every visitor and show total visitors. But my client requirement is different, he wants different counters. Example: Online user = 2 Today visitor = 20 Total visito...

de-normalization, weighted aggregates for updated tables in MySQL

Dear all, this time I got a more general question. Should I use multiple views rather than stored procedures for weighted aggregation of data, if the original data is updated periodically? Basically I have a local MySQL database that is updated periodically by importing the same kind of data (tables) from a bigger transaction databas...

HSQLDB 1.8 not able to do order by in derived tables?

Hi, I have a query where I am doing an Order by inside of a derived table that is being inner joined. Ex: SELECT g.* FROM ( SELECT ... FROM ... ORDER BY alias.some_column LIMIT 0, 20 ) as g ... # other joins This works fine in MySQL, but it fails in HSQLDB. The reason I put the order by here is that mysql is much fa...

Using zero value in MySQL columns of type TIMESTAMP

I'm a little confused over the documentation of type TIMESTAMP in MySQL and whether the zero value is safe to use. The manual says: The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. ... which implies that '0000-00-00 00:00:00' is out of range and thus not valid. But, as far as I can see,...

MySQL COUNT with GROUP BY and zero entries

I have 2 tables: Table 1. options_ethnicity with the following entries: ethnicity_id ethnicity_name 1 White 2 Hispanic 3 African/American Table 2. inquiries with the following entries: inquiry_id ethnicity_id 1 1 2 1 3 1 4 2 5 2 I want to generate a table that shows the number of inquires by ethnicity. My que...

How would I encrypt my php files so only a person with a key could access them?

I've done a project for a client and as part of the deal since they couldn't afford much we agreed that I own the code. however the server is in their name and they could go and steal the code. How would I avoid that? I would like only apache and me to be able to read the files! Any alternative solutions will be appreciated! ...

Retrieve and play mp3 files from mySQL using Javascript/PHP

I am building a site that stores a few mp3 audio files in mySQL. At the moment only 5, so efficiency is not an issue at all. CUrrently, I am still trying to make a cross browser/platform solution that will not require other file formats. I have a PHP script that retrieves the mp3 binary data and sends the appropriate header to the brow...

C# connection becomes null

Hi I'm developing a class to manage the operations on a Mysql database. I have the following code: using System; using MySql.Data.MySqlClient; public class MysqlAccess { private MySqlConnection pCnn; public enum OperationType {Select = 1,Insert = 2,Update = 3,Delete = 4}; public MysqlAccess() { MySqlConnecti...

MySQL/CakePHP DB Design Question

Our real estate application has a table, Events, which has historically been linked to the Homes table via an Event.homes_id column. Recently, for the first time, we added an event type which is not connected to a home, but a realtor. The question: is it good practice to now add a realtor_id column to the Events table? Something in me...