update column by row number
hello All, I have one table that contain lots of record and i want to upadte my primary key by row number . My database => MYSQL ...
mysql
MySQL: Cannot create table with VARBINARY?
I am running this query to set up a VARBINARY (I wish for it to be so, for a real reason) field for my database: CREATE TABLE `test_books` (`id` int UNSIGNED NOT NULL,`book` VARBINARY, `timestamp` int(11) NOT NULL, UNIQUE KEY `id` (`id`)) It hands me a standard syntax error telling me to check all the remaining code after 'VARBINARY'...
Grab x number of words before and after a given keyword?
How can I go about grabbing [x] number of words before and after a given keyword in a string in PHP? I am trying to tokenize results from a mysql query tailored to the keyword as a snippet. ...
trying to install MySQL-python-1.2.3 but I get an error
Here tis the error I get while trying to install MySQL-python-1.2.3. any idea's? Traceback (most recent call last): File "C:\Documents and Settings\Desktop\MySQL-python-1.2.3\setup.py", line 15, in <module> metadata, options = get_config() File "C:\Documents and Settings\Desktop\MySQL-python-1.2.3\setup_windows.py", line 7, in get_c...
What index should be used for this query AND for the ORDER BY clause?
Hi, I have a MyISAM table T with the following schema: f1 (integer unsigned not null) f2 (integer unsigned not null) This table has an index on f2 and it currently contains 320 million rows, and is expected to grow at the rate of about 200,000 rows once a week. I perform the following query on this table: SELECT DISTINCT T.f1 FROM T ...
mysql_real_escape_string and postal address data
I'm using mysql_real_escape_string to cleanse data that's used in insert and update queries (postal addresses). But thing is, when I have something like this "25\5" the output ends up as 25. Mysql removes whats after the 25. I know it can be entered as 25/5, but some users may deliberately enter it as 25\5. How can I keep and retrieve it...
MySQL Connector/NET connection multiple DataReaders per Connection?
Hey guys, I'm migrating from Java to C# now that I've realized I prefer the C# language features over the ones in Java, but I have this small issue. In MySQL Connector/J and JDBC, I believe that one of my applications allowed multiple PreparedStatements to be executed while another one is open, like I could perform a query that returns a...
why dosn't MySQL like the user 'a'@'b' when I just dropped them from mysql.user?
This is what I did. mysql> CREATE USER 'matrix'@'%.something.com' IDENTIFIED BY 'weak'; ERROR 1396 (HY000): Operation CREATE USER failed for 'matrix'@'%.something.com' mysql> CREATE USER 'foo'@'%.something.com' IDENTIFIED BY 'weak'; Query OK, 0 rows affected (0.00 sec) And this is what I end up with: mysql> select user,host from mysq...
most efficient way to add index to large mysql table
I have a mysql database with a particular table with a little over 6 million rows and no indexes. A simple query such as SELECT ... FROM log ORDER BY log_date ASC will take an unacceptable amount of time. I obviously need to add indexes to the table, but am unsure of the most efficient way to go about this. 1) My first option would be t...
MD5(RAND()) on MySQL returning only digits
I'm running this sample code I found while googling: SELECT MD5(RAND()) But, to my surprise, MD5 is returning plain digits, instead of hexadecimal digits. Using CONV(MD5(RAND()), 10, 16) seems to solve my problem, but the MySQL documentation states that the MD5 function should return a string already in hexadecimal. Am I doing someth...
Handling sessions without ACID database?
Hi everybody, I am thinking about using a noSQL (mongoDB) paired with memcached to store sessions with in my webapp. The idea is that upon each page load, the user data is compared to the data in the memcache and if something has changed, the data would be written to both memcached and mySQL. This way the reads would be greatly reduced ...
Mysql multi select with excluding row
Have 2 tables: A(`A_id`,`A_name`) and B(`B_id`,`A_id`,`B_kind`) B_kind column is ENUM('type 1','type 2'). So, how can I get all rows A which have an entry in table B with a B_kind='type 1' but without B_kind='type 2'. Representation in php: $A = array(); $B = array(); $sample = array(); foreach( $A as $key => $value ) { if( f...
unnecessary normalization
My friend and I are building a website and having a major disagreement. The core of the site is a database of comments about 'people.' Basically people can enter comment and they can enter the person the comment is about. Then viewers can search the database for words that are in the comment or parts of the person name. It is complete...
How can my SQL query be optimized?
Hi all, I have a problem with a quite slowish MYSQL query. I'm building an AJAX menu with PHP and performance is really an issue. The query takes about 0,5 sec to complete, and i don't know how to optimize it. The SQL query : SELECT M.nom_mat, M.id_mat, M.rang, SC.nom_sous_cat, SC.id_sous_cat, C.nom_cat,M.id_cat FROM besson_mat M LEF...
How to create Id on hibernate entity that is a byte[] that maps to a varbinary value in mysql
I am trying to create an Entity that has a byte[12] id in hibernate. It seems like it does not like to have a byte[] as a primary key and as another column it sets it up as a tinyblob in the backing mysql db. I have tried to create a String, but the problem is that the string in java is 2 bytes per char, while it is one byte per char i...
how to select fields from different db's with the same table and field name
I have two databases, for argument sake lets call them db1 and db2. they are both structured exactly the same and both have a table called table1 which both have fields id and value1. My question is how do I do a query that selects the field value1 for both tables linked by the same id??? ...
Help with myql query - multiple having statements not working
I'm trying to use the following query, if it only has one having statement it works as expected, however if i add the second having statement it does not work. Could someone please give me some assistance.... SELECT candidate.first_name, candidate.last_name, qualification.code, property.value AS funding_band_value, qualification...
Not able to drop foreign key constraints
I am using ruby on rails 2.3.9. The rake db:migrate passed successfully. However when I run rake db:migrate:redo to test the down part I get error message. I am using mysql with Innodb. class AddConstraints < ActiveRecord::Migration def self.up ActiveRecord::Base.connection.execute <<-EOS ALTER TABLE venues ADD CONSTRA...
Unable to connect to remote MySQL Server
I have a server attempting to connect to a MySQL database hosted outside of the local network. I am attempting to use a public IP address to connect to it. Testing the connection in the command line gives me this error: ERROR 2003 (HY000): Can't connect to MySQL server on '[ip_address]' (146) PDO gives me the same error. In any case, ...
Problem with an agenda/availability query
I have a mysql table with users and their weekly calendar. Every user can set his own availability for the week (morning, afternoon, night / MON thru SAT), and that is not going to change often, almost never. Imagine those users are personal trainers in a gym, or tennis courts you can book... My problem here is to find the right quer...