Re: questions about timestamps and DST

On 31/03/2015 12:20, Larry Martell wrote: On Tue, Mar 31, 2015 at 1:13 AM, Andrew Moore wrote: When you use a timezone with DST there is no such thing as 2.30am on the date of changeover. That hour doesn't exist. I am using UCT - I am not using a timezone. In MySQL 5 and above, TIMESTAMP va

Re: forum vs email

On 10/12/2014 23:40, Reindl Harald wrote: Am 10.12.2014 um 18:38 schrieb h...@tbbs.net: 2014/12/10 09:00 +0100, Johan De Meersman One of the (for me, at least) defining features of a forum, is that the subjects tend to be divided up into a tree structure, which has it's own benefits

Re: Unique index - opinions sought

On 16/07/2012 17:39, Rick James wrote: How many rows? If 1K, it does not matter. If 1 billion, we need to discuss in more detail. Let's assume 1M... Around 1M in the item_spine table and 10M in item_detail. Dates should be stored in DATE datatype, which is 3 bytes. Your GUID is non-standa

Unique index - opinions sought

I have a MySQL table (call it, say, item_spine) which contains three fields which, together, form a unique key. These three fields are a guid, a start date and an end date. The guid is alphanumeric (a fixed-length six characters) and the dates are ISO format dates (-MM-DD). I also have an

Re: Postal code searching

On 24/04/2012 17:24, Tompkins Neil wrote: How about if I want to only return postal codes that are like W1U 8JE not W13 0SU. Because in this example I have W1 as the postal code and W13 is the other postal code No, you don't. In this example you have W1U as one outbound code and W13 as the ot

Re: One inst has 39 columns- the other 40

On 22/03/2012 04:41, Brown, Charles wrote: Look man, there has to be someone out there that can tell me why one user table has 5 extra columns. As far as I can see, plenty of people already have told you. Is it version related or my sysprog person missed out on a step. Yes, it's related to

Re: In case you all missed it.

On 11/11/2011 16:29, Curtis Maurand wrote: mysql> select date_format(now(),'%m-%d%-%y %h:%i:%s') AS time; +---+ | time | +---+ | 11-11-11 11:11:11 | +---+ 1 row in set (0.00 sec) Actually, it should be select date_format(now(),'%y-%m%-%d %h:%i:

Re: Databasename/Tablename is marked as crashed and should be repaired

On 14/10/2011 08:07, James wrote: Hello, I have the following error on my mysql server log and managed to repaired the broken table. However, it keeps occurring by time to time. I am using MyISAM storage engine to all database and having some locking table which I know / aware about the disadvan

Re: Allowing all users to access a specified database

On 03/06/2011 11:24, John Daisley wrote: The reason *GRANT SELECT, INSERT, UPDATE, DELETE ON mydyb TO '%'@'%';* * * does not work is because that command would be suicidal in terms of security. If you are hosting a large number of ecommerce sites and granting any user access to those databases

Allowing all users to access a specified database

Hi, I have a database server with multiple users and multiple databases. I have a situation where I want to allow any user to connect to a specified database. Unfortunately, the documentation has this to say: "MySQL does not support wildcards in user names." Which means, that, although I ca

Re: ` vs '

On 30/03/2011 09:05, Brent Clark wrote: Hiya Im wondering if someone could help me understand this. If you look at my two queries below. By the ORDER BY one is using ` and the other ', as a result, if you do an explain you will see that the top query does a filesort, while the other does not.

Re: Auto-Increment Values in Mysql

On 09/02/2011 11:41, Adarsh Sharma wrote: Dear all, I have an auto-increment column in Mysql database table. Let's say the column has below values : 1 2 3 4 5 6 7 8 9 10 Now if i deleted some rows where id= 3 ,5 and 8 The data look like as : 1 2 4 6 7 9 10 I want to have it id's as 1 2 3

Re: CURRENT insert ID

On 24/01/2011 15:42, Jerry Schwartz wrote: -Original Message- From: Donovan Brooke [mailto:li...@euca.us] Sent: Friday, January 21, 2011 7:28 PM Cc: mysql@lists.mysql.com Subject: Re: CURRENT insert ID Just an idear.. Don't auto_increment the main table.. create a unique Id table, auto_

Re: Lowest non-zero number

On 03/12/2010 16:56, Paul Halliday wrote: On Fri, Dec 3, 2010 at 12:50 PM, Mark Goodge wrote: Given a table containing a range of INT values, is there any easy way to select from it the lowest non-zero number? SELECT number FROM table WHERE number> 0 ORDER BY number ASC LIMIT 1; Sorry

Lowest non-zero number

Given a table containing a range of INT values, is there any easy way to select from it the lowest non-zero number? Obviously, MAX(column) will return the highest, but MIN(column) will return 0 if any row contains a 0, which isn't what I want. Any clues? Mark -- http://mark.goodge.co.uk --

Possible causes of table crashing

Hi, I have a very strange problem whereby one particular table in our database is repeatedly (on average, every couple of days) generating errors stating that the table is crashed and needs to be repaired. Running a repair fixes it. What makes it strange (and something that I've never encoun

Re: backfill results for the same month and year

On 11/11/2010 09:00, HaidarPesebe wrote: Dear All, I have the following data ID CITY QTY MONTH YEAR --- 1 Chigago10 11 2010 1 NewYork 22 11 2010 1 London 54 11 2010 1 Chigago7 10 2010 1 NewYork 26

Order by "in" clause

Hi, I have a query like this: select id, title from product where id in (1,3,5,8,10) What I want it to do is return the rows in the order specified in the "in" clause, so that this: select * from product where id in (10,3,8,5,1) will give me results in this order: +--+-+ | id

Re: SELECT WHERE IN help

On 21/09/2010 16:44, Tompkins Neil wrote: Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? No, that isn't

Re: Best method to keep totals

On 03/09/2010 16:32, Arthur Fuller wrote: While I agree with the general take on this subject ("Never store totals without a good reason" and "where there is duplication there is the opportunity for skew"), I must say that there are exceptions. A couple of years ago I worked on an inherited datab

Re: Performing subtraction between fields

On 21/08/2010 07:25, b...@qxhp.com wrote: Hello, For simplicity's sake, let's say I have three fields, A, B and C, all of which are integers. I'd like the value of C to be equal to A less B (A-B). Is there a way I can perform this calculation? I'm guessing it would happen when I INSERT a row and

Re: searching serialized data stored in mysql

On 09/08/2010 18:33, Norman Khine wrote: hello, i have a table called checkout, this has a row called products which has contains a python dictionary data, like http://pastie.org/1082137 {products: [{productId: 123, productName: APPLE, productPrice: 2.34, productUrl: http://appple-

Re: Help me

On 21/07/2010 16:33, Karthik Pr wrote: I have created a table as follows but i was not able to use full text search on a specific data. create table racebike (id int auto_increment not null primary key, name varchar(10), user text,fulltext(name,user)); [snip] The query is mysql> select * fro

Re: Decimal points

On 19/07/2010 10:04, Ashley M. Kirchner wrote: Is there a way to tell MySql to only return '2' in the first select as opposed to '2.0'? The second select is correct and should remain as such. Not easily, no. Basically I have two columns, one with an integer and another with a de

Re: MySQL Replication

On 24/06/2010 09:18, Tompkins Neil wrote: HI, We have set-up MySQL Community Server 5.1.46 with Master to Slave replication and everything appears to be working correctly, however I have a couple of questions which I hope somebody can shed some light. (1) When the network connection goes down b

Re: substring query

On 10/06/2010 16:55, Aaron Savage wrote: I am looking for some guidance on creating a substring query. I have a column that stores a path to a file. I would like to extract that file extension and that is it and display it on my results. However, the paths are different lengths and some extens

Re: Master - master replication

On 24/05/2010 13:40, Walter Heck wrote: Carl, if you want to be secure, do not use the internet to transfer your data. SSH, VPN and SSL can not give you the kind of security a private line can give you. That is a tad expensive though :) That's true, but again that's not really answering the qu

Re: Array data type

On 14/05/2010 09:54, Joerg Bruehe wrote: Hi Samrat, all! Samrat Kar wrote: Hello, How to store multiple values in a single field? Is there any array data type concept in mysql? "Multiple values in a single field" would be an explicit violation of the relational model (on which the SQL lan

Re: Fulltext Match BOOLEAN MODE not searching integers

On 31/03/2010 16:52, Tompkins Neil wrote: Hi I have the following fulltext search which appears to work fine for string phrases. However if I search like just "51" which is part of the string name like 51 Blue Widget in the table it doesn't return any results. However if I search like "bl" it r

Re: Possible to find this duplicate?

On 13/02/2010 16:12, Brian Dunning wrote: Hey all - I have a table listing references for chapters in a book. I'm trying to find all the cases where a single chapter lists more than one reference from the same author. In this example table, I want it to find IDs 1 and 2, because they're both

Re: Good source for sample data?

On 29/01/2010 15:20, Jerry Schwartz wrote: That's brilliant. The only minor issue is that, at least for UK data, it won't validate for mapping purposes - the postcodes are syntactically correct, but non-existent. I don't know if it would have the same problem for US or Canadian data. [JS] Sorr

Re: Good source for sample data?

On 29/01/2010 03:18, John Meyer wrote: If I may recommend: http://www.generatedata.com/#download That's brilliant. The only minor issue is that, at least for UK data, it won't validate for mapping purposes - the postcodes are syntactically correct, but non-existent. I don't know if it would h

Re: Record old passwords ?

On 21/01/2010 11:07, Lucio Chiappetti wrote: On Tue, 19 Jan 2010, Tompkins Neil wrote: I can enforce that the user can't use the same password as the previous four - when they change their password. However, the user can manipulate this by changing the password four times and then resetting bac

Re: Record old passwords ?

On 19/01/2010 09:14, Tompkins Neil wrote: I think I will go with the four additional column approach as I proposed (in the current table) - since this need is a PCI compliancy security requirement. Do you have a reference for that? Storing past passwords as additional fields like that is infl

Re: Record old passwords ?

On 19/01/2010 14:44, Tompkins Neil wrote: Hi All, Following on from my earlier email - I've the following question now : I can enforce that the user can't use the same password as the previous four - when they change their password. However, the user can manipulate this by changing the passwor

Re: last_insert_id

Gary Smith wrote: Steve Edberg wrote: (2) autoincrement values are not reused after deletion, so if you deleted the record with ID=1000 inserted in (1), the next autoincrement would still be 1001, even if the existing records are IDs 1,2,3. This is usually the desired behavior, but again, may

Re: Join Statement

Victor Subervi wrote: On Mon, Dec 14, 2009 a mysql> select SKU, Quantity, Name, Price, t.sizes, t.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID ; Empty set (0.00 sec) mysql> select ID, SKU, Name, Price from products; ++--+---

Re: Update Doesn't Update!

Jørn Dahl-Stamnes wrote: On Friday 11 December 2009 10:38, Victor Subervi wrote: Hi; mysql> update products set sizes="('Small', 'Large')" where ID=0; Query OK, 0 rows affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 1 Look at the

Re: mysqldump vs phpmyadmin dump

Wang Zi Feng wrote: Hi everyone, Here is a rookie question. The problem what I found is that mysqldump and phpmyadmin generate different size of backup file against same database. I try to dump same database with the 2 different methods, the original database is 2.8mb, phpmyadmin export 1.5mb

Re: Another Join Problem

Victor Subervi wrote: Hi; I get the following error: *SQL query:* SELECT ID, Item FROM products JOIN categories ON categories.ID = products.Category LIMIT 0 , 30; *MySQL said:* #1052 - Column 'ID' in field list is ambiguous Please note the error is about ambiguity. "products" has an ID fiel

Re: Wierd PHP/MySQL result

Mogens Melander wrote: Well, no typos. The relevant piece of code: if (! $cust = mysql_fetch_assoc($res)) { echo "{success: false, errors: { reason: 'Fetch Customer failed: " . htmlspecialchars(mysql_error(),ENT_QUOTES) . "" . htmlspecialchars($sql,ENT_QU

Re: How to compare 2 columns in different tables.

John Furlong wrote: My question is, can the argument in AGAINST() refer to a column in another table or does it have to be a specific string you are searching for? If the MATCH() function won't work, any suggestions on how else to compare table1.name against table2.name? The columns are defined

Fulltext query expansion query

Hi, I'm currently working on a project which uses fuulltext searching. The "with query expansion" feature is useful, but I was wondering if there's any way to obtain the list of terms that the expanded query uses (other than those originally input, of course). Is that possible, and, if so, how

Re: Mysqld fails to start

JD wrote: Hello List, mysqld fails to start, and emits the following error messages in /var/log/mysqld.log 090127 10:00:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 10:00:30 [ERROR] Can't open the mysql

Re: Optimizing nullable expiration dates

Norman Elton wrote: I've got a table that tracks expiration dates. Currently, if the record hasn't expired, the column is stored as NULL. In order to find records that haven't expired, I search for "WHERE expire_at > NOW() OR expire_at IS NULL". This seems dirty, and I suspect it makes my index

Re: normalised designs: customer database

metastable wrote: Hello all, I have a question that's been bugging me for quite some time. Let's say we have a small business that has both private and corporate customers. We want to store contact and address data about these customers, as well as invoicing data. Off course, only companies ha

Re: Most efficient way of handling a large dataset

Joerg Bruehe wrote: Hi Mark, all! Mark Goodge wrote: I'd appreciate some advice on how best to handle a biggish dataset consisting of around 5 million lines. At the moment, I have a single table consisting of four fields and one primary key: partcode varchar(20) region varchar(10) loc

Most efficient way of handling a large dataset

I'd appreciate some advice on how best to handle a biggish dataset consisting of around 5 million lines. At the moment, I have a single table consisting of four fields and one primary key: partcode varchar(20) region varchar(10) location varchar(50) qty int(11) PRIMARY KEY (partcode, region, lo

Re: Blocking HTML code in inserts?

Skip Evans wrote: Hey all, What is the most effective way to block HTML code in insert statements? I have a client with a comments form that is being bombarded with people inserting references to their own sites, etc, and I need an effective way to filter basically any HTML tags at all. You

Re: reset auto increment to a lesser value

Chris W wrote: Mark Goodge wrote: On a more general note, if the actual value of the primary key matters for anything other than simply existing as a primary key, then you shouldn't be using auto-increment at all. You should generate the value through some other means and insert it wit

Re: reset auto increment to a lesser value

abhishek jain wrote: On Sat, Aug 2, 2008 at 10:49 But as per a thread on this link, if the resetted value is less than the highest value already in table then the effective increment value will start from highest number and not from 100. I have deleted some rows and i want the increment to start

RE: Trying to find a .zip binary install for Windows 2000 for 4.0.7 gamma

At 11:41 30/12/2002 -0600, Cal Evans wrote: Is there a problem with your mail server or do you keep re-sending this message? If the latter, please stop. Those that have read the message do not have an answer. Continuing to spam this overloaded list with the same request will simply make those who

RE: Sorting UK Postcodes (WAS Sorting Results)

At 22:53 12/12/2002 +0100, Alliax wrote: Sorry, I forgot to say that postcode can be one or 2 letters in front of the numbers. > -Message d'origine- > They can be E1,E2,..,E12,E13 > Order by name would do: > E1,E10,E11,E12,E13,E2,E3,E4,... > how can I get with a simple ORDER BY query > E1

combine data from two tables

Hi, I'm hoping that someone can give me some pointers on this problem. I have two tables that contain similar data (eg, people.employees and people.volunteers). I need to run a select statement to retrieve names from both tables, and then display the data sorted by name so that it doesn't matt

Time comparisons

Can anyone help with (what I hope is) a pretty simple time function query? I need to extract a set of records from a table based on separate date and time columns, where both the date and time are older or equal to now. For example, my sql query is going to look a bit like this: select ID, Live

select using regexp

Hi, I've got a problem that I'm hoping someone can help with. I need to do a query against a text column in order to extract entries that will match whole words only - for example, a search for "cat" should match any of: The cat sat on the mat It was a large cat. Cat food is interesting.

Re: Mysql vs. Oracle and concat "||"

At 20:46 26/09/2002 +0200, MySQL wrote: >Hi all, > >I'm a DBA in the Oracle World. > >I want to make a sql query in mysql, with a concat (||) known i Oracle >world. > >Like this. > >select numer ||','|| text from Table: You need to specify the keyword CONCAT and enclose it in brackets, like this:

Re: INSERT IGNORE

At 10:24 17/09/2002 +0100, [EMAIL PROTECTED] wrote: >According to the manual, if INSERT IGNORE finds a row already present in >the table, it does not update the row with the new data. How can I get the >opposite behaviour? In my case, there shouldn't be a record with the unique >ID I am inserting.

Re: Mysql and Cobalt

At 07:45 05/07/01, dijana cetinic wrote: >Hi all, >just a quick question .. has neone setup mysql on a cobalt RAQ4 > before? If so ne >recommendations on how to go about doing it? I've installed it on a Cobalt RAQ. I tried several of the rpm files (from both MySQL and Red Hat), but none

full outer join?

According to the MySQL documentation, it doesn't currently support full outer joins and won't do in the immediate future. Does anyone have any suggestions for a workaround? I need to join three tables, only one of which has a common identifier with the others, and produce a result which shows