Re: Counting rows when order is ambiguous
On Thu, Feb 26, 2004 at 09:50:39AM -0700, Sasha Pachev wrote:
> If I understood the problem correctly, the answer to it is actually
> undefined. If you order by lastPostTime, the records with the same
> lastPostTime value can be returned in any order.
>
> I guess to accomplish your goal you could add a column seq_ord int
> not null to keep track of the record order according to your
> expectations, and then order by lastPostTime,seq_ord
The table has an "id" column ("id" is the primary key) that works like
your seq_ord suggestion, so I guess I could have a query like this:
SELECT *
FROM topics
ORDER BY lastPostTime DESC, id DESC
But then given a certain id = $id, I'm not sure of the best way to
count the number of rows that would be returned in the above query
before the row with id = $id. Is there any solution that looks like
this:
SELECT COUNT(*)
FROM topics
ORDER BY lastPostTime DESC, id DESC
HAVING <>
Or do I have to do this, which feels kludgy:
$postTime = SELECT lastPostTime FROM topics WHERE id = $id;
SELECT COUNT(*)
FROM topics
WHERE lastPostTime > $postTime
OR (lastPostTime = $postTime AND id > $id);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting rows when order is ambiguous
On Thu, Feb 26, 2004 at 10:49:08AM -0700, Sasha Pachev wrote: > >SELECT COUNT(*) > >FROM topics > >WHERE lastPostTime > $postTime > >OR (lastPostTime = $postTime AND id > $id); > > Can you just add id > $id to the where clause? No, that won't work because id is only used to disambiguate the order of two rows that have the same lastPostTime. (This is a forum software where topics.lastPostTime indicates the last time a topic was posted in. This may be in a different order than the topics were originally created.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Counting rows when order is ambiguous
Say I have this query: SELECT * FROM topics ORDER BY lastPostTime DESC; How would I modify it to answer the question "How many rows would be returned before the row that has topics.id = $x"? I was thinking of something like this: $xPostTime = SELECT lastPostTime FROM topics WHERE id = $x; SELECT COUNT(*) FROM topics WHERE lastPostTime > $xPostTime; but this statement breaks down in the case where there are rows having lastPostTime = $xPostTime. Would I have to do something un-portable like this: SELECT COUNT(*) FROM topics WHERE lastPostTime > $xPostTime OR (lastPostTime = $xPostTime AND id < $x); or could I do something more elegant that looks like: SELECT COUNT(*) FROM topics ORDER BY lastPostTime DESC HAVING <>; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reducing MySQL memory consumption?
On Sat, Jan 24, 2004 at 01:56:53PM -0600, Dan Nelson wrote: > You didn't say what OS you're using, but the below top output looks > like Linux's procps top, which doesn't tell you your paging rate. Run > "vmstat 1" and watch the si and so columns. Just because swap is being > used doesn't mean you're thrashing. You need to know the rate. It's Linux, yes. The si and so columns were in the hundreds when I did "vmstat 1", but I didn't save it at the moment the machine had a high load average and lots of swap space used. > No, mysql is taking up 23MB. Linux creates separate processes for each > thread, which is why you see lots of mysqlds in top. They all share > the same address space, though. Are you sure you don't maybe have a > couple dozen apache processes consuming your memory instead? Well, here's a ps (sorted by memory, greatest memory usage is at the bottom): http://lina.aaanime.net/~pmak/memory.txt At the bottom, it's all mysqld processes. A total of about 1400 MB of RAM is being used... so if mysqld's memory is all shared, I guess it's all from the apache processes adding up? Oh, does anyone know how to make "ps" not show shared memory used by processes more than once? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reducing MySQL memory consumption?
My machine appears to be swapping excessively, degrading performance. Note the high load average, combined with the mostly idle CPU and a lot of swap space being used. 3:11am up 47 days, 2:13, 12 users, load average: 6.06, 4.79, 3.19 482 processes: 480 sleeping, 1 running, 1 zombie, 0 stopped CPU states: 1.7% user, 3.5% system, 0.0% nice, 94.6% idle Mem: 1031204K av, 1010340K used, 20864K free, 0K shrd, 76084K buff Swap: 1020116K av, 397720K used, 622396K free 424652K cached I looked at "top" sorted by memory usage, and saw dozens of mysqld processes at the top (each with size 35492, RSS 23M, share 1240). So it appears that mysqld is taking up a lot of memory. How can I reduce memory consumption? sql table query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Depth-first-traversal order?
I have the following columns in a table: posts.id posts.parentId REFERENCES posts.id This represents a tree that has a single root node, and each node can have 0 or more children. posts.id is the id number of a node, and posts.parentId is the id number of that node's parent (is NULL for the root node). Is there a way to do "SELECT ... FROM posts ORDER BY " where is something that will give me depth-first-traversal order? The best solution I've been able to think of so far, short of reading the entire tree into memory and sorting it with a procedural language such as Perl, is to make a new column called "posts.order" and do "ORDER BY posts.order", and have some code that maintains posts.order whenever a new node is inserted into the tree. I was hoping there's a way MySQL could automatically keep track of depth-first-traversal order for me, though... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL server thread keeps crashing?
Does anyone know why I'm getting this error? Does it have to do with character locale problems (notice the "search.glyphs LIKE" part)? This code used to work fine before, but lately it's been getting "MySQL server has gone away" errors every time it executes, so I'm confused. Breakpoint 2, main (argc=2, argv=0xbfffedf4) at search.c:65 65 query(&mysql, query); (gdb) x/2s query 0xbfff2b80: "SELECT lyrics.lnum, romaji, glyphs, type, shandle, lhandle, name, title FROM series_name, lyrics, series_lyric, series, search WHERE lyrics.lnum = series_lyric.lnum AND series.snum = series_lyric.snum"... 0xbfff2c48: " AND series_lyric.alias = 0 AND lyrics.lnum = search.lnum AND series_name.snum = series.snum AND series_name.alias = 0 AND search.glyphs LIKE '%\227 §\224\212\201%' ORDER BY type, name, title" (gdb) next MySQL Error: MySQL server has gone away - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Deleting from one table blocks other tables?
On Sun, Jan 12, 2003 at 08:12:35PM -0700, Rodney Broom wrote: > > I'm trying to delete 5 million rows... > > Do you have to do this often? Sort of. I have a process that logs to MySQL, and I want to delete old log entries. I haven't been deleting old entries at all recently because it will freeze up everything, though. If I was deleting things regularly, I'd have to delete maybe a couple hundred thousand rows every day. > > ...access to the other tables in the database are blocked... > > How do you mean "blocked"? Do you mean that you're getting an error > message someplace, or that other operations take way too long, or > something else? When I say "blocked", I mean e.g. another process tries to SELECT from another table in the database, but it takes way too long. > > Any suggestions on how I can delete those rows without causing a lot > > of downtime? > > Do the big operations at a time when your site isn't busy. Like at night. My site is always busy. People don't use it as much at night, but there's still many people using it. sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Deleting from one table blocks other tables?
I have a table in a database. I'm trying to delete 5 million rows from it. But whenever I try to do this, while the delete command is executing access to the other tables in the database are blocked (which freezes up a website that runs off that database, even though that website doesn't use the table I'm deleting from)! Any suggestions on how I can delete those rows without causing a lot of downtime? I'm using 3.23.47. Would it help if I upgraded to 4.x? sql (stupid filter...) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Storing a SHA1 checksum
On Sun, Dec 29, 2002 at 11:09:47PM -0600, Paul DuBois wrote: > At 5:28 -0500 12/29/02, Philip Mak wrote: > >sql, table > > > >I'm storing a SHA1 checksum as "varchar(20) binary" in my application. > > Other people have addressed other aspects of your message, but I'm > curious why you're using a VARCHAR(20), when SHA1() returns a 40-byte > string. > > > >After running a test, it seems MySQL will strip trailing spaces from a > >varchar column, even if it is binary! That means if the last character > >of my SHA1 checksum happens to be a space, MySQL will corrupt it. > > SHA1() returns a string of 40 hexadecimal digits. There won't be trailing > spaces. > > Are you converting the hex string to some other representation before > storing it? Yes, I'm converting it to a binary representation first, so that it only takes 20 bytes instead of 40 bytes. I ended up using a TINYBLOB to store my checksum, since performance shouldn't be overly critical in my application. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Storing a SHA1 checksum
sql, table I'm storing a SHA1 checksum as "varchar(20) binary" in my application. After running a test, it seems MySQL will strip trailing spaces from a varchar column, even if it is binary! That means if the last character of my SHA1 checksum happens to be a space, MySQL will corrupt it. What should I do? It seems I can: 1. Use blob instead of varchar. Problem: blob type is slower. 2. Make my application pad the checksum out to 20 spaces. Problem: Increases my code complexity a bit. 3. Wait for MySQL to fix the strip trailing spaces bug. Problem: That doesn't provide an immediate solution. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
LAST_INSERT_ID() and INSERT DELAYED
I have a daemon which uses MySQL (INSERT INTO) to log. The problem is, whenever I try to do anything with the log table that takes a long time, it makes the daemon freeze until the table is free again. I'm thinking of making the daemon use INSERT DELAYED instead, so that it won't get blocked. But, each log entry requires inserting data into table1 AND table2 (for each entry in table1, there are multiple entries in table2). INSERT INTO table1 SET id = NULL, ...; $id = SELECT LAST_INSERT_ID(); INSERT INTO table2 SET id = $id, ...; INSERT INTO table2 SET id = $id, ...; INSERT INTO table2 SET id = $id, ...; If I use INSERT DELAYED, then the LAST_INSERT_ID() isn't going to work, right? How can I make the daemon avoid blocking, yet still be able to insert data into both tables? Should I perhaps make my daemon generate its own ids (rather than relying on AUTO_INCREMENT), and then use INSERT DELAYED for everything? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to backup without blocking?
sql, query Right now, every day I run the equivalent of a mysqlhotcopy on my database (read-lock the tables, copy the files, unlock the tables). This freezes up everything (websites, etc.) that uses the database for 10 minutes. That is too long. Is there a way to get a snapshot of the database without locking the tables? Do I need to convert from MyISAM to InnoDB perhaps? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: To join, or not to join?
On Fri, Nov 22, 2002 at 06:56:53PM -0500, Michael T. Babcock wrote:
> On Fri, Nov 22, 2002 at 06:20:14PM -0500, Philip Mak wrote:
> > sql,query
> >
>
> Why not just:
> SELECT * FROM users, boardAdmins, boardMembers WHERE id = 5;
>
> You're not really 'joining', since boardAdmins and boardMembers
> don't have the structure JOINs are made for (it seems).
Oops! I was typing my example from memory, and did it wrong.
Sorry, it's supposed to be like this:
SELECT *
FROM boards
LEFT JOIN boardAdmins
ON boardAdmins.userId = #{userId}
AND boardAdmins.boardId = boards.id
LEFT JOIN boardMembers
ON boardMembers.userId = #{userId}
AND boardMembers.boardId = boards.id
AND boards.id = #{boardId}
For each entry in "boards", there are zero or more corresponding
entries in boardAdmins and boardMembers.
The above could be rewritten with 3 separate SELECT statements:
SELECT *
FROM boards
WHERE id = #{boardId}
SELECT *
FROM boardMembers
WHERE userId = #{userId}
AND boardId = #{boardId}
SELECT *
FROM boardAdmins
WHERE userId = #{userId}
AND boardId = #{boardId}
So, I'm wondering which way would be faster.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
To join, or not to join?
sql,query Which way is faster? Way 1: SELECT * FROM users LEFT JOIN boardAdmins ON boardAdmins.userId = users.id LEFT JOIN boardMembers ON boardMembers.userId = users.id WHERE id = 5; Way 2: SELECT * FROM users WHERE id = 5; SELECT * FROM boardAdmins WHERE userId = 5; SELECT * FROM boardMembers WHERE userId = 5; (Note that all of these SELECT statements only retrieve a single row, since the primary keys are users.id, boardAdmins.userId and boardMembers.userId.) The second way probably is going to have more latency between the client and the database server. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
What's data/*-bin.*?
What are all these rei-bin.* files in this MySQL data directory? Are they needed? They're taking up a lot of disk space. They seem to be some sort of log. horderei.aaanime.net.pid rei-bin.008 rei-bin.016 linkwars rei-bin.001 rei-bin.009 rei-bin.017 my.cnf rei-bin.002 rei-bin.010 rei-bin.018 mysqlrei-bin.003 rei-bin.011 rei-bin.019 plesk.rackshack.net.err rei-bin.004 rei-bin.012 rei-bin.020 plesk.rackshack.net.pid rei-bin.005 rei-bin.013 rei-bin.021 psa rei-bin.006 rei-bin.014 rei-bin.022 rei.aaanime.net.err rei-bin.007 rei-bin.015 rei-bin.index - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to create temporary table in 4.0?
I can't get CREATE TEMPORARY TABLE to work due to a privilege problem. Does anyone know what I'm doing wrong? mysql> update user set create_tmp_table_priv='Y'; Query OK, 0 rows affected (0.00 sec) Rows matched: 2 Changed: 0 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> quit Bye [root@priam root]# su - seasft [seasft@priam seasft]$ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 48909 to server version: 4.0.2-alpha-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create temporary table x (x int); ERROR 1142: create command denied to user: 'seasft@localhost' for table 'x' I've also tried: grant create temporary tables on * to seasft@localhost; grant create temporary tables on *.* to seasft@localhost; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Full-text search
On Thu, May 30, 2002 at 08:15:41AM -0700, Ryan Barber wrote: > 2) Why would these sample words have no match? > Cannot match against "when", "most", "like", "goes" Why are these > words not in the index? The file contains 1 million title records > and all of these words are in the file many times. Those words are considered 'stopwords'; because they are so common, MySQL doesn't index them since (1) they won't help much in distinguishing a search query, and (2) it saves a lot of space. You can make MySQL index these words if you recompile MySQL without a stopword list. To do that, see http://www.mysql.com/doc/F/u/Fulltext_Fine-tuning.html (search for the word "stopword" in that webpage). - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Trasfer SQL requests
On Thu, May 30, 2002 at 05:53:03PM +0300, Dmitry Alyabyev wrote: > Is it possible to set up one mysql server *only* for listening on > socket and transfer client requests to another one server through > network connection ? Of course I mean return results too :-) It sounds like what you want can be accomplished by a simple "port forwarder" program (search for that phrase on Google) which listens on one TCP/IP port and forwards the results to another TCP/IP port (which would be the MySQL server on port 3306); this is independent of MySQL. The only problem with this approach is that from the point of view of the MySQL server, every client will seem to have the IP address of the port forwarder. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: fulltext - exact phrase
On Wed, May 29, 2002 at 02:10:07PM -0700, ddd wrote: > I have a question, is possible to search on mysql 4.0 in fulltext > index the exact phrase ? http://www.mysql.com/doc/F/u/Fulltext_Search.html suggests that it is possible. Here is the relevant excerpt: The boolean full-text search capability supports the following operators: ... " The phrase, that is enclosed in double quotes ", matches only rows that contain this phrase literally, as it was typed. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Shared message queue?
I have a bunch of processes running on the same machine that should be able to send unicast, multicast and broadcast messages to each other. Messages should be received in the same order that they were sent. I'm trying to figure out how to implement that. These processes are already sharing the same MySQL database, so I'm thinking that a process which wants to send a message could INSERT it into a table, then another process can SELECT it to read it. This would seem to require all processes to constantly poll the table, though, so I'm thinking maybe I should use something other than MySQL for the shared message queue functionality... Suggestions? Should I: (1) use MySQL as I described above, (2) use MySQL with some other method I haven't thought of, or (3) use something else? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA INFILE sets blanks to 0 instead of NULL
On Tue, Feb 26, 2002 at 12:03:25PM -0600, Paul DuBois wrote: > >it set the integer columns to 0 if they were blank in the text file. > >How can I make it set those to NULL instead? > > You'll have to preprocess the file to convert empty fields to \N. Damn, that's what I thought. I guess I'll have to do that for now; thanks for the perl code. I think that it would have made sense for MySQL to treat empty fields as NULL when importing into a numeric column. (Right now, the empty field causes a warning anyway since it converts it to 0.) Is there a place where I can put this in as a feature request (or is there a good reason that it shouldn't do this)? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
LOAD DATA INFILE sets blanks to 0 instead of NULL
I made an Excel spreadsheet containing grades of students in my class, like this: (name) (email) (assignment 1 grade)(assignment 2 grade) For when a student did not submit an assignment, I just left the field blank. I'm trying to import the spreadsheet into a MySQL table of the following format: name VARCHAR(30) email VARCHAR(30) a1 TINYINT a2 TINYINT When I typed the following: load data local infile 'grades.txt' into table grades fields terminated by '\t'; it set the integer columns to 0 if they were blank in the text file. How can I make it set those to NULL instead? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [SECURITY] How do these blank passwords get into mysql.user?
On Tue, Feb 19, 2002 at 04:39:10PM +0100, Peter Banik wrote: > you should explicitly specify host/password in the GRANT statement, like > this: > > GRANT ALL ON xxx.* TO user@'localhost' IDENTIFIED BY 'password'; > FLUSH PRIVILEGES; > > This way the user will only granted access from the specified host, you > don't need to manually INSERT into the user table. (You'll also get rid > of the empty passwords.) Hmm, I just ran another experiment: mysql> create database xxx; Query OK, 1 row affected (0.00 sec) mysql> grant all on xxx to xxx; Query OK, 0 rows affected (0.00 sec) mysql> select host,user,password from user where user='xxx'; +--+--+--+ | host | user | password | +--+--+--+ | %| xxx | | +--+--+--+ 1 row in set (0.00 sec) So if I execute a GRANT statement without specifying a password, and MySQL decides that it needs to create a new user for this (host,user) pair, then it will create it with blank password! This seems to be insecure default behavior to me. I wonder if it would be better to change MySQL such that it will not create a user with blank password like this unless "IDENTIFIED BY ''" is explicitly specified? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[SECURITY] How do these blank passwords get into mysql.user?
One thing's been bothering me for a while: When I create a user and
database in MySQL, the user always ends up with an extra entry with
host='%' and password=''. How is this happening? This is how I create
a new database and user:
mysql> create database xxx;
Query OK, 1 row affected (0.01 sec)
mysql> insert into user set host='localhost', user='xxx', password=password('yyy');
Query OK, 1 row affected (0.03 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on xxx to xxx;
Query OK, 0 rows affected (0.03 sec)
mysql> grant all privileges on xxx.* to xxx;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,password from user where user='xxx';
+---+--+--+
| host | user | password |
+---+--+--+
| % | xxx | |
| localhost | xxx | 66debff13dff1053 |
+---+--+--+
2 rows in set (0.00 sec)
What did I do wrong to cause these users with blank passwords to be
created (essentially opening me wide to the outside)? My MySQL version
is 3.23.47. It's worked fine after I delete the extra row in the user
table manually, but this could be dangerous to someone who doesn't
notice it!
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: A password column
On Sat, 29 Dec 2001, Tom Jones wrote:
> I'm fairly new to MySQL and I was wonder if there was a way to, well let's
> say encrypt a password in a column? I would like to have a users table in my
> database and in it I would like to have a password column, but I don't want
> the password in plain text. Is this possible?
Use the PASSWORD() function. Example:
INSERT INTO users VALUES ('pmak', PASSWORD('secret'));
This is a one way hash. I think it works the same way on all MySQL
servers (i.e. it's portable).
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Security hole in mysqlhotcopy?
On Sun, 30 Dec 2001, Bogdan Stancescu wrote: > You can usually try providing an empty -p parameter and be asked for the > password afterwards. Doesn't seem to work: [mysql@lina mysql]$ mysqlhotcopy -u root -p "" test . DBI->connect(;host=localhost;mysql_read_default_group=mysqlhotcopy) failed: Access denied for user: 'root@localhost' (Using password: NO) at /usr/local/mysql/bin/mysqlhotcopy line 161 [mysql@lina mysql]$ mysqlhotcopy -u root -p test . DBI->connect(;host=localhost;mysql_read_default_group=mysqlhotcopy) failed: Access denied for user: 'root@localhost' (Using password: YES) at /usr/local/mysql/bin/mysqlhotcopy line 161 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Security hole in mysqlhotcopy?
As far as I can tell, mysqlhotcopy does not provide a way of specifying the password anywhere other than the command line (e.g. it doesn't seem to read .my.cnf). Isn't this a security risk? I noticed that mysqlhotcopy *does* change its "ps" entry after it starts in order to mask the arguments, but if I'm not mistaken, there's still a brief moment when any normal user on the same system can do "ps" to capture the password. Also, the password can show up in places like .bash_history. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL v.s. Oracle
Hi all, We are currently using MySQL for our database driven website. Currently, we have about 100,000 users. In January, we will be getting 2 million new registered users on our website. We're buying a $50,000 Sun box to run the database server on. We're deciding whether we should switch to Oracle. Can MySQL handle this kind of load? The president (who doesn't know much about databases) was thinking about buying Oracle, but from what I've heard, Oracle is actually slower than MySQL since it needs to check FOREIGN KEYs, TRIGGERs, ASSERTIONs, etc. Can someone provide some advice? Thanks. My main concern is whether that massive scale (2 million registered users, along with all the data and CPU load they generate) is supported by MySQL. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
TEXT v.s. VARCHAR
[table] Does TEXT have any significant disadvantages compared to VARCHAR? We need to store a bit of text, but in some cases it can exceed 255 characters, so we have to use TEXT. I'm guessing that the only difference is that the length counter needs 2 bytes instead of 1 byte of space, right? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
CPU consumption on sorting
[table] Is there a significant CPU consumption difference between this: SELECT * FROM posts ORDER BY lastPostTime, id compared to this: SELECT * FROM posts ORDER BY lastPostTime My gut feeling tells me that they should almost take the same amount of time to execute, because 'id' only needs to be compared if 'lastPostTime' is the same (when comparing two rows to decide which ones go first) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL on FreeBSD Dual CPU machine
On Fri, 16 Nov 2001, Jeremy Zawodny wrote: > > Where can I find instructions for compiling MySQL on LinuxThreads? > > Try out the FreeBSD MySQL port. I've been told that it's trivial. What's the URL for that? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL on FreeBSD Dual CPU machine
I have a FreeBSD 4.4-STABLE machine with two CPUs running mysql-3.23.44. I'm wondering about how to make MySQL use both CPUs. Am I correct in saying: 1. The binary distribution of MySQL for FreeBSD from www.mysql.com does not make use of both CPUs. 2. The only way to make MySQL use both CPUs on FreeBSD is to compile it using LinuxThreads. Where can I find instructions for compiling MySQL on LinuxThreads? I've searched around the mailing list but have not been able to find a comprehensive set of instructions. I tried copying some of the examples of people compiling with LinuxThreads, but didn't succeed. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Stale perlhttpd connections
I am running Apache with mod_perl (Apache::DBI is off) with connections to MySQL. I'm finding that the number of connections used by perlhttpd seems to be too high. I did this: [root@sg3 root]# ps -A | grep mysqld | wc 88 3532743 [root@sg3 root]# /etc/httpd/bin/perlhttpdctl restart /etc/httpd/bin/perlhttpdctl restart: httpd restarted [root@sg3 root]# ps -A | grep mysqld | wc 15 61 480 Restarting perlhttpd freed up 73 connections, but it shouldn't have been using that many connections (perlhttpd has MaxClients set to 15). I'm getting "Too many connections" error on the server about every 12 hours now; there seems to be a leak in the number of connections. Can anyone suggest a solution to this? BTW, I don't use $dbh->disconnect in my scripts. Is that a problem? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Weird table corruption?
Does anyone know what happened here? Notice the strangely formatted output from the second command. I did a "repair table" but it doesn't seem to detect any table corruption. mysql> repair table pairings; +---++--+--+ | Table | Op | Msg_type | Msg_text | +---++--+--+ | shoujoai.pairings | repair | status | OK | +---++--+--+ 1 row in set (0.01 sec) mysql> select name from pairings where fid=0 and pid=1 and name != 'One'; +--+ | name | +--+ |Two +--+ 1 row in set (0.01 sec) __ Do You Yahoo!? Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Perl DBI: Same column name in different tables problem
Let's say I performed the following query using Perl DBI:
$row = $dbh->selectrow_hashref(<<"~");
SELECT fanfics.handle, authors.handle
FROM fanfics, authors
WHERE fanfics.aid = authors.aid
~
I won't be able to access both fanfics.handle and authors.handle this
way, because they're called "handle". I would like to be able to access
them e.g. by doing $row->{fanfics.handle} and $row->{authors.handle},
or something like that.
What workarounds have people found for this problem?
__
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA INFILE Question
On Tue, 4 Sep 2001, Curtis Spencer wrote: > I have a text file with around 25 fields but I only want 5 of them. > They are not the first 5 fields that I want. Is there a way to skip > fields using LOAD DATA INFILE so I don't have to build a 25 field table > and then cut it down? Read the manual for LOAD DATA INFILE; look at the part where it says IGNORE. You can tell it to IGNORE the first 5 lines. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: limit and order by issuse
I see... if adding "ORDER BY articleId DESC LIMIT 0, 20" causes a query to start returning 0 rows, then that sounds like something is wrong. Try using the REPAIR TABLE command on all your tables from inside MySQL, e.g.: REPAIR TABLE article; REPAIR TABLE asubject; REPAIR TABLE atype; REPAIR TABLE author; REPAIR TABLE source; REPAIR TABLE user; If there is corruption in the tables, that might be causing the problem. Other than that, I am out of ideas... On Tue, 4 Sep 2001, [EMAIL PROTECTED] wrote: > >Use this instead: > >ORDER BY articleId DESC LIMIT 0, 20 > > That is not a problem as first parameter is just offset. But I have also > tested with 0, but still the same problem. > > Secondly even if the no. of rows returned are greater then 20 there is a > same error message , while running from phpadmin. > > Have you ever faced this problem , with order by and limit? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: limit and order by issuse (fwd)
[table sql stupid spam filter] On Tue, 4 Sep 2001, [EMAIL PROTECTED] wrote: > order by articleId desc limit 1 , 20 That will cause no rows to be returned if there are less than 20 results. Use this instead: ORDER BY articleId DESC LIMIT 0, 20 It starts counting at 0 instead of 1. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: limit and order by issuse
On Tue, 4 Sep 2001 [EMAIL PROTECTED] wrote: > I am trying to use limit and order by in the same query for paging of > records. But faceing the problem that query does not return any row. > After removing of limit every thing is working fine. > > Is it really a bug. Any solution. Going by what you have told me, I'm thinking that your LIMIT statement may be incorrect. Show us what the query you are executing is so that we can see what is happening; I don't have enough information to answer your question right now. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql c api
On Tue, 4 Sep 2001, ahmed wrote:
> i hope that some one send me a programme illustrating how to access
> to mysql database with c api ..
I run a site that has a free e-mail forwarding service. The following
program is what runs .qmail-default.
#define HOST "localhost"
#define USER "username"
#define PASSWORD "password"
#define DATABASE "database"
#include "mysql.h"
#include
#include
/* Returns 1 if s contains any UNSAFE characters.
* Returns 0 otherwise. */
#define UNSAFE "&;`'\"|*?~<>^()[]{}$\n\r"
int unsafe(char *s) {
char *p = s;
while (*p) {
if (strchr(UNSAFE, *p)) {
return 1;
}
p++;
}
return 0;
}
/* This program is called by ~/.qmail-default. It queries the MySQL
* database to forward the e-mail to someone. */
main() {
MYSQL mysql;
MYSQL_RES *result;
MYSQL_ROW row;
char *user, query[255], email[255], buf[8192];
FILE *pipe;
mysql_init(&mysql);
if (!mysql_real_connect(&mysql, HOST, USER, PASSWORD, DATABASE, 0,
NULL, 0)) {
fprintf(stderr, "Failed to connect to database: Error: %s\n",
mysql_error(&mysql));
exit(111);
}
user = getenv("EXT");
sprintf(query, "SELECT email FROM records WHERE login='%s'", user);
if (mysql_query(&mysql, query)) {
fprintf(stderr, "Unable to query database: %s\n",
mysql_error(&mysql));
exit(111);
}
result = mysql_store_result(&mysql);
if (!mysql_num_rows(result)) {
printf("There is no user '%s' registered here.\n", user);
exit(100);
}
row = mysql_fetch_row(result);
if (!row[0] || !*row[0]) {
printf("User '%s' has not setup a forwarding address.\n", user);
exit(100);
}
strcpy(email, row[0]);
if (unsafe(email)) {
printf("User '%s' has a forwarding address containing unsafe characters (%s).\n",
user, email);
exit(100);
}
/* Everything checks out. Forward the message. */
sprintf(query, "/var/qmail/bin/forward %s", email);
if (pipe = popen(query, "w"))
while (fgets(buf, sizeof(buf), stdin))
fputs(buf, pipe);
exit (0);
}
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Alternate word finding with '%'
On Mon, 3 Sep 2001, Chris Cameron wrote:
> I'm sure this is disscused somewhere, but I'm not sure how I'd look for
> it.
>
> Is it possible to supply a thesaurus-like file for mysql so when you
> go "%oil%" it finds petrol gas and/or lubricant?
As far as I know, there is no way to do that natively in MySQL. It's
relatively easy for you to write a wrapper function to do this, though.
Assuming you're working in perl, you can do something like this:
# WHERE data LIKE '%oil%' => thesaurus('data', 'oil')
sub thesaurus {
my ($column, $word) = shift;
return join(' OR ', map { "$column LIKE '%$_->[0]%' }
$dbh->selectall_arrayref("SELECT words FROM thesaurus WHERE key=?", $word);
}
So thesaurus('data', 'oil') would return:
data LIKE '%petroleum%' OR data LIKE '%gas%' OR data LIKE '%oil%'
and you can use that as part of your SQL query.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Storing Arrays?
Is there a good way that I can store an array in a single row using MySQL? I was taught that under a clean relational database design, arrays should be stored one element per row. However, this makes retrieving the entire array less practical, especially when I have an array of arrays. I've thought about storing an array of words as a VARCHAR() string, and using a FULLTEXT index to search through it, but I'm concerned about efficiency. What should I do? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FULLTEXT search with hyphens in data
On Wed, 22 Aug 2001, Marty McCoy wrote: > I'm performing a FULLTEXT search against varchar columns of one of our > tables. One of our columns contains embedded serial numbers such as > "02-123456" and "04-234567". The problem that when I do a FULLTEXT search > against "02-123456", it brings back results with the following values: I'm guessing that you have a VARCHAR column that lists multiple serial numbers. Have you thought about splitting this into a different table that has just one serial number per row? This would be better for efficiency and you can also match the serial numbers exactly. The FULLTEXT method that you are using seems an awkward way to do this. (I'd answer the question you asked, but I don't know how to set the delimiter for FULLTEXT. It would make sense that you can do something about it---otherwise people couldn't search for words like "CD-ROM". Maybe someone else can shed some light on this.) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with INSERT INTO ... SELECT
On Mon, 20 Aug 2001, Michiel Leegwater wrote: > insert into table1 select Startnr, Tijd, Afstand, Slag, Datum, Opmerking, > CRvan,CRtot,PR,Categorie from table2; > > This doesn't work, it says "Column count doesn't match value count at row 1" > I understand the problem. But I can't use my ID column in the select query > because both tables have an AUTO INCREMENT ID. Does someone have any > suggestions how to work around this?? What if you do: SELECT NULL, Startnr, Tijd, ... FROM table2 Put NULL in place of the AUTO INCREMENT id. That might work. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Case-preserving is not consistent
On Sun, 19 Aug 2001, Sinisa Milivojevic wrote: > If this is happening on Windows, we truly can not do anything about > it. > > You could help there by forcing all table names to be lower-case by > starting mysql service with a corresponding option. No, it's happening on Linux. Here's a transcript of what happens. I create a table called "test" with a column called "HELLO". Then when I execute "modify column hello", the case of the column becomes "hello". This will cause perl scripts that use $sth->fetchrow_hashref to fail. pmak@trapezoid [/home/animel/www/include]# mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 813 to server version: 3.23.36 Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql> create table test (HELLO int); Query OK, 0 rows affected (0.07 sec) mysql> alter table test modify column hello int; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe test; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | hello | int(11) | YES | | NULL| | +---+-+--+-+-+---+ 1 row in set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqlctl configtest?
In Apache web server, the command used to start/stop the web server also has an option called "configtest". One can type "apache configtest" and it will tell you if the configuration file has a valid syntax. This is to save you from the situation where you modified the configuration file, then stop and start the server, but then realize that the configuration file is broken, so you can't start the server again until you fix it (bad in production environments!!). Does MySQL have a similar utility? (Suggestion: Perhaps support-files/mysql.server would benefit from the addition of a "configtest" switch. A "restart" switch that just does "stop" and then "start" would be nice too. Also "reload" to make the MySQL server reload the configuration file without restarting, if this is possible.) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Database File group ownership
On Sat, 18 Aug 2001, Philippe Paravicini wrote: > could you not add the 'animel' user to the 'mysql' group? > > > I have done "chgrp animel *" on the directory that contains the files of > > the database "animelyrics", but new tables that I create are group "mysql" > > instead of group "animel". Is there a way I can make it create new table > > files with the group of "animel"? I thought about doing that, but the problem is that I would end up giving the 'animel' user access to ALL the MySQL databases on the system, rather than just the 'animelyrics' database. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Case-preserving is not consistent
On Sun, 19 Aug 2001, Lars Bruun Hansen wrote: > mysql> show tables from test; > ++ > | Tables_in_test | > ++ > | MyTable| > ++ > > mysql> alter table MyTable add (y char(1)); > mysql> show tables from test; > ++ > | Tables_in_test | > ++ > | mytable| > ++ > > If this is not a bug how do I then get MySQL to be case-preserving for > the table names? I second that this should be fixed. I had a similar problem once; I did an ALTER TABLE MODIFY COLUMN, which is theoretically supposed to leave the column name unchanged (as opposed to ALTER TABLE CHANGE COLUMN). However, I had typed the case of the column differently, so it changed the case of the column. This caused my perl scripts that access the database to break, since perl is case sensitive for variable names. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Database File group ownership
I have a database called "animelyrics". I want to make it so that the files inside this database are readable to the UNIX user "animel", so that I can do backups easier. I have done "chgrp animel *" on the directory that contains the files of the database "animelyrics", but new tables that I create are group "mysql" instead of group "animel". Is there a way I can make it create new table files with the group of "animel"? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multiple SELECTS v.s. one SELECT
I'm using the Tangram perl module, which makes MySQL act like an object oriented database. My database has a table of stories (STORY). Each STORY is written by an AUTHOR. So, the database design is like this: CREATE TABLE story ( id int PRIMARY KEY NOT NULL, title VARCHAR(80), author int # references author.id ); CREATE TABLE author ( id int PRIMARY KEY NOT NULL name VARCHAR(80), email VARCHAR(80) ); If I want to list all of the stories in a table, along with their author names, then I would do this in SQL: SELECT story.*, author.* FROM story, author WHERE story.author = author.id But when I'm using the Tangram object oriented interface, it does this: SELECT * FROM story SELECT * FROM author WHERE id = 927 SELECT * FROM author WHERE id = 76 SELECT * FROM author WHERE id = 502 (etc., until it gets all the author ids for the stories that it selected) How inefficient is this compared to simply performing a join? And, does anyone know how to make Tangram do a join, rather than retrieving the author objects individually? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Single Quotes in a Select Statement
On Fri, 17 Aug 2001, Cal Evans wrote:
> Select * from product where title = 'This title has a \' in it.';
>
> This query does not work. Can anyone tell me how to build a query that will
> return that title?
Are you sure you're not overlooking something? I just tried the following
commands and had no problems with the single quote...
mysql> create temporary table product (title varchar(80));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into product values ('This title has a \' in it.');
Query OK, 1 row affected (0.00 sec)
mysql> select * from product;
+---+
| title |
+---+
| This title has a ' in it. |
+---+
1 row in set (0.00 sec)
smysql> select * from product where title='This title has a \' in it.';
+---+
| title |
+---+
| This title has a ' in it. |
+---+
1 row in set (0.00 sec)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Spawns Many Processes and Uses 100% CPU! PART #1
It may be that MySQL is simply spawning too many processes for your machine to handle. I might be wrong, but here's something to try: 1. Set MySQL max_connections to 45. 2. Set httpd MaxClients to 50. That will limit the number of processes MySQL is allowed to start, and also makes sure that Apache will delay web requests such that it won't try to service a request when MySQL is at the maximum number of processes. This may keep your machine from spinning out of control. However, there might be a bug somewhere that's making MySQL take up more system resources than it should, and my solution does not address that possibility. Maybe someone else has an idea about this. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: User specific queries.
On Fri, 17 Aug 2001, Mysql Mailing List wrote: > If he sends in "select * from table", we would like the mysql actaully > execute "select * from table where hisID=1000"; > > Basically, we want mysql database to screen the query sent from a certain > user, and apply some conditions on that query. > > Is it possible to do it? Is there a better way to do it? As far as I know, the permissions control system of MySQL can only restrict operations at the table level, not the row level. Someone correct me if I'm wrong. I think you would have to write a middleware layer that accepts queries directly from the user, then modifies the queries so that they only affect the rows that they should. If you do this, you have to be very careful in parsing the queries. For example, what if you provide a function to him that executes this query: SELECT ___ WHERE hisID=1000 AND ___ and let him fill in the ___. Guess what, that's not secure at all! He could do: SELECT * FROM table WHERE hisID=1000 AND 1 OR 1 and he's bypassed your security check. You'll have to think about this carefully if you try to do this. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Backups from Server to localhost
On Fri, 17 Aug 2001, hanan khader wrote: > Hi everybody > I want to make backing up for my databases from the server into my pc, I > login to the server with the administrator username and password, how could > this backing up be done? and is there any risk that i should be aware of ? > is there any softwares that could be used for administrator managemnet or > shall we still keep using the command prompt ? You can run "mysqldump" to get a local copy of the CREATE TABLE and INSERT statements needed to re-create the tables. This is the easiest way, but if your database is tens of megabytes or larger, it takes longer than it should. The other option is to copy the data files (.MYD, .FRM) directly from the MySQL data directories. In order to get a consistent snapshot of those files, you need to flush the buffers (only MySQL root can flush) and lock the tables first. You'll also have to use a UNIX user that can read the data files directly. So what you could do is something like: - open MySQL connection to remote host as MySQL user "root" - open FTP connection to remote host as UNIX user "mysql" - execute LOCK TABLES on all the tables you want to get - execute FLUSH TABLES - download the .MYD and .FRM files via FTP - execute UNLOCK TABLES - close the FTP and MySQL connection You can replace rsync with FTP in order to transfer the file faster on subsequent backups. Note that while you're waiting for the FTP to complete, the tables will remain locked so no one else can use them. If this would take too long, then you should LOCK TABLES, copy the data files to another location on the same system (should only take a few seconds), UNLOCK TABLES, then FTP the copied data files. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql optional file (my.cnf) problem
On Sat, 18 Aug 2001, Fai wrote: > Does any body know how to prevent mysql server looking for user specific > optional file ( .my.cnf)? So, user cannot put the optional file (.my.cnf) in > his home directory to affect the mysql server behaviour. Even if user can put the optional file in his home directory, it does not affect the mysql server behavior. It only affects the mysql client behavior. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: check if an index exists?
On Fri, 17 Aug 2001, Christopher Teli wrote: > How can I check if an index exists on a table??? > Is there some where I can query the admin tables? SHOW INDEX FROM - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Accessing MySql database using Bash shell Script
On Fri, 17 Aug 2001, Grigory Bakunov wrote: > echo "select * from test;"|mysql test; Actually, for one liner commands you don't have to use echo| to mysql. You could use the -e command line switch, e.g.: mysql -e"SELECT * FROM test" test Here is the definition for the -e switch: -e, --execute=... Execute command and quit. (Output like with --batch) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: html
On Thu, 16 Aug 2001, Theo Richel wrote: > Is it possible to store html in Mysql-fields? Where can I find more > info in this? Did you need MySQL to treat the HTML in some special way? I'm confused as to why you're asking this question. I would think that you can just use the TEXT data type to store the HTML. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: @HOSTNAME@ ?
On Thu, 16 Aug 2001, mysql wrote: > "@HOSTNAME@: command not found" > > Does anyone know how to solve that ? I didn't find anything on the net > or on the mysql.com homepage. You're getting that from support-files/mysql.server, yes? I've gotten that message too and was puzzled by it. I just replaced @HOSTNAME@ with `hostname` and it worked fine. (Actually, it'll work fine as is, unless your machine shares its disk with another machine. If your machine doesn't share a disk, then you can safely ignore this error message.) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to make this code pretty?
Doesn't doing it that way preclude using $dbh->quote? That could mess up
if the name had a single quote in it.
One idea I had was to do something like this:
my ($f, $d); # form data, database data
for (qw(friendly parent intentional address port timeout priority)) {
$f{$_} = $q->param($_);
$d{$_} = $dbh->quote($f{$_});
}
$d{address} = "INET_ATON($d{address})";
$dbh->do("REPLACE INTO services SET ".
join(',', map { "$_ = $d{$_}" } keys %d));
Basically, I put all my variables into a hash, then I do some magic with
join, map and keys to automatically generate the SQL query part after SET.
If I use it a lot, the "for" loop could be replaced by a function call
that passes $f, $d and the list of variables to set.
What do you guys think of that technique?
On 16 Aug 2001, Harald Fuchs wrote:
> I'd do it like that:
>
> my $sql = q{
> REPLACE INTO services
> SET friendly = ?,
> parent = ?,
> intentional = ?,
> address = INET_ATON(?),
> port = ?,
> timeout = ?,
> priority = ?
> };
> $dbh->do ($sql, undef,
> $friendly, $parent, $intentional, $address,
> $port, $timeout, $priority);
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Left join? Is this query possible.
On Thu, 16 Aug 2001, Rodney Moses wrote: > Thanks for the reply Philip! > > Unfortunately that doesn't work. I should have clarified that there never > are null values in the 'amount' fields. Even if you don't have NULL values in the 'amount' fields, doing the LEFT JOIN will cause NULL values to appear. Here's an example: Table o: id amount 1 5.00 2 6.00 Table p: id amount 2 4.00 3 7.00 If you join tables o and p, you'll get this: id o.amountp.amount 1 5.00NULL 2 6.004.00 3 NULL7.00 That's where the NULL values come from; table p didn't have an amount for id=1, and table o didn't have an amount for id=3. Maybe you can get some insight into what is going on if you try running this query: SELECT a.id, o.amount, p.amount FROM accounts AS a LEFT JOIN orders AS o ON a.id = o.account_id LEFT JOIN payments AS p ON a.id = p.account_id GROUP BY a.id; This will show you how it's doing those joins. > Maybe it is wrong to join both the payment and the order tables to the > account table in the query as neither of these directly related to > each other. BTW, maybe it's just because you over-simplified your example, but if you really only had an "id" and an "amount" field, then you could combine orders and payments into one table, and use negative amount for order, and positive amount for payment. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Left join? Is this query possible.
On Thu, 16 Aug 2001, Rodney Moses wrote: > This doesn't work, it has really wacky results: > #balance of all accounts > select a.id, (sum(o.amount) - sum(p.amount)) from > accounts as a left join orders as o on a.id = o.account_id left join > payments as p on a.id = p.account_id > group by a.id; > > Can anyone tell me what I'm doing wrong? Is this sort of query possible? Just a hunch, but the NULL columns might be screwing it up. If an account has payments but no orders, then its sum of orders would be NULL (I think) when you do the left join. NULL + anything = NULL. Try replacing sum(o.amount) with ifnull(sum(o.amount),0) and the same for p.amount; see if that helps. That should make it interpret NULL as 0. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to make this code pretty?
I've had an ongoing problem where the SQL statements in my perl programs
come out rather messily.
Does anyone have specific and general suggestions on how to write embedded
SQL code cleanly in perl programs? Here's an example of an ugly piece of
SQL statement I wrote recently:
$dbh->do
("REPLACE INTO services SET friendly=".$dbh->quote($friendly).
", parent=".$dbh->quote($parent).
", intentional=".$dbh->quote($intentional).
", address=INET_ATON(".$dbh->quote($address).")".
", port=".$dbh->quote($port).
", timeout=$timeout".
", priority=".$dbh->quote($priority));
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT .. OR from multiple tables
Your query will work in every case, EXCEPT when either "Current" or "Temporary" has 0 rows, because then there's nothing to join. I'm not sure if doing a join is a "clean" way of doing this though. If you know that "Current" will never be empty (but "temporary" might be), then this query would work: SELECT * FROM current LEFT JOIN temporary ON 1=1 WHERE current.login = 'keric' OR temporary.login = 'keric' That won't work if "Current" is empty. There's got to be a better way of doing this though... anyone else want to take a crack at this problem? On Tue, 14 Aug 2001, Eric Anderson wrote: > I've got two tables, "Current" and "Temporary", Current has a row with > login='keric', Temporary doesn't. > > The following query: > > mysql> select * from Current, Temporary where Current.login='username' > or Temporary.login='username' > -> \g > Empty set (0.01 sec) > > obviously doesn't work. I want to know if that row exists in either > table in one SQL call. Maybe it's just me today.. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Best Practice for mysqlhotcopy?
I have a user on my system with a normal shell account, and a normal MySQL account that owns a database. How can this unpriviledged user back up his database using mysqlhotcopy? I've run into two problems: 1. He has to login as the MySQL root user in order to be able to execute "FLUSH TABLES". 2. The MySQL data files for that database are not readable by him. I've tried to chgrp those files to his group and chmod g+r them, which allows him to read those files. But, they spontaneously revert back to the mysql group for some reason! (Why?) And new tables created in his database have the group of mysql. Can I get around the problems from #1 and #2 somehow? I realize that I could just use "mysqldump" to back up the tables, but this takes a lot longer since my database has over 70 MB of data in it. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BIT data type?
I have an application that requires the storage of various flags (by
"flag", I mean a variable that is either 1 or 0).
What's a good way of doing this? I've thought about two ways:
Method 1: Make a TINYINT column for each flag
I can use TINYINT NOT NULL datatype for storing each flag. The
disadvantage is that I use 8 bits when 1 bit would have sufficed.
Method 2: Make a single SET column for all the flags
I can use a SET for storing all the flags. But the disadvantage is that
the programming syntax gets a bit more complicated. e.g. instead of being
able to do:
SELECT * FROM fanfics WHERE unfinished = 0
I would have to do:
SELECT * FROM fanfics WHERE FIND_IN_SET('unfinished', flags) = 0
Does anyone have other suggestions?
Another concern that I have is the time required to add a new flag. If I
have a large table, adding a new column to the table can be quite time
consuming. (I'm guessing if I use the SET method, adding a new flag would
be instantaneous unless the number of flags was divisible by 8, requiring
the SET to expand by one byte.)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How do I increment a field?
I have a table called "stats" with: user varchar(40) not null primary key, count mediumint not null. I want to do something like this: UPDATE stats SET count=count+1 WHERE user='$user'; But if there is no row in the "stats" table having user='$user', that statement will not create the row. The following will not work since it is not possible to access the old values of a row being replaced: REPLACE INTO stats SET count=count+1, user='$user'; The following will raise an error if the user already exists, causing the program to stop: INSERT INTO stats SET user='$user', count=0; UPDATE stats SET count=count+1 WHERE user='$user'; So far, the only solution I've found is: SELECT * FROM stats WHERE user='$user'; if $sth->rows then INSERT INTO stats SET user='$user', count=1; else UPDATE stats SET count=count+1 WHERE user='$user'; I was wondering, is there a more concise way I can write this query to achieve the same effect? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to calculate age?
>Description: There is an example in this tutorial which I think is done incorrectly. http://www.mysql.com/documentation/mysql/bychapter/manual_Tutorial.html It says: "SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age" That code does not take into account leap years. I think such an example should be done the correct way because people might copy it and use it. In the program I'm writing, that one extra day of a leap year is important. One day makes a difference, legally, when a person is one day shy of being 18 years old. >How-To-Repeat: >Fix: >Submitter-Id: >Originator: Philip Mak >Organization: -Philip Mak ([EMAIL PROTECTED]) > >MySQL support: none >Synopsis: manual errata >Severity: non-critical >Priority: low >Category: mysql >Class: doc-bug >Release: mysql-3.23.36 (Official MySQL RPM) >Environment: System: Linux sg1.indexthis.net 2.2.17-14 #1 Mon Feb 5 17:53:36 EST 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='egcs' CFLAGS=' -O6 -fno-omit-frame-pointer' CXX='egcs' CXXFLAGS=' -O6-felide-constructors -fno-exceptions -fno-rtti -fno-omit-frame-pointer' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Apr 4 16:33 /lib/libc.so.6 -> libc-2.1.3.so -rwxr-xr-x 1 root root 4101836 Jan 15 10:49 /lib/libc-2.1.3.so -rw-r--r-- 1 root root 20273324 Jan 15 10:49 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Jan 15 10:49 /usr/lib/libc.so lrwxrwxrwx 1 root root 10 Apr 4 16:34 /usr/lib/libc-client.a -> c-client.a Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --enable-assembler --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --without-berkeley-db --without-innobase '--with-comment=Official MySQL RPM' Perl: This is perl, version 5.005_03 built for i386-linux - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql GUI for windows NT/98 is it available?
Some months ago I had the same question and I looked at many different Windows MySQL GUI clients. The best one I know of is Mascon. They have a free version for download. http://www.scibit.com/Products/Software/Utils/Mascon.asp -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: my.cnf mystery
On Tue, 12 Jun 2001, William M. Quarles wrote: > Which program? Here's what the /etc/my.cnf file on my system looks like: [client] port=3306 socket=/home/mysql/mysql.sock [mysqld] port=3306 socket=/home/mysql/mysql.sock The first section affects the "mysql" client. The second section affects the "mysqld" server. So, type "mysql --help" for a list of things that you can put in the client section, and "/usr/sbin/mysqld --help" (substitute the correct path name for mysqld) for a list of things that you can put in the server section. That said, I do think the MySQL manual should include a section that explains my.cnf better, if it doesn't have one already. -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Dinamic Compression/Decompression
On Tue, 12 Jun 2001, Emiliano F Castejon (Castle John) wrote: > I would like to know if there is a way to use a compressed MYSQL bank > for read and write (dynamic compression/decompression). I'm not sure if it is possible to do this natively in MySQL; I'll let someone else answer that. You could perform compression at the filesystem level independent of MySQL; make a partition on your disk that is stored compressed (check your operating system manuals to determine how to do this; I don't know), and then MySQL would be able to read and write, and it would be compressed. However, performance will suffer. -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql.sock not created
On Wed, 13 Jun 2001, Brian Walker wrote: > I've established that the MySQL server is running, but I cannot create > or do anything with it. Here is some examples. > > ERROR 2002: Can't connect to local MySQL server through socket > '/var/lib/mysql/mysql.sock' (111) It probably created mysql.sock somewhere else on your machine. Look in /tmp; if that doesn't work, try find / -name "mysql.sock" to search your whole disk for it. Once you've found it, type: cd /var/lib/mysql ln -s /tmp/mysql.sock mysql.sock substituting the correct path name for /tmp/mysql.sock. This will create a symbolic link between /var/lib/mysql/mysql.sock and /tmp/mysql.sock so that both of them are treated as the same file. Then everything should work. -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie Backup Question
I'm trying to write a cron job to back up my databases. The manual says: > Do a full backup of your databases: > > shell> mysqldump --tab=/path/to/some/dir --opt --full > > You can also simply copy all table files (`*.frm', `*.MYD' and`*.MYI' > files), as long as the server isn't updating anything.The script > mysqlhotcopy does use this method. My question is, is it safe to use "mysqldump" to back up the database when updates are occuring? If not, how do I use mysqlhotcopy to back up every database, without having to keep track of what the names of my databases are? The perldoc for mysqlhotcopy shows that it needs a database name. -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Using mifluz for full text indexing
Has anyone here used mifluz for full text indexing of TEXT columns in MySQL? I have a table with a "num" (MEDIUMINT, unique row identifier) and "message" (MEDIUMTEXT, the column to be indexed) column. I'd like to be able to do full text searches on the "message" column and get back a list of "num". I think that mifluz is the best library to use for doing this because of its scalability and robustness. I can't use MySQL's built-in full text indexing because I need phrase searching. I have a problem, though. After reading through the mifluz documentation, I can't figure out how to create the inverted index and populate it with words. Does anyone know how to do this (I'm guessing a program that reads the data from the database and calls the mifluz routines is required)? Or, is there another, easier to use, good full text searching package that I can use for searching MySQL text columns? Thanks, -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to change database directory?
Right now, my MySQL databases are inside /var/lib/mysql. I need to move them into /home, because there is not enough room in the /var partition for the databases to grow much more. This is a production server. How can I safely move the MySQL databases into /home, with minimal downtime and no data corruption? I'm guessing that I should: 1. Change mysqld configuration file to use /home/mysql instead 2. /etc/rc.d/init.d/mysql stop # stop mysqld 3. Keep checking ps -A | grep mysql until mysqld is stopped 4. mv /var/lib/mysql /home/mysql 5. /etc/rc.d/init.d/mysql start # start mysqld Is this correct? And how would I do step #1? Thanks, -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Indizes abschalten.
Es gibt eine deutsche MySQL-Sendenliste: There is a German MySQL mailing list: [EMAIL PROTECTED] "subscribe mysql-de [EMAIL PROTECTED]" -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ODBC$B@\B3$GFCDj$NJ8;z$,(B$BF~NO$G$-$J$$(B
$B$3$l$O1Q8l$N(BML$B$G$9!#F|K\8l$N(BML$B$r;H$C$F2<$5$$!#!J$9$_$^$;$s$,!"F|K\8l$N(BMySQL$B$N(BML $B$O$I$3$K$"$k$N$,J,$j$^$;$s!#!K(B (This is an English mailing list. Please use a Japanese mailing list. Sorry, but I don't know where a Japanese MySQL mailing list is.) -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Efficiency of MEDIUMTEXT columns?
On Wed, 18 Apr 2001, ryc wrote: > The subject field is a bit more tricky. You COULD change it to > Char(255) but that would waste a lot of space seeing that most > subjects are a lot shorter. You can move the subject into the table > that holds the varchar bodies. This wont hurt anything because that > table already has varaible length rows from the body column, adding > the subject wont hurt. Well, I was wondering if there is a point in putting the SUBJECT field in a separate table, when every query that I would execute needs to retrieve the SUBJECT field anyway... > > Also, is it a good idea to make a field called size and use that instead > > of calling length(message) (message is MEDIUMTEXT) when I don't need to > > retrieve message, or does MySQL have length(message) cached? > > Yes definitly. Although it is tempting to calculate the length/count/other > stats on the fly using database functions, it is not scalable. Okay, I'll do that. The messages don't change once they're posted so it's easy to keep another field indicating the size. > Instead of splitting it into lines based on a newline, you can do a > global replace using \n as an anchor. Something like: search for "\n>" > and replace with "\n$colorcolor>". It's not quite that simple; the color changes depending on how many >'s there are. My current algorithm counts the (number of >'s + 1) % 6 at the beginning of the line to decide what color to use. If this were C, I could probably do this efficiently using character pointers. Come to think of it, when I split the message into a list, that causes two copies of the message to be held in memory: the unsplit version, and the split version. Maybe that's what's slowing it down. I can't think of doing it any other way though. > thinking scalability you might want to do this conversion once, and store > the results in the database. So you dont have to worry about parsing it into > lines once you retrieve it from the db, you can just spit it out because it > already has the color info in it. I might do that once I get my system better developed. Most pages never change once they've been created, and the ones that do change (e.g. when a message gets replied to, its page needs to link to the new reply) can be found and updated relatively simply. So I could just write out a lot of this stuff to static HTML pages for the ultimate performance boost. -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to structure a random query
On Wed, 18 Apr 2001, Alec Smith wrote: > I've got a table of X rows, each with a unique ID as determined by > auto_increment when the row is inserted into the database. How would I go > about doing a SELECT on a row of the database and have MySQL return a row > at random? Maybe you can do something with the MySQL RAND() function, which returns a random number between 0.0 and 1.0 (so you'd have to normalize it to between 1 and MAX(ID)). You may not be able to use the grouping function that way, though. MySQL doesn't seem to let you use grouping functions in the WHERE clause. mysql> select num from ffml where num=floor(rand()*max(num))+1; ERROR : Invalid use of group function Anyone else have an idea? -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: there is no mysql/var directory
On Wed, 18 Apr 2001, feldekis wrote: > Hi everybody!! > I've got a pb during the installation of the binary of mysql-3.22.32 (the same > for 3.23.36 version): > there is no mysql/var directory, so when i write: chown -R mysql > /usr/local/mysql/var > that doesn't work. And it's exactly the same for mysql/bin. > So PLEASE!!! if someone can help me > Thanks in advance Find the correct path for the mysql directory. I think it might be in /usr/bin/*mysql* and /var/lib/mysql. Try typing locate mysql, or find /usr -name "*mysql*" Then chown those files. -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Phrase based fulltext searching
I read through the MySQL documentation on full text indexing, and there does not seem to be a way to search for a *phrase*, e.g. searching for a document that contains "Sailor Moon", as opposed to one that contains the word "Sailor" and the word "Moon", not necessarily together. (Unless I use LIKE "%Sailor Moon%", but that's inefficient...) Is the above statement correct? What techniques do people here use to overcome that limitation? (Using a program separate from MySQL for indexing text, perhaps?) -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: "Too Many Connections" error
On Wed, 18 Apr 2001, Jesse E. Stay II wrote: > I'm having a problem, which has occured before, in which I keep getting "Too > Many Connections" Errors in my logs on the web server for MySQL. I am using > Apache::DBI to connect. I fixed the problem before by just increasing the > max_connections. Unfortunately, I am at the max amount of max_connections > (the MySQL docs say that in order to increase it, you have to compile it in > with the code, which I would rather not do.), and I cannot add any more. If > I switch to just regular DBI, will that solve my problem, or what else could > be causing this problem to occur? I've got my boss breathing down my neck, > and I'm unsure what answer to give him. Here's some random thoughts: If you have more Apache processes running than you have maximum connections (I think the hard maximum is around 1000), then you will run out of connections. One thing you could try is setting "MaxClients" in httpd.conf to your max_connections. This will prevent Apache from spawning too many processes, but may cause people viewing your website to have to wait longer. If you use the normal DBI instead of Apache::DBI, then connections will be non-persistent and you'll have more to go around, at the cost of slightly slower website response time. There might be a problem somewhere that is causing your system to use up more MySQL connections than it should. 1000 is a lot of connections, and it shouldn't use that many unless your website is very heavily loaded. (I had a website that got 3 million page views a month and it fit in 40 simultaneous connections, but it was all static files so queries could be served quickly.) -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Efficiency of MEDIUMTEXT columns?
On Wed, 18 Apr 2001, ryc wrote:
> A few tips for you... Store all the varchar/text columns for each message in
> a seperate table so that the primary message table only has fixed length
> fields (ie messageid, size, #of replies, userid, bodyid, ...).
I have some VARCHAR columns (name, e-mail address and subject) that are
used in every query, though. Should I change them to CHAR(255) so that I
can do this? (On a related note, does anyone know whether the RFC for
e-mail messages limit the subject length/name length/e-mail address length
to a certain number of characters? What is this number?)
Also, is it a good idea to make a field called size and use that instead
of calling length(message) (message is MEDIUMTEXT) when I don't need to
retrieve message, or does MySQL have length(message) cached?
...
I just ran a crude benchmark on three different ways to retrieve text from
the database. The time required for 100 iterations of each method is:
DB2: 3.7 seconds
DB3: 11.5 seconds
Text: 5.6 seconds
DB2 is where I retrieve the message from the database:
my $sth = query("SELECT * FROM ffml WHERE num=6051");
my $this = $sth->fetchrow_hashref;
print $this->{message};
DB3 is where I retrieve the message from the database and split it:
my $sth = query("SELECT * FROM ffml WHERE num=6051");
my $this = $sth->fetchrow_hashref;
my @message = split("\n", $this->{message});
for my $line (@message) { print $line."\n"; }
Text is where I retrieve the message from a disk file:
open(MSG, '6051');
while ($line = ) { print $line."\n"; }
So it would appear that retrieving a message (the message that I used in
this test is 300k, about the largest that is in the archive) from the
database is faster than loading it from a disk file. However, when I have
to split() the message into a list (one entry per line), it becomes
slower. Any idea how I can split it more efficiently? (My code has to
process each line individually in order to hilight text from quoted
replies; e.g. look at the different colors in
http://qd.anifics.com/read.asp?num=32)
> So if you are dumping/posting a lot of messages into the table the
> select performance is going to suffer.
Inserts shouldn't be too common; just once every time the mailing list
receives a new message (no more than 100 per day).
Thanks,
-Philip Mak ([EMAIL PROTECTED])
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Efficiency of MEDIUMTEXT columns?
Is it efficient to store things in MEDIUMTEXT columns? I am developing a web based system (using perl and MySQL) for viewing an archive of mailing list messages. In my database, I have one row per message. The column called "message" is of type MEDIUMTEXT. My perl code retrieves it using $sth->fetchrow_hashref and then prints it. Message size is typically ~10K; sometimes it's as high as a few hundred K. Here's an example URL from my site: http://qd.anifics.com/browse.asp?y=1995&m=12&d=30 I'm wondering if it's a good idea to use MEDIUMTEXT like this, or should I put the message body into individual separate files? It runs fine right now, but I'm worried that it won't scale up under significant load. -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Efficiency of MEDIUMTEXT columns?
Is it efficient to store things in MEDIUMTEXT columns? I am developing a web based system (using perl and MySQL) for viewing an archive of mailing list messages. In my database, I have one row per message. The column called "message" is of type MEDIUMTEXT. My perl code retrieves it using $sth->fetchrow_hashref and then prints it. Message size is typically ~10K; sometimes it's as high as a few hundred K. Here's an example URL from my site: http://qd.anifics.com/browse.asp?y=1995&m=12&d=30 I'm wondering if it's a good idea to use MEDIUMTEXT like this, or should I put the message body into individual separate files? It runs fine right now, but I'm worried that it won't scale up under significant load. -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Out of disk space
I'm suddenly getting "Got error 28 from table handler" from a MySQL script that used to work fine. On searching the MySQL list archives, I determined that means I'm out of disk space. Something's weird here though: root@trapezoid [~]# df Filesystem 1k-blocks Used Available Use% Mounted on /dev/hda8 256667245754 0 100% / /dev/hda123302 2445 19654 11% /boot /dev/hda6 6672828 3809672 2524188 60% /home /dev/hda5 6672828954312 5379548 15% /usr /dev/hda7 256667 54830188585 23% /var If there are 256667 total, and 245754 used, shouldn't there be 10913 available? I tried doing a /sbin/reboot but it still says available 0. Also, is editing /etc/rc.d/init.d/mysql and adding "--tmpdir=/var/tmp" to the mysql command the correct way of setting the temporary directory that MySQL uses? (It seems to be working, but I just want to make sure) -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Your favorite Windows MySQL GUI client?
Hello, What is your favorite Windows MySQL GUI client? I'm developing a database driven website and I'm looking for a program that I can use to maintain the database. One of my special requirements is that I need a client that can edit text blobs. So far the clients I've seen display the text blob as one very long line that is difficult to edit. Criteria: - fairly stable - free - can directly edit the data by clicking on rows - can edit text blobs in a multiline text area Thanks, -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Tunneling problem
Hello, I have a MySQL account that only accepts connections from localhost for security reasons. I wish to use a local Windows GUI client with the remote MySQL database. I setup SecureCRT (my Windows SSH client) to forward local port to remote port 3306. I try telnetting to localhost and see: - 3.23.24-betaBI*2\Y*b, which indicates that the tunnel appears to be setup correctly. Now, I go to MySQL GUI, set Server host name to "localhost" and port number to "". I set the username on the Client tag. Then I connect but I get the following error message: Can't connect to MySQL server on 'localhost' (10061) Does anyone know what I did wrong? Thanks, -Philip Mak ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
