Re: Mysql Finding the country name from country prefix
Jasper Bryant-Greene wrote: Michael's solution will not produce reliable results as it will return at least two results for many different phone numbers, with no way to tell which is the correct result. As long as the database contains Abhishek's bogus XYZ and ZXF records, yes. If the database contains only real country codes, no. Then there will be at most one match for any number, and if the set of country codes is complete there will be exactly one match for any real and complete phone number. Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: case insensitive search
[EMAIL PROTECTED] wrote: This is what I thought: I have three entries: 12. afan, pasalic, afan_at_afan_dot_net, 2003-12-18 13. Afan, Pasalic, Pasalic_at_yaoo_dot_com, 2001-01-01 14. AFAN, PASALIC, ammtar_at_gamil_dot_com, 2003-12-18 If I search for *afan* only entry no. 12 will be shown. If I search for *Afan* only entry no. 13 will be shown. If I search for *AFAN* only entry no. 14 will be shown. Then the collation in use is either case sensitive or binary. To find all three entries you want a case insensitive collation – one with a name that ends in _ci. I really recommend that you read the relevant part of the manual, and if you still have problems after that, return to this mailing list with a detailed question. http://www.mysql.se/doc/refman/5.0/en/charset.html Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Country codes
Rhino wrote: Be careful when reading those lists! When I looked at page 3 in the English PDF, it said it was in numeric order and the list showed all the countries that use country code 1, then country 20 (Egypt), then country 210 (spare) *without* showing 55 (Brazil). Page 6 shows countries 500 through 509, then 51 through 58, and so on. Therefore, Brazil doesn't appear until Page 6. They have obviously chosen to sort the list only on the FIRST DIGIT of the country code; that's a pretty odd form of numerical order, in my opinion! That's not normal numerical order of course, but it's exactly the order you need if you're parsing a phone number where you don't know beforehand how many digits are the country code. I suppose you could call it alphabetical order, only it's applied to digits instead of letters. Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database IDs
Gleb Paharenko wrote: Really, in my opinion, it contradicts with the manual. Please, send me the bug id in case you'll report the bug. It turns out that it was reported nearly a year ago: http://bugs.mysql.com/bug.php?id=6880 Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: case insensitive search
It's not clear to me whether you want the search to be case sensitive or not, but you need to make sure that the right collation is used. Case sensitive collations end in _cs and case insensitive collations end in _ci. Read about collations in chapter 10 of the manual: http://www.mysql.se/doc/refman/5.0/en/charset.html Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Finding the country name from country prefix
Rhino wrote: If you parsed the first example, you might assume that I am in Brazil, because '55' is the country code for Brazil. (Country codes '5' and '555' are not in use at present.) And they never will as long as 55 is in use, because then phone numbers would become ambiguous. Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database IDs
Logan, David (SST - Adelaide): It looks like the LAST_INSERT_ID() is returning the id of the last auto_increment INSERT, this seems to be in line with the documentation. quote The ID that was generated is maintained in the server on a per-connection basis. This means that the value which the function returns to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. /quote It all depends on what [the] most recent statement means – or the last INSERT or UPDATE query in the part Gleb quoted. When I do this: insert into child (parent_ID, value) values (last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'), (last_insert_ID(), 'b3'); is that one statement, or three? I think all of it, from insert to the semicolon, is a single statement. Do you mean that (last_insert_ID(), 'b2') is a statement on its own? Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database IDs
Gleb Paharenko:
Could you provide a repeatable test case for you problem?
I don't know how repeatable this is. As I said I have one computer where it
seems to work sometimes, as if there's a race condition. Right now it seems
repeatable on the computer I'm using at the moment:
mysql select version();
+---+
| version() |
+---+
| 4.1.14|
+---+
1 row in set (0.00 sec)
mysql create table parent (
- ID int unsigned not null auto_increment,
- value varchar(50),
- primary key (ID)
- );
Query OK, 0 rows affected (0.01 sec)
mysql create table child (
- ID int unsigned not null auto_increment,
- parent_ID int unsigned not null,
- value varchar(50),
- primary key (ID)
- );
Query OK, 0 rows affected (0.00 sec)
mysql insert into parent (value) values ('a');
Query OK, 1 row affected (0.00 sec)
mysql insert into child (parent_ID, value) values
- (last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'),
- (last_insert_ID(), 'b3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql insert into parent (value) values ('c');
Query OK, 1 row affected (0.00 sec)
mysql insert into child (parent_ID, value) values
- (last_insert_ID(), 'd1'), (last_insert_ID(), 'd2'),
- (last_insert_ID(), 'd3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql select * from child;
++---+---+
| ID | parent_ID | value |
++---+---+
| 1 | 1 | b1|
| 2 | 1 | b2|
| 3 | 2 | b3|
| 4 | 2 | d1|
| 5 | 4 | d2|
| 6 | 5 | d3|
++---+---+
6 rows in set (0.00 sec)
Note how child rows 1 and 4 have the IDs of their respective parent row in the
parent_ID field, but child rows 3, 5 and 6 have the ID of the preceding child
row instead. (For row 2 we can't tell the difference.)
Here's the SQL code for easy copying:
use test;
create table parent (
ID int unsigned not null auto_increment,
value varchar(50),
primary key (ID)
);
create table child (
ID int unsigned not null auto_increment,
parent_ID int unsigned not null,
value varchar(50),
primary key (ID)
);
insert into parent (value) values ('a');
insert into child (parent_ID, value) values
(last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'),
(last_insert_ID(), 'b3');
insert into parent (value) values ('c');
insert into child (parent_ID, value) values
(last_insert_ID(), 'd1'), (last_insert_ID(), 'd2'),
(last_insert_ID(), 'd3');
select * from child;
Björn Persson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database IDs
Gleb Paharenko: So in you query both last_insert_id() should return the same value, which equals to the value that was set for an AUTO_INCREMENT column by the last INSERT or UPDATE query. Note that you have one query, which just inserts several rows, so during it is processed the returned value of last_insert_id() is constant, even if your bulk insert is changing an AUTO_INCREMENT field. That's what I thought, and on one server I have (4.0.21) it seems to work reliably that way. On another server (4.1.10a) that kind of insertions failed on a foreign key constraint. (I use InnoDB.) On a third box (4.1.14) it seems to work sometimes and fail sometimes. I think what happens when it fails is that the second last_insert_ID() gets the ID of the first row in the same query. I suppose I should file a bug report then? Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: With Rollup wrong results
Noel Stratton: As you can see the summary total in the 'Total' column is incorrect. The summary total should be 36 but is spitting out 40. I am not even sure where it is getting 40. The only way to get 40 is if it is multiplying 5X8. Isn't that what you told it to do? ROUND(price*count(log.product), 2) AS 'Total' For the summary line that means Pick a price field at random (as there are several rows to choose from) and multiply it with the count of all the log.product fields. Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Fieldtype to store IP address...
Jasper Bryant-Greene:
Rhino wrote:
Isn't there a new way to express IP addresses called IPV6(?) which has a
possibility of 6 distinct parts instead of the traditional 4? I haven't
seen one of these new formats myself yet but for all I know, they will
become soon in the near future. Maybe you'd better choose a field type
that can accomodate those as well as the traditional 123.123.123.123
type
It's not a new way to express IP addresses. It's a new version of IP,
the Internet Protocol, and theoretically if the OP follows the good
advice already given and stores the IP address in an unsigned integer
field, he should be fine (assuming, probably safely, that
INET_{ATON,NTOA}6() functions are made); although he may need a bigger
integer type for IPv6 addresses.
An IPv6 address is 128 bits. That's twice as big as a MySQL bigint, so you
can't store it as an integer. Use binary(16).
Besides, you don't want to mix IPv4 addresses and IPv6 addresses in the same
column unless you have another column that keeps track of which kind of
address it is, but I seem to recall that there is a standard mapping from
IPv4 addresses to IPv6 addresses, so you could use that and store them all as
IPv6 addresses.
Björn Persson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with collation...
Alvaro Cobo: I have a database with thousands of rows which by mistake was imported in the wrong way and all the spanish characters went wrong. For example the letter 'á' appears like 'Ãf¡'. That looks like text that was in the UTF-8 encoding has been mistaken for an eight-bit encoding, transcoded from that eight-bit encoding to UTF-8, and then *again* mistaken for an eight-bit encoding. When your data is messed up that badly it will take some manual work to fix it. That is, you can't get MySQL to do it for you; you'll need some conversion tool to reverse the mangling. As a Fedora user I'd run it through Iconv. I don't know what tools are available for Windows. The problem is that I am trying to recover this database and have tried all the possible collations and no one results, and also cannot export this database again because I have dropped it, and have only the dump file. Assuming your example above was how the text looks when retrieved from MySQL, what does it look like in the dump file? Do non-English letters look like four garbage letters like above, or do they perhaps look like only two garbage letters? If you're lucky, the dump file isn't quite as messed up as the imported data. Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database IDs
[EMAIL PROTECTED]: Robert Crowell [EMAIL PROTECTED] wrote on 11/11/2005 12:39:37 AM: If I am relying on MySQL's AUTO_INCREMENT feature for the id for a table,how can I reliably retrieve the ID of an item I just entered into the database? TFM is your friend. What you are looking for is LAST_INSERT_ID() Or mysql_insert_id() in the client library – or the equivalent function that ought to be in any binding for another language. Speaking of LAST_INSERT_ID(), does anyone know how MySQL is supposed to process a statement like this:? insert into some_table (ref_field, other_field) values (last_insert_ID(), 1), (last_insert_ID(), 2); I've looked in the manual for details on which order that statement is processed in, but I haven't found an answer. Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Triggers that handle multiple events (insert and update)
onsdagen den 9 november 2005 18:07 skrev Burke, Dan: For example, one field has to be = 0, so I put this validation Why don't you just declare that field as unsigned? Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete all but the newest 100 records?
söndagen den 6 november 2005 16:15 skrev Brian Dunning: I'd like to delete all but the newest 100 records from a table. I have a timestamp field. I can't figure out how to do this in a single SQL statement - can anyone help? Thanks! I'd try something like this: delete from x where ID not in (select ID from x order by timestamp desc limit 100); Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Add New User
söndagen den 6 november 2005 18:39 skrev Bruce Martin: However, even though I specified 'test.*' my test user can access the mysql database. That's because this statement: UPDATE user SET Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_p riv='Y' WHERE user.User='testUser'; granted those privileges on _all_ databases. Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Are functions evaluated before or during insertion?
Hi everybody!
It seems like different instances of MySQL do things in different order, with
rather unfortunate effects on the last_insert_ID function.
Consider the following tables and insertions:
create table parent (
ID int unsigned not null auto_increment,
value varchar(50),
primary key (ID)
) engine=InnoDB;
create table child (
ID int unsigned not null auto_increment,
parent_ID int unsigned not null,
value varchar(50),
primary key (ID),
key (parent_ID),
foreign key (parent_ID) references parent (ID)
) engine=InnoDB;
insert into parent (value) values ('a');
insert into child (parent_ID, value) values
(last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'), (last_insert_ID(), 'b3');
The intention is of course that all three child rows should reference the
parent row that was inserted. This will work if the server first evaluates
all the calls to last_insert_ID and then starts inserting the rows. I have
one MySQL 4.0 server where this appears to work consistently.
On some MySQL 4.1 servers I sometimes get the error a foreign key constraint
fails. On one server it sometimes works and sometimes not. What I think
happens is that the server evaluates the first call to last_insert_ID and
inserts the first row (b1), and then starts processing the b2 row, evaluates
last_insert_ID again, and gets the ID of the b1 row.
I thought I had read somewhere in the documentation that functions are
evaluated before data is inserted, and as this never failed on my development
server I relied on it. Then it failed on the production server. Ouch!
So my questions are:
1: Is this expected behaviour? That is, should I expect some function calls to
be evaluated after some rows have already been inserted and auto_increment
counters have been incremented, or are functions supposed to be evaluated
before insertion starts?
2: Whichever order a query is processed in, wouldn't it be better to always do
things in the same order?
Björn Persson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
