RE: 100,000,000 row limit?
I'm fairly sure that the ONLY limitation is the size of the table files, adn those depend on the OS. http://www.mysql.com/doc/en/Table_size.html Chris -Original Message- From: Andres Montiel [mailto:[EMAIL PROTECTED] Sent: Monday, December 22, 2003 9:57 PM To: [EMAIL PROTECTED] Subject: 100,000,000 row limit? I was informed that MySQL has a 100,000,000 row limit. Is this true? We were planning to use MySQL for an inventory system. However, our current data (rows) for 1 year for one area is already 8.8 million. We want to place data for 5 years for 7 areas. This would exceed 100,000,000. Is there a possible work around for this? Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Error 1065
Hello I need help MySQL 3.23.47 running on localhost linux I want to create an Test Instance. Based on my Production Instance. My Batch (it has work for month) head prax2test.sh time mysqldump -u transfer -ptransfer timesheet timesheet.temp echo drop database timetest; timetest.sql echo create database timetest; timetest.sql echo use timetest; timetest.sql cat timesheet.temp timetest.sql rm timesheet.temp time cat timetest.sql | mysql -u transfer -ptransfer timetest Starting the batch: prax2test.sh exporting real0m7.323s user0m3.050s sys 0m0.970s Importing ERROR 1065 at line 10: Query was empty real0m0.019s user0m0.010s sys 0m0.010s What's wrong ? What mean's Error 1065 ? Script Contents: head -40 timetest.sql drop database timetest; create database timetest; use timetest; -- MySQL dump 10.2 -- -- Host: localhostDatabase: timesheet -- -- -- Server version 3.23.47 /*!40101 SET NAMES latin1*/; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */; -- -- Table structure for table `Aufgabe` -- DROP TABLE IF EXISTS Aufgabe; CREATE TABLE Aufgabe ( ID int(5) NOT NULL auto_increment, SequenzNr int(4) NOT NULL default '0', ID_AufgabenArt int(4) default NULL, ID_Person int(5) default NULL, Tage decimal(4,1) NOT NULL default '0.0', TageSoll decimal(3,1) unsigned NOT NULL default '0.0', Kommentar varchar(50) default NULL, gueltigFuer date default NULL, AUTO int(11) NOT NULL default '0', Timestamp timestamp(14) NOT NULL, ID_Firma int(11) NOT NULL default '0', KW int(2) default NULL, PRIMARY KEY (ID), KEY ID_Person (ID_Person) ) TYPE=MyISAM; -- -- Dumping data for table `Aufgabe` -- /*!4 ALTER TABLE Aufgabe DISABLE KEYS */; LOCK TABLES Aufgabe WRITE; INSERT INTO Aufgabe VALUES (3906, . e.t.c. for all my tables ... DROP TABLE IF EXISTS ZugriffsRechte; CREATE TABLE ZugriffsRechte ( ID int(11) NOT NULL auto_increment, Bezeichnung tinytext NOT NULL, Rechte bigint(20) NOT NULL default '0', PRIMARY KEY (ID) ) TYPE=MyISAM; -- -- Dumping data for table `ZugriffsRechte` -- /*!4 ALTER TABLE ZugriffsRechte DISABLE KEYS */; LOCK TABLES ZugriffsRechte WRITE; INSERT INTO ZugriffsRechte VALUES (1,'Kategorie_bearbeiten',1048576),(2,'Projekt_berabeiten',1048576),(3,'Teilprojekt_bearbeiten',28372994),(4,'Teamdaten_bearbeiten',1048576),(5,'Mitarbeiterdaten_bearbeiten',26214400),(6,'Zugriffsrechte_bearbeiten',1048576),(7,'Tätigkeit_anlegen',96464898),(8,'Tätigkeiten_anlegen_Team',28893186),(9,'Tätigkeit_anlegen_alle',1048576),(10,'Monatsübersicht_anzeigen',96071682),(11,'erw_Monatsübersicht_anzeigen',93941760),(12,'Monatsübersicht_I_S_anzeigen',26218496),(13,'Projektübersicht_anzeigen',32632834),(14,'Teamstruktur_anzeigen',28962818),(17,'Telefonbuch_anzeigen',28401664),(20,'Monat_abschliessen_oeffnen',1048578),(21,'Gruppe_bearbeiten',1048576),(22,'Gruppenmonatsbericht_anzeigen',28438530),(23,'Taetigkeit_bearbeiten',28336128),(24,'Emailbenachrichtigung',1048576),(25,'Projektübersicht_Teammitglieder_anzeigen',28438530),(26,'Gruppenmonatsbericht_erweitert_anzeigen',3223552),(27,'Monatsübersicht_Projekte_anzeigen',26341378); UNLOCK TABLES; /*!4 ALTER TABLE ZugriffsRechte ENABLE KEYS */; /*!40101 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; Thanks Jens Gronau - Otto (GmbH Co KG) Wandsbeker Straße 3-7 22172 Hamburg Jens Gronau Abt. IT-GA-DR Fon: +49 40 64617473 Fax: +49 40 64647473 mailto: Jens:[EMAIL PROTECTED] - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with EQ_REF and ALL
Hi, Sorry for not responding more promptly. To remove a index you can use a command like this one ALTER TABLE tbl_klanten DROP INDEX klant_id; or using phpMyAdmin - just click on the delete link next to the index. Anyway as I told you before I don't think this is causing the problem - the additional index will never be used and it will just take unneccessary space in your index file. You should check the SQL query syntax - for example adding brackets might help. HTH Dobromir Velev - Original Message - From: Koen Van Mulders To: Dobromir Velev Sent: Friday, December 19, 2003 13:52 Subject: Re: Problem with EQ_REF and ALL Hello, first of all, tnx for the great help so far ! I run MySQL 3.24. You say I have some duplicate indexes I didn't add KEY(klant_id) for instance, phpmyadmin did that :-s How do i drop the KEY but keep the PRIMARY KEY. I can't find any info on KEY, only on PRIMARY KEY... What did you change on the create table syntax ? And how do I make your changes but on the already existing table ? Tnx in advance ! - Original Message - From: Dobromir Velev To: [EMAIL PROTECTED] Sent: Friday, December 19, 2003 11:42 AM Subject: Re: Problem with EQ_REF and ALL Hi, I can't seem to find any problems, except for the few duplicate indexes in tbl_klanten, tbl_v_levering, tbl_v_bestelbon - when you have PRIMARY KEY (klant_id) you don need to add KEY klant_id (klant_id). I created the tables on a 4.0.12 server and the EXPLAIN returned EQ_REF on both queries. Here is the EXPLAIN otput mysql EXPLAIN SELECT * FROM tbl_v_batch b LEFT JOIN tbl_v_levering l ON l.ver_lev_id = b.batch_lever_id LEFT JOIN tbl_klanten k ON k.klant_id = l.ver_lev_klant_id; +---++---+-+-++--+---+ | table | type | possible_keys | key | key_len | ref| rows | Extra | +---++---+-+-++--+---+ | b | ALL| NULL | NULL|NULL | NULL |2 | | | l | eq_ref | PRIMARY | PRIMARY | 4 | b.batch_lever_id |1 | | | k | eq_ref | PRIMARY | PRIMARY | 4 | l.ver_lev_klant_id |1 | | +---++---+-+-++--+---+ 3 rows in set (0.00 sec) mysql EXPLAIN SELECT * FROM tbl_v_batch b LEFT JOIN tbl_v_bestelbon bb ON bb.ver_id = b.batch_bestel_id LEFT JOIN tbl_klanten k1 ON k1.klant_id = bb.ver_klant_id; +---++---+-+-+---+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+--+---+ | b | ALL| NULL | NULL|NULL | NULL |2 | | | bb| eq_ref | PRIMARY | PRIMARY | 4 | b.batch_bestel_id |1 | | | k1| eq_ref | PRIMARY | PRIMARY | 4 | bb.ver_klant_id |1 | | +---++---+-+-+---+--+---+ 3 rows in set (0.00 sec) So the last thing I could think of is putting some brackets to define the order in which the joins will be made like SELECT * FROM (tbl_v_batch b LEFT JOIN tbl_v_levering l ON l.ver_lev_id = b.batch_lever_id) LEFT JOIN tbl_klanten k ON k.klant_id = l.ver_lev_klant_id; HTH Dobromir Velev [EMAIL PROTECTED] - Original Message - From: Koen Van Mulders To: Dobromir Velev Sent: Thursday, December 18, 2003 17:52 Subject: Re: Problem with EQ_REF and ALL Please excuse me if this comes to your personal mailbox, i have no clue on how to use these lists :-) I am proud I could post my problem, I don't know how to reply to it :-(. Anyway, here are the table structures : --- # # Tabel structuur voor tabel `tbl_klanten` # CREATE TABLE tbl_klanten ( klant_id int(11) NOT NULL auto_increment, klant_nummer int(11) NOT NULL default '0', klant_bedrijfsnaam text NOT NULL, klant_voornaam tinytext NOT NULL, klant_achternaam tinytext NOT NULL, klant_straat_nr text NOT NULL, klant_postnr text NOT NULL, klant_stad text NOT NULL, klant_tel text NOT NULL, klant_fax text NOT NULL, klant_gsm text NOT NULL, klant_email text NOT NULL, klant_btw text NOT NULL, klant_hr text NOT NULL, klant_specialisatie text NOT NULL, klant_q_assistenten smallint(6) NOT NULL default '0', klant_q_tandartsen
Re: MYSQL 3.22.30 data missing
Teresa A Narvaez [EMAIL PROTECTED] wrote: We run MYSQL 3.22.30 on a TRU64 alpha server(4.0F). Our scripts found corruption on one of our tables. The script tried to automatically clear the corruption but failed. Then we cleared the corruption by manually running isamchk -r.Everything seemed okay until we tried to pull data for certain hours. The select count(*) stattement displays 830 records. However when I execute select * it only displays 150 records. 3.22.30 is a _very_ old version of MySQL. Please, upgrade MySQL server to the recent version. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What am I doing wrong????a
Kirti S. Bajwa [EMAIL PROTECTED] wrote: Hello List: Please point to what am I doing wrong: I am trying to install MySQL on top of RH9. I am following steps outlined MySQL official documentation from Section 2.3.1 titled Quick Source Installation Overview. These are the steps I have performed: # cd /usr/local Download: mysql-standard-4.0.17-pc-linux-i686.tar.gz Note: on RH9 group (mysql) user (mysql) are already there.. # gunzip mysql-standard-4.0.17-pc-linux-i686.tar.gz # tar -xvf mysql-standard-4.0.17-pc-linux-i686.tar # cd mysql-standard-4.0.17-pc-linux-i686 NOW THE PROBLEM = According to the instructions, my next step is: # ./configure --prefix=/usr/local/mysql But I get a message that ./configure is already done This is where I am struck-up for the whole day. Can someone help to direct me to the right direction. mysql-standard-4.0.17-pc-linux-i686.tar.gz is a binary distribution. You should download source or install MySQL from binary distribution. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
non-ASCII characters in LIKE and regexp comparisons?
Hi mysql-list, 1. my installation: [EMAIL PROTECTED]:~ uname -a Linux rosetta 2.4.19-4GB #1 Fri Sep 13 13:14:56 UTC 2002 i686 unknown [EMAIL PROTECTED]:~ mysql show global variables like version; +---++ | Variable_name | Value | +---++ | version | 4.0.12-max-log | +---++ 1 row in set (0.00 sec) mysql mysql show global variables like character_set; +---++ | Variable_name | Value | +---++ | character_set | latin1 | +---++ 1 row in set (0.00 sec) mysql show global variables like character_sets; ++---+ | Variable_name | Value | ++---+ | character_sets | latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 | ++---+ 1 row in set (0.00 sec) mysql 2. my problem: when i am looking for entries with a name containing an u-umlaut by means of the LIKE construct, i get all chunk result-rows: mysql select name from contacts where name like %ü% order by name; +---+ | name | +---+ | A Côte beauty treats | | AA Anonyme Alkoholiker| | AA Anonyme Alkoholiker| | AAA All Activities Agency AG | | Adèle Uldry | | Adolf Deyhle | | Alain Barbey | | Alain et Dorothée Ryser | | Alain et Thérèse Sauty| | Alex et Silvia Fritschy | | Alexandre Stransky| ... ... | Yves Moullet | | Yves Zehfus | | Yves-André et Jennifer Cornioley | | Yves-François Weill | | Yves-Pierre Poscia| | Yvonne Badel | | Yvonne Frédérique Maeder | | Yvonne Malka | | Yvonne Quartenoud | | Zekarias Araya| | Zero Gravity | | Zürich| +---+ 485 rows in set (0.02 sec) mysql if i, however, do the search with a regexp, it seams to work: mysql select name from contacts where name regexp ü order by name; +---+ | name | +---+ | Alfred Zürcher| | Alig Grossküchen | | Alois Kühner | | Ambulante Spitex Hausbetreuungsdienst für Stadt und Land | | Andreas Bühlmann | | Anton Bühlmann| | Arnd Küchel | | Aurélia et Yann Füllemann | | Bachblütenberatung| | Bahnhofleitung, Fundbüro, | | Batigroup AG Graubünden | | Beratungsbüro f. Personalmanagement | | Berghüsli | | Beyeler AG Drogerie-Parfümerie| | Brigitte Schüller
Re: Quering user privileges
Plinio Conti [EMAIL PROTECTED] wrote: The manual page at http://www.mysql.com/doc/en/SHOW_GRANTS.html says: To list grants for the current session one may use CURRENT_USER() function But if I run SELECT SHOW GRANTS FOR CURRENT_USER(); I get syntax error (Error 1064). While if I run directly SELECT SHOW GRANTS FOR [EMAIL PROTECTED]; It works. Did you guess why? Look at the example of usage SHOW GRANTS in the manual: mysql SHOW GRANTS FOR [EMAIL PROTECTED]; There is no SELECT word in the query. On Mon, 22 Dec 2003 15:37:43 +0200 Egor Egorov [EMAIL PROTECTED] wrote: Plinio Conti [EMAIL PROTECTED] wrote: Is there a way to query the privileges of current user if he hasn't the rights to read the mysql system tables (user, db, hosts, etc..) I mean, I CAN'T give a standard user the chance of read system tables! But my client app wants to know if the current user has (for example) the rights to write on table xxx, because if not, I don't want to let him try and fail, I want to disable the updates in advance. Take a look at SHOW GRANTS command: http://www.mysql.com/doc/en/SHOW_GRANTS.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More OS X/mySQL problems
The MacOS X package installer runs mysql_install_db itself, I've chown-ed and chgrp-ed the relavent files (even though the installer did that as well) and everything because I thought it was a permissions problem. However, I did wipe it and install mySQL 4.0.17, which works fine, so it may be a problem with 4.1.1a. -Jon On Monday, Dec 22, 2003, at 16:11 Europe/London, Gabriel Ricard wrote: Have you run mysql_install_db? Does the mysql user have full permissions (read, write, execute) on the mysql data directory you created with mysql_install_db? - Gabriel On Dec 20, 2003, at 9:00 AM, Jon Pearse wrote: --- http://www.jonpearse.net[EMAIL PROTECTED] Some people come into our lives and quickly go. Some stay for a while and leave footprints on our hearts. And we are never, ever the same. --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Prepared statements in MySQL 4.0
Hi, I'm looking for prepared statement support in MySQL 4.0. I know it exists in 4.1 but 4.1 seems to be stuck in Alpha for forever. I looked at the documentation that came with the latest release and it seems to be for 4.1. Furthermore, I couldn't find a reference to MYSQL_BIND in the header files. Does prepared statement support exist in 4.0? - Yossie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What am I doing wrong????a
Thanks. I think your help is what I needed is highly appreciated. Kirti -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 4:22 AM To: [EMAIL PROTECTED] Subject: Re: What am I doing wronga Kirti S. Bajwa [EMAIL PROTECTED] wrote: Hello List: Please point to what am I doing wrong: I am trying to install MySQL on top of RH9. I am following steps outlined MySQL official documentation from Section 2.3.1 titled Quick Source Installation Overview. These are the steps I have performed: # cd /usr/local Download: mysql-standard-4.0.17-pc-linux-i686.tar.gz Note: on RH9 group (mysql) user (mysql) are already there.. # gunzip mysql-standard-4.0.17-pc-linux-i686.tar.gz # tar -xvf mysql-standard-4.0.17-pc-linux-i686.tar # cd mysql-standard-4.0.17-pc-linux-i686 NOW THE PROBLEM = According to the instructions, my next step is: # ./configure --prefix=/usr/local/mysql But I get a message that ./configure is already done This is where I am struck-up for the whole day. Can someone help to direct me to the right direction. mysql-standard-4.0.17-pc-linux-i686.tar.gz is a binary distribution. You should download source or install MySQL from binary distribution. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Prepared statements in MySQL 4.0
On Tue, Dec 23, 2003 at 02:20:50PM +0200, Yosef Teitz wrote: Hi, I'm looking for prepared statement support in MySQL 4.0. I know it exists in 4.1 but 4.1 seems to be stuck in Alpha for forever. I looked at the documentation that came with the latest release and it seems to be for 4.1. Furthermore, I couldn't find a reference to MYSQL_BIND in the header files. Does prepared statement support exist in 4.0? No, they're a 4.1 feature. Please give 4.1 a try. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 100,000,000 row limit?
On Tue, Dec 23, 2003 at 12:57:23AM -0500, Andres Montiel wrote: I was informed that MySQL has a 100,000,000 row limit. Is this true? No. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does it matter: safe_mysqld or mysqld_safe?
I am on 4.0.17 does it really matter how I start the server? whether I use /bin/safe_mysqld or /bin/mysqld_safe? Thanks. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does it matter: safe_mysqld or mysqld_safe?
I am on 4.0.17 does it really matter how I start the server? whether I use /bin/safe_mysqld or /bin/mysqld_safe? safe_mysqld is a link to mysqld_safe, so no, it doesn't matter. mack / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Prepared statements in MySQL 4.0
Jeremy writes: Please give 4.1 a try. Ah, yes - 4.1. I actually did quite a bit of testing on 4.1 alpha in July/August which is one of the reasons why we like MySQL, but I'm not sure that my management wants an alpha version in their production system. Which leads me to the next question for the MySQL list: What is the schedule for the 4.1 release? When is it planned to go to beta? To FCS? I've seen on the list something to the effect of it will be ready when it's ready. But I can't base a business decision on that. Until 4.1 is out we'll have to make do with the 4.0 series of releases. - Yossie -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] On Behalf Of Jeremy Zawodny Sent: Tuesday, December 23, 2003 3:39 PM To: Yosef Teitz Cc: [EMAIL PROTECTED] Subject:Re: Prepared statements in MySQL 4.0 On Tue, Dec 23, 2003 at 02:20:50PM +0200, Yosef Teitz wrote: Hi, I'm looking for prepared statement support in MySQL 4.0. I know it exists in 4.1 but 4.1 seems to be stuck in Alpha for forever. I looked at the documentation that came with the latest release and it seems to be for 4.1. Furthermore, I couldn't find a reference to MYSQL_BIND in the header files. Does prepared statement support exist in 4.0? No, they're a 4.1 feature. Please give 4.1 a try. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query syntax.
Will and Michael, Thanks very much for the the replies. This works well. Is it possible to use this syntax and search for cases where they have two different kinds of equipment? Say an Ultra 5 and a HP Plotter? TIA, DB SELECT User_Account FROM Users AS a, Device_Name from Devices AS b WHERE a.User_Account = b.Device_Account AND b.Device_Name LIKE 'HP%' - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Will Lowe [EMAIL PROTECTED] Cc: Data Boy [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 22, 2003 1:25 PM Subject: Re: Query syntax. Will Lowe wrote: Select User_Account from Users as a, Devices as b WHERE a.User_Account = (Select DISTINCT(b.Device_Account) from b.Devices WHERE b.Device_Name LIKE 'HP%' ) I'm running 3.23.49 which I know is not the most current..it was installed 3.x does not support subselects (select x from (select y from ...)). You'll need to upgrade to 4.1. But 4.1 is alpha, so he may not want to do that (though it would be a good idea to upgrade to 3.23.58 or 4.0.17). In that case, the solution is to replace the subselect with a join, which may even be more efficient. Try: SELECT User_Account FROM Users AS a, Device_Name from Devices AS b WHERE a.User_Account = b.Device_Account AND b.Device_Name LIKE 'HP%' See http://www.mysql.com/doc/en/Rewriting_subqueries.html for more. Michael Hi, I'm having problems with the syntax of a select statement. I have two tables linked together by account number. The first table (Users) looks similar to this |User_Account | User_Name | User_Address |X10010100110 | Michael Smith | 1000 North Main St |X10010100240 | David Wilson | 1200 State Street The second table (Devices) looks like this |Device_Account | Device_Name| Installaton_Date |X10010100240 | Ultra 5| 19981010 |X10010100240 | HP1055CM | 20010528 |X10010100240 | LEXMARK | 20010529 |X10010100110 | HP1055CM | 20010528 |X10010100211 | HP LJET 4M | 20010528 There is a one to many relationship between users and devices and not all users have devices. I'd like to select a list of User accounts with certain devices. Select User_Account from Users as a, Devices as b WHERE a.User_Account = (Select DISTINCT(b.Device_Account) from b.Devices WHERE b.Device_Name LIKE 'HP%' ) I get an error code Error Code:1064 You have an error in your SQL Syntax near 'Select DISTINCT'. I'm running 3.23.49 which I know is not the most current..it was installed with another package. Thanks for any advice. I know my SQL skills are limited! TIA, DB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quering user privileges
Yes, I can do it with a middle tier, but currently my app is a simple 2-tier and introducing a middle-tier just to do that looks excessive... On Sun, 21 Dec 2003 18:40:40 -0330 jamie murray [EMAIL PROTECTED] wrote: What about creating a flat file on the middle tier from the database system tables that the clients can view with your gui or web page. Only things is keeping the file in sync with the system tables. But access will be faster to the flat file and no security risks because there won't even be a connection to the database from the client when viewing the file. - Original Message - From: Plinio Conti [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, December 21, 2003 5:55 PM Subject: Quering user privileges Is there a way to query the privileges of current user if he hasn't the rights to read the mysql system tables (user, db, hosts, etc..) I mean, I CAN'T give a standard user the chance of read system tables! But my client app wants to know if the current user has (for example) the rights to write on table xxx, because if not, I don't want to let him try and fail, I want to disable the updates in advance. Tahnk you, Plinio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quering user privileges
Sorry, I was not using any SELCT in my queries, it's just an error typing the email I confirm: SHOW GRANTS FOR CURRENT_USER(); does not work, while that query is exaclty what you expect to do reading mysql manual. On Tue, 23 Dec 2003 12:47:11 +0200 Egor Egorov [EMAIL PROTECTED] wrote: Plinio Conti [EMAIL PROTECTED] wrote: The manual page at http://www.mysql.com/doc/en/SHOW_GRANTS.html says: To list grants for the current session one may use CURRENT_USER() function But if I run SELECT SHOW GRANTS FOR CURRENT_USER(); I get syntax error (Error 1064). While if I run directly SELECT SHOW GRANTS FOR [EMAIL PROTECTED]; It works. Did you guess why? Look at the example of usage SHOW GRANTS in the manual: mysql SHOW GRANTS FOR [EMAIL PROTECTED]; There is no SELECT word in the query. On Mon, 22 Dec 2003 15:37:43 +0200 Egor Egorov [EMAIL PROTECTED] wrote: Plinio Conti [EMAIL PROTECTED] wrote: Is there a way to query the privileges of current user if he hasn't the rights to read the mysql system tables (user, db, hosts, etc..) I mean, I CAN'T give a standard user the chance of read system tables! But my client app wants to know if the current user has (for example) the rights to write on table xxx, because if not, I don't want to let him try and fail, I want to disable the updates in advance. Take a look at SHOW GRANTS command: http://www.mysql.com/doc/en/SHOW_GRANTS.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quering user privileges
Plinio Conti [EMAIL PROTECTED] wrote: Sorry, I was not using any SELCT in my queries, it's just an error typing the email I confirm: SHOW GRANTS FOR CURRENT_USER(); does not work, while that query is exaclty what you expect to do reading mysql manual. It means that you can use CURRENT_USER() function to know username and hostname that the current session was authenticated as. And then you can use this info in the SHOW GRANTS statement. For example: mysql SELECT CURRENT_USER(); ++ | CURRENT_USER() | ++ | [EMAIL PROTECTED] | ++ 1 row in set (0.00 sec) mysql SHOW GRANTS FOR [EMAIL PROTECTED]; ++ | Grants for [EMAIL PROTECTED] | ++ | GRANT USAGE ON *.* TO 'egor'@'localhost' IDENTIFIED BY PASSWORD '332ab33958e81d12' | | GRANT ALL PRIVILEGES ON `egor`.* TO 'egor'@'localhost' | ++ 2 rows in set (0.00 sec) On Tue, 23 Dec 2003 12:47:11 +0200 Egor Egorov [EMAIL PROTECTED] wrote: Plinio Conti [EMAIL PROTECTED] wrote: The manual page at http://www.mysql.com/doc/en/SHOW_GRANTS.html says: To list grants for the current session one may use CURRENT_USER() function But if I run SELECT SHOW GRANTS FOR CURRENT_USER(); I get syntax error (Error 1064). While if I run directly SELECT SHOW GRANTS FOR [EMAIL PROTECTED]; It works. Did you guess why? Look at the example of usage SHOW GRANTS in the manual: mysql SHOW GRANTS FOR [EMAIL PROTECTED]; There is no SELECT word in the query. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quering user privileges
To be honest, the fact I can't get it with only one query but I have to do: 1) SELECT CURRENT_USER(); 2) SHOW GRANTS FOR valueExtractedByPreviousQuery; is a little thing compared with work remaing to get usefull information: I have to parse the strings returned by query 2, handle the wildcards, handle ALL PRIVILEGES, comparing db-level privileges with table-level ones and with column-level ones, etc... I probably will do all that, only I wonder there is not a more simple solution. I mean, it looks a so common issue that I imagine there is better solution but I can't figure which. User authenticates, and client app wants to know what that user is allowed to do, so the app can show to its user the correct user interface (for example editing disabled if user can't write, etc..) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
union?
I'm trying to figure out how to combine these two tables. I have.. t1 date accidentInfo cost t2 date upgradeInfo whereUpgraded Salesman cost I want to combine the two in a way to reference each piece of data such as t1.accidentInfo,t2upgradeInfo and so on, but I need the resultset to be ordered by the combined dates. If I do order by t1,t2 I get the t1 in order by date then the t2 so I don't see how to combine them. I understand that a union would put the dates under the same column but it looks like it also puts accidentInfo and upgradeInfo under the same column too. Can someone help me see the light here? Larry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query syntax.
Data Boy wrote:
Will and Michael,
Thanks very much for the the replies. This works well.
Is it possible to use this syntax and search for cases
where they have two different kinds of equipment? Say
an Ultra 5 and a HP Plotter?
TIA, DB
SELECT User_Account FROM Users AS a, Device_Name from Devices AS b
WHERE a.User_Account = b.Device_Account
AND b.Device_Name LIKE 'HP%'
You're welcome.
On second look, I realize we didn't even need a join for your orginal
question as you only wanted the account ID. Since Users.User_Account =
Devices.Device_Account, we could simply have done
SELECT Device_Account AS User_Account FROM Devices
WHERE Device_Name LIKE 'HP%'
Of course, usually you would want some corresponding information from
the Users table, such as the User_Name. Then you need the join I suggested.
I know of two ways to search for users with a specified list of equipment:
1) You can join the Devices table with itself on Device_Account, looking
for results where the left copy matches the first piece of equipment and
the right copy matches the other piece of equipment. You can also join
to the Users table to look up user info at the same time. Something
like this:
SELECT User_Account AS ID, User_Name AS Name
FROM Users AS u, Devices AS d1, Devices AS d2
WHERE u.User_Account = d1.Device_Account
AND d1.Device_Account = d2.Device_Account
AND d1.Device_Name = 'HP Plotter'
AND d2.Device_Name = 'Ultra 5'
You replace the last two lines according to your desired equipment
search. I like this conceptually -- it's simply an extension of the
join we already did -- but it doesn't generalize very well to longer
lists of equipment.
2) You can use some aggregation functions to get what you want.
Something like this:
SELECT Users.User_Account AS account, Users.User_Name AS Name
FROM Users JOIN Devices ON Users.User_Account = Devices.Device_Account
WHERE Devices.Device_Name IN ('HP Plotter', 'Ultra 5')
GROUP BY account
HAVING COUNT(*) = 2
This version generalizes nicely:
SELECT Users.User_Account AS account, Users.User_Name AS Name
FROM Users JOIN Devices ON Users.User_Account = Devices.Device_Account
WHERE Devices.Device_Name IN (List_of_Equipment)
GROUP BY account
HAVING COUNT(*) = Number_of_items_in_list
You replace List_of_Equipment and Number_of_items_in_list with
appropriate values, of course..
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
100,000 row limit evil rumor spread by Larry Ellison and B. Gates
_ Tired of slow downloads? Compare online deals from your local high-speed providers now. https://broadband.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
union?
I'm trying to figure out how to combine these two tables. I have.. t1 date accidentInfo cost t2 date upgradeInfo whereUpgraded Salesman cost I want to combine the two in a way to reference each piece of data such as t1.accidentInfo,t2upgradeInfo and so on, but I need the resultset to be ordered by the combined dates. If I do order by t1,t2 I get the t1 in order by date then the t2 so I don't see how to combine them. I understand that a union would put the dates under the same column but it looks like it also puts accidentInfo and upgradeInfo under the same column too. Can someone help me see the light here? Larry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Re: Query syntax.]
The mysql list sent this back to me, so I'm resending. My apologies to
anyone who ends up getting it twice.
Data Boy wrote:
Will and Michael,
Thanks very much for the the replies. This works well.
Is it possible to use this syntax and search for cases
where they have two different kinds of equipment? Say
an Ultra 5 and a HP Plotter?
TIA, DB
SELECT User_Account FROM Users AS a, Device_Name from Devices AS b
WHERE a.User_Account = b.Device_Account
AND b.Device_Name LIKE 'HP%'
You're welcome.
On second look, I realize we didn't even need a join for your orginal
question as you only wanted the account ID. Since Users.User_Account =
Devices.Device_Account, we could simply have done
SELECT Device_Account AS User_Account FROM Devices
WHERE Device_Name LIKE 'HP%'
Of course, usually you would want some corresponding information from
the Users table, such as the User_Name. Then you need the join I suggested.
I know of two ways to search for users with a specified list of equipment:
1) You can join the Devices table with itself on Device_Account, looking
for results where the left copy matches the first piece of equipment and
the right copy matches the other piece of equipment. You can also join
to the Users table to look up user info at the same time. Something
like this:
SELECT User_Account AS ID, User_Name AS Name
FROM Users AS u, Devices AS d1, Devices AS d2
WHERE u.User_Account = d1.Device_Account
AND d1.Device_Account = d2.Device_Account
AND d1.Device_Name = 'HP Plotter'
AND d2.Device_Name = 'Ultra 5'
You replace the last two lines according to your desired equipment
search. I like this conceptually -- it's simply an extension of the
join we already did -- but it doesn't generalize very well to longer
lists of equipment.
2) You can use some aggregation functions to get what you want.
Something like this:
SELECT Users.User_Account AS account, Users.User_Name AS Name
FROM Users JOIN Devices ON Users.User_Account = Devices.Device_Account
WHERE Devices.Device_Name IN ('HP Plotter', 'Ultra 5')
GROUP BY account
HAVING COUNT(*) = 2
This version generalizes nicely:
SELECT Users.User_Account AS account, Users.User_Name AS Name
FROM Users JOIN Devices ON Users.User_Account = Devices.Device_Account
WHERE Devices.Device_Name IN (List_of_Equipment)
GROUP BY account
HAVING COUNT(*) = Number_of_items_in_list
You replace List_of_Equipment and Number_of_items_in_list with
appropriate values, of course..
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IDENTITY column
Hi, I am a beginner in using database. And I appreciate your support and help. When we first create a table, 1. is it possible to create a column that identifies each record that is to be inserted? 2. If we can create this IDENTITY column, how do we create it? Do we set a maximum to the value of this column? Or the value simply increases as the number of records get inserted into the table grows? 3. when we try to insert the first record to this table, does this record go to the first row in the table? And the value of the IDENTITY for this record is 1? When we try to insert the second record to this table, does the second record automatically go to the second row in the table? And the value of the IDENTITY is 2? __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird bug in MySQL?
Michael Bacarella [EMAIL PROTECTED] wrote:
We're running MySQL 3.23.56 w/ InnoDB on Red Hat Linux 8.0,
compiled natively with bundled gcc:
# gcc -v
Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
--infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking
--host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit
Thread model: posix
gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
What is the option --enable-__cxa_atexit?
Could you try our binaries?
For the second time in the last week, mysqld has reached a point where
it started reporting too many connections. We managed to connect
a root session, and 'show processlist' showed indeed max_connections
number of connections, with all of them frozen in some state or
another executing a query.
mysql kill process #
had no effect, nothing changed.
Interestingly enough, the load average dropped to zero, which
lead me to try...
# ps auxw | grep mysql
which showed only the mysqld master thread. Usually there are a whole
mess of other threads too. The one master was using almost no CPU.
I straced the mysqld and got a whole lot of:
select(5, [3 4], NULL, NULL, NULL) = 1 (in [3])
fcntl64(3, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(3, {sin_family=AF_INET, sin_port=htons(50246),
sin_addr=inet_addr(10.0.0.2)}}, [16]) = 320
fcntl64(3, F_SETFL, O_RDWR) = 0
getsockname(320, {sin_family=AF_INET, sin_port=htons(3306),
sin_addr=inet_addr(10.0.0.8)}}, [16]) = 0
fcntl64(320, F_GETFL) = 0x2 (flags O_RDWR)
fcntl64(320, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(320, SOL_IP, IP_TOS, [8], 4) = 0
setsockopt(320, SOL_TCP, TCP_NODELAY, [1], 4) = 0
kill(32685, SIGRTMIN) = 0
kill(32685, SIGRTMIN) = 0
select(5, [3 4], NULL, NULL, NULL) = 1 (in [3])
fcntl64(3, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(3, {sin_family=AF_INET, sin_port=htons(56842),
sin_addr=inet_addr(10.0.0.3)}}, [16]) = 320
fcntl64(3, F_SETFL, O_RDWR) = 0
getsockname(320, {sin_family=AF_INET, sin_port=htons(3306),
sin_addr=inet_addr(10.0.0.8)}}, [16]) = 0
fcntl64(320, F_GETFL) = 0x2 (flags O_RDWR)
fcntl64(320, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(320, SOL_IP, IP_TOS, [8], 4) = 0
setsockopt(320, SOL_TCP, TCP_NODELAY, [1], 4) = 0
kill(32678, SIGRTMIN) = 0
kill(32678, SIGRTMIN) = 0
select(5, [3 4], NULL, NULL, NULL) = 1 (in [3])
fcntl64(3, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(3, {sin_family=AF_INET, sin_port=htons(37833),
sin_addr=inet_addr(10.0.0.6)}}, [16]) = 320
fcntl64(3, F_SETFL, O_RDWR) = 0
getsockname(320, {sin_family=AF_INET, sin_port=htons(3306),
sin_addr=inet_addr(10.0.0.8)}}, [16]) = 0
fcntl64(320, F_GETFL) = 0x2 (flags O_RDWR)
fcntl64(320, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(320, SOL_IP, IP_TOS, [8], 4) = 0
setsockopt(320, SOL_TCP, TCP_NODELAY, [1], 4) = 0
kill(32685, SIGRTMIN) = 0
kill(32685, SIGRTMIN) = 0
select(5, [3 4], NULL, NULL, NULL) = 1 (in [3])
fcntl64(3, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(3, {sin_family=AF_INET, sin_port=htons(50247),
sin_addr=inet_addr(10.0.0.2)}}, [16]) = 320
fcntl64(3, F_SETFL, O_RDWR) = 0
getsockname(320, {sin_family=AF_INET, sin_port=htons(3306),
sin_addr=inet_addr(10.0.0.8)}}, [16]) = 0
fcntl64(320, F_GETFL) = 0x2 (flags O_RDWR)
fcntl64(320, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(320, SOL_IP, IP_TOS, [8], 4) = 0
setsockopt(320, SOL_TCP, TCP_NODELAY, [1], 4) = 0
kill(32678, SIGRTMIN) = 0
kill(32678, SIGRTMIN) = 0
select(5, [3 4], NULL, NULL, NULL) = 1 (in [3])
fcntl64(3, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(3, {sin_family=AF_INET, sin_port=htons(37834),
sin_addr=inet_addr(10.0.0.6)}}, [16]) = 320
fcntl64(3, F_SETFL, O_RDWR) = 0
getsockname(320, {sin_family=AF_INET, sin_port=htons(3306),
sin_addr=inet_addr(10.0.0.8)}}, [16]) = 0
fcntl64(320, F_GETFL) = 0x2 (flags O_RDWR)
fcntl64(320, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(320, SOL_IP, IP_TOS, [8], 4) = 0
setsockopt(320, SOL_TCP, TCP_NODELAY, [1], 4) = 0
The 10.0.0.x connects are from our web servers. Wish I could've seen
what killed off all of the threads.
Nothing in the logs.
So, anyone have any idea what the heck's going on?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives:
Re: Quering user privileges
Plinio, Hence my suggestion for use of a flat file. The file can be located on the db server middle tier wherever it doesnt matter. The important part is that you control what goes in the file and its done ahead of time not when an enduser presses a button. What about creating tables the users can view loaded with the system table information your users need to see(same as flat file just do it with table in the database). It would be much better if you could get the information together and then let endusers view it as opposed to firing two statements and going through a bunch of parsing etc ... like you stated below everytime they press a button. The route your taking will be slower,more resource intensive and more complicated. - Original Message - From: Plinio Conti [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 11:58 AM Subject: Re: Quering user privileges To be honest, the fact I can't get it with only one query but I have to do: 1) SELECT CURRENT_USER(); 2) SHOW GRANTS FOR valueExtractedByPreviousQuery; is a little thing compared with work remaing to get usefull information: I have to parse the strings returned by query 2, handle the wildcards, handle ALL PRIVILEGES, comparing db-level privileges with table-level ones and with column-level ones, etc... I probably will do all that, only I wonder there is not a more simple solution. I mean, it looks a so common issue that I imagine there is better solution but I can't figure which. User authenticates, and client app wants to know what that user is allowed to do, so the app can show to its user the correct user interface (for example editing disabled if user can't write, etc..) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IDENTITY column
Hi, I think what you need is an auto increment (primary key) column. When a record is inserted, it will automatically generate the next number in the sequence. Being a primary key, the column must be unique (i.e no two rows can be the same), so you can identify a specify record in the table. When you insert a record, you can run SELECT LAST_INSERT_ID(); to retrieve the id that was used. The LAST_INSERT_ID is determined on a per connection basis, so you are guaranteed to get the last id that _you_ inserted, even if there are other users inserting records, as long as you do not run another INSERT. As for 3, you should not worry about how MySQL stores the rows. You should specify an ORDER BY clause in your SELECT statements to get the order in which you want. If you want to know, MySQL stores the rows ascending (1 is first, 2 is second, ...). However, if a record gets deleted from the table, then MySQL will try to re-use the space that once held that row. Check out the following links: http://www.mysql.com/doc/en/CREATE_TABLE.html http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html HTH Matt At 01:14 PM 12/23/2003, you wrote: Hi, I am a beginner in using database. And I appreciate your support and help. When we first create a table, 1. is it possible to create a column that identifies each record that is to be inserted? 2. If we can create this IDENTITY column, how do we create it? Do we set a maximum to the value of this column? Or the value simply increases as the number of records get inserted into the table grows? 3. when we try to insert the first record to this table, does this record go to the first row in the table? And the value of the IDENTITY for this record is 1? When we try to insert the second record to this table, does the second record automatically go to the second row in the table? And the value of the IDENTITY is 2? __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 100,000,000 row limit?
At 0:57 -0500 12/23/03, Andres Montiel wrote: I was informed that MySQL has a 100,000,000 row limit. Is this true? We were planning to use MySQL for an inventory system. However, our current data (rows) for 1 year for one area is already 8.8 million. We want to place data for 5 years for 7 areas. This would exceed 100,000,000. Is there a possible work around for this? Where did you hear this? -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dale Goolcharan.
Hi, I am currently working on a web application(USING JSP AND JAVA) that uses microsoft sql server 7.0 as the database. I have decided to use MySql4.0 as the database. I have been able to successfully create the database schema in mysql and it is the same as the ms sql server 7.0 database. Apache tomcat 4.0 is the application server. Upon server startup, I can connect to the blank MYSQL database with some default data that I have input into the database. With microsoft sql server 7.0, I can specify a url that allows me to obtain a list of databases. The url is as follows: !--sDataServerURLjdbc:inetdae:163.141.54.233:1433?sql7=true/sDataServerURL-- In the java code, I can write a sql string that allows me to retrieve a list of databases with a certain prefix and display them in a drop down list dynamically on the jsp. On the user interface, the user has an option to select a database, enter a user name and password. The user is validated an allowed access into the system. How can I establish the same scenario using mysql. What I can do at present is connect to one database but I would like to use the web app. to display a list of available mysql databases on the jsp page and allow the user to select a database and be granted access into the system. What is the url I can use to allow me to dynamically retrieve a list of mysql databases running on a particular IP address and upon selecting one, the user can log into the database. If you require samples of code, or additional info. please let me know. Thank you. Dale Goolcharan. _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/photospgmarket=en-caRU=http%3a%2f%2fjoin.msn.com%2f%3fpage%3dmisc%2fspecialoffers%26pgmarket%3den-ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dale Goolcharan.
At 17:43 + 12/22/03, Dale Goolcharan wrote: Hi, I am currently working on a web application(USING JSP AND JAVA) that uses microsoft sql server 7.0 as the database. I have decided to use MySql4.0 as the database. I have been able to successfully create the database schema in mysql and it is the same as the ms sql server 7.0 database. Apache tomcat 4.0 is the application server. Upon server startup, I can connect to the blank MYSQL database with some default data that I have input into the database. With microsoft sql server 7.0, I can specify a url that allows me to obtain a list of databases. The url is as follows: !--sDataServerURLjdbc:inetdae:163.141.54.233:1433?sql7=true/sDataServerURL-- In the java code, I can write a sql string that allows me to retrieve a list of databases with a certain prefix and display them in a drop down list dynamically on the jsp. On the user interface, the user has an option to select a database, enter a user name and password. The user is validated an allowed access into the system. How can I establish the same scenario using mysql. What I can do at present is connect to one database but I would like to use the web app. to display a list of available mysql databases on the jsp page and allow the user to select a database and be granted access into the system. You must connect to the MySQL server using an account that is allowed to run the SHOW DATABASES statement. Run it and you'll get back a list of databases. What is the url I can use to allow me to dynamically retrieve a list of mysql databases running on a particular IP address and upon selecting one, the user can log into the database. If you require samples of code, or additional info. please let me know. Thank you. Dale Goolcharan. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb, Assertion failure in file log0log.c with 4.0.!7
j,
how you have set
innodb_thread_concurrency
and
innodb_log_file_size
in my.cnf?
I was able to repeat the assertion failure by setting the log file size to
only 8 MB, and setting concurrency to 500. I changed now InnoDB so that it
no longer asserts, but calls exit(1). It prints the following instructions
if log files are too small for the concurrency:
if (!success) {
fprintf(stderr,
InnoDB: Error: ib_logfiles are too small for innodb_thread_concurrency
%lu.\n
InnoDB: The combined size of ib_logfiles should be bigger than\n
InnoDB: 200 kB * innodb_thread_concurrency.\n
InnoDB: To get mysqld to start up, set innodb_thread_concurrency in
my.cnf\n
InnoDB: to a lower value, for example, to 8. After an ERROR-FREE
shutdown\n
InnoDB: of mysqld you can adjust the size of ib_logfiles, as explained
in\n
InnoDB: section 5 of http://www.innodb.com/ibman.php;,
(ulong)srv_thread_concurrency);
fprintf(stderr,
InnoDB: Cannot continue operation. Calling exit(1).\n);
exit(1);
}
Merry Christmas!
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
Order MySQL technical support from https://order.mysql.com/
- Original Message -
From: j.random.programmer [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Tuesday, December 23, 2003 5:49 AM
Subject: BUG: Innodb, Assertion failure in file log0log.c with 4.0.!7
There is a MySQl/Innodb bug with Mysql-Max 4.0.17
on mac osx 10.3 panther. A google search showed
a similar problem with another platform (I think
it was windows).
I am running 4.0.14 without any problems. After
upgrading to 4.0.17, copying the data directory
from 4.0.14 to 4.0.17 and then starting mysqld
I get:
---
031222 22:05:51 mysqld started
InnoDB: Error: log file group too small for
innodb_thread_concurrency
031222 22:05:51 InnoDB: Assertion failure in thread
2684396012 in file log0log.c line 856
InnoDB: Failing assertion: log_calc_max_ages()
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to
[EMAIL PROTECTED]
mysqld got signal 10;
This could be because you hit a bug. It is also
possible that this binary
or one of the libraries it was linked against is
corrupt, improperly built,
or misconfigured. This error can also be caused by
malfunctioning hardware.
We will try our best to scrape up some info that will
hopefully help diagnose
the problem, but since we have already crashed,
something is definitely wrong
and this may fail.
-
Reverting back to 4.0.14 works fine and I
get no error messages.
Best regards,
--j
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Database Merge
I have a folder with all of the files from a database that the server shot craps (database is used for phpBB). I now have another server up and running with phpBB setup and a database with the same name. My question is, is there any way to merge the information into the new database? We had quite a few posts and attachments on the old machine that would be useful to have again. Even if there was a way to dump them to a text file so I could re-post them at my leisure, that would work as well. I can't seem to find a way to read any of the files I have, nor can I get the new server to do anything with them. I have tried using tools mentioned here on this list to connect to the old server remotely and it will not allow me, so hopefully there is something I can do with them locally on my PC. Thanks! Ryan Sinnwell Regional IT Engineer The Weitz Company 515-698-4281 515-229-5517 (Cell) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem w. loosing access when using passwords on MySQL 4
hi gang Got a wee bit of a problem with MySQL... Running Distrib 4.0.14, for Win95/Win98 on i32 ... on Windows XP Pro SP1 ... as long as I keep the default setup, with no password on the root user and nothing modder in 'user' at all, I can access the DB. But as soon as I try adding a user or change the password for the root, I loose all contact to the DBs. After changing the root pw, I tried accessing it with \mysql\bin\mysql.exe but it still fails ... tried just restarting the MySQL service, telling MySQLAdmin to reload, and by rebooting the entire computer ... none of it makes any difference tried going through the crappy MySQL manual, but all I got out of it was a headache ... I'm running Apache 2.0.48 and PHP 4.2.3, and latest PHPMyAdmin. As long as there's no pass on the root, I can use PHPMyAdmin just fine ... as soon as I set a password on root, using any method I could find on the web, I loose access (as in it reports 'access denied') This is all just a test/development setup, not an actual server, but nevertheless for security sake I'd like to get the thing working ... tried adding a test install of PHPBB, but it runs into the same problem with the access ... Using PHPMyAdmin I created a user called 'phpbb', and gave it a password, and set everything to 'Y' in the DB... but when I run the install setup for PHPBB I get this: Warning: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) in E:\web\phpBB2\db\mysql4.php on line 48 Warning: MySQL Connection Failed: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) in E:\web\phpBB2\db\mysql4.php on line 48 Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in E:\web\phpBB2\db\mysql4.php on line 330 Warning: mysql_errno(): supplied argument is not a valid MySQL-Link resource in E:\web\phpBB2\db\mysql4.php on line 331 phpBB : Critical Error Could not connect to the database ^obviously that's just a PHPBB error, and not an actual MySQL error, but since the problem originates in the MySQL, I figured it's worth mentioning ... PHPBB can't run on the root user without a PW, and since I loose access to the MySQL DBs _when_ I make a PW, I've not done so yet ... so ... does anyone have any ideas for how to get this working like it's supposed to ??? ... I have no clue about MySQL ... never worked with SQL databases till 2 months ago when I did the first MySQL install ... I've changed comps in the meantime, so it's a new installation ... but the problem was the same with the old one ... I figure there's just something I need to change the setting off to get it working, but I have no clue what that would be ... like I said, the MySQL manual is mostly useless to me... TIA Rene -- Rene Brehmer aka Metalbunny http://metalbunny.net/ References, tools, and other useful stuff... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign Keys in CREATE TABLEs produced by mysqldump
First let me state that this is not a question where a valid answer is to SET FOREIGN_KEY_CHECKS=0... (no, its not *that* question..) I have noticed that mysqldump includes the database name in foreign key specifications within CREATE TABLEs. This is causing a bit of grief as I would like to reimport such a dumped data set with a *different* database name (multiple instances of a data set being created for development, qa, and ua purposes..) Is there any way to modify this behaviour? I would prefer not to have to modify a mysqldump'ed file to have it reimported (at all!) In addition, is there a known bug with 3.23.58 where reading bulk queries is very slow? (i.e., mysql FOODB foodb.dump.) Ever since upgrading (on FreeBSD 5.2-RC1) this is horrendously slow. Almost NO cpu time is consumed by any process involved, and both server/client processes are often in state 'S' (sleeping for less than 20 seconds.) This also occurs when using a client running on a linux machine to feed data to the aforementioned mysqld on the FreeBSD host. I'm about to break up my dumps into table-specific files so that I can use LOAD DATA INFILE to help work around this problem. -mike
Delete items which aren't present in another table?
I have a table which maintains a list of categories and has a field called catID. This field is a one-to-many relationship with another table. What I want to do is remove any empty categories - aka: remove any categories which aren't used in the second table. I thought MySQL at least partially supported nested SELECT queries, but either it does not support them in DELETE's or I got the syntax wrong. Here's what I was trying to use: DELETE FROM Categories WHERE NOT(catID IN (SELECT DISTINCT catID FROM items)); Any easy alternatives here? Adam Clauss cabadam@ mailto:[EMAIL PROTECTED] houston.rr.com
Re: Quering user privileges
At 15:46 +0100 12/23/03, Plinio Conti wrote: Sorry, I was not using any SELCT in my queries, it's just an error typing the email I confirm: SHOW GRANTS FOR CURRENT_USER(); does not work, while that query is exaclty what you expect to do reading mysql manual. I agree, one might easily come to that conclusion based on the wording. The manual was imprecise/wrong. I've changed it to point out that you must first select the value of CURRENT_USER(), then use the value it returns in the SHOW GRANTS statement. On Tue, 23 Dec 2003 12:47:11 +0200 Egor Egorov [EMAIL PROTECTED] wrote: Plinio Conti [EMAIL PROTECTED] wrote: The manual page at http://www.mysql.com/doc/en/SHOW_GRANTS.html says: To list grants for the current session one may use CURRENT_USER() function But if I run SELECT SHOW GRANTS FOR CURRENT_USER(); I get syntax error (Error 1064). While if I run directly SELECT SHOW GRANTS FOR [EMAIL PROTECTED]; It works. Did you guess why? Look at the example of usage SHOW GRANTS in the manual: mysql SHOW GRANTS FOR [EMAIL PROTECTED]; There is no SELECT word in the query. On Mon, 22 Dec 2003 15:37:43 +0200 Egor Egorov [EMAIL PROTECTED] wrote: Plinio Conti [EMAIL PROTECTED] wrote: Is there a way to query the privileges of current user if he hasn't the rights to read the mysql system tables (user, db, hosts, etc..) I mean, I CAN'T give a standard user the chance of read system tables! But my client app wants to know if the current user has (for example) the rights to write on table xxx, because if not, I don't want to let him try and fail, I want to disable the updates in advance. Take a look at SHOW GRANTS command: http://www.mysql.com/doc/en/SHOW_GRANTS.html -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange performance problem importing dumped data (3.23.58 on FreeBSD 5.2-RC1)
I am using 3.23.58 (server and client) on FreeBSD 5.2-RC1 and all of what I am about to describe is being performed locally. When importing dumped data (with something like mysql dbname dbname.dump) the import is being performed at ~60 queries a second. This is MUCH slower than when I was running 3.23.51 on FreeBSD 4.x-STABLE. My data set is approx 200k rows, so this isn't really acceptable for a ~45MB import. I have the following information, all captured during the import process: Storage device is pegged making a large number of small transfers (iostat output): tty ipsd0 fd0 cpu tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id 1 110 17.41 2 0.03 0.00 0 0.00 0 0 0 0 99 0 36 15.91 108 1.68 0.00 0 0.00 3 0 8 0 88 0 12 16.00 90 1.41 0.00 0 0.00 1 0 7 1 91 0 12 16.00 96 1.50 0.00 0 0.00 2 0 9 0 88 0 12 16.00 84 1.31 0.00 0 0.00 3 0 5 0 91 mysqld process call frequency: 189549517 mysqld CALL gettimeofday 92175517 mysqld CALL read 46255517 mysqld CALL pwrite 46171517 mysqld CALL fsync 46067517 mysqld CALL write 5567517 mysqld CALL poll 4857517 mysqld CALL sigreturn 4455517 mysqld CALL sigprocmask 229517 mysqld CALL fcntl 106517 mysqld CALL clock_gettime 35517 mysqld CALL setitimer 13517 mysqld CALL lseek 10517 mysqld CALL open 10517 mysqld CALL access 9517 mysqld CALL fstat 9517 mysqld CALL close 8517 mysqld CALL pread 7517 mysqld CALL setsockopt 7517 mysqld CALL getsockname 7517 mysqld CALL accept 6517 mysqld CALL shutdown This is over a span of approx 6 minutes. (whats up with the gettimeofday calls...??) And lastly here is vmstat output for 30 seconds during the same import: procs memory pagedisks faults cpu r b w avmfre flt re pi po fr sr ip0 fd0 in sy cs us sy id 1 0 0 195588 411848 0 0 0 9 1 0 0 3370 291 1 1 99 0 1 0 195588 407521 0 0 0 2 0 82 0 4170 1458 3 7 90 0 1 0 195588 402240 0 0 0 0 0 83 0 4170 1505 3 6 91 0 1 0 195588 396960 0 0 0 0 0 94 0 4260 1654 3 7 89 0 1 0 195588 391040 0 0 0 0 0 83 0 4170 1477 3 7 90 0 1 0 195588 385920 0 0 0 1 0 95 0 4280 1671 4 9 88 My mysqld configuration is as follows: [mysqld] innodb_data_file_path = ibdata1:24M:autoextend set-variable = innodb_log_file_size=8M set-variable = innodb_log_buffer_size=4M set-variable = innodb_buffer_pool_size=8M set-variable = innodb_additional_mem_pool_size=2M set-variable = table_cache=128 set-variable = sort_buffer=8M set-variable = record_buffer=2M set-variable = key_buffer_size=32M set-variable = wait_timeout=60 My ibdata1 file is approx 180MB right now. It looks to me as though the mysqld process is blocking like crazy on I/O.. but I have not noticed poor performance with any other processing on the same system.. All filesystem based tests I can come up with (creation of many small files, large files, etc) pass with flying colours. Note that the throughput on the ipsd0 device (tranfer rate shown in iostat output listed above) is FAR greater than the amount of data that is (should be?) being read/written related to mysqld (nothing else is running on the host at this time.) Could this possibly be an ips driver bug in FreeBSD 5.2 that mysqld is somehow exploiting? Is there something amiss with the above call frequencies? Am I ignorant of a known issue? Is there some buffer-related configuration for mysqld that I might need to (re)specify? -mike
Re: Benefits of MAX_ROWS and AVG_ROW_LENGTH
Hi Jeremy, - Original Message - From: Jeremy Zawodny Sent: Monday, December 22, 2003 2:20 PM Subject: Re: Benefits of MAX_ROWS and AVG_ROW_LENGTH On Fri, Dec 19, 2003 at 06:40:17PM -0600, Matt W wrote: Hi Mark, I'll tell you what I know. :-) First, AVG_ROW_LENGTH is only needed for dynamic row-length tables (it's ignored with fixed-length rows) -- more specifically, those with TEXT/BLOB columns. And VARCHAR/VARBINARY. Yes, in that VARCHAR makes variable length rows, but *not* that you *have to* (or rather really should) specify AVG_ROW_LENGTH with MAX_ROWS. Sure, include it if your VARCHARs aren't always going to be filled to the max length. Otherwise, MySQL will just assume that the rows will be as long as the sum of the max length of all the VARCHAR (and other) columns. When VARCHAR(n) is specified, and n is the max length that will be stored in the column, this should be a pretty accurate assumption, no? But if you have TEXT/BLOB columns, their max length (well, plain TEXT anyway; not TINY/MEDIUM/LONG) is equivalent to 255 VARCHAR(255) columns! And again, MySQL will assume you're going to use it all. That's fine if you're actually storing 64K in each column, but that's hardly ever the case. Hence why I said, more specifically, those with TEXT/BLOB columns. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [LONG] Connector/J SQLState 0S100 usage/meaning
Mark Matthews wrote : Gilles Magnier wrote: [snip] I think having identical error code for unsuported exception and bad parameter error is a bit confusing, for me at least ... Thanks for any help, These definitely look like typos. After the holidays, I will look into a fix for these using constants in SQLError so that typos will no longer be a problem. Thanks, i've an other question : it's looks like there is an other possible problem in SQLStates error code usage in Connector/J. Error code 08003 witch means using a closed connection according to both sqlstate.h and SQLError.java is used in Connection.java (function setAutoCommit line 576) when transaction is not supported but i don't know if it's the good error code to use or if not wich one shoud be used. throw new SQLException(MySQL Versions Older than 3.23.15 + do not support transactions, 08003); Could you take a look on that while fixing typos ? Again thanks a lot, Gilles. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Benefits of MAX_ROWS and AVG_ROW_LENGTH
Hi Mark, Maybe you intentionally only replied to me (instead of the list too), but I'm sending this to the list also so others can follow the discussion. :-) I never know how much I have to explain things for a person's knowledge level, but it sounds like you understand what's going on very well. :-) More below... - Original Message - From: Mark Hawkes Sent: Saturday, December 20, 2003 3:50 PM Subject: Re: Benefits of MAX_ROWS and AVG_ROW_LENGTH Hi Matt, Thanks very much for your thoughts and advice. I was going to ignore using MAX_ROWS, MIN_ROWS and AVG_ROW_LENGTH because the tables I'll be working with are small. That doesn't stop me wanting to tune them though, so I've included them anyway. I figure it's better to give MySQL a clue - better than specifying no size params whatsoever. Maybe future versions will use them intelligently (?). I doubt it. It's already doing all it can. You have to tell it the rest. 4 byte pointers are the default since most tables don't have data files 4GB. It can't go smaller unless you give MySQL that information -- otherwise people would be getting Table is full errors. :-) The reason I asked is that I'm accustomed to tuning options for filesystems (inode density, cluster size etc..), and hash tables when programming (e.g. load factor and ensuring the number of buckets isn't a power of 2, blah). I just wanted to make sure that something as important as a database table would also be sized or tuned correctly. Yeah, I'm always trying to optimize things as much as possible. :-D And I never see anyone use MAX_ROWS/AVG_ROW_LENGTH -- unless it's to get around the 4GB limit. He he. First, AVG_ROW_LENGTH is only needed for dynamic row-length tables (it's ignored with fixed-length rows) -- more specifically, those with TEXT/BLOB columns. Using VARCHAR also makes a table dynamic doesn't it? (Unless it's below 4 chars.) Yes, but see my reply to Jeremy Zawodny about that. MySQL should still be able to fairly accurately estimate the average row length if the only dynamic columns are VARCHAR. Otherwise, if MAX_ROWS is used, MySQL will assume that each TEXT/BLOB column will be filled completely, [probably] making it think the data file will be 4GB. I discovered this when I was ONLY specifying MAX_ROWS. It made the index file larger because 5 byte data pointers were used instead of 4. I see, so if we had a table like this... CREATE TABLE Foo ( essay TEXT NOT NULL ) MAX_ROWS = 25; then, without specifying AVG_ROW_LENGTH at all, MySQL would assume the table could get as large as 1.6MB (25 * 65536) and thus use a 3 byte datafile pointer. Right. :-) I said it would probably use 5 byte pointers without AVG_ROW_LENGTH, unless MAX_ROWS is small enough. If there's just 1 regular TEXT/BLOB column: MAX_ROWS ~65,532 -- 4 byte pointer MAX_ROWS ~256 -- 3 byte pointer With 1 TEXT column like your example, the max row length would probably be more like 65,540: 65,535 for TEXT data + 2 bytes to record that length + ~3 bytes (I think) for the row header. And that's not taking into account possible split/fragmented rows which will take extra space for the pointer to where the row continues. ;-) But if we did this... CREATE TABLE Foo ( essay TEXT NOT NULL ) MAX_ROWS = 25 AVG_ROW_LENGTH = 2000; then 2 byte datafile pointers would be used because 50,000 bytes can be addressed by a 16-bit pointer. Okay, I get it. Yep, just verified that. :-) No, as far as I know, MySQL doesn't use MAX_ROWS or AVG_ROW_LENGTH to do any optimizations -- only to decide the pointer size and therefore the max size of the table. BTW, I don't know what the use of MIN_ROWS is, do you?? I agree - looks like MAX_ROWS and AVG_ROW_LENGTH determine the datafile pointer size and that's all. The only mention of MIN_ROWS in the manual says Minimum number of rows you plan to store in the table Boy, that's really informative! I have no idea what it does but have used it anyway. Exactly what I was thinking! I honestly can't think of any way that it would be useful, though. Don't know how the minimum number or rows would change anything... Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why i select * from a table,mysql should select 2000000 records out,but
actually ,mysql only return 17000 records if that means mysql select has max record limit? _ MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange performance problem importing dumped data (3.23.58 on FreeBSD 5.2-RC1)
I have found that by dumping with --extended-insert, the subsequent import is MUCH faster. Of course it only issues 450 odd queries for the data set instead of ~200k... -mike [EMAIL PROTECTED] 12/23/03 05:02 PM To [EMAIL PROTECTED] cc Subject Strange performance problem importing dumped data (3.23.58 on FreeBSD 5.2-RC1) I am using 3.23.58 (server and client) on FreeBSD 5.2-RC1 and all of what I am about to describe is being performed locally. When importing dumped data (with something like mysql dbname dbname.dump) the import is being performed at ~60 queries a second. This is MUCH slower than when I was running 3.23.51 on FreeBSD 4.x-STABLE. My data set is approx 200k rows, so this isn't really acceptable for a ~45MB import. I have the following information, all captured during the import process: Storage device is pegged making a large number of small transfers (iostat output): tty ipsd0 fd0 cpu tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id 1 110 17.41 2 0.03 0.00 0 0.00 0 0 0 0 99 0 36 15.91 108 1.68 0.00 0 0.00 3 0 8 0 88 0 12 16.00 90 1.41 0.00 0 0.00 1 0 7 1 91 0 12 16.00 96 1.50 0.00 0 0.00 2 0 9 0 88 0 12 16.00 84 1.31 0.00 0 0.00 3 0 5 0 91 mysqld process call frequency: 189549517 mysqld CALL gettimeofday 92175517 mysqld CALL read 46255517 mysqld CALL pwrite 46171517 mysqld CALL fsync 46067517 mysqld CALL write 5567517 mysqld CALL poll 4857517 mysqld CALL sigreturn 4455517 mysqld CALL sigprocmask 229517 mysqld CALL fcntl 106517 mysqld CALL clock_gettime 35517 mysqld CALL setitimer 13517 mysqld CALL lseek 10517 mysqld CALL open 10517 mysqld CALL access 9517 mysqld CALL fstat 9517 mysqld CALL close 8517 mysqld CALL pread 7517 mysqld CALL setsockopt 7517 mysqld CALL getsockname 7517 mysqld CALL accept 6517 mysqld CALL shutdown This is over a span of approx 6 minutes. (whats up with the gettimeofday calls...??) And lastly here is vmstat output for 30 seconds during the same import: procs memory pagedisks faults cpu r b w avmfre flt re pi po fr sr ip0 fd0 in sy cs us sy id 1 0 0 195588 411848 0 0 0 9 1 0 0 3370 291 1 1 99 0 1 0 195588 407521 0 0 0 2 0 82 0 4170 1458 3 7 90 0 1 0 195588 402240 0 0 0 0 0 83 0 4170 1505 3 6 91 0 1 0 195588 396960 0 0 0 0 0 94 0 4260 1654 3 7 89 0 1 0 195588 391040 0 0 0 0 0 83 0 4170 1477 3 7 90 0 1 0 195588 385920 0 0 0 1 0 95 0 4280 1671 4 9 88 My mysqld configuration is as follows: [mysqld] innodb_data_file_path = ibdata1:24M:autoextend set-variable = innodb_log_file_size=8M set-variable = innodb_log_buffer_size=4M set-variable = innodb_buffer_pool_size=8M set-variable = innodb_additional_mem_pool_size=2M set-variable = table_cache=128 set-variable = sort_buffer=8M set-variable = record_buffer=2M set-variable = key_buffer_size=32M set-variable = wait_timeout=60 My ibdata1 file is approx 180MB right now. It looks to me as though the mysqld process is blocking like crazy on I/O.. but I have not noticed poor performance with any other processing on the same system.. All filesystem based tests I can come up with (creation of many small files, large files, etc) pass with flying colours. Note that the throughput on the ipsd0 device (tranfer rate shown in iostat output listed above) is FAR greater than the amount of data that is (should be?) being read/written related to mysqld (nothing else is running on the host at this time.) Could this possibly be an ips driver bug in FreeBSD 5.2 that mysqld is somehow exploiting? Is there something amiss with the above call frequencies? Am I ignorant of a known issue? Is there some buffer-related configuration for mysqld that I might need to (re)specify? -mike
RE: 100,000,000 row limit?
I don't believe this. I'm going to write a script to disprove this theory right now.. Cheers, Andrew -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday 23 December 2003 20:08 To: Andres Montiel; [EMAIL PROTECTED] Subject: Re: 100,000,000 row limit? At 0:57 -0500 12/23/03, Andres Montiel wrote: I was informed that MySQL has a 100,000,000 row limit. Is this true? We were planning to use MySQL for an inventory system. However, our current data (rows) for 1 year for one area is already 8.8 million. We want to place data for 5 years for 7 areas. This would exceed 100,000,000. Is there a possible work around for this? Where did you hear this? -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 100,000,000 row limit?
I don't believe this. I'm going to write a script to disprove this theory right now.. We have a lot more than 100,000,000 more than that in a single MyISAM table at work: mysql select count(*) from probe_result; +---+ | count(*) | +---+ | 302045414 | +---+ 1 row in set (0.00 sec) -- thanks, Will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quering user privileges
Paul DuBois wrote: At 15:46 +0100 12/23/03, Plinio Conti wrote: Sorry, I was not using any SELECT in my queries, it's just an error typing the email I confirm: SHOW GRANTS FOR CURRENT_USER(); does not work, while that query is exactly what you expect to do reading mysql manual. I agree, one might easily come to that conclusion based on the wording. The manual was imprecise/wrong. I've changed it to point out that you must first select the value of CURRENT_USER(), then use the value it returns in the SHOW GRANTS statement. From the manual, To list grants for the current session, you can find out what user the session was authenticated as by selecting the value of the CURRENT_USER() function (new in version 4.0.6). Then use that value in the SHOW GRANTS statement. I assume that's the reworked version. I don't think that's sufficient. Though it does not suggest SHOW GRANTS FOR CURRENT_USER(), it doesn't say it won't work. Even then, if you are familiar with mysql user variables, surely get a value in one statement then use it in the next implies a user variable, but SET @u = CURRENT_USER(); SHOW GRANTS FOR @u; doesn't work, either. As I see it, use that value here really means copy/paste, or assign it to a variable in your external client program. There does not appear to be any way *within mysql* to pass the result of CURRENT_USER() to SHOW GRANTS. I assume this is because the processing of SHOW GRANTS FOR simply does not consider the possibility that what comes next might be a variable or function, as SELECT does, for example. Now that we've determined that's the way it works, isn't Should it work that way? the next logical question? After all, CURRENT_USER() returns [EMAIL PROTECTED], while SHOW GRANTS FOR expects [EMAIL PROTECTED] Intuitively, SHOW GRANTS FOR CURRENT_USER() should work. But it doesn't. Shouldn't that be considered a bug, or at least a missing feature? Admittedly, I haven't yet looked at that portion of the code, so I don't know how easy or difficult this would be to change. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
