how disable engine=memory
Hello, when we create user accounts on our mysql server, then we strictly disallow "CREATE TEMPORARY TABLE". This said, I was a bit shocked that mysql doesn't really care and allows this command without a problem: create table x engine = memory; What is the use to deny users to create a temporary table (which is kept in memory only), when each user is free to consume all memory using the memory-engine for a table? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT causing connections to back up?
In response to an email offlist, I forgot to specify that these are InnoDB tables. On Mon, Mar 3, 2008 at 2:53 PM, Waynn Lue <[EMAIL PROTECTED]> wrote: > I've been noticing strange load spikes on our mysql machine, throwing > back the dreaded "max connections error", even though the value is set > to 500. I'm wondering if this is related to an hourly script I run > that does a few somewhat-db intensive queries. The script runs a > query that groups by Users to find out who's been the most active > across our system. Would that cause other connections to the database > to back up? I just saw it happen, so I killed the query and > everything returned to normal pretty quickly, but I'm somewhat > confused, because it looked like connections to other databases were > being backed up as well. One other weird thing, if I'm doing a > complex SELECT from a table, and something else is INSERTing into that > same table, the INSERT is taking awhile, even though there shouldn't > be any contention since I'm not doing SELECT FOR UPDATE. > > Any ideas? > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode sorting and binary comparison, please!
On 03.03.2008 23:17 CE(S)T, Anders Karlsson wrote: And you are right of course, you may use the COLLATE keyword also, to enforce a certain collation, although if you want BINARY, I think using BINARY might be slightly more effective. I was also considering compatibility with other DBMS. At least SQLite only supports the COLLATE syntax. I'm unable to find out whether the BINARY keyword is part of SQL-92, because it appears too often in it. But since MySQL seems to be the only system (of those I have tested now) that requires such special care anyway, using BINARY only here could also work. What about a feature request to allow WHERE clauses to use a different collations than the one used for ORDER BY. So collation_connection controls the ORDER BY collation, and then I could say SET collation_connection_comparison = 'utf8_bin'. That would do what you want basically, and I think there might possibly be a need for this. That would effectively be what I originally wanted. Use Unicode for sorting things, but do not use Unicode for comparing with the = operator. LIKE may work with Unicode, as its name already implies a level of fuzzyness. I'd expect LIKE to return more than one record on a unique column. But I always expect = to work as in other programming languages, as in maths and anywhere else: absolute equality, not just something similar. So I'd be happy with such an option. Where can I vote for it? :) Is there a chance to see it in a MySQL 5.0 version? Meanwhile, I have chosen to use utf8_bin for all my tables. This breaks sorting for some few cases (but it hasn't really been a problem back in the non-Unicode-MySQL days) but in exchange finds only what I want to find. -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT causing connections to back up?
I've been noticing strange load spikes on our mysql machine, throwing back the dreaded "max connections error", even though the value is set to 500. I'm wondering if this is related to an hourly script I run that does a few somewhat-db intensive queries. The script runs a query that groups by Users to find out who's been the most active across our system. Would that cause other connections to the database to back up? I just saw it happen, so I killed the query and everything returned to normal pretty quickly, but I'm somewhat confused, because it looked like connections to other databases were being backed up as well. One other weird thing, if I'm doing a complex SELECT from a table, and something else is INSERTing into that same table, the INSERT is taking awhile, even though there shouldn't be any contention since I'm not doing SELECT FOR UPDATE. Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode sorting and binary comparison, please!
Yves! OK. I agree I don't like this much myself, but we have to live with the multi-lingual aspect of UNICODE. Or rather, we have to agree to be either multi-lingual, and have the cons and pros of that (using UNICODE), or ignore UNICODE and have binary collations etc. And collation also determine equalness. real life example: I have a friend called called Widén, with an accented e. In Sweden, someone called Widen (with a non-accented e, and which is also a perfectly valid name) would sort and compare the same. I.e. in Sweden "Widén" = "Widen". That's just how it works. But the same names, which are binary different but the same using swedish language and swedish collations, would be different when using a french collation. I happen ti live on a street with a ringed and and an umlauted character in the name. When in the US, these two guys have their unlauts removed are are sorted as the umlauts weren't there. Which is OK in US. Which is not OK in sweden. In essence, string comparisons needs to and must use collations when using UNICODE data. You state that "Handel" is different than "Händel". I tend to agree with you, I am swedish by all means. But using a language collation where these characters don't exist just doesn't cut it. UNICODE collation determines not only sorting but also equality (i.e. "é" = "e" etc). Right or wrong, well I think that however you turn something will break. Frankly, I think a lot of blame here is on UNICODE to try to do too much, I'm not a big fan of this myself. But whichever way we do it, it will not be perfect. I think MySQL right now follows the UNICODE spec quite well, although there are still things missing. UNICODE is a reasonable compromise, and I see no better means of dealing with this. So even though I admit I'm no big fan of how UNICODE operates, I've still not figurted out a better way of delaing with it. And you are right of course, you may use the COLLATE keyword also, to enforce a certain collation, although if you want BINARY, I think using BINARY might be slightly more effective. What about a feature request to allow WHERE clauses to use a different collations than the one used for ORDER BY. So collation_connection controls the ORDER BY collation, and then I could say SET collation_connection_comparison = 'utf8_bin'. That would do what you want basically, and I think there might possibly be a need for this. /Karlsson Yves Goergen wrote: On 03.03.2008 10:27 CE(S)T, Anders Karlsson wrote: > [a lot about why sorting unicode is complicated] If you want to accknowledge exact matching, and say any character, accented / unlauted etc, is different from any other character, specifiy a binary comparison: SELECT * FROM phonebook WHERE BINARY name = 'Handel'; Hm, not quite compatible. The solution I found is using this: SELECT * FROM table WHERE column = 'value' COLLATE ...; But still there binary collation has a different name on MySQL and SQLite. PostgreSQL doesn't support the COLLATE clause, although part of the SQL-92 standard. But you din't quite get my actual problem. You said that sorting Unicode things is complicated. I agree. I can live with a trade-off for sorting. But I cannot accept incorrect selection of records. When I want something that I can specify exactly, I only want to get that back, nothing else. The same counts for uniqueness constrains. I've asked a freind who could test the matter with PostgreSQL. He said, it works exactly as expected. Sorting is unicode-like, selection is precise. Why can't MySQL do that, too? Is it so hard to distinguish sorting and selecting? -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm <___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unicode sorting and binary comparison, please!
yves when creating a varchar field in table creation, use the binary. that way, selection is exact. always. david -Original Message- From: Yves Goergen [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2008 1:44 PM To: Anders Karlsson Cc: MySQL Subject: Re: Unicode sorting and binary comparison, please! On 03.03.2008 10:27 CE(S)T, Anders Karlsson wrote: > [a lot about why sorting unicode is complicated] > If you want to > accknowledge exact matching, and say any character, accented / > unlauted etc, is different from any other character, specifiy a binary comparison: > SELECT * FROM phonebook WHERE BINARY name = 'Handel'; Hm, not quite compatible. The solution I found is using this: SELECT * FROM table WHERE column = 'value' COLLATE ...; But still there binary collation has a different name on MySQL and SQLite. PostgreSQL doesn't support the COLLATE clause, although part of the SQL-92 standard. But you din't quite get my actual problem. You said that sorting Unicode things is complicated. I agree. I can live with a trade-off for sorting. But I cannot accept incorrect selection of records. When I want something that I can specify exactly, I only want to get that back, nothing else. The same counts for uniqueness constrains. I've asked a freind who could test the matter with PostgreSQL. He said, it works exactly as expected. Sorting is unicode-like, selection is precise. Why can't MySQL do that, too? Is it so hard to distinguish sorting and selecting? -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode sorting and binary comparison, please!
On 03.03.2008 10:27 CE(S)T, Anders Karlsson wrote: > [a lot about why sorting unicode is complicated] If you want to accknowledge exact matching, and say any character, accented / unlauted etc, is different from any other character, specifiy a binary comparison: SELECT * FROM phonebook WHERE BINARY name = 'Handel'; Hm, not quite compatible. The solution I found is using this: SELECT * FROM table WHERE column = 'value' COLLATE ...; But still there binary collation has a different name on MySQL and SQLite. PostgreSQL doesn't support the COLLATE clause, although part of the SQL-92 standard. But you din't quite get my actual problem. You said that sorting Unicode things is complicated. I agree. I can live with a trade-off for sorting. But I cannot accept incorrect selection of records. When I want something that I can specify exactly, I only want to get that back, nothing else. The same counts for uniqueness constrains. I've asked a freind who could test the matter with PostgreSQL. He said, it works exactly as expected. Sorting is unicode-like, selection is precise. Why can't MySQL do that, too? Is it so hard to distinguish sorting and selecting? -- Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]> Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change pw
On Mon, Mar 3, 2008 at 2:46 PM, Hiep Nguyen <[EMAIL PROTECTED]> wrote:
>
> mysql> select user,host,password from mysql.user;
> +--+--+--+
> | user | host | password |
> +--+--+--+
> | root | localhost| |
> | root | dev.jss.com | |
> | | dev.jss.com | |
> | | localhost| |
> +--+--+--+
> 4 rows in set (0.00 sec)
Okay, I wasn't aware that it's all on the same server. Try this:
USE mysql;
UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE
User='root' AND host='dev.jss.com' LIMIT 1;
FLUSH PRIVILEGES;
--
Daniel P. Brown
Senior Unix Geek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dealing With Very Large Tables
I'm in the design stages of creating a database schema and my problem is that one of the tables I'm creating has the possibility of growing to several billion rows over time. I could probably cut this by a few factors, but we're still looking at least a billion rows. Each row will only be a few columns so I'm not too worried about space but only the number of rows. Is there some limit on the number of rows? I assume if I index then searching won't be much of a problem? I know this isn't a unique problem so I'm wondering how others have approached it. Any feedback is appreciated. -- View this message in context: http://www.nabble.com/Dealing-With-Very-Large-Tables-tp15812712p15812712.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change pw
On Mon, 3 Mar 2008, Daniel Brown wrote:
On Mon, Mar 3, 2008 at 2:01 PM, Hiep Nguyen <[EMAIL PROTECTED]> wrote:
hi all, i just installed mysql and started mysqld.
it suggested i change pw for root, so i did:
mysqladmin -u root password "my_pw";
but i can't do:
mysqladmin -u root -h dev.jss.com password "my_pw";
how do i change pw for [EMAIL PROTECTED]
Quickly STFW'ing/RTFM'ing would give you an answer.
One way is to log into the remote host (dev.jss.com) via SSH as
root (or use a control panel such as cPanel).
Another is to use the MySQL client and log in remotely as such:
mysql -h dev.jss.com -u root -p
(Enter the MySQL root password)
Then type the following MySQL queries:
USE mysql;
UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
FLUSH PRIVILEGES;
Just be sure to replace 'newpwd' with the password you want to
use. MySQL's PASSWORD() function will handle hashing the password, so
don't send it encrypted or pre-hashed.
mysql> select user,host,password from mysql.user;
+--+--+--+
| user | host | password |
+--+--+--+
| root | localhost| |
| root | dev.jss.com | |
| | dev.jss.com | |
| | localhost| |
+--+--+--+
4 rows in set (0.00 sec)
i have no problem set password for [EMAIL PROTECTED], but for some reason i
can't set a password for [EMAIL PROTECTED]
is there any security issue not to set password for [EMAIL PROTECTED]
what about these two:
''@'localhost' & ''@'dev.jss.com'
thanks,
t. hiep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is a schema? what is a database?
http://dev.mysql.com/doc/refman/5.0/en/schemata-table.html According to MYSQL doc: A schema is a database Wikopedia says a Schema is defined as: Pronounced as skee-ma, the structure of a database system, described in a formal language supported by the database management system (DBMS). In a relational database, the schema defines the tables, the fields in each table, and the relationships between fields and tables.Schemas are generally stored in a data dictionary. Although a schema is defined in text database language, the term is often used to refer to a graphical depiction of the database structure.[1] and further categorised to: Conceptual Schema: A Map of concepts and their relationships Logical Schema a map of entities and their attributes and relations Physical Schema an implementation of a logical schema Schema Object such as oracle DB Object M-- - Original Message - From: "Thufir" <[EMAIL PROTECTED]> To: Sent: Sunday, March 02, 2008 3:46 AM Subject: what is a schema? what is a database? > I've been referencing a variety of sources, including wikipedia. What I > know about a schema is that in SQLite the .schema command will show the > the SQL structure of that databases tables, which would be analogous to > "DESCRIBE foo" in MySQL (with the difference that SQLite shows all tables > in one go). > > The schema is the structure of the database? > > I'm trying to understand what the wikipedia article is driving at. I > would assume that only tables which are related go in the same database? > That would my instinct, at least. Tables which are unrelated going into > a different database. > > The quote from wikipedia: > > The problem that arises is that former MySQL users will mistakenly create > multiple databases for one project. In this context MySQL databases are > analogous in function to Postgres-schemas, insomuch as Postgres lacks off- > the-shelf cross-database functionality that MySQL has. Conversely, > Postgres has rightfully applied more of the specification, in a sane- > bottom-up approach, implementing cross-table, cross-schema, and then left > room for future cross-database functionality. > > http://en.wikipedia.org/wiki/ > Comparison_of_relational_database_management_systems#Databases_vs_Schemas_.2 8terminology.29 > > > thanks, > > Thufir > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change pw
On Mon, Mar 3, 2008 at 2:01 PM, Hiep Nguyen <[EMAIL PROTECTED]> wrote:
> hi all, i just installed mysql and started mysqld.
>
> it suggested i change pw for root, so i did:
> mysqladmin -u root password "my_pw";
>
> but i can't do:
> mysqladmin -u root -h dev.jss.com password "my_pw";
>
> how do i change pw for [EMAIL PROTECTED]
Quickly STFW'ing/RTFM'ing would give you an answer.
One way is to log into the remote host (dev.jss.com) via SSH as
root (or use a control panel such as cPanel).
Another is to use the MySQL client and log in remotely as such:
mysql -h dev.jss.com -u root -p
(Enter the MySQL root password)
Then type the following MySQL queries:
USE mysql;
UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
FLUSH PRIVILEGES;
Just be sure to replace 'newpwd' with the password you want to
use. MySQL's PASSWORD() function will handle hashing the password, so
don't send it encrypted or pre-hashed.
--
Daniel P. Brown
Senior Unix Geek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
change pw
hi all, i just installed mysql and started mysqld. it suggested i change pw for root, so i did: mysqladmin -u root password "my_pw"; but i can't do: mysqladmin -u root -h dev.jss.com password "my_pw"; how do i change pw for [EMAIL PROTECTED] thanks, t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grant user
You're always safe using your own localhost (127.0.0.1) but You probably want to re-think using a dynamically assigned address such as 192.168.xxx.xxx which can change anytime FWIW Martin- - Original Message - From: "Hiep Nguyen" <[EMAIL PROTECTED]> To: Sent: Monday, March 03, 2008 11:15 AM Subject: Re: grant user > On Mon, 3 Mar 2008, Hiep Nguyen wrote: > > > hi all, i have a user that can only access localhost, how do i grant this > > user permission so that can also be accessed from 192.168.1.50? > > > i got it. thanks > t. hiep > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Preferences?
The primary key sounds like the right one. You don't need an additional one for userid alone, as the primary key will serve to accelerate those queries as well. - michael dykman On Sun, Mar 2, 2008 at 9:30 PM, Waynn Lue <[EMAIL PROTECTED]> wrote: > Whoops, finished my thought too early. I was going to append, should > we also have UserId, PreferenceId be the PRIMARY KEY? Then possibly a > secondary index on UserId in case I ever want to get all preferences > for a user? > > > > On Sun, Mar 2, 2008 at 6:18 PM, Waynn Lue <[EMAIL PROTECTED]> wrote: > > But if you're using a varchar for the purposes of getting a list, you > > might as well store it in a more compact format like int or SET. The > > only benefit (which shouldn't be ignored) is that you have a > > human-readable string to parse, right? > > > > It seems like most people ended up with a normalized format with > > UserPreferences, then? Have people generally found any performance > > problems with that? I'd imagine it would be something like > > > > UserId, PreferenceId, PreferenceSetting > > > > Where PreferenceSetting might just be another int, that could take all > > possible settings of that preference. > > > > > > > > On Fri, Feb 29, 2008 at 8:41 AM, Curtis Maurand <[EMAIL PROTECTED]> wrote: > > > > > > I think that I'd set up a varchar column and store a tab separated list > in > > > it. > > > > > > Then parse it upon retrieval. > > > > > > Curtis > > > > > > > > > > > > > > > Dan Buettner wrote: > > > > Waynn, I've used both schemes 1 and 2 as you describe, and in my > > > > experience > > > > 2 is the best way to go. It's easy to scale up as you add users and > > > > settings, and it's easy to make changes if the meaning of settings > should > > > > change (i.e. you need to do a backend change to people's settings). > > > > > > > > #1 is harder to make those kind of back end updates on, and harder for > > > > someone troubleshooting to make sense of the data. > > > > > > > > #3 may not scale well - you would end up having to track too many > tables, > > > > I > > > > think. > > > > > > > > What I'm doing in my current project is using a data model that has a > > > > method > > > > for each preference setting, and returns a sensible value by default > if > > > > the > > > > user has no pref set for a given lookup key; otherwise, I return what > the > > > > user has set. This means adding a method every time I add a preference > > > > setting, which on the one hand means adding code - on the other hand, > > > > chances are very high that if I am adding the ability for a user to > set a > > > > preference, I'm already adding code somewhere to ensure that > preference > > > > has > > > > an effect. > > > > > > > > HTH, > > > > Dan > > > > > > > > > > > > > > > > On Thu, Feb 28, 2008 at 9:50 AM, Waynn Lue <[EMAIL PROTECTED]> wrote: > > > > > > > >> I'm looking for a good way to store user preferences. The most > > > >> straightforward way is just to add a column to the Users table for > > > >> each preference we're looking to store. Downside is that it requires > > > >> an ALTER TABLE which gets prohibitively expensive as it gets larger, > > > >> as it's fairly inflexible. I've come up with a few alternatives, and > > > >> I'm wondering if people have ideas or suggestions, as this has to be > a > > > >> common problem. A quick Google search didn't turn up anything. > > > >> > > > >> 1. Store the preferences as a binary blob on the Users table. This > > > >> blob could be either a blob, or an integer that I use application > > > >> logic to read/write from, or I could use the SET datatype. > > > >> 2. Store the preferences in normalized form, with a new table called > > > >> UserPreferences that has UserId, Setting, Preference and we add a row > > > >> for each setting of that user. > > > >> 3. Create a separate table each time we want to add a new setting, > > > >> UserId, WhateverTheNameOfThePreferenceIs. > > > >> > > > >> Anyone have any experience with this, or better suggestions? > > > >> > > > >> Thanks, > > > >> Waynn > > > >> > > > >> -- > > > >> MySQL General Mailing List > > > >> For list archives: http://lists.mysql.com/mysql > > > >> To unsubscribe: > > > >> http://lists.mysql.com/[EMAIL PROTECTED] > > > >> > > > >> > > > > > > > > > > > > > > > > > -- > > > Curtis Maurand > > > Head Honcho > > > Xyonet Hosting Services > > > Biddeford, ME 04005 > > > mailto:[EMAIL PROTECTED] > > > mailto:[EMAIL PROTECTED] > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.
Re: grant user
On Mon, 3 Mar 2008, Hiep Nguyen wrote: hi all, i have a user that can only access localhost, how do i grant this user permission so that can also be accessed from 192.168.1.50? i got it. thanks t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: grant user
GRANT [ALL PRIVILEGES|Appropriate Privileges] *.* TO root@'192.168.1.50' IDENTIFIED BY '' WITH GRANT OPTION; Or if you want a root user from a subnet GRANT [ALL PRIVILEGES|Appropriate Privileges]ON *.* TO root@'192.168.1.%' IDENTIFIED BY '' WITH GRANT OPTION; http://dev.mysql.com/doc/refman/5.0/en/grant.html -Original Message- From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2008 10:17 AM To: mysql@lists.mysql.com Subject: grant user hi all, i have a user that can only access localhost, how do i grant this user permission so that can also be accessed from 192.168.1.50? thanks t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
grant user
hi all, i have a user that can only access localhost, how do i grant this user permission so that can also be accessed from 192.168.1.50? thanks t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiling mysql-5.0.51a -> `./t/*.disabled'?
Hello. I get a rather strange error which I so far not been able to find the cause of: --- /usr/bin/install: cannot stat `./t/*.disabled': No such file or directory make[4]: [install-data-local] Error 1 (ignored) --- I thought maybe it could be beacuse I run Debian Testing? So far I have not been able to find the cause of this error and don't know where to look. :< I configure the build using this command line: ./configure --prefix=/tmp/mysqltest50051a \ --with-charset=utf8 --with-extra-charsets=all \ --enable-thread-safe-client --with-big-tables \ --with-openssl --with-mysqld-user=mysqld Thanks in advance for any hints/pointers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode sorting and binary comparison, please!
Yves!
This is a complicated matter alright, but it is a complicated
problem to solve here also. Your statement about characters being the
same isn't really correct. To take an example: Let's assume you were
doing a phonebook, in print, of all people in the world. How would you
sort that? All names in the book should be printed correctly as the name
is usually printed in the respective country of origin. You would
realize that such a phonebook just couldn't be made in just one print.
Certain characters, although they can appear (at least as part of a
name) are treated differently in different countries.
Two examples: The nordic "umlaut / ring" characters: å ä and ö.
(aring;, auml; and ouml; in HTML lingo). These are sorted differently in
the different countries where they are used. In sweden, the are last in
the alphabet, in germay, they are usually, IIRC, intermixed with the a
and o respectively.
Another, and much better, example is the accented characters. In some
languages, accnts are very important parts of the language, French
probably being the best example here. leaving an accent out would change
things considerably, and with or without an eccent would change the sort
order. In Sweden, accents exist, even in Swedish names, and they change
the pronounciation of the word slightly (although you usually know what
the intention is, even when it is left out). But the accented characters
are treated, collation wise in any type of listing, phonebooks etc, as
the accent just wasn't there. The names Linden and Lindén are pronounced
differently, but sorted together as the accent wasn't there at all.
To you specific problem then, the issue is that as we can have just
about every character in the world available in UNICODE (this isn't
true, really, but for this discussion, let's assume this is the case).
The important thing when you store data is that you allow all these
characters to be stored, i.e. the utf8 charcater set is supported. The
"collate" specification to the is just the default ordering for the
column. Like the phonebook example above, this is how we would sort the
characters in the phonebook, lets assume we use swedish. Then the nice
thing with MySQL is that you can allow another sort order and/or
comparison method, like being able to resort the phonebook for
non-swedish people.
As for comparisons, the issue is the same. You don't know, assuming
the phonebook problem above, if someone looking for a person in the book
is French, when accented characters should be properly compared, or
swedish, when they are to be ignored. The solution is to say what
language you want, or if you want a binary comparison. If you want to
accknowledge exact matching, and say any character, accented / unlauted
etc, is different from any other character, specifiy a binary comparison:
SELECT * FROM phonebook WHERE BINARY name = 'Handel';
Look into the character set casting / conversion functions in the
MySQL manual: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html
Alternatively, you could specify the client collation, which would
apply to all operations. Or you could create your own collation. I would
really like more case sensitive collations myself. Case sensitiveness is
also something that is different for different characters in different
languages.
Hope this helps a bit
/Karlsson
Yves Goergen wrote:
Hello,
I've just read through the MySQL documentation about Unicode support,
collations and how it affects sorting and comparison of strings. And I
find it horrible, at least. I feel like I'm back in the MySQL 3.x days
where I used UTF-8 in my application and MySQL treated it binary. The
only problem was incorrect sorting of things. Today we have UTF-8
support in MySQL, which brings correct sorting (for whatever
definition of "correct") but has taken correct comparison again.
When I have three strings, e.g. "Handel", "Händel" and "Hendel", I'd
like to have them sorted correctly. Using the
utf8_{general,unicode}_ci collation seems the only way. Now when I
want the row with "Handel" in it, I'll get two rows back. One of them
is not what I wanted. So strictly, the result is incorrect. The only
way to get this right is using the utf8_bin collation. But this again
makes correct sorting impossible.
It's a nightmare. Why can't I get correct sorting *and* correct (i.e.
precise) comparison in one?
If I cannot even rely on the = operator, what good is a text-storing
database? There even isn't a case-sensitive unicode collation other
than utf8_bin. This means that in every database application that uses
unicode, I cannot separate lower from uppercase when retrieving stuff.
MySQL is simply blind for that. Not to mention different characters
that Unicode, MySQL, DIN, ISO or whoever think are the same, but they
aren't. If they were the same, you wouldn't need both of them.
Finally, my application should really be portable. I hav
