Re: Saving file into database
I've been reading this thread, but I can't see the advantages of storing files in a database. I've always had the impression that a *file* system was the appropriate place to store files. Access can be arranged easily by other means. Replication seems more silly, since one also copies the stored files to the slave. This not only adds to the traffic between slave and master, but also wastes diskspace. Furthermore large files may cause you to hit the max_allowed_packet size (PDF's of 5MB or larger are no exception in real life). I've built a system which includes the possibility of downloading one's own bills in PDF format. This system runs on load balanced webservers with one mysql server. The files are stored on a different machine that cannot be accessed directly from the web. Scripts validate access to the PDF and serve it to the client. Works like a charm ;-) Regards, Jigal. - Original Message - From: [EMAIL PROTECTED] To: Eve Atley [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, March 11, 2004 5:42 PM Subject: RE: Saving file into database It does make the database larger.. as far as overhead... As you can't just store the file as a blob.. You'll need some referencing data in order to find it, and restore it back out of the database.. I just checked out my database (100's of files) which has: Total file size: 1765.34MB Mysql files are: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Determining when slave has synchronized.
Hello all, Is there a scriptable way to determine if a slave in a replication setup has been fully synchronized with its master? Kind of like the following: echo SHOW SLAVE STATUS | mysql -u root | awk -f somescript.awk I'm not asking you to write the awk script for me ;) Just would like to know if there is a parameter that indicates full (or not) synchronization with the master that I can obtain through a perl/shell/python script or C program. The mysql version is 3.23.57. Thanks for any input. Ian. -- Ian van der Neut KNMI, RD Observations, De Bilt [EMAIL PROTECTED] -- Zie ook/see also: http://www.knmi.nl/maildisclaimer.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Maximum Database size
Hi, I'm planning to use MySQL for logging from an SMTP-Relay. What is the maximum size of the database, that can be reached? Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New Prepared Statements !!!!!
Hi, I am really astonishing when I see the sample program on the URL: http://www.mysql.com/doc/en/mysql_stmt_execute.html I am using MySQL 5.0.0 alpha Windows 2000 SP 4 VC++ 6.0 I found that the C API changes as mysql_prepare() --- mysql_stmt_prepare() mysql_bind_param() --- mysql_stmt_bind_param() mysql_execute() --- mysql_stmt_execute() and new API mysql_stmt_init() I just want to know these changes are implemented in which version of MySQL ???. Today I have downloded MySQL 5.0.0 and verify that it don't have these statements in libmysql.lib. Please inform me which version implements these C API'S ?? I'm much in need of these API's Thanks Arun. Yahoo! India Insurance Special: Be informed on the best policies, services, tools and more. Go to: http://in.insurance.yahoo.com/licspecial/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum Database size
Baum, Stefan [EMAIL PROTECTED] wrote: Hi, I'm planning to use MySQL for logging from an SMTP-Relay. What is the maximum size of the database, that can be reached? Table size (for MyISAM/ISAM tables) and number of tables per database is limited by your filesystem. -- 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: SELECT ... UPDATE
Darran Kartaschew [EMAIL PROTECTED] wrote: Hi Everyone, Is there an easy way to update a field for the last record referenced by an ID number? where that ID number is used on multiple rows? At the moment I am doing this which works: CREATE TEMPORARY TABLE tmp_user ( SELECT user_id, MAX(last_updated) AS max_last_updated FROM employee GROUP BY user_id); UPDATE tmp_user, employee SET picture='My Pic3' WHERE employee.user_id='19' AND last_updated=3Dtmp_user.max_last_updated; Where user_id is the user I wish to update, and picture is just a string (which will hold the filename for their picture). But this just seems too complicated? So any thoughts? I'm also using v4.0.18... If you update data only for one user, you can use ORDER BY and LIMIT in the UPDATE statement: UPDATE employee SET picture='My Pic3' WHERE employee.user_id='19' ORDER BY last_updated DESC LIMIT 1; -- 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: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum Database size
Stefan, it depends ... For MyISAM tables max size is limited by the maximum filesize of your OS/Filesystem. Regards, Thomas On Fri, 12 Mar 2004, Baum, Stefan wrote: Hi, I'm planning to use MySQL for logging from an SMTP-Relay. What is the maximum size of the database, that can be reached? Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum Database size
Stefan, it depends ... For MyISAM tables max size is limited by the maximum filesize of your OS/Filesystem. Following on from that, the maximum file size you can have on 32-bit Linux is 2GB, unless your distro has LFS (Large File Support), in which case is is much larger (terabytes, not sure exactly how big). I know that Fedora Core 1 has LFS, and RedHat Linux 9 might do as well. For Windows under NTFS, the file size limit is 2TB, on FAT32 it is 4GB. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB, SUBSTRING and 65536 characters limit
I'm sorry, the problem was of course between keyboard and chair. I didn't realize I'm using BLOB which is limited to 65536 characters insted of longblob that is quite larger. Thanks for your answers. Tomas Tomas Zvala wrote: Hello, I run into a problem where I need to get contents of BLOB to my php script. I found out that I'm limited by max_packet_size (which i can't change because of my ISP) and i tried to work around it by using SELECT substring(column,x,1024) where x is number increasing in steps of 1024. But once x reaches 65536 i get empty result. What am I doing wrong or how can I work around this? I'm using MySQL 4.0.14 running on FreeBSD 4.9. Thanks in advance for your help, Tomas Zvala -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment id
Stefan Schuster [EMAIL PROTECTED] wrote: Stefan Schuster [EMAIL PROTECTED] wrote: Hi, I have a question about auto_increment: I have 2 tables, on of them holds my online transactions, the other one the offline transactions. Every transaction is created in the first table (call it t1) and then moved to t2. The id is generated using auto_increment. My problem is that the id's should be unique across both tables. If I dont restart the server this works fine, but if I restart it, mySQL assigns the lowest number for the new row in t1, but this id exits already in t2 which results in an error if I try to move from t1 to t2. I hope I explained the problem in a appropriate way. How can I tell mySQL to hold the auto_increment counter for Table t1 persistent ? Do you use InnoDB tables? If so, it's a known behavior. If you restart MySQL server InnoDB may reuse old value for AUTO_INCREMENT column. I need transaction support, as I understand it InnoDB is the only tabletype that supports this. Is there any way to change the behaviour, maybe a table type that has the desired (persistent incrementcounter) behaviour AND supports transactions ? BDB tables are also support transaction, but they are reuse deleted auto_increment values not only if server restarts. For InnoDB tables you can initialize the auto-increment counter by yourself. You should insert into t1 dummy row with max id value from t2 and then remove this row. Something like: INSERT IGNORE INTO db_name.t1(id) SELECT greatest(max(t1.id), max(t2.id)) as id from db_name.t1, db_name.t2; So, with SELECT statement you find greatest value and insert this value into t1 table. If this value is already exists, INSERT statement is ignored. Then you should remove this dummy row: DELETE db_name.t1 FROM db_name.t1, db_name.t2 WHERE db_name.t1.id=db_name.t2.id; Put INSERT and DELETE commands to the file and specify file name with --init-file option of mysqld. MySQL server will read this file at startup: http://www.mysql.com/doc/en/Server_options.html -- 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: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld don't start
I installed mysql under RedHat 9 with mysql-standard-4.1.1-alpha-pc-linux-i686.tar.gz but when I try to start the mysqld with the sentence ./bin/mysqld_safe it gives me this message: [EMAIL PROTECTED] mysql]# ./bin/mysqld_safe [1] 2341 [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /usr/local/mysql/data 040312 08:33:47 mysqld ended [1]+ Done./bin/mysqld_safe in other side the mysqld don't appears in the services window of Red Hat, ¿how do I do this? Thanks __ Introducing the New Netscape Internet Service. Only $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Privilege to single database being revoked occasionally
Terence [EMAIL PROTECTED] wrote: Dear Lists, We moved to 4.1.0 when it was first launched, and twice a month or so, we get access denied commands to one particular database for one particular user. The privileges are correctly set, and the only way to restore access for the user, is to revoke and re-apply the privileges and all works fine again. I am the only user with permission to change privileges. I am just wondering if anyone else has stumbled across this problem in 4.1.0? It doesnt seem to have been fixed in version 4.1.1 according to: http://www.mysql.com/doc/en/News-4.1.1.html If there's any data I can provide for debugging purposes, please let me know. There was a bug in 4.1.0: http://bugs.mysql.com/bug.php?id=2009 It's already fixed in 4.1.1. Please, upgrade MySQL server and see if you get 'access denied' error again. -- 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: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query reference help
hi i'm having problems trying to write a query that returns a list of images and the stories (if any) that they're associated with. in this case, the story references the image embedded into the story body text in the format [img]myimage.jpg[/img] SELECT i.id, i.image_name, i.image_width, i.image_height, s.id as story_id, s.title FROM images i LEFT JOIN stories s ON s.body like '[img]' + i.image_name + '[/img]' WHERE i.image_name like '%$search%' ORDER by i.image_name this returns the appropriate images, but doesn't pick up any associated stories (which i *know* exist). i'm guessing that it's something to do with the ON clause, but i don't understand what's wrong. many thanks kris -- kris burford midtempo ltd http://www.midtempo.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New Prepared Statements !!!!!
Arunachalam [EMAIL PROTECTED] wrote: I am really astonishing when I see the sample program on the URL: http://www.mysql.com/doc/en/mysql_stmt_execute.html I am using MySQL 5.0.0 alpha Windows 2000 SP 4 VC++ 6.0 I found that the C API changes as mysql_prepare() --- mysql_stmt_prepare() mysql_bind_param() --- mysql_stmt_bind_param() mysql_execute() --- mysql_stmt_execute() and new API mysql_stmt_init() I just want to know these changes are implemented in which version of MySQL ???. Today I have downloded MySQL 5.0.0 and verify that it don't have these statements in libmysql.lib. Please inform me which version implements these C API'S ?? I'm much in need of these API's Function names were changed in version 4.1.2: http://www.mysql.com/doc/en/News-4.1.2.html -- 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: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New Prepared Statements !!!!!
Sorry to all, I found that these API are renamed in next release of 4.1.2 (not yet released) from the URL: http://www.mysql.com/doc/en/News-4.1.2.html But i could not able to use properly the existing prepared statements API, When I use it it'll excute the statements as such and made updation too in the database but atlast it'll reset the MySQL server 5.0.0. in Windows 2000 SP 4 platform... I could not find out the reason why it is happened??? since i struggle with these API for the past 1 months I have cleary set the Log files and watch it too... any boy help me... thanks... Arun. --- Arunachalam [EMAIL PROTECTED] wrote: Hi, I am really astonishing when I see the sample program on the URL: http://www.mysql.com/doc/en/mysql_stmt_execute.html I am using MySQL 5.0.0 alpha Windows 2000 SP 4 VC++ 6.0 I found that the C API changes as mysql_prepare() --- mysql_stmt_prepare() mysql_bind_param() --- mysql_stmt_bind_param() mysql_execute() --- mysql_stmt_execute() and new API mysql_stmt_init() I just want to know these changes are implemented in which version of MySQL ???. Today I have downloded MySQL 5.0.0 and verify that it don't have these statements in libmysql.lib. Please inform me which version implements these C API'S ?? I'm much in need of these API's Thanks Arun. Yahoo! India Insurance Special: Be informed on the best policies, services, tools and more. Go to: http://in.insurance.yahoo.com/licspecial/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Yahoo! India Insurance Special: Be informed on the best policies, services, tools and more. Go to: http://in.insurance.yahoo.com/licspecial/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld don't start
Pedro Baquero [EMAIL PROTECTED] wrote: I installed mysql under RedHat 9 with mysql-standard-4.1.1-alpha-pc-linux-i686.tar.gz but when I try to start the mysqld with the sentence ./bin/mysqld_safe it gives me this message: [EMAIL PROTECTED] mysql]# ./bin/mysqld_safe [1] 2341 [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /usr/local/mysql/data 040312 08:33:47 mysqld ended [1]+ Done./bin/mysqld_safe in other side the mysqld don't appears in the services window of Red Hat, ?how do I do this? Look into error log file (/usr/local/mysql/data/host_name.err). What is error message? -- 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]
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
Hello, I am having a hard time getting Mysql to let [EMAIL PROTECTED] connect. I am constantly getting this: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) when I issue mysql -u root I can log in if I use mysql -h 192.168.1.10 -u root mysql I guess I have some sort of host thing mixed up but I have tried all sorts of things out of the list with no avail. I using Redhat 9 and I used the rpm's distributed by mysql. Thanks in advance. Les -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query reference help
Kris, Is ...LIKE '%[img]' + i.image_name + '[/img]%' what you're looking for? PB - Original Message - From: Kris Burford To: [EMAIL PROTECTED] Sent: Friday, March 12, 2004 7:03 AM Subject: query reference help hi i'm having problems trying to write a query that returns a list of images and the stories (if any) that they're associated with. in this case, the story references the image embedded into the story body text in the format [img]myimage.jpg[/img] SELECT i.id, i.image_name, i.image_width, i.image_height, s.id as story_id, s.title FROM images i LEFT JOIN stories s ON s.body like '[img]' + i.image_name + '[/img]' WHERE i.image_name like '%$search%' ORDER by i.image_name this returns the appropriate images, but doesn't pick up any associated stories (which i *know* exist). i'm guessing that it's something to do with the ON clause, but i don't understand what's wrong. many thanks kris -- kris burford midtempo ltd http://www.midtempo.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Using OR
is there any alternative to using OR for selecting between values? ie: pla.type='1' OR pla.type='2' OR pla.type='3' OR pla.type='4' Cheers, Keith
Re: Using OR
Keith wrote: is there any alternative to using OR for selecting between values? ie: pla.type='1' OR pla.type='2' OR pla.type='3' OR pla.type='4' Keith for stuff like this i try and arrange things in numerical blocks so i am doing selects like pla.type = 1 AND play.type = 4, probably not needing both constraints depending on how you arrange your query. I also had a table that had two columns that needed an OR on. I moved it to another table, and used a join on those two tables and boy did that make a difference. Removing the OR makes a huge performance gain. hope this helps, i'll let the real SQL experts jump in here and give their response. -- Alan Williamson, City Planner w: http://www.BLOG-CITY.com/ e: [EMAIL PROTECTED] b: http://alan.blog-city.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using OR
Hi Keith,
you can use:
pla.type IN ('1','2','3','4');
/Johan
Keith wrote:
is there any alternative to using OR for selecting between values?
ie: pla.type='1' OR pla.type='2' OR pla.type='3' OR pla.type='4'
Cheers,
Keith
--
Johan Höök, Pythagoras Engineering Group
- MailTo:[EMAIL PROTECTED]
- http://www.pythagoras.se
Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden
Phone: +46 8 760 00 10 Fax: +46 8 761 22 77
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
Hi! You seems to have just installed mysql. The root user should be able to acces a mysql server from anywhere by default. I don't know why you can connect from localhost, probably you delete the [EMAIL PROTECTED]? Anyway it is a good thing to delete the root user after the initial setup, once you have a full rights user created that can manage the db. To do this, loginto mysql as root, and create a full-blown user with everything enable and give it a secure password. like this: mysql -u root -h 192.168.1.10 Now create a full rights user like this grant all privileges on *.* to yournewusername_here@% identified by 'passowrdhere' with grant option; flush privileges; This will create a new user with a set of rights comparable with the root's own permissions. Now, with this superuser created you can delete the root user use mysql delete from user where User='root'; flush privileges; Now logout, and login again using the new password and the new user! voilà! Best Regards! On Fri, 2004-03-12 at 09:25, Leslie Vance wrote: Hello, I am having a hard time getting Mysql to let [EMAIL PROTECTED] connect. I am constantly getting this: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) when I issue mysql -u root I can log in if I use mysql -h 192.168.1.10 -u root mysql I guess I have some sort of host thing mixed up but I have tried all sorts of things out of the list with no avail. I using Redhat 9 and I used the rpm's distributed by mysql. Thanks in advance. Les -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data + odbc
The first test i've done was on, lets say, BoxA and it worked just fine. Than, i said,'ok, lets install the client on another WKS' that is BoxB. And from BoxB it's not working. I've got 2 workstations and a server on which i have Mysql 3.23.52 - nt. Lets call the workstations BoxA and BoxB, and the server BoxC. The clients are build in Visual FoxPro and use MyODBC for connecting to server. From both WKS statements like select, insert, update, delete work just fine, but when i want to 'LOAD DATA local INFILE' it works OK only from one WKS. Here's my code open database opreluare CREATE CONNECTION transfer ; DATASOURCE MYSQLSERVER ; USERID incarc PASSWORD incarc ; DATABASE OCUPAT vQuery=LOAD DATA local INFILE 'c:/ocupat/preluare/baza.txt' into table baza FIELDS TERMINATED BY '' r=sqlexec(sqlconnect(transfer),vQuery) and r is -1 after that. h = SqlConnect(transfer) r=sqlexec(h,vQuery) if r0 ? aError( laErrors ) _cliptext = laErrors[1,2] endif Paste the result here. Carl K -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql
hi, When using MyISAM table, the limit will be determined by your operating system, ie. the number of files you can have in one directory. Using InnoDB tables removes that limitation by allowing multiple tables within a single datafile (tablespace). The maximum tablespace size is 4 billion database pages. By default, a 'page' is 16K, but it is possible to recompile with 64K pages. This will allow a single tablespace size of ... 'BIG'. A table will obviously take up at least one database page, meaning you can theoretically have a maximum of 4 billion tables per tablespace. The number of tablespaces will again be limited by your operating system. Using NTFS on NT, the limit is 4,294,967,295. I imagine UNIX will be limited by the number of inodes. However, before reaching the physical limit of files you wil probably run into an issue with the maximum number of 'open' files allowed. --- Victoria Reznichenko [EMAIL PROTECTED] wrote: kavitha kutty [EMAIL PROTECTED] wrote: I am a student currently doing my final year MCA project.My project is in JSP and MySql.I have not studied MySql before.But I got more information about this database from MySql.com.But I have a doubt How many number of tables are possible in one MySql database? Maximum number of tables depends on your filesystem. But you should not put too many tables in a database, as opening tables will slow down. Yahoo! India Insurance Special: Be informed on the best policies, services, tools and more. Go to: http://in.insurance.yahoo.com/licspecial/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment id
Mrs Reznichenko, About the ID problem, I´d create a single table just for ID´s (and ´d keep it as thin as possible). This would avoid the repeating id numbers. Sincerely Leandro da Rocar. [EMAIL PROTECTED] - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 12, 2004 9:35 AM Subject: Re: auto_increment id Stefan Schuster [EMAIL PROTECTED] wrote: Stefan Schuster [EMAIL PROTECTED] wrote: Hi, I have a question about auto_increment: I have 2 tables, on of them holds my online transactions, the other one the offline transactions. Every transaction is created in the first table (call it t1) and then moved to t2. The id is generated using auto_increment. My problem is that the id's should be unique across both tables. If I dont restart the server this works fine, but if I restart it, mySQL assigns the lowest number for the new row in t1, but this id exits already in t2 which results in an error if I try to move from t1 to t2. I hope I explained the problem in a appropriate way. How can I tell mySQL to hold the auto_increment counter for Table t1 persistent ? Do you use InnoDB tables? If so, it's a known behavior. If you restart MySQL server InnoDB may reuse old value for AUTO_INCREMENT column. I need transaction support, as I understand it InnoDB is the only tabletype that supports this. Is there any way to change the behaviour, maybe a table type that has the desired (persistent incrementcounter) behaviour AND supports transactions ? BDB tables are also support transaction, but they are reuse deleted auto_increment values not only if server restarts. For InnoDB tables you can initialize the auto-increment counter by yourself. You should insert into t1 dummy row with max id value from t2 and then remove this row. Something like: INSERT IGNORE INTO db_name.t1(id) SELECT greatest(max(t1.id), max(t2.id)) as id from db_name.t1, db_name.t2; So, with SELECT statement you find greatest value and insert this value into t1 table. If this value is already exists, INSERT statement is ignored. Then you should remove this dummy row: DELETE db_name.t1 FROM db_name.t1, db_name.t2 WHERE db_name.t1.id=db_name.t2.id; Put INSERT and DELETE commands to the file and specify file name with --init-file option of mysqld. MySQL server will read this file at startup: http://www.mysql.com/doc/en/Server_options.html -- 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: 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]
shorten SELECT query
Hi,
I have multiple queries nested inside each other and was wondering, if
there is a way to shorten this thing, e.g. run it within a single query
with subqueries? I couldn't find anything in the subquery section of the
docs or maybe I just don't know how to put it.
At the moment my code fragment looks like this:
$query = SELECT id, showid, date, status FROM tour WHERE
date$daysInMonths AND date$enddate AND ontheroad=0 ORDER BY date ASC;
$result = mysql_query($query);
if ($num = mysql_num_rows($result)) {
echo(table width=\535\ class=\copy\ border=\0\\n);
for($i=0;$i$num;$i++) {
//$id = mysql_result($result,$i,id);
$showid = mysql_result($result,$i,showid);
$date = mysql_result($result,$i,date);
$stat = mysql_result($result,$i,status);
$query3 = SELECT id, statusmsg FROM stat WHERE id=$stat;
$result3 = mysql_query($query3);
$statusmsg = mysql_result($result3,0,statusmsg);
if ($stat != 1) {
$statusmessage = span class=\alert\Hinweis: .
$statusmsg . /span;
}
$showdaynum = strftime('%u', $date);
$showdate = strftime('%d.%m.%Y', $date);
$showtime = strftime('%H:%M', $date);
$query2 = SELECT id, darsteller, showname, pseudo FROM show
WHERE id=$showid;
$result2 = mysql_query($query2);
if ($num2 = mysql_num_rows($result2)) {
$id = mysql_result($result2,0,id);
$darsteller = mysql_result($result2,0,darsteller);
$showname = mysql_result($result2,0,showname);
$pseudo = mysql_result($result2,0,pseudo);
}
}
}
Any ideas to clean this up would be appreciated.
Thanks,
Holger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: link error gcc compiling mysql on solaris 9 (lib not found)
That's a mess of a problem. If the libraries were included in the link, configure must have detected them on your system somehow, but now they are not working. Did you by any chance use --with-mysqld-ldflags=-all-static ? Yes I did, now I realize this doesn't work on Solaris. I tried linking statically because of the error on the mysql-test script from the mysql client. So now I am back to a dynamic compile, which completes just fine and the mysqld starts OK! But now I get an error from the command line mysql: hill pwd /export/home/ken/mysql/mysql-4.0.18/client hill ./mysql ld.so.1: /export/home/ken/mysql/mysql-4.0.18/client/.libs/lt-mysql: fatal: libgcc_s.so.1: open failed: No such file or directory Killed hill But the mysqld daemon seems to star just fine for testing! Thanks for the hints, Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Perfomance issues
Hi folks: I would like to know if there are a perfomance loss using PEAR DB comparing with PHP native Mysql functions, and if this is a significant issue. Also I´m wondering if using persistent connections in PHP could lead to have many connections at the same time slowing the server, and how to handle this. Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query reference help
Sorry, my mistake, I think you need CONCAT( '%[img]', i.image_name, '[/img]%' ) PB - Original Message - From: kris burford To: Peter Brawley ; [EMAIL PROTECTED] Sent: Friday, March 12, 2004 8:06 AM Subject: Re: query reference help SELECT i.id, i.image_name, i.image_width, i.image_height, s.id as story_id, s.title FROM images i LEFT JOIN stories s ON s.body like '[img]' + i.image_name + '[/img]' WHERE i.image_name like '%$search%' ORDER by i.image_name peter wrote: Is ...LIKE '%[img]' + i.image_name + '[/img]%' what you're looking for? unfortunately not. i'd tried this without success and even set up some dummy text in a story with *just* the [img]myimage.jpg[/img] text. still doesn't find it... kris
Re: shorten SELECT query
Do you need something like ...
SELECT tour.id, tour.showid, tour.date, tour.status,
stat.id, stat.statusmesg
FROM tour INNER JOIN stat USING (id)
WHERE tour.date$daysInMonths
AND tour.date$enddate
AND tour.ontheroad=0
ORDER BY tour.date ASC;
?
PB
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, March 12, 2004 9:31 AM
Subject: shorten SELECT query
Hi,
I have multiple queries nested inside each other and was wondering, if
there is a way to shorten this thing, e.g. run it within a single query
with subqueries? I couldn't find anything in the subquery section of the
docs or maybe I just don't know how to put it.
At the moment my code fragment looks like this:
$query = SELECT id, showid, date, status FROM tour WHERE
date$daysInMonths AND date$enddate AND ontheroad=0 ORDER BY date ASC;
$result = mysql_query($query);
if ($num = mysql_num_rows($result)) {
echo(table width=\535\ class=\copy\ border=\0\\n);
for($i=0;$i$num;$i++) {
//$id = mysql_result($result,$i,id);
$showid = mysql_result($result,$i,showid);
$date = mysql_result($result,$i,date);
$stat = mysql_result($result,$i,status);
$query3 = SELECT id, statusmsg FROM stat WHERE id=$stat;
$result3 = mysql_query($query3);
$statusmsg = mysql_result($result3,0,statusmsg);
if ($stat != 1) {
$statusmessage = span class=\alert\Hinweis: .
$statusmsg . /span;
}
$showdaynum = strftime('%u', $date);
$showdate = strftime('%d.%m.%Y', $date);
$showtime = strftime('%H:%M', $date);
$query2 = SELECT id, darsteller, showname, pseudo FROM show
WHERE id=$showid;
$result2 = mysql_query($query2);
if ($num2 = mysql_num_rows($result2)) {
$id = mysql_result($result2,0,id);
$darsteller = mysql_result($result2,0,darsteller);
$showname = mysql_result($result2,0,showname);
$pseudo = mysql_result($result2,0,pseudo);
}
}
}
Any ideas to clean this up would be appreciated.
Thanks,
Holger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Re: query reference help
SELECT i.id, i.image_name, i.image_width, i.image_height, s.id as story_id, s.title FROM images i LEFT JOIN stories s ON s.body like '[img]' + i.image_name + '[/img]' WHERE i.image_name like '%$search%' ORDER by i.image_name peter wrote: Is ...LIKE '%[img]' + i.image_name + '[/img]%' what you're looking for? unfortunately not. i'd tried this without success and even set up some dummy text in a story with *just* the [img]myimage.jpg[/img] text. still doesn't find it... kris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to minimize Master-Slave Traffic during replication - Activating slave_compressed_protocol question
Hello Sasha, thanks for your advise, but I'm not shure whether I understand how to find out if the master is offering (and using) the compressed protocol. When I execute the strings-command, I get can see the string slave_compressed_protocol two times, which I expected running 4.0.18, but if I do a mysqld --help to see the standard values for the variables, I see slave_compressed_protocol FALSE. Do I have to start mysqld with an entry like slave_compressed_protocol=ON in my.cnf or something like this to get the master running with a compressed protocol? Greetings Lutz Maibach - Original Message - From: Sasha Pachev [EMAIL PROTECTED] To: Lutz Maibach [EMAIL PROTECTED] Cc: mysql Liste [EMAIL PROTECTED] Sent: Thursday, March 11, 2004 11:32 PM Subject: Re: How to minimize Master-Slave Traffic during replication? Lutz Maibach wrote: Hi, we got a little problem with a master-slave replication (both running MySQL 4.0.18) eating up our complete bandwidth. The slave is connected via a 2MBit-SDSL-Line which is also used to connect our Office-PCs with the internet so I get complaints about the slow connection which is caused by the huge amount of master-slave traffic through this line. Most of the replication traffic is caused by tables, which are only created once, used for further selects and then deleted. The tables can't be created as temporary tables (that's what our programmers are telling me - I'm only the Admin and don't know whether thei're right or wrong) but are completely useless for replication. Use SET SQL_LOG_BIN=0 on the connection (process privilege needed) to turn off binary logging for the queries that do not need to be replicated to the slave. SET SQL_LOG_BIN=1 to turn logging back on. Does anyone use the slave_compressend_protocol - variable successfully and can tell me, where I can see, whether the slave recognized this switch and do I have to set this switch on the server too? It's a fairly new feature, but it should be very safe - it just enables the use of some very well field tested code. You should set it only on the slave - the slave will tell the master that it wants to use compression. To see if the daemon has it, strings mysqld | grep slave_compression_protocol -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DB init script problem with Grant statements
Hello, I am having a weird problem with some sql that is supposed to set up a DB for me. When I run the .sql file manually from the command line like this: mysql --user=root DB_init.sql It works fine. But when I do the same command from a shell script that is run during the kickstart setup of the server, it seems to be stopping processing of the file after the first grant statement. I changed the command to: mysql --user=root -v -v -v DB_init.sql to try to get some indication of what mysql is doing and why it's stopping, but that didn't tell me much. It just showed me where it was stopping (after the first grant statement), but didn't seem to provide any answer as to why. Here's the DB_init.sql script that I'm trying to run (this is run during kickstart setup to set up a DB and some tables on the server): DB_init.sql begin # create users CONNECT mysql; # perl user REPLACE INTO user (host, user, password) VALUES ( 'localhost', 'vsperl', 'encrpyted passwd' ); REPLACE INTO db (host, db, user, select_priv, insert_priv, update_priv, delete_priv, create_priv, drop_priv) VALUES ( 'localhost', 'RelayStats', 'vsperl', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y' ); # php user REPLACE INTO user (host, user, password) VALUES ( 'localhost', 'vsphp', 'encrpyted passwd' ); REPLACE INTO db (host, db, user, select_priv, insert_priv, update_priv, delete_priv, create_priv, drop_priv) VALUES ( 'localhost', 'RelayStats', 'vsphp', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y' ); # database creation CREATE DATABASE RelayStats; CONNECT RelayStats; # # Table structure for table `host` # CREATE TABLE `host` ( `id` float NOT NULL auto_increment, `name` varchar(255) NOT NULL default '', `ip` varchar(255) default NULL, PRIMARY KEY (`id`), KEY `Name` (`name`) ); # # Table structure for table `stats` # CREATE TABLE `stats` ( `id` float NOT NULL auto_increment, `host_id` float NOT NULL default '0', `virus_id` float NOT NULL default '0', `date` date NOT NULL default '-00-00', `time` time NOT NULL default '00:00:00', PRIMARY KEY (`id`), KEY `Host_Id` (`host_id`,`virus_id`), KEY `date` (`date`), KEY `Timestamp` (`time`) ); # # Table structure for table `virus` # CREATE TABLE `virus` ( `id` float NOT NULL auto_increment, `name` varchar(255) NOT NULL default '', `date_added` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`id`), KEY `Name` (`name`), KEY `date_added` (`date_added`) ); # grant permissions on all the tables #GRANT SELECT, UPDATE, INSERT, DELETE ON table to user; GRANT SELECT, UPDATE, INSERT, DELETE ON host to [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ; GRANT SELECT, UPDATE, INSERT, DELETE ON host to [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ; GRANT SELECT, UPDATE, INSERT, DELETE ON stats to [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ; GRANT SELECT, UPDATE, INSERT, DELETE ON stats to [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ; GRANT SELECT, UPDATE, INSERT, DELETE ON virus to [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ; GRANT SELECT, UPDATE, INSERT, DELETE ON virus to [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ; FLUSH PRIVELEGES; # done. DB_init.sql end And the output from mysql ends like this: begin mysql output -- CREATE TABLE `virus` ( `id` float NOT NULL auto_increment, `name` varchar(255) NOT NULL default '', `date_added` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`id`), KEY `Name` (`name`), KEY `date_added` (`date_added`) ) -- Query OK, 0 rows affected (0.00 sec) -- GRANT SELECT, UPDATE, INSERT, DELETE ON host to [EMAIL PROTECTED] -- Bye end mysql output Does anybody have any idea why it's just stopping after that first Grant statement? And why it works when I run it manaully, but for some unknown reason doesn't work during the kickstart install? thx! k
Re: mysql-4.0.18 build problem in FreeBSD-5.2-CURRENT
Hi Ganbold, I think you are asking in the wrong place. I would suggest [EMAIL PROTECTED] or [EMAIL PROTECTED] or [EMAIL PROTECTED] see . But my personal thoughts on this is if you are not ready for these types of issues don't track freebsd-current. This is bleeding edge stuff. FreeBSD-stable or FreeBSD 4.9 for production or even 5.2. I know there have been massive threads changes on -current. see also http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/eresources.html#ERESOURCES-MAIL Hope that helps, Ken - Original Message - From: Ganbold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 11, 2004 10:47 PM Subject: mysql-4.0.18 build problem in FreeBSD-5.2-CURRENT Hi, Today I wanted to upgrade mysql-4.0.17 to mysql-4.0.18 in FreeBSD-5.2-CURRENT and got error below. I used following options to compile from ports collection: make WITH_CHARSET=cp1251 WITH_LINUXTHREADS=yes BUILD_STATIC=yes install Error message: -- --- /usr/lib/libc.a(res_init.o): In function `__h_error': res_init.o(.text+0x1104): multiple definition of `__h_error' /usr/local/lib/liblthread.a(errno.o):/usr/ports/devel/linuxthreads/wor k/linuxthreads-2.2.3_14/errno.c:29: first defined here /usr/bin/ld: Warning: size of symbol `__h_error' changed from 88 to 36 in /usr/lib/libc.a(res_init.o) *** Error code 1 Stop in /usr/ports/databases/mysql40-server/work/mysql-4.0.18/sql. *** Error code 1 Stop in /usr/ports/databases/mysql40-server/work/mysql-4.0.18/sql. *** Error code 1 Stop in /usr/ports/databases/mysql40-server/work/mysql-4.0.18/sql. *** Error code 1 Stop in /usr/ports/databases/mysql40-server/work/mysql-4.0.18. *** Error code 1 Stop in /usr/ports/databases/mysql40-server/work/mysql-4.0.18. *** Error code 1 Stop in /usr/ports/databases/mysql40-server. --- What should do in this case? Is there anybody solved this problem before? tia, Ganbold -- 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]
Can table structure cause table corruption?
I am having some problems with constant table corruption on a database. I have now had this occur on two different operating systems with two different versions of MySQL. I edit these tables using Phpmyadmin (again two different version) and eventually, either after 1-5 actions, the table gets corrupted and get a message like this: [EMAIL PROTECTED]:d2 MBPlatforms]myisamchk -s *.MYI myisamchk: ISAM file Manus.MYI myisamchk: error: Size of datafile is: 620 Should be: 640 myisamchk: error: Record-count is not ok; is 16 Should be: 15 myisamchk: warning: Found 16 partsShould be: 17 parts MyISAM-table 'Manus.MYI' is corrupted Fix it using switch -r or -o And then something like this in the mysql.log file: 040312 01:58:40 mysqld started /usr/libexec/mysqld: ready for connections 040312 2:01:59 read_const: Got error 127 when reading table ./MBPlatforms/IOs 040312 2:01:59 read_const: Got error 127 when reading table ./MBPlatforms/IOs 040312 2:02:09 read_const: Got error 127 when reading table ./MBPlatforms/IOs And this from PHPmyAdmin: Error SQL-query : SELECT * FROM `Manus` LIMIT 0, 30 MySQL said: Got error 127 from table handler I have included my table structures below (sorry its long!): CREATE TABLE `AGPs` ( `ID` int(11) NOT NULL auto_increment, `Name` varchar(20) NOT NULL default '', `Desc` text NOT NULL, PRIMARY KEY (`ID`) ) TYPE=MyISAM AUTO_INCREMENT=4 ; # # # Table structure for table `Audios` # CREATE TABLE `Audios` ( `ID` int(11) NOT NULL auto_increment, `Name` varchar(50) NOT NULL default '', `Desc` text NOT NULL, PRIMARY KEY (`ID`) ) TYPE=MyISAM AUTO_INCREMENT=14 ; # # # Table structure for table `FormFactors` # CREATE TABLE `FormFactors` ( `ID` int(11) NOT NULL auto_increment, `Name` varchar(50) NOT NULL default '', `Desc` text NOT NULL, PRIMARY KEY (`ID`) ) TYPE=MyISAM AUTO_INCREMENT=3 ; # # # Table structure for table `IOs` # CREATE TABLE `IOs` ( `ID` int(11) NOT NULL auto_increment, `Name` varchar(50) NOT NULL default '', `Desc` text NOT NULL, PRIMARY KEY (`ID`) ) TYPE=MyISAM AUTO_INCREMENT=6 ; # # # Table structure for table `Manus` # CREATE TABLE `Manus` ( `ID` int(11) NOT NULL auto_increment, `Name` varchar(50) NOT NULL default '', `Desc` text NOT NULL, PRIMARY KEY (`ID`) ) TYPE=MyISAM AUTO_INCREMENT=19 ; # # # Table structure for table `MemorySlots` # CREATE TABLE `MemorySlots` ( `ID` int(11) NOT NULL auto_increment, `Name` varchar(50) NOT NULL default '', `Desc` text NOT NULL, PRIMARY KEY (`ID`) ) TYPE=MyISAM AUTO_INCREMENT=7 ; # # # Table structure for table `Memorys` # CREATE TABLE `Memorys` ( `ID` int(11) NOT NULL auto_increment, `Name` varchar(50) NOT NULL default '', `Desc` text NOT NULL, PRIMARY KEY (`ID`) ) TYPE=MyISAM AUTO_INCREMENT=8 ; # # # Table structure for table `Motherboards` # CREATE TABLE `Motherboards` ( `ID` int(11) NOT NULL auto_increment, `ManuID` int(11) NOT NULL default '0', `NorthBridgeID` int(11) NOT NULL default '0', `SouthBridgeID` int(11) NOT NULL default '0', `MemoryID` int(11) NOT NULL default '0', `PlatformID` int(11) NOT NULL default '0', `AudioID` int(11) NOT NULL default '0', `NetworkID` int(11) NOT NULL default '0', `PCIID` int(11) NOT NULL default '0', `PCIeID` int(11) NOT NULL default '0', `SATAID` int(11) NOT NULL default '0', `RAIDID` int(11) NOT NULL default '0', `MemorySlotID` int(11) NOT NULL default '0', `Name` varchar(50) NOT NULL default '', `AGPID` int(11) NOT NULL default '0', `FormFactorID` int(11) NOT NULL default '0', `Review` varchar(150) NOT NULL default '', `Image` varchar(150) default NULL, `IOID` int(11) NOT NULL default '0', `Misc` text NOT NULL, PRIMARY KEY (`ID`) ) TYPE=MyISAM AUTO_INCREMENT=128 ; # # # Table structure for table `Networks` # CREATE TABLE `Networks` ( `ID` int(11) NOT NULL auto_increment, `Name` varchar(50) NOT NULL default '', `Desc` text NOT NULL, PRIMARY KEY (`ID`) ) TYPE=MyISAM AUTO_INCREMENT=10 ; # # # Table structure for table `NorthBridges` # CREATE TABLE `NorthBridges` ( `ID` int(11) NOT NULL auto_increment, `ManuID` int(11) NOT NULL default '0', `Name` varchar(50) NOT NULL default '', `Desc` text NOT NULL, PRIMARY KEY (`ID`) ) TYPE=MyISAM AUTO_INCREMENT=27 ; # # # Table structure for table `PCIes` # CREATE TABLE `PCIes` (
Auditing
I'd like to be able to audit connections to mysql databases. Here is what I'd like to see: Who connected to the server. What IP did they come from. When they disconnected. Any permission denied errors they may have had. The only thing that comes close to that is the general log, which is quite a bit of overhead. Has anyone come up with a solution for this? Does anyone see this in future versions? I've attempted the general query log, but it's not really in the best format for generating reports. Additionally, it's quite bloated when you do 300q/s. I've tried pushing it to a fifo, so I can have a daemon get the information, but mysql doesn't support that either. (my.cnf:log=/var/log/mysql.fifo) Has anyone else found solutions for this? Thanks! Dan Vande More -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Unicode Data and C API
Hello, From MySQL 4.1, MySQL supports storing of data in utf-8 and ucs2. But all the C API till supports only char*. This has brought up some issues in my mind: 1.) How do you I send Unicode data to MySQL server using C API()? Should I convert the query to utf-8 or double byte etc.? 2.) How are Unicode data returned to the client? MYSQL_ROW is defined to be char? Are they sent in utf-8? Sorry for my ignorance but my app is written with Unicode support and all the string functions require Unicode data so should I convert them to utf-8 before sending them to C API and convert them to Unicode character after fetching them? Thanks for your help. regards karam __ Do you Yahoo!? Yahoo! Search - Find what youre looking for faster http://search.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow query with like clause (repost)
Posted this yesterday, but it never showed up...? Hi, I have the following table (sorry if the formatting gets tweaked) and query. The table is around 36k rows long, and the query returns about 350 rows. The query takes 12.15 seconds. Is there any way I can speed this up? Thanks, Bryan mysql desc locus_anno_3_blat; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | subsnp_fk | int(11) unsigned | | MUL | 0 | | | locus | varchar(15) | | MUL | | | | locus_orig | varchar(15) | | MUL | | | | locusid | int(11) | | MUL | 0 | | | snptype | varchar(30) | YES | MUL | NULL| | | allele | char(1) | YES | | NULL| | | frame | smallint(6) | YES | | NULL| | | residue | char(1) | YES | | NULL| | | aa_position | int(11) | YES | | NULL| | | locusChrom | varchar(20) | YES | MUL | NULL| | | locusStart | int(11) unsigned | YES | | NULL| | | locusEnd| int(11) unsigned | YES | | NULL| | +-+--+--+-+-+---+ 12 rows in set (0.00 sec) mysql show index from locus_anno_3_blat; +---++-+--+- +---+-+--++--++- + | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++-+--+- +---+-+--++--++- + | locus_anno_3_blat | 1 | fk_ix |1 | subsnp_fk | A | 2308025 | NULL | NULL | | BTREE | | | locus_anno_3_blat | 1 | name_ix |1 | locus_orig | A | 21570 | NULL | NULL | | BTREE | | | locus_anno_3_blat | 1 | locusid_ix |1 | locusid | A | 25644 | NULL | NULL | | BTREE | | | locus_anno_3_blat | 1 | chrs_ix |1 | locusChrom | A | 25 |3 | NULL | YES | BTREE | | | locus_anno_3_blat | 1 | chrs_ix |2 | locusStart | A | 25362 | NULL | NULL | YES | BTREE | | | locus_anno_3_blat | 1 | chre_ix |1 | locusChrom | A | 25 |3 | NULL | YES | BTREE | | | locus_anno_3_blat | 1 | chre_ix |2 | locusEnd| A | 25362 | NULL | NULL | YES | BTREE | | | locus_anno_3_blat | 1 | locus_ix|1 | locus | A | 25087 | 10 | NULL | | BTREE | | | locus_anno_3_blat | 1 | l_a_3_b_snptype |1 | snptype | A | 9 | NULL | NULL | YES | BTREE | | +---++-+--+- +---+-+--++--++- + 9 rows in set (0.00 sec) And I am trying to do the following simple query: select distinct locus from locus_anno_3_blat where locus like 'A%' order by locus; mysql explain select distinct locus from locus_anno_3_blat where locus like 'A%' order by locus; +---+---+---+--+-+--+--- -+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+--+-+--+--- -+-+ | locus_anno_3_blat | range | locus_ix | locus_ix | 10 | NULL | 117253 | where used; Using temporary; Using filesort | +---+---+---+--+-+--+--- -+-+ 1 row in set (0.04 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
two masters, one slave
Is it possible to have two masters and one slave? I'm trying to replicate two master databases so that I have a failover. The alternate, I guess, would be to have two instances of mysql listing on different ports on the slave server platform? ari Ari Davidow [EMAIL PROTECTED] http://www.ivritype.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: two masters, one slave
A slave can connect to only one master. -Original Message- From: Ari Davidow To: [EMAIL PROTECTED] Sent: 3/12/04 12:30 PM Subject: two masters, one slave Is it possible to have two masters and one slave? I'm trying to replicate two master databases so that I have a failover. The alternate, I guess, would be to have two instances of mysql listing on different ports on the slave server platform? ari Ari Davidow [EMAIL PROTECTED] http://www.ivritype.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: two masters, one slave
Not at this time, however you could have two different slaves on the same physical server using mysql_mutli type of configuration. http://www.mysql.com/doc/en/mysqld_multi.html Hope it helps Ken - Original Message - From: Ari Davidow [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 12, 2004 1:30 PM Subject: two masters, one slave Is it possible to have two masters and one slave? I'm trying to replicate two master databases so that I have a failover. The alternate, I guess, would be to have two instances of mysql listing on different ports on the slave server platform? ari Ari Davidow [EMAIL PROTECTED] http://www.ivritype.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: link error gcc compiling mysql on solaris 9 (lib not found)
OK, sorry to answer my own post, libgcc was is /usr/local/lib which did not get properl added with crle! crle -u -l /usr/local/lib Problem gone! Sorry to waste bandwidth (we are just learning solaris!). Ken - Original Message - From: Ken Menzel [EMAIL PROTECTED] To: Sasha Pachev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, March 12, 2004 10:33 AM Subject: Re: link error gcc compiling mysql on solaris 9 (lib not found) That's a mess of a problem. If the libraries were included in the link, configure must have detected them on your system somehow, but now they are not working. Did you by any chance use --with-mysqld-ldflags=-all-static ? Yes I did, now I realize this doesn't work on Solaris. I tried linking statically because of the error on the mysql-test script from the mysql client. So now I am back to a dynamic compile, which completes just fine and the mysqld starts OK! But now I get an error from the command line mysql: hill pwd /export/home/ken/mysql/mysql-4.0.18/client hill ./mysql ld.so.1: /export/home/ken/mysql/mysql-4.0.18/client/.libs/lt-mysql: fatal: libgcc_s.so.1: open failed: No such file or directory Killed hill But the mysqld daemon seems to star just fine for testing! Thanks for the hints, Ken -- 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: Security
Usernames, passwords, and then perform the queries select ... where customerid = the variable name you feed Its all handled by your app. Curtis On Wed, 10 Mar 2004, Mulugeta Maru wrote: Hi Mike, I am sorry for the confusion I might have caused. May be it would help to give a clear example. Table - Customers (CustomerID, CustomerName, Address, etc) Table - Transaction(TransactionID,CustomerID,Date,Amount) Note: CustomerID in Customer Table is a Primary Key. TransactionID is a Primary Key and CustomerID is a Foreign Key in Transaction Table). Question: How would I be able to give my customers access to the database so that they can update the customer table (for example address change) and add transactions to the transaction table. What I do not want to happen is that customer A is able to modify customer B's record. In short how would you restrict customer a to see transactions that pertain to him/her. Many thanks. - Original Message - From: Mike Johnson [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Wednesday, March 10, 2004 4:55 PM Subject: RE: Security From: Maru, Mulugeta [mailto:[EMAIL PROTECTED] When I go online to access my bank account I only see transactions pertain to my account only. I think when ever I make a transaction the database records my account number in the transaction table. When I log-in using my account number and password the system checks whether it is correct or not and run another query to get all transaction that match my account number. Do I make sense? (sent offlist by mistake, please excuse the dupe) The point being made is that you're looking at your bank account information in a client that is set to read records only pertaining to your account. The native mysql client is not such a program and was never intended to be. While you can customize access for users to certain databases or certain tables within those databases, it's simply not built as a multi-user transactional client for limiting access to data in commonly-used tables. It begs the question why you're giving your clients access to the native mysql client itself rather than developing an application to do this, in which you could quite easily limit such access. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General Help - ERROR 1045: Access denied for user: 'ackerley@localhost' Using password: YES
Can someone please help me sort this out. System info: Windows XP Apache Server Situation: I have done a fresh install of MySQL 4.0.18 using the windows installer. Everything seems to go fine. I execute the WinMySQLAdmin and it prompts me for a user name and password. I enter this info and click okay. It then opens up the Admin window. The Environment tab main window gives me this message: Not Found Driver 3.51 Not Found Anyhow, I open up my command prompt and change the directory to c:\mysql\bin From there I type mysql -u ackerley -p Enter Prompts for password and I enter it. Then it beeps and gives me this error: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' Using password: YES I am at a loss as to what to do. Please help. Thanks Rob
Re: General Help - ERROR 1045: Access denied for user: 'ackerley@localhost' Using password: YES
Hello Rob, Friday, March 12, 2004, 7:39:47 PM, you wrote: RA ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' Using password: YES Does that user actually exist within MySQL? I have you used the mysqladmin program and created a user account called ackerley? If not, that's why you can't get in. Try using root and giving nothing as the password. If that lets you in, create yourself the ackerley user. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: General Help - ERROR 1045: Access denied for user: 'ackerley@localhost' Using password: YES
Is this user a super user, or do they only have access to a single DB? If they are a super user, try changing the password using mysqladmin -u ackerley password 'newpassword' If this user only has access to a single db, alter your mysql command to: shell mysql -u ackerley -p 'database_name' J.R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving file into database
On Fri, 12 Mar 2004, Jigal van Hemert wrote: I've been reading this thread, but I can't see the advantages of storing files in a database. I've always had the impression that a *file* system was the appropriate place to store files. Scalability, searching, security.. just a few.. Replication seems more silly, since one also copies the stored files to the slave. This not only adds to the traffic between slave and master, but also wastes diskspace. Disk is relatively cheap.. And if your using mysql file storage on a large site you'll probably need replication to feed those data hungry frontend webservers.. Furthermore large files may cause you to hit the max_allowed_packet size (PDF's of 5MB or larger are no exception in real life). Anyone implementing mysql file storage using largeblobs, I feed needs to re-address their storage implementation. I've built a system which includes the possibility of downloading one's own bills in PDF format. This system runs on load balanced webservers with one mysql server. The files are stored on a different machine that cannot be accessed directly from the web. Scripts validate access to the PDF and serve it to the client. Works like a charm ;-) Sweet.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: General Help - ERROR 1045: Access denied for user: 'ackerley@localhost' Using password: YES
Then like Richard Davey sent earlier to the list, try: shell mysql -u root If you haven't created the users yet with the mysql tool, then your user won't have access to it. First, change the root password using: shell mysqladmin -u root password 'new_password' Then log into mysql and use: mysql Create database 'database_name'; mysql grant all on 'database_name'.* to 'username'@'%' identified by 'password' using grant option; You can also download the MySQL Control Center to do this for you if you don't want to mess with the command line options. (www.mysql.com/downloads/index.html) The ODBC error that you are getting is simply because the WinMySQLAdmin tool doesn't see that you are using it yet. If you haven't already, download and install it. Then add it to a System DSN and use Access (or other program) to view your database via linked tables. Then you will see that WinMySQLAdmin will see your ODBC connector. J.R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Odd Table locking issue
If you've read my last email, you know what the problem is, but I have now
pinpointed it a bit further using the processlist feature.
[EMAIL PROTECTED] MBPlatforms]# mysqladmin -v processlist -p
Enter password:
+-+--+---+-+-+--++---+
| Id | User | Host | db | Command | Time | State | Info
|
+-+--+---+-+-+--++---+
| 75 | pcpadmin | localhost | MBPlatforms | Query | 357 | update | INSERT
INTO MemorySlots VALUES ('6', '8 slots', 'There are 8 slots.') |
| 86 | pcpadmin | localhost | MBPlatforms | Query | 311 | Locked | SELECT *
FROM MemorySlotsORDER BY Name ASC |
| 154 | root | localhost | | Query | 0|| show full
processlist |
+-+--+---+-+-+--++---+
This also happens with the REPLACE command seen here. The state is locked but
it doesn't come out of it until I manually kill the thread.
What could be causing this? Here is the table design, just FWIW:
Field Type Attributes Null Default Extra Action
ID int(11) Noauto_increment
Name varchar(50) No
Desc text No
Thanks for your help!
Ryan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query with IF acting wierd.
Mike Johnson wrote: From: Mike Johnson SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main, IF(main.type,items,categories) WHERE IF(main.type,categories.id,items.id)=main.id; Oh, my mistake. I just realized I reversed items and categories in the IF clause. Try this instead: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main, IF(main.type,categories,items) WHERE IF(main.type,categories.id,items.id)=main.id; Sorry! Actually, now I'm really curious if this works or not. Let me know how it turns out. Unfortunately, that won't work. IF can return a value, not a column reference. As I understand it, you want to join main to categories for rows where main.type is 1, but you want to join main to items for rows where main.type is 0. As Mike explained, these are two separate 2-table joins, not one 3-table join. You didn't say which version of mysql you have. If you have at least 4.0.0, you can combine the results of 2 selects with UNION, so this should work: SELECT main.id, categories.name, 'cat ' AS type FROM main, categories WHERE main.id = categories.id AND main.type UNION SELECT main.id, items.name, 'item' AS type FROM main, items WHERE main.id = items.id AND NOT main.type; If you are still using 3.23.x, you can accomplish the same thing with a temporary table. CREATE TEMPORARY TABLE joins SELECT main.id, categories.name, 'cat ' AS type FROM main, categories WHERE main.id = categories.id AND main.type; INSERT INTO joins SELECT main.id, items.name, 'item' AS type FROM main, items WHERE main.id = items.id AND NOT main.type; SELECT * FROM joins; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql error grant tables, help
get this when i run scripts/mysql_install_db. also tried mysqld --skip-grant and got the same thing. how do i fix? mysql-standard-4.0.18-sun-solaris2.8-sparc binary on SunOS ipdev1 5.8 Generic_108528-06 sun4u sparc SUNW,Ultra-5_10. [EMAIL PROTECTED]:/usr/local/mysql $ scripts/mysql_install_db Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables Unknown suffix '@' used for variable 'port' (value '@MYSQL_TCP_PORT@') ./bin/mysqld: Error while setting value '@MYSQL_TCP_PORT@' to 'port' Installation of grant tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: ./bin/mysqld --skip-grant You can use the command line tool ./bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the ./bin/mysqlbug script! [EMAIL PROTECTED]:/usr/local/mysql --skip-grant// [EMAIL PROTECTED]:/usr/local/mysql $ ./bin/mysqld --skip-grant @ Unknown suffix '@' used for variable 'port' (value '@MYSQL_TCP_PORT@') ./bin/mysqld: Error while setting value '@MYSQL_TCP_PORT@' to 'port' [EMAIL PROTECTED]:/usr/local/mysql $ ./bin/mysqld --skip-grant [1] 21116 [EMAIL PROTECTED]:/usr/local/mysql $ Unknown suffix '@' used for variable 'port' (value '@MYSQL_TCP_PORT@') ./bin/mysqld: Error while setting value '@MYSQL_TCP_PORT@' to 'port' [1]+ Exit 9 ./bin/mysqld --skip-grant [EMAIL PROTECTED]:/usr/local/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
utf8 - left function
Hi, I need this query; SELECT LEFT(FieldName, 1) AS FirstLetter, COUNT(*) FROM TableName GROUP BY FirstLetter my datas in utf8 format. I can't get first characters of the datas in utf8 format but others is ok. Any suggestion? Thank you
Re: Security
Thank you very much. Makes sense. - Original Message - From: Curtis Maurand [EMAIL PROTECTED] To: Mulugeta Maru [EMAIL PROTECTED] Cc: Mike Johnson [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Friday, March 12, 2004 1:17 PM Subject: Re: Security Usernames, passwords, and then perform the queries select ... where customerid = the variable name you feed Its all handled by your app. Curtis On Wed, 10 Mar 2004, Mulugeta Maru wrote: Hi Mike, I am sorry for the confusion I might have caused. May be it would help to give a clear example. Table - Customers (CustomerID, CustomerName, Address, etc) Table - Transaction(TransactionID,CustomerID,Date,Amount) Note: CustomerID in Customer Table is a Primary Key. TransactionID is a Primary Key and CustomerID is a Foreign Key in Transaction Table). Question: How would I be able to give my customers access to the database so that they can update the customer table (for example address change) and add transactions to the transaction table. What I do not want to happen is that customer A is able to modify customer B's record. In short how would you restrict customer a to see transactions that pertain to him/her. Many thanks. - Original Message - From: Mike Johnson [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Wednesday, March 10, 2004 4:55 PM Subject: RE: Security From: Maru, Mulugeta [mailto:[EMAIL PROTECTED] When I go online to access my bank account I only see transactions pertain to my account only. I think when ever I make a transaction the database records my account number in the transaction table. When I log-in using my account number and password the system checks whether it is correct or not and run another query to get all transaction that match my account number. Do I make sense? (sent offlist by mistake, please excuse the dupe) The point being made is that you're looking at your bank account information in a client that is set to read records only pertaining to your account. The native mysql client is not such a program and was never intended to be. While you can customize access for users to certain databases or certain tables within those databases, it's simply not built as a multi-user transactional client for limiting access to data in commonly-used tables. It begs the question why you're giving your clients access to the native mysql client itself rather than developing an application to do this, in which you could quite easily limit such access. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backward compatable?
I am using MySQL 5.0 at the moment with a number of databases. These databases are created to learn MySQL. Some of the tables are Innodb. I would like to remove MySQL 5.0 and install 4.0.18. What do I need to do to reserve the databases so that I can continue to use them in 4.0.18. Thank you. Maru
Uninstall MySQL for Panther?
I am two days into troubleshooting this. I need some help badly. I installed MySQL 4.0.18 for Mac. I was able to get in at first: Welcome to MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 to server version: 4.0.18-standard then I would type mysql create database mytest; and get: ERROR 1044: Access denied for user: '@localhost' to database 'mytest' I tried all kinds of commands to change the root password, and every single one would result in Access denied for user . . . I would like to install, instead, CompleteMySQL from the serverlogisitics.com site, which has interfaces to help newbies like me. However, it is only at version 4.0.15. So my question is: How can I UNINSTALL the current 4.0.18 version I already have, before installing the other one? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Uninstall MySQL for Panther?
John Mistler wrote: I installed MySQL 4.0.18 for Mac. I was able to get in at first: I tried all kinds of commands to change the root password, and every single one would result in Access denied for user . . . I would like to install, instead, CompleteMySQL from the serverlogisitics.com site, which has interfaces to help newbies like me. No, no! Be strong, you don't need the training wheels :-) Re-read the doc about grant tables and then restart the server with the `--skip-grant-tables` flag so you can reset the root password. http://www.mysql.com/doc/en/Resetting_permissions.html HTH, -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Uninstall MySQL for Panther?
At 18:33 -0800 3/12/04, John Mistler wrote: I am two days into troubleshooting this. I need some help badly. I installed MySQL 4.0.18 for Mac. I was able to get in at first: Welcome to MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 to server version: 4.0.18-standard Please show us the command you entered to start the mysql program. then I would type mysql create database mytest; and get: ERROR 1044: Access denied for user: '@localhost' to database 'mytest' The account name (@localhost) has no username before the '@' character, which indicates that you have connected as the anonymous user. This user has no privileges to create the mytest database. I tried all kinds of commands to change the root password, and every single one would result in Access denied for user . . . Please show what these commands were. No one can help you diagnose the problem without information to go on. I would like to install, instead, CompleteMySQL from the serverlogisitics.com site, which has interfaces to help newbies like me. However, it is only at version 4.0.15. So my question is: How can I UNINSTALL the current 4.0.18 version I already have, before installing the other one? Well ... I'm afraid we cannot tell you that, either, because although you've indicated that you installed MySQL 4.0.18 for Mac, you haven't told us how you installed it. Did you use the PKG distribution? Did you install from source? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Union Query Sorting
Can anyone tell me how to sort the combined results of a Union query? Right now I have a Group By and Order By in both of my queries that make up my Union but the results are grouped and sorted within the two queries but not between the two queries. The example below demonstrates my problem. In the results the first four people are employees and the last three are consultants (Select Name From Employee Group By Name Order By Name) Union (Select Name From Consultant Group By Name Order By Name); Results, A Person John Doe Paul Dude Ronald McDonald Beer Drinker Mayor McCheese Pizza Eater What I want is both queries grouped and sorted together. So the Results should look like this, A Person Beer Drinker John Doe Mayor McCheese Paul Dude Pizza Eater Ronald McDonald Can anyone help. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backward compatable?
Mulugeta Maru wrote: I am using MySQL 5.0 at the moment with a number of databases. These databases are created to learn MySQL. Some of the tables are Innodb. I would like to remove MySQL 5.0 and install 4.0.18. What do I need to do to reserve the databases so that I can continue to use them in 4.0.18. Thank you. Maru Dump all your databases with 'mysqldump' and then import them into your new installation. Read up on mysqldump. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: utf8 - left function
Hi, I need this query; SELECT LEFT(FieldName, 1) AS FirstLetter, COUNT(*) FROM TableName GROUP BY FirstLetter my datas in utf8 format. I can't get first characters of the datas in utf8 format but others is ok. Any suggestion? Thank you It works for me. I'm using the development source 4.1.2. Maybe the problem has already been fixed. You are using a 4.1.x version, right? It definitely would not work before 4.1. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Union Query Sorting
Can anyone tell me how to sort the combined results of a Union query? (Select Name From Employee Group By Name Order By Name) Union (Select Name From Consultant Group By Name Order By Name); Just add another order by on the end after the parenthesis: (Select Name From Employee Group By Name Order By Name) Union (Select Name From Consultant Group By Name Order By Name) ORDER BY Name; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem selecting from 3 tables
I'm having trouble with sql statement to select from 3 tables.
The first column is in ProductsPics and the rest in Products.
(qq{SELECT PictureName, ProdName, Description, SalesPrice FROM Products
INNER JOIN
ProdCategory USING(CategoryNr) INNER JOIN ProductsPics
USING(ProdNr) WHERE Products.CategoryNr
= ProdCategory.CategoryNr AND Products.ProdNr =
ProductsPics.ProdNr
AND ProdCategory.Category = $category});
Thanks for any help
Babs
Re: Uninstall MySQL for Panther?
First I want to thank all of you guys for responding. I feel a new sense of confidence that I will be able to get this thing going! However, my problems have compounded. We now have a new problem. I took the advice of one archived postings and dragged the four files seemingly installed by the mysql package at the location (in Mac language) users:johnmistler:the four files to the trash. That didn't feel right to me once I did it, so I dragged them back. They were named: 1. cd 2. md5 3. md5 sum 4. mysql Now, when I go to the terminal and type: /usr/local/mysql/bin/mysql I get: tcsh: /usr/local/mysql/bin/mysql: Command not found This suggests to me that the mysql file it is being directed to is not there. However, I can verify that the file mysql exists at this path (in Mac Language): users:johnmistler:mysql It is listed as a document with Zero KB for its size (?). It definitely is one of the files that I dragged back from the trash. Should I attempt to fix all of this, or go ahead with an uninstall? If I do need to uninstall, how do I do it? I installed the latest PACKAGE from the MySQL.com site 4.0.18-standard along with the startup item package. If you guys still think I need to tough it out, what is the next step? Once again (embarrassingly) -- THANK YOU! John on 3/12/04 7:22 PM, Paul DuBois at [EMAIL PROTECTED] wrote: At 18:33 -0800 3/12/04, John Mistler wrote: I am two days into troubleshooting this. I need some help badly. I installed MySQL 4.0.18 for Mac. I was able to get in at first: Welcome to MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 to server version: 4.0.18-standard Please show us the command you entered to start the mysql program. then I would type mysql create database mytest; and get: ERROR 1044: Access denied for user: '@localhost' to database 'mytest' The account name (@localhost) has no username before the '@' character, which indicates that you have connected as the anonymous user. This user has no privileges to create the mytest database. I tried all kinds of commands to change the root password, and every single one would result in Access denied for user . . . Please show what these commands were. No one can help you diagnose the problem without information to go on. I would like to install, instead, CompleteMySQL from the serverlogisitics.com site, which has interfaces to help newbies like me. However, it is only at version 4.0.15. So my question is: How can I UNINSTALL the current 4.0.18 version I already have, before installing the other one? Well ... I'm afraid we cannot tell you that, either, because although you've indicated that you installed MySQL 4.0.18 for Mac, you haven't told us how you installed it. Did you use the PKG distribution? Did you install from source? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backward compatable?
I was ignorant. The information is readily available at MySQL site. mysqldump -u root -p --opt accounting Thank you for your help. - Original Message - From: Daniel Kasak [EMAIL PROTECTED] To: Mulugeta Maru [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Friday, March 12, 2004 9:36 PM Subject: Re: Backward compatable? Mulugeta Maru wrote: I am using MySQL 5.0 at the moment with a number of databases. These databases are created to learn MySQL. Some of the tables are Innodb. I would like to remove MySQL 5.0 and install 4.0.18. What do I need to do to reserve the databases so that I can continue to use them in 4.0.18. Thank you. Maru Dump all your databases with 'mysqldump' and then import them into your new installation. Read up on mysqldump. Dan -- 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: Problem selecting from 3 tables
I'm having trouble with sql statement to select from 3 tables.
The first column is in ProductsPics and the rest in Products.
(qq{SELECT PictureName, ProdName, Description, SalesPrice FROM Products
INNER JOIN
ProdCategory USING(CategoryNr) INNER JOIN ProductsPics
USING(ProdNr) WHERE Products.CategoryNr
= ProdCategory.CategoryNr AND Products.ProdNr =
ProductsPics.ProdNr
AND ProdCategory.Category = $category});
No need to write the join conditions twice in the query. Is this what you
want...
SELECT PictureName, ProdName, Description, SalesPrice
FROM Products
INNER JOIN ProdCategory USING(CategoryNr)
INNER JOIN ProductsPics USING(ProdNr)
WHERE ProdCategory.Category = $category;
ie only rows with matches across the 3 tables, scoped by $category?
PB
List of fields
describe table; almost does what I need, how would I get a nice clean unformed list back, perhaps \r\n separated. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Uninstall MySQL for Panther?
on 03/12/2004 09:31 PM, John Mistler at [EMAIL PROTECTED] wrote: First I want to thank all of you guys for responding. I feel a new sense of confidence that I will be able to get this thing going! However, my problems have compounded. We now have a new problem. I took the advice of one archived postings and dragged the four files seemingly installed by the mysql package at the location (in Mac language) users:johnmistler:the four files to the trash. That didn't feel right to me once I did it, so I dragged them back. They were named: 1. cd 2. md5 3. md5 sum 4. mysql I don't know what those files are, they are not part of where mysql should have installed its stuff, so there should in theory be no hard in removing them. You can remove mysql by doing the following rm -r /usr/local/mysql rm -r /Library/StartupItems/MySQL This will get you back to a clean machine with no mysql, from there, run the installer and this time follow the instructions exactly on how to start mysql as well as how to set the root password to the mysql permissions database. If you still get stuck, drop me a email directly and I can walk you thought it. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update one field with more fields from another table
Dear mysql user
I found this example in the doc of mysql. I have a similar problem. Is
there any solution to this?
Thanks
Update one field with more fields from another table
Table A
++---+
| A-num | text |
|1 | |
|2 | |
|3 | |
|4 | |
|5 | |
++---+
Table B:
+--+--+--+
| B-num| date| A-num |
| 22 | 01.08.2003 | 2 |
| 23 | 02.08.2003 | 2 |
| 24 | 03.08.2003 | 1 |
| 25 | 04.08.2003 | 4 |
| 26 | 05.03.2003 | 4 |
I will update field text in table A
with
UPDATE `Table A`,`Table B`
SET `Table A`.`text`=concat_ws('',`Table A`.`text`,`Table B`.`B-num`, from
,`Table B`.`date`,'/')
WHERE `Table A`.`A-num` = `Table B`.`A-num`
and come to this result
Table A
+++
| A-num | text |
|1 | 24 from 03 08 2003 / |
|2 | 22 from 01 08 2003 / |
|3 ||
|4 | 25 from 04 08 2003 / |
|5 ||
+-+
(only one field from Table B is accepted)
But i will come to this result
Table A
+++
| A-num | text |
|1 | 24 from 03 08 2003|
|2 | 22 from 01 08 2003 / 23 from 02 08 2003 / |
|3 ||
|4 | 25 from 04 08 2003 / 26 from 05 03 2003 / |
|5 ||
+++
Department of Genetics
Faculty of Veterinary Medicine
University of Liege
Bd de Colonster B43
B4000 Liege, Belgium
32-43-66.41.59
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
