How to make a primary key start from 1000?
create table tablename ( id integer unsigned not null AUTO_INCREMENT, .... primary key id ); I need the primary key to start from 1000. I'm using MySQL. ...
mysql
Adding new field to pre-existing table and modifying previously created data in Rails
So I've a user model and a corresponding table (users) with fields: User_id, username, password etc. But then I added invitation_limit to users table. Newly created users have invitation_limit, however the users created before the invitation_token migration was run don't have the invitation_limit field. How do I update the previously c...
Partitioning Mysql table
What is the best method to partition a MySQL table(MyISAM)? table contains product details arranged by auto incrementing product id. It is expected to contain millions of records, so performance is essential. ...
With a created by timestamp, is it better to have the DB manage it, or the application?
We have your bog standard Java app under development, and a lot of the records we're creating (Hibernate entities in MySQL) have 'created' and 'modified' timestamps on them. Now, me and one of the developers disagree - I believe that both of those fields should have a MySQL default of CURRENT_TIMESTAMP, and then the modified can be chan...
How to alter images/ to assets/images in MySQL data?
I have the following table with data. CREATE TABLE IF NOT EXISTS `omc_product` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `shortdesc` varchar(255) NOT NULL, `longdesc` text NOT NULL, `thumbnail` varchar(255) NOT NULL, `image` varchar(255) NOT NULL, `class` varchar(255) DEFAULT NULL, ... ... ...
php database update issue
im running application on php.. after i update the status on the database, its not reflecting the correct status on the webpage... It will still display the first status which is -FOUND- mysql_query("UPDATE registration.user SET STATUS ='-LOST-' WHERE UserID = '$userid'"); Anyone assist? For the login page , i fetch from database: ...
Crystal reports, mysql using a php website, Is it possible?
I have been asked to find out if it is possible to connect Cyrstal Reports to a MYSQL database within a php website. Is this possible? Are their any alternatives Thanks Edit I suppose in the perfect world I am wanting to show crystal reports within a PHP website ...
Better way save password in mysql which can be decrypted also using php
Hi, I am currently using md5 function to encrypt my password and save to mysql db which can not be decrypted. Now my user want that when they forgot password, they should get same (old) password instead of new password. So my question is that what should i use to encrypt my password and store in mysql Database. And i can decrypt that...
Proper mysql datastructure for a fulltext search...
Hoping someone can provide some mysql advice... I have 2 tables that look like this: searchTagsTable ID tag dataTable ID title desc tagID So the column "tagID" in "dataTable" is a comma-delimmited string of ids pointing to searchTagsTable. I'd like to use mysql's built in fulltext search capabilities to sear...
MySQL Embedded SELECTs vs. JOINs
Is there a noticeable difference between: SELECT userid, username, userdept, (SELECT deptname FROM depts WHERE deptid=userdept) AS deptname FROM users and SELECT userid, username FROM users INNER JOIN depts ON depts.deptid=users.userdept Which one is better? ...
How can I implement this functionality into SOLR?
I have a classifieds website, and users may for example search for cars. When searching for a car, there are a number of endings in their names as you all probably know. For example lets say Bmw 330ci (ending beeing 'ci'), but there is also Bmw 330i, or Bmw 330di etc etc. How can I make SOLR "understand" this, so if users search for 33...
SHOW FIELDS but with filter on field type?
I have a mySQL database with a number of tables with many fields. Is there a native way to find only the fields of the type TEXT? I know how to do it using a scripting language like PHP. I just want to know whether there is a trick using SHOW TABLES/SHOW FIELDS I am not aware of. ...
Jetty mysql connection-pool configuration error: javax.naming.NameNotFoundException; remaining name 'env/jdbc/---(mysql 5.0+jetty 7.0.1)
My configuration files project/WEB-INF/web.xml: <resource-ref> <description>ConnectionPool DataSource Reference</description> <res-ref-name>jdbc/mysql</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref> project/WEB-INF/jetty-env.xml: <New id="mysql" class="org.eclip...
MySQL - SELECT WHERE date < X
How do I do this in MySQL? SELECT * FROM MyTable WHERE MyDateCol < TODAY() I googled a lot on the subject and didn't really find anything except "look in the MySQL date ref" which does not explain the above easily enough. I know I can do the following which is kinda indirect: SELECT * FROM MyTable WHERE MyDateCol BETWEEN (0000-00-0...
Mysql transaction waiting for lock which is already granted .. This is causing deadlock
If following situation a bug in mysql?. Mysql Version: mysql.x86_64 5.0.77-4.el5_4.1 Kernel: Linux box2 2.6.18-128.el5 #1 SMP Wed Jan 21 10:41:14 EST 2009 x86_64 x86_64 x86_64 GNU/Linux LATEST DETECTED DEADLOCK 100125 4:24:41 * (1) TRANSACTION: TRANSACTION 0 210510625, ACTIVE 155 sec, process no 28125, OS thread id 1243162944 st...
how to select a field from a row that is chosen by group-by in mySQL
I have a GIANT MYSQL database that contains 100,000,000 records. Simplified, three of my columns are ID, date and time, i have several indexes over id and date ( ID , DATE , ID&DATE ) so no performance problem on join select id, max(date) as last_record from mytable where date < "2010-01-02" mytable /-----------------...
PHP PDO: how does re-preparing a statement affect performance
I'm writing a semi-simple database wrapper class and want to have a fetching method which would operate automagically: it should prepare each different statement only the first time around and just bind and execute the query on successive calls. I guess the main question is: How does re-preparing the same MySql statement work, will PDO ...
Database connection string and collation
Is it possible to set connection collation within MySql connection string and how, since there's a default setting on the server that's used for new connections. Two things I can't do: Can't call SET COLLATION_CONNECTION after I open a connection, because I'm using Entity Framework that does all the calls for me not entirely true as y...
MySQL: Querying multiple databases at once
I have WordPress instances with each in its own database. For an update I need to query all active plugins, that are stored in a table 'wp_options' and accessible via WHERE option_name='active_plugins' How can I access all active plugin settings (spread over multiple databases) and output them in one single SQL result? I know the data...
How to cast MySQL result to a string?
There is a query: SELECT blalist FROM blatable WHERE blafield=714 which returns a string that looks like: "2,12,29,714,543,1719". And there is another query: SELECT userid, name, surname, creditcardnum, items FROM stolencards WHERE userid IN (SELECT blalist FROM blatable WHERE blafield=714) Now that's not working. I only ma...