Re: Select * from multiple tables
At 1:23 -0400 6/26/03, [EMAIL PROTECTED] wrote: I grown my db to 4 tables 8). I'm going to ask this plainly in hopes that my syntax in ok: I know how to SELECT * from 2 related tables and get all the records listed in the resultset. (Either using INNER JOIN or WHERE.) Now... and I have been looking some books! How do get a resultset of all records from 4 related tables? Same idea, but you use a 4-way join rather than a 2-way join. Ted -- 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: Select * from multiple tables
Ok, I trying to get this example... what is table1 t1, table2 t2, table3 t3, table4 t4, I mean, what does the t1, t2, t3, t4 represent? If you say, table 1, table 2, etc well, I assume that, but isn't that there already? Let me, or may I, give ask again with my visual? Here are my tables and keys: table1: person_IDprimary key) lastName firstName table2: machine_ID primary key)person_ID model_ID location OS table3: model_ID primary key)make_ID model table4: make_ID primary Key)make I tried various: SELECT lastName, location, model, make FROM table1, table2, table3, table4 WHERE table2.person_ID = table1.person_ID AND table3.make_ID = table4.make_ID; The relationships are: table1 one-to-many table2 table2 many-to-one table3 table3 many-to-one table4 (help?) Thanks, you all are GREAT! (I googled 4-way JOIN... not a lot of joy -for me anyway.) Ted On Thursday, June 26, 2003, at 02:00 AM, Venkata Srinivasa Rao, Yerra wrote: SELECT t1.key,t2.col,t3.*,t4.col2 FROM table1 t1, table2 t2, table3 t3, table4 t4 WHERE t1.key=t2.key AND t1.key=t3.key AND t1.key=t4.key At 01:23 AM 6/26/2003 -0400, you wrote: I grown my db to 4 tables 8). I'm going to ask this plainly in hopes that my syntax in ok: I know how to SELECT * from 2 related tables and get all the records listed in the resultset. (Either using INNER JOIN or WHERE.) Now... and I have been looking some books! How do get a resultset of all records from 4 related tables? Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DELETE SYNTAX - [QUICK] option
Hi MySQL Fans ;-); Does anybody understand what the QUICK option is doing when used with the DELETE command ? It says in the manual ..no MERGEing of INDEX leaves during delete. Sorry, but I could not make any sense out of this. Could somebody please be so kind to enlighten me ? I guess it is related somehow to optimizing the index or space of a table. Best regards -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select * from multiple tables
[EMAIL PROTECTED]: Ok, I trying to get this example... what is table1 t1, table2 t2, table3 t3, table4 t4, I mean, what does the t1, t2, t3, t4 represent? If you say, table 1, table 2, etc well, I assume that, but isn't that there already? t1, t2 etc represents an alias for the table. If you have a long tablename, you can give it a short alias, and then just use the short alias in the rest of the query. Peter Rønning -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dumping data
Text1Hi all, Is there any way to do a mysql dump in which each table is dumped into a separate file. I know I can use the --tables option to specify a table, but this means I have to type out each table name (and there are a lot of them). Is there any way to get mysql to automatically iterate through all the table names and dump each table to a separate file. The reason for this is that the db is BIG and we don't want to have to lug 200+mb files around. Plus mysql seems to have a real issue with dumping large dbs into one file. We've tried about 5 - 10 times and we keep getting corrupted data in the file. Thanks --- Rob ** Rob Cherry mailto:[EMAIL PROTECTED] +27 21 447 7440 Jam Warehouse RSA Smart Business Innovation http://www.jamwarehouse.com ** All information contained in this email is confidential and may be used by the intended recipient only. AdmID:99B3219F743413F896891151CEA36E03 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: user privileges question
-Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, June 26, 2003 1:26 AM To: michael young; [EMAIL PROTECTED] Subject: Re: user privileges question At 18:02 -0400 6/25/03, michael young wrote: Hi, I am creating a small web based program for users to access the data in the datbases. New databases will be added in later. I want to create a user to access these databases with certain rights, not all rights. Will I have to grant this user right to each database as I add them in or is there a way to give this user rights to all databases (EXCEPT for mysql DB) including DB's I'll add later. Other than the mysql database there is no database on this server that this user should be elcluded from. What is the best way to handle this? Grant the user privileges for each database. There is no syntax for all except. Thanks, Michael -- 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] Victoria Reznichenko said few days ago, MySQL reads the privileges table sequentially and bails out as soon as it finds a match. (http://www.mysql.com/doc/en/Connection_access.html, and for our case: http://www.mysql.com/doc/en/Request_access.html) Therefore, I reckon the logical thing would be first to deny that user's access to the mysql db, then to allow access for the same user, to everything else. So my soultion would be: In the user table give TheUser N on every field. In the db table you should have 2 rules similar to: Host | Db | User | Select_priv | Insert_priv |... -- %| mysql | TheUser |N|N| %| % | TheUser |Y|N| localhost | mysql | TheUser |N|N| localhost | % | TheUser |Y|N| ... The point is to have 2 records for each host (be it % or a specific one), for the same user. According to the sorting rules defined in docs, this should work. Any validation/comments of this logic are highly welcomed ;-) Bests, Lian Sebe Freelance Analyst-Programmer www.programEz.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Should data type returned by MAX (x) be the same data type as x ?
Hi, I have been working with databases for many years and MySQL for a few months, so I'm almost embarrassed to be asking this question :( I have searched the manual and Deja news and come up blank. Live environment is MySQL 3.23.56 on Linux, test environment is 3.23.57 on WinXP. I am using SELECT MAX(intSomeColumn) where intSomeColumn is a basic Integer data type. The data type of the result column is BigInt - which my ASP environment does not like very much. Changing the Unsigned and/or Auto_Increment attributes of intSomeColumn does not help. How can I do a CAST so that the result is an Integer, but without using CAST or CONVERT as they are not available in 3.23? I have come across the trick of doing MAX(intSomeColumn+0.0) to return a Double, but I would rather keep my Ints as Ints if I can. I am aware of the 'Change BigInt columns to Int' option in the ODBC driver and this works fine for my test environment but I do not have access to these setting in my live environment. Thanks for reading, Rhys -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I Need an MySQL 4 MSI Installer
Regular MySQL 4 setup exe does not work on my version of WINXP Pro. Can you provide link to an MSI installer download of MySQL 4 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dumping data
Hi Rob, I think there was a similar question today. Is mysqldump -T perhaps doing what you are looking for ? Best regards Nils Valentin Tokyo/Japan 2003 6 26 17:[EMAIL PROTECTED] : Text1 -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils
Compiling with the C API
I have a question regarding compiling a C program with the mysql C api. From the documentation, the only way I really see explained for compiling is a shell script that looks like this: CFG=/usr/bin/mysql_config sh -c gcc -o $1 `$CFG --cflags` $1.c `$CFG --libs` This works great when all my source code is in one file. However, I need to modularize things and do separate compile and link steps. When I do this, sh -c gcc -o $1 `$CFG --cflags` $1.c It complains that it cannot find the header file for mysql.h. This does not make sense to me. Also, I do not understand why I need to do the sh -c. Why can I not just do the gcc part here? I really want to build a makefile but I am just trying to take baby steps here (I am new to Linux programming). Thanks. Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
clustering mysql, e.g., EMIC software
Hi, I've seen this question once already on this list, but no answer - has anybody here used or otherwise have any experience of the cluster software from www.emicnetworks.com ? What other clustering packages exist? Andy -- Andy Stubbs, B.A., Ph.D. Network Manager, Active Hotels Ltd. +44 1223 578106 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user defined static variables in mysql
Daniel Rossi [EMAIL PROTECTED] wrote: hi there i was wondering if there is anyway to setup static variables in mysql , ie to setup predefined paths in mysql . so all i can do is go select variablname as path etc .. ?? Nope. -- 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: MySQL Newbie: MySQL stops as soon as it starts
David Shealy [EMAIL PROTECTED] wrote: I don't know where the error log is. I used the -l option with safe-mysqld and this is what I got: /usr/libexec/mysqld, Version: 3.23.56-log, started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id CommandArgument Error log is located in the MySQL data dir (/var/lib/mysql) -- 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: SHOW DATABASES user rights
[EMAIL PROTECTED] wrote: I have a MySQL server running with some users besides root, that I want to grant all privileges for their own databases, but no rights for anything else. So to say, I have a user web01 that shall be able to do whatever he wants with the database db01. There are more databases, like db02 etc. If user web01 runs a 'SHOW DATABASES' command, he'll get to see ALL databases on the server, but I don't want him to see them... When the user does a 'USE dbname;', it says 'access denied'. There must be some special trick to let him only see the database(s) he's got rights on. (At least my webhoster managed that somehow...) Anyone an idea what rights I have to assign to get this running? Since 4.0.0 user must have SHOW DATABASES privilege to see databases on with he has no permissions: http://www.mysql.com/doc/en/Privileges_provided.html In 3.23.xx you can run mysqld with --skip-show-database option: http://www.mysql.com/doc/en/Command-line_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]
Fwd: Help me pls
Date: Thu, 26 Jun 2003 18:02:23 + To: [EMAIL PROTECTED] From: Syamsul Arifien Subject: Help me pls Hi ... I hv problem... I can't conec to my MysqlSvr with mysql_connect() in my php script.. anybody knows? best regard Syamsul Arifien -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fwd: Help me pls
Syamsul Arifien [EMAIL PROTECTED] wrote: Hi ... I hv problem... I can't conec to my MysqlSvr with mysql_connect() in my php script.. anybody knows? What error message did you get? -- 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: Help me pls
You may be better asking this one on the PHP list: http://www.php.net/mailing-lists.php But you will need a bit more detail such as your version/setup/platform and some code (without password/username that is!) On 6/26/03 2:12 PM, Syamsul Arifien [EMAIL PROTECTED] wrote: Date: Thu, 26 Jun 2003 18:02:23 + To: [EMAIL PROTECTED] From: Syamsul Arifien Subject: Help me pls Hi ... I hv problem... I can't conec to my MysqlSvr with mysql_connect() in my php script.. anybody knows? best regard Syamsul Arifien -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sum() problems
Dear All, I have this two queries The first one gives me the right result for the sum(): SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y'), SUM(ordini.totale), SUM(ordini.quantita), ordini.codcliente, ordini.fromprev FROM ordini WHERE ordini.numordine = 2302 GROUP BY ordini.numordine; this other one gives me problems with the sum(), wrong result. SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y'), SUM(ordini.totale), SUM(ordini.quantita), ordini.codcliente, ordini.fromprev, carello.numprev, DATE_FORMAT(carello.dataordine,'%d-%m-%Y') FROM ordini, carello WHERE ordini.numordine = 2302 AND carello.numprev = ordini.fromprev GROUP BY ordini.numordine, carello.numprev; Help is appreciated I'm using mysql 4.0.13 Patrizio
Help me: I'm a beginner
I run the following query: select * from x where x like 'x/%' ; I would like the result of this query to go to a file or pause at the end of the screen so that I can view each page. Any help would be appreciated.
Re: Help me: I'm a beginner
On Thu, Jun 26, 2003 at 08:07:48AM -0400, David Bush wrote: I run the following query: select * from x where x like 'x/%' ; I would like the result of this query to go to a file or pause at the end of the screen so that I can view each page. Any help would be appreciated. mysql pager /usr/bin/less mysql tee somefile.log I guess this is documented somewhere, or try: mysql help Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me: I'm a beginner
- Original Message - From: Fred van Engen [EMAIL PROTECTED] To: David Bush [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 3:11 PM Subject: Re: Help me: I'm a beginner On Thu, Jun 26, 2003 at 08:07:48AM -0400, David Bush wrote: I run the following query: select * from x where x like 'x/%' ; I would like the result of this query to go to a file or pause at the end of the screen so that I can view each page. Any help would be appreciated. try using the into outfile syntax after your select...(see manual/ask me for an example if needed)... aik_b -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: EMS MySQL Manager 2.5 released!
EMS HiTech company is pleased to announce MySQL Manager for Windows 2.5 -
the next version of the best MySQL administration and development tool!
You can download the latest version and user's guide from
http://www.mysqlmanager.com/download.phtml
What's new in version 2.5?
1. We have added a possibility to save the Visual Database Designer diagrams
as graphical images. Now you can save your diagrams into four graphical file
formats: bitmap, JPEG image, GIF image and Windows metafile. This feature is
available through the Save as Image button of the Visual Database Designer
toolbar. (*)
2. Now SQL Editor and Visual Query Builder support query parameters. This
feature allows you to specify different values within a query in a popup
dialog just before the query execution. Use the colon (':') character before
some identifier (e.g. :P1) to specify a parameter within a query. Note that
query parameters are allowed only when the Allow using parameters in query
text option is checked on the Tools tab of the Environment Options dialog.
3. Data View: we have added a possibility to set filters for the Grid View.
Now you can filter the current data view using drop-down arrows on the right
of the column captions.
4. We have added a possibility to forward a local port to the MySQL server
located on a different from the SSH host. Now you can connect to the remote
MySQL server through the SSH protocol even if the SSH and MySQL hosts are
not on the same computer. All the parameters for connecting through the SSH
tunnel are set on the Register Database Dialog or the Register Host Wizard.
(*)
5. The Grant Manager provides now two new menu items: Grant All on All and
Revoke All on All. Use them for granting or revoking to the selected user
all privileges on all objects at once.
6. The Environment Options - Tools - Table tab provides the new Do not
retrieve record count for a table option. Enable this option for not to
retrieve record count, as this can take a long time for large InnoDB tables.
...
15. Some minor improvements and corrections.
(*) - Professional Edition only
Full press release is available at
http://www.ems-hitech.com/news.phtml?id=392.
What is MySQL Manager?
EMS MySQL Manager provides powerful tools for MySQL Server administration
and object management. Its Graphical User Interface (GUI) allows you to
create/edit all MySQL database objects in a simple and direct way, design
databases visually, run SQL scripts, manage users and administer user
privileges, visually build SQL queries, extract, print, and search metadata,
create database structure reports in HTML format, export/import data,
view/edit BLOBs, and supplies many more services that will make your work
with the MySQL server as easy as it can be...
Don't forget to check out other our products:
http://www.ems-hitech.com/sqlmanagers
Powerful database administration tools for InterBase/FireBird, MySQL,
PostgreSQL and DBISAM servers
http://www.ems-hitech.com/sqlutils/
Cross-platform data management utilities for MS SQL, MySQL and PostgreSQL
servers
http://www.ems-hitech.com/components/
Powerful components for Delphi/C++ Builder developers
We hope you'll like our products.
Thank you for your attention.
Best Regards,
EMS HiTech Team
http://www.ems-hitech.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sum() problems
Pat, I think it might be caused by the fact that you are grouping by a column that isn't being selected - ordini.numordine is not in the select part. Andy -Original Message- From: PaT! [mailto:[EMAIL PROTECTED] Sent: 26 June 2003 12:50 To: [EMAIL PROTECTED] Subject: sum() problems Dear All, I have this two queries The first one gives me the right result for the sum(): SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y'), SUM(ordini.totale), SUM(ordini.quantita), ordini.codcliente, ordini.fromprev FROM ordini WHERE ordini.numordine = 2302 GROUP BY ordini.numordine; this other one gives me problems with the sum(), wrong result. SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y'), SUM(ordini.totale), SUM(ordini.quantita), ordini.codcliente, ordini.fromprev, carello.numprev, DATE_FORMAT(carello.dataordine,'%d-%m-%Y') FROM ordini, carello WHERE ordini.numordine = 2302 AND carello.numprev = ordini.fromprev GROUP BY ordini.numordine, carello.numprev; Help is appreciated I'm using mysql 4.0.13 Patrizio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I Need an MySQL 4 MSI Installer
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 pswebcode, nyc wrote: Regular MySQL 4 setup exe does not work on my version of WINXP Pro. Can you provide link to an MSI installer download of MySQL 4 [EMAIL PROTECTED] There is not an MSI installer for MySQL 4.0. There will be one for 4.1 (it is currently being developed). There is an installer-less version of MySQL 4.0.13 for Windows posted on the download page (it is a simple .zip archive). The installer does work on Windows XP Pro, many people have tested it (including myself) on this operating system. Can you let us know exactly how it is failing? This might be related to some level of hotfix or service pack you have installed, or some application that is running in the background on your system, but not on others. -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE++u1+tvXNTca6JD8RAiQSAJ46MGsTgQcqmbadTxVbYUPUbvRBiACeL3Oc nWXyFoRtTMz1+U+MgJiLrnU= =Fs71 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select * from multiple tables
[EMAIL PROTECTED] wrote: Ok, I trying to get this example... what is table1 t1, table2 t2, table3 t3, table4 t4, I mean, what does the t1, t2, t3, t4 represent? If you say, table 1, table 2, etc well, I assume that, but isn't that there already? Let me, or may I, give ask again with my visual? Here are my tables and keys: table1: person_IDprimary key) lastName firstName table2: machine_ID primary key)person_ID model_ID location OS table3: model_ID primary key)make_ID model table4: make_ID primary Key)make I tried various: SELECT lastName, location, model, make FROM table1, table2, table3, table4 WHERE table2.person_ID = table1.person_ID AND table3.make_ID = table4.make_ID; You are missing a relationship SELECT lastName, location, model, make FROM table1, table2, table3, table4 WHERE table2.person_ID = table1.person_ID AND table3.make_ID = table4.make_ID AND table2.model_ID=table3.model_ID; The relationships are: table1 one-to-many table2 table2 many-to-one table3 table3 many-to-one table4 (help?) Thanks, you all are GREAT! (I googled 4-way JOIN... not a lot of joy -for me anyway.) Ted On Thursday, June 26, 2003, at 02:00 AM, Venkata Srinivasa Rao, Yerra wrote: SELECT t1.key,t2.col,t3.*,t4.col2 FROM table1 t1, table2 t2, table3 t3, table4 t4 WHERE t1.key=t2.key AND t1.key=t3.key AND t1.key=t4.key At 01:23 AM 6/26/2003 -0400, you wrote: I grown my db to 4 tables 8). I'm going to ask this plainly in hopes that my syntax in ok: I know how to SELECT * from 2 related tables and get all the records listed in the resultset. (Either using INNER JOIN or WHERE.) Now... and I have been looking some books! How do get a resultset of all records from 4 related tables? Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need your help in a search query
Hi MySQL Experts, I am pretty new at this and need your help in figuring out if it is possible to create a query to search for words in a given field. What I am trying to do is allow a user to enter words to search for a given title in the database. For example, a person is looking up the title 20,000 Leagues Under the Sea but is entering only 2 as the search criteria. When I run the following query, I get no results. The problem is with the comma in 20,000. Any suggestions on how to find the title even though the user only entered 2??? I am running this on the web using PHP and MySQL. Possible someone knows of a good search script in PHP that might help also? Here is the query I was trying to run: SELECT * FROM ardata WHERE title LIKE '%user_input%'; I am using MySQL 4.0.13 and PHP 4.3.2 Thanks for helping! Tom Johnson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sum() problems
PaT! [EMAIL PROTECTED] wrote: I have this two queries The first one gives me the right result for the sum(): SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y'), SUM(ordini.totale), SUM(ordini.quantita), ordini.codcliente, ordini.fromprev FROM ordini WHERE ordini.numordine = 2302 GROUP BY ordini.numordine; this other one gives me problems with the sum(), wrong result. SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y'), SUM(ordini.totale), SUM(ordini.quantita), ordini.codcliente, ordini.fromprev, carello.numprev, DATE_FORMAT(carello.dataordine,'%d-%m-%Y') FROM ordini, carello WHERE ordini.numordine = 2302 AND carello.numprev = ordini.fromprev GROUP BY ordini.numordine, carello.numprev; Help is appreciated What is wrong with your second query? Please, be more detailed. -- 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: Help me: I'm a beginner
David Bush [EMAIL PROTECTED] wrote: I run the following query: select * from x where x like 'x/%' ; I would like the result of this query to go to a file or pause at the end of the screen so that I can view each page. Any help would be appreciated. Use SELECT .. INTO OUTFILE command: http://www.mysql.com/doc/en/SELECT.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: Need your help in a search query
You could try SOUNDEX(str) http://www.mysql.com/doc/en/String_functions.html which might do what you are looking for HTH Peter -Original Message- From: Tom Johnson [mailto:[EMAIL PROTECTED] Sent: 26 June 2003 14:23 To: [EMAIL PROTECTED] Subject: Need your help in a search query Hi MySQL Experts, I am pretty new at this and need your help in figuring out if it is possible to create a query to search for words in a given field. What I am trying to do is allow a user to enter words to search for a given title in the database. For example, a person is looking up the title 20,000 Leagues Under the Sea but is entering only 2 as the search criteria. When I run the following query, I get no results. The problem is with the comma in 20,000. Any suggestions on how to find the title even though the user only entered 2??? I am running this on the web using PHP and MySQL. Possible someone knows of a good search script in PHP that might help also? Here is the query I was trying to run: SELECT * FROM ardata WHERE title LIKE '%user_input%'; I am using MySQL 4.0.13 and PHP 4.3.2 Thanks for helping! Tom Johnson -- 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]
transactions and per connection details
hi all!
i am very much interested in knowing the
intricacies of the MySql server. i use Mysql version
3.23.52.
how can we get informations regarding all Mysql
transactions and regarding the tables and databases
that have been dropped. Also how can we get the amount
of time spent by each client{or connection}. is there
any API available to retrieve it or where in the
source code i can get all these informations!
kindly help me!
Want to chat instantly with your online friends? Get the FREE Yahoo!
Messenger http://uk.messenger.yahoo.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: use of UNION
I think you are after subselects more than a UNION. The latest example of me using union was where a had 2 queries with table A and B: first query has tableA LEFT JOIN tableb, second query had tableb LEFT JOIN tablea. By using UNION I was able to retrieve the results of both with one query. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Daniel Rossi [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 9:15 PM To: [EMAIL PROTECTED] Subject: use of UNION hi there , i finally worked out union joines are supported in 4.0 i thought it was 4.1 , anyway what are possible examples of its uses ? say i'm trying to join two tables the second table has 10 rows returned with the key of the first table i would like to only get one record from the first table is this possible in the union statement ? currently i have to loop through the first query statement then in that loop do another select statement within the loop and loop through the second query statement returning those ten rows, if i join them on the same query i'll get multiple records of the first table -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slow performance on simple queries
Hi- I'm running mysql Ver 12.20 Distrib 4.0.13, for pc-linux (i686) / RedHat 7.3 on a AMD 2400XP 256 MB. I've got a table (type=MyISAM) that's grown to 1,7 GB (154,815 records) and it takes several seconds to perform even simple querys: select data from eod where symbol='ZOOM' yields: 1 row in set (3.15 sec) (data is about 29K) a repeat of the query yields 0.00 seconds, so the slowdown appears to be in the initial accessing of the data, not the handling/display of the result. nearby records (key 'ZOWI') execute faster (1 row in set (0.28 sec)) but still slowly. this is the create: CREATE TABLE `eod` ( `symbol` varchar(10) binary NOT NULL default '', `updated` date default NULL, `data` mediumtext, PRIMARY KEY (`symbol`) ) TYPE=MyISAM Is there a relationship between filesystem/recordsize/recordcount that needs to be optimized for this type of table? bob. == The RediNews Network http://www.redinews.com/ Custom Financial Web Site Content phone: (516) 997-4343 ==
RE: Need your help in a search query
This may not help with the comma problem, but your app looks like a good candidate for FULLTEXT searching, check out http://www.mysql.com/doc/en/Fulltext_Search.html for information. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Tom Johnson [mailto:[EMAIL PROTECTED] Sent: Thursday, June 26, 2003 7:23 AM To: [EMAIL PROTECTED] Subject: Need your help in a search query Hi MySQL Experts, I am pretty new at this and need your help in figuring out if it is possible to create a query to search for words in a given field. What I am trying to do is allow a user to enter words to search for a given title in the database. For example, a person is looking up the title 20,000 Leagues Under the Sea but is entering only 2 as the search criteria. When I run the following query, I get no results. The problem is with the comma in 20,000. Any suggestions on how to find the title even though the user only entered 2??? I am running this on the web using PHP and MySQL. Possible someone knows of a good search script in PHP that might help also? Here is the query I was trying to run: SELECT * FROM ardata WHERE title LIKE '%user_input%'; I am using MySQL 4.0.13 and PHP 4.3.2 Thanks for helping! Tom Johnson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow performance on simple queries
Do you have disk performance data? My guess would be it's the time to read the information off the disk. I think redhat has the iostat command. Try running iostat 1 in one window while you run your query in another, what result do you get from the iostat? -- Michael Conlen Bob Ostermann wrote: Hi- I'm running mysql Ver 12.20 Distrib 4.0.13, for pc-linux (i686) / RedHat 7.3 on a AMD 2400XP 256 MB. I've got a table (type=MyISAM) that's grown to 1,7 GB (154,815 records) and it takes several seconds to perform even simple querys: select data from eod where symbol='ZOOM' yields: 1 row in set (3.15 sec) (data is about 29K) a repeat of the query yields 0.00 seconds, so the slowdown appears to be in the initial accessing of the data, not the handling/display of the result. nearby records (key 'ZOWI') execute faster (1 row in set (0.28 sec)) but still slowly. this is the create: CREATE TABLE `eod` ( `symbol` varchar(10) binary NOT NULL default '', `updated` date default NULL, `data` mediumtext, PRIMARY KEY (`symbol`) ) TYPE=MyISAM Is there a relationship between filesystem/recordsize/recordcount that needs to be optimized for this type of table? bob. == The RediNews Network http://www.redinews.com/ Custom Financial Web Site Content phone: (516) 997-4343 == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting Signal 11 on Solaris for Large DB type Config
Hi, MySql crashed complaining about signal 11 after we reconfigured the db for a large db type.(It was running fine on medium db configuration) It is running on Solaris 2.8 with 1 GB RAM along with Resin App Server. Does large db configuration require the whole machine to run mysql?. Thanks in Advance Sakthi.
Re: Select * from multiple tables
At 2:58 -0400 6/26/03, [EMAIL PROTECTED] wrote: Ok, I trying to get this example... what is table1 t1, table2 t2, table3 t3, table4 t4, I mean, what does the t1, t2, t3, t4 represent? If you say, table 1, table 2, etc well, I assume that, but isn't that there already? t1, t2, etc. are table aliases. Elsewhere in the table, you refer to the tables using the alias names. If all your table names are distinct, you don't need to use aliases, but they can be useful in providing shorter names by which to refer to the tables. If your table names are not distinct, aliases are essential for letting MySQL know which instance of the table you mean in other parts of the query. Let me, or may I, give ask again with my visual? Here are my tables and keys: table1: person_IDprimary key) lastName firstName table2: machine_ID primary key)person_ID model_ID location OS table3: model_ID primary key)make_ID model table4: make_ID primary Key)make I tried various: SELECT lastName, location, model, make FROM table1, table2, table3, table4 WHERE table2.person_ID = table1.person_ID AND table3.make_ID = table4.make_ID; The relationships are: table1 one-to-many table2 table2 many-to-one table3 table3 many-to-one table4 (help?) Thanks, you all are GREAT! (I googled 4-way JOIN... not a lot of joy -for me anyway.) Ted On Thursday, June 26, 2003, at 02:00 AM, Venkata Srinivasa Rao, Yerra wrote: SELECT t1.key,t2.col,t3.*,t4.col2 FROM table1 t1, table2 t2, table3 t3, table4 t4 WHERE t1.key=t2.key AND t1.key=t3.key AND t1.key=t4.key At 01:23 AM 6/26/2003 -0400, you wrote: I grown my db to 4 tables 8). I'm going to ask this plainly in hopes that my syntax in ok: I know how to SELECT * from 2 related tables and get all the records listed in the resultset. (Either using INNER JOIN or WHERE.) Now... and I have been looking some books! How do get a resultset of all records from 4 related tables? Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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: DELETE SYNTAX - [QUICK] option
At 16:22 +0900 6/26/03, Nils Valentin wrote: Hi MySQL Fans ;-); Does anybody understand what the QUICK option is doing when used with the DELETE command ? It says in the manual ..no MERGEing of INDEX leaves during delete. Sorry, but I could not make any sense out of this. Could somebody please be so kind to enlighten me ? I guess it is related somehow to optimizing the index or space of a table. It may make the query quicker because the server doesn't do as much messing around fixing up the leaves of the index tree. Best regards -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- 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: Compiling with the C API
At 6:32 -0400 6/26/03, Andrew Pierce wrote: I have a question regarding compiling a C program with the mysql C api. From the documentation, the only way I really see explained for compiling is a shell script that looks like this: CFG=/usr/bin/mysql_config sh -c gcc -o $1 `$CFG --cflags` $1.c `$CFG --libs` This works great when all my source code is in one file. However, I need to modularize things and do separate compile and link steps. When I do this, sh -c gcc -o $1 `$CFG --cflags` $1.c It complains that it cannot find the header file for mysql.h. This does not make sense to me. Also, I do not understand why I need to do the sh -c. Why can I not just do the gcc part here? I really want to build a makefile but I am just trying to take baby steps here (I am new to Linux programming). Go to: http://www.kitebird.com/mysql-book/ Grab the PDF of Chapter 6 and read pp 321 - 324. That may help you. Thanks. Andrew -- 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: load_file
I've been having a few problems loading blobs via the LOAD_FILE function I've tried things along the lines of: UPDATE table SET binaryRecord=LOAD_FILE(/Users/shared/filename.mov) where refNo=1; It only seems to happen when loading something over 1-1.5mb, as I've been able to load smaller files no problem but can't do anything with files of around 4mb. I'm using mediumblob (and have tried long), max allowed packet is set at 16mb. The files are readable by all, and I have file permission. I'm basically out of ideas... -s -- __ Seth Redmond DNA resource and Database Curator Wellcome Trust Laboratories for Molecular Parasitology Department of Biological Sciences Imperial College London SW7 2AZ [EMAIL PROTECTED] __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database replication
* Jeremy Zawodny ([EMAIL PROTECTED]) wrote: On Tue, Jun 24, 2003 at 09:14:18PM -0700, Carl B. Constantine wrote: I want to be able to take a database running on a primary server and duplicate/mirror it on a secondary server. I want to be able to update the secondary server on a selectable interval (every 15 mins, every hour, etc). What is the best way to accomplish this task? You'd need to start and stop replication on the slave using a bit of custom code. See the the replication related commands in the manual. Will do. thank you. Here is another question, is it possible for some people to work on the master and some on the slave, and then merge the data back and forth so that they are both the same or does it only work one way thus the DB's are out-of-sync? Additionally, where does MySQL write it's transactions? To a transaction log. :-) ha ha. I meant physically. If the power to the box dies and the system crashes, What does MySQL do to recover? What about tables that are not InnoDB? InnoDB tables scan the log for transactions that must be committed or rolled back. MyISAM tables are not transactional. Ok, That confirms what I thought. Thanks. -- .''`. Carl B. Constantine : :' : [EMAIL PROTECTED] `. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8 `- Debian GNU/Linux -- The power of freedom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error message
can someone help me with this error, with my database in MySQL 3.23.49 SQL-query : SELECT * FROM `traffic`.`trafficdb` LIMIT 132947 MySQL said: Got error 127 from table handler have I lost the data, or is there something odd happened which i should be able to fix? Steven Dowd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_thread_init()
Please bear with me here as I am new to this whole MySQL thing. Does anyone know where I can find a good example of how the C API function call mysql_thread_init() is implemented? Any help would be appreciated. Thanks in advance. Sean -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_thread_init()
Check the client sources. The mysql client is a great example for the c API. Sean Macmillan wrote: Please bear with me here as I am new to this whole MySQL thing. Does anyone know where I can find a good example of how the C API function call mysql_thread_init() is implemented? Any help would be appreciated. Thanks in advance. Sean -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error message
Just a guess from a raw newbie, but try using: `traffic.trafficdb` instead of `traffic`.`trafficdb` Ryan Thursday, June 26, 2003, 1:13:03 PM, you wrote: SD can someone help me with this error, with my database in MySQL 3.23.49 SD SD SQL-query : SD SELECT * SD FROM `traffic`.`trafficdb` SD LIMIT 132947 SD MySQL said: SD Got error 127 from table handler SD SD have I lost the data, or is there something odd happened which i should be SD able to fix? SD Steven Dowd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best Pratices for mySQL Backups in Enterprise
We are in the process of implementing enterprise wide (20,000+ users) application that will use mySQL as the Database engine. I was wondering if the slashdot readers can provide me some details about best practices / experiences for Backing Up and Restoring mySQL Databases. I am planning to setup a cron job, to lock the tables, use msqlhotcopy and then unlock the tables. Is that a good backup strategy? Is there a ready-made perl script that I can use? Is there a commercial solution for Backups? What other things do I need to keep in mind? What do other people do in production use, where the DB is frequently? I have already read mySQL: The definitive guide to to using, programming and administring mySQL 4, but did not find ay good information about backing up and restoring, in the 24x7 operation for Data Center use. Any ideas will highly appreciated. Thanks Saqib Ali -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting MSQL to MySQL
Where can I find more info on converting and what can be converted. Thanks Mike Benzel [EMAIL PROTECTED] Promote your Product with a Free SkyWiremeeting! SkyWire Meeting - Internet Training from you PC or our Server.
Re: SHOW DATABASES user rights
thanks for your hint in that direction! i'm currently using the latest mysql 3.23, but i guess, i'll upgrade to 4.0 before i get my server up and running. but what i found, and may be more appropriate for my needs is the --safe-show-database option. i just added it to /etc/my.cnf, restarted the server and things were fine again :-) so that's what i did to my.cnf: [mysqld] safe-show-database HTH anyone else, too... yves at unclassified.de -Ursprngliche Nachricht- Von: Victoria Reznichenko [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Donnerstag, 26. Juni 2003 11:25 Betreff: Re: SHOW DATABASES user rights [EMAIL PROTECTED] wrote: I have a MySQL server running with some users besides root, that I want to grant all privileges for their own databases, but no rights for anything else. So to say, I have a user web01 that shall be able to do whatever he wants with the database db01. There are more databases, like db02 etc. If user web01 runs a 'SHOW DATABASES' command, he'll get to see ALL databases on the server, but I don't want him to see them... When the user does a 'USE dbname;', it says 'access denied'. There must be some special trick to let him only see the database(s) he's got rights on. (At least my webhoster managed that somehow...) Anyone an idea what rights I have to assign to get this running? Since 4.0.0 user must have SHOW DATABASES privilege to see databases on with he has no permissions: http://www.mysql.com/doc/en/Privileges_provided.html In 3.23.xx you can run mysqld with --skip-show-database option: http://www.mysql.com/doc/en/Command-line_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]
RE: Best Pratices for mySQL Backups in Enterprise
If your tables are InnoDB, you could look at InnoDB HotBackup: http://www.innodb.com/hotbackup.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: SAQIB [mailto:[EMAIL PROTECTED] Sent: Thursday, June 26, 2003 11:45 AM To: [EMAIL PROTECTED] Subject: Best Pratices for mySQL Backups in Enterprise We are in the process of implementing enterprise wide (20,000+ users) application that will use mySQL as the Database engine. I was wondering if the slashdot readers can provide me some details about best practices / experiences for Backing Up and Restoring mySQL Databases. I am planning to setup a cron job, to lock the tables, use msqlhotcopy and then unlock the tables. Is that a good backup strategy? Is there a ready-made perl script that I can use? Is there a commercial solution for Backups? What other things do I need to keep in mind? What do other people do in production use, where the DB is frequently? I have already read mySQL: The definitive guide to to using, programming and administring mySQL 4, but did not find ay good information about backing up and restoring, in the 24x7 operation for Data Center use. Any ideas will highly appreciated. Thanks Saqib Ali -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error message
Sounds like a corrupt table, try REPAIR TABLE: http://www.mysql.com/doc/en/REPAIR_TABLE.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Steven Dowd [mailto:[EMAIL PROTECTED] Sent: Thursday, June 26, 2003 11:13 AM To: [EMAIL PROTECTED] Subject: error message can someone help me with this error, with my database in MySQL 3.23.49 SQL-query : SELECT * FROM `traffic`.`trafficdb` LIMIT 132947 MySQL said: Got error 127 from table handler have I lost the data, or is there something odd happened which i should be able to fix? Steven Dowd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Best Pratices for mySQL Backups in Enterprise
If your tables are InnoDB, you could look at InnoDB HotBackup: http://www.innodb.com/hotbackup.html No I m not using InnoDB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow performance on simple queries
I ran iostat, what are important values to consider? I ran the job from 12:31:51 to 12:32:18. I can see there was a noticeable drop in tps. I should mention that this node is basically idle at this time (it's a new development node, so no non-core processes except mysqld. Linux 2.4.18-3 (venus) 06/26/2003 Time: 12:31:48 PM Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn Time: 12:31:49 PM dev3-0 219.00 5128.00 0.00 5128 0 Time: 12:31:50 PM dev3-0 216.00 4688.00 0.00 4688 0 Time: 12:31:51 PM dev3-0 181.00 3976.00 0.00 3976 0 Time: 12:31:52 PM dev3-0 139.00 2824.00 0.00 2824 0 Time: 12:31:53 PM dev3-0 129.00 2056.00 176.00 2056176 Time: 12:31:54 PM dev3-0 133.00 2296.00 0.00 2296 0 Time: 12:31:55 PM dev3-0 136.00 2720.00 0.00 2720 0 Time: 12:31:56 PM dev3-0 115.00 2664.00 0.00 2664 0 Time: 12:31:57 PM dev3-0 119.00 3024.00 0.00 3024 0 Time: 12:31:58 PM dev3-0 147.00 3008.00 224.00 3008224 Time: 12:31:59 PM dev3-0 152.00 3360.00 0.00 3360 0 Time: 12:32:00 PM dev3-0 143.00 3200.00 0.00 3200 0 Time: 12:32:01 PM dev3-0 153.00 3408.00 0.00 3408 0 Time: 12:32:02 PM dev3-0 149.00 3264.00 0.00 3264 0 Time: 12:32:03 PM dev3-0 151.00 3264.0048.00 3264 48 Time: 12:32:04 PM dev3-0 150.00 3304.00 0.00 3304 0 Time: 12:32:05 PM dev3-0 156.00 3376.00 0.00 3376 0 Time: 12:32:06 PM dev3-0 136.00 2856.00 0.00 2856 0 Time: 12:32:07 PM dev3-0 143.00 2952.00 0.00 2952 0 Time: 12:32:08 PM dev3-0 133.00 3152.00 624.00 3152624 Time: 12:32:09 PM dev3-0 147.00 3080.00 0.00 3080 0 Time: 12:32:10 PM dev3-0 158.00 3648.00 0.00 3648 0 Time: 12:32:11 PM dev3-0 153.00 3584.00 0.00 3584 0 Time: 12:32:12 PM dev3-0 162.00 3848.00 0.00 3848 0 Time: 12:32:13 PM dev3-0 158.00 3704.0080.00 3704 80 Time: 12:32:14 PM dev3-0 161.00 3848.00 0.00 3848 0 Time: 12:32:15 PM dev3-0 159.00 3816.00 0.00 3816 0 Time: 12:32:16 PM dev3-0 157.00 3680.00 0.00 3680 0 Time: 12:32:17 PM dev3-0 152.00 3616.00 0.00 3616 0 Time: 12:32:18 PM dev3-0 170.00 4000.0048.00 4000 48 Time: 12:32:19 PM dev3-0 229.00 5376.00 0.00 5376 0 Time: 12:32:20 PM dev3-0 236.00 5664.00 0.00 5664 0 Time: 12:32:21 PM dev3-0 238.00 5664.00 0.00 5664 0 Time: 12:32:22 PM dev3-0 225.00 5232.00 0.00 5232 0 Time: 12:32:23 PM dev3-0 208.00 4776.00 120.00 4776120 Time: 12:32:24 PM dev3-0 220.00 5280.00 0.00 5280 0 Time: 12:32:25 PM dev3-0 249.00 5752.00 0.00 5752 0 Time: 12:32:26 PM dev3-0 220.00 5216.00 0.00 5216 0 Time: 12:32:27 PM dev3-0 236.00 5648.00 0.00 5648 0 At 10:31 AM 6/26/03 -0400, you wrote: Do you have disk performance data? My guess would be it's the time to read the information off the disk. I think redhat has the iostat command. Try running iostat 1 in one window while you run your query in another, what result do you get from the iostat? -- Michael Conlen Bob Ostermann wrote: Hi- I'm running mysql Ver 12.20 Distrib 4.0.13, for pc-linux (i686) / RedHat 7.3 on a AMD 2400XP 256 MB. I've got a table (type=MyISAM) that's grown to 1,7 GB (154,815 records) and it takes several seconds to perform even simple querys: select data from eod where symbol='ZOOM' yields: 1 row in set (3.15 sec) (data is about 29K) a repeat of the query yields 0.00 seconds, so the slowdown appears to be in the initial accessing of the data, not the handling/display of the result. nearby records (key 'ZOWI') execute faster (1 row in set (0.28 sec)) but still slowly. this is the create: CREATE TABLE `eod` ( `symbol` varchar(10) binary NOT NULL default '', `updated` date default
Re: Best Pratices for mySQL Backups in Enterprise
mysqlhotcopy does your locking for you. SAQIB wrote: We are in the process of implementing enterprise wide (20,000+ users) application that will use mySQL as the Database engine. I was wondering if the slashdot readers can provide me some details about best practices / experiences for Backing Up and Restoring mySQL Databases. I am planning to setup a cron job, to lock the tables, use msqlhotcopy and then unlock the tables. Is that a good backup strategy? Is there a ready-made perl script that I can use? Is there a commercial solution for Backups? What other things do I need to keep in mind? What do other people do in production use, where the DB is frequently? I have already read mySQL: The definitive guide to to using, programming and administring mySQL 4, but did not find ay good information about backing up and restoring, in the 24x7 operation for Data Center use. Any ideas will highly appreciated. Thanks Saqib Ali -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
full disk: ERROR 1016: Can't open file
Hello all, My /var partition has become full, and now when I try load my db (the files are in /var/lib/mysql/db/) I get the following message for the table my_table: use db; Didn't find any fields in table 'my_table' I then do describe my_table and I get: ERROR 1016: Can't open file: 'my_table.MYD'. (errno: 145). I freed up some space under /var but I still recieve the above messages. Does this mean that my table has been corrupted? Is there any way to recover this table? Thanks in advance, Vikas _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Uploading scripts in MySQLGUI
Hi there, Is there a way to upload a saved script file into the GUI window to execute? On the GUI interface, there are SAVE, EXECUTE buttons. But I cannot find anything for uploading, even copying functions. Thanks for the help. Regards, Sean Tang
Re: full disk: ERROR 1016: Can't open file
perror 145 Vikas Gupta wrote: Hello all, My /var partition has become full, and now when I try load my db (the files are in /var/lib/mysql/db/) I get the following message for the table my_table: use db; Didn't find any fields in table 'my_table' I then do describe my_table and I get: ERROR 1016: Can't open file: 'my_table.MYD'. (errno: 145). I freed up some space under /var but I still recieve the above messages. Does this mean that my table has been corrupted? Is there any way to recover this table? Thanks in advance, Vikas _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sum() problems - I don't understand
Dear All, I have these two queries which are supposed to return the same results for the fields with the same name (ex query 1 sum1 = query 2 sum1): The first query gives me back the correct result for the sum(): SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y') date1, SUM(ordini.totale) sum1, SUM(ordini.quantita) sum2, ordini.cod, ordini.fromprev FROMordini WHERE ordini.numordine = 2302 GROUP BYordini.numordine; ++-+--++--+ | date1 | sum1| sum2 | cod| fromprev | ++-+--++--+ | 29-05-2003 | 436.498 | 10 | SC0001GP | 2666 | ++-+--++--+ this other one gives me problems with the sum(), wrong result. SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y') date1, SUM(ordini.totale) sum1, SUM(ordini.quantita) sum2, ordini.cod, ordini.fromprev, carello.numprev, DATE_FORMAT(carello.dataordine,'%d-%m-%Y') date2 FROM ordini, carello WHEREordini.numordine = 2302 AND carello.numprev = ordini.fromprev GROUP BY ordini.numordine, carello.numprev; ++--+--+-+--+-+- ---+ | date1 | sum1 | sum2 | cod | fromprev | numprev | date2 | ++--+--+-+--+-+- ---+ | 29-05-2003 | 3491.984 | 80 | SC0001GP| 2666 |2666 | 28-05-2003 | ++--+--+-+--+-+- ---+ Help is required. I run Mysql 4.0.13 Thanks Patrizio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
distributed database architecture for a large database
I've got an interesting problem for you all. I'd love to hear what you think. I've simplified the database design of my web application to its root problem, so it'll be very easy to see my difficulty, and if you're feeling generous possibly offer a solution. I have two tables. member table (circa 1,000,000 rows - 100 meg - easily fit in a single table) username varchar(30) pk password varchar(30) settings varchar(30) member_log table (circa 3,000,000,000 rows - a few 100 gigs - a few 100 very simple updates and selects a second, with some quite long selects every minute or so - when the update is being done it needs to select the settings for the user from the member table before it does the update to the member_log table) logid medint pk fk_username varchar(30) fk description varchar(200) My read/update ratio would be something along the lines of 1:3, 3 updates for every read. So it is highly unusual, and more or less rules replication out of the picture. Now ehm what's the most efficient way of doing this? What I would like to do is: Is have a copy of the member table on every server, then break up the member_log based on the username, and spread it across multiple servers. database server a full member table 1/4 member_log table database server b full member table 1/4 member_log table database server c full member table 1/4 member_log table database server d full member table 1/4 member_log table In the future, if the servers start to slow down then I'll just add database server e full member table member_log table Well that's what I'd like to do, but I don't know how to do this. My main problem is keeping the full member table in sync. I can't use replication because my read/update ratio just isn't right for it. And I only want to keep one table in sync, not the whole database. So i don't know what to do. How do I do this, and do this efficently? Any ideas anyone? regards, Aodhan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Pratices for mySQL Backups in Enterprise
mysqlhotcopy does your locking for you. So running ./mysqlhostcopy dbase /path/to/backup/dir is perfectly safe while database operations (selct, insert, update etc) are being performed? --- Saqib Ali http://www.xml-dev.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anyone had a chance to try an Opteron yet?
I'd be interested to see what happens when MySQL gets run on a G5. SPECint and SPECfp numbers look very good. there is an 8GB RAM limit on it, though. Curtis On Tue, 24 Jun 2003, Jeremy Zawodny wrote: On Tue, Jun 24, 2003 at 05:37:24PM -0400, Curtis Maurand wrote: Linux compiled on an opteron and targeted for it will lose the 2-gig process limit. the 2-gig number is based on a 32 bit integer. You now would a have 64 bit integer (5 Quintillion as an unsigned integer. :-)). Imagine the 10GB database in memory, plus the temp and heap tables and the indexes. Lets not forget about the ability to have large heap tables, too. Life gets very interesting in the 64 bit space, especially since IA64's aren't exactly plentiful. As soon as I can afford one, I'm buying one. I'm very interesting. Yeah, the operton should kick major ass with MySQL and sufficient memory. :-) Jeremy -- -- 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]
Re: sum() problems - I don't understand
Why would you expect they should have the same results? The second query contains a join and may have many times more rows in the result set. PaT! wrote: Dear All, I have these two queries which are supposed to return the same results for the fields with the same name (ex query 1 sum1 = query 2 sum1): The first query gives me back the correct result for the sum(): SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y') date1, SUM(ordini.totale) sum1, SUM(ordini.quantita) sum2, ordini.cod, ordini.fromprev FROMordini WHERE ordini.numordine = 2302 GROUP BYordini.numordine; ++-+--++--+ | date1 | sum1| sum2 | cod| fromprev | ++-+--++--+ | 29-05-2003 | 436.498 | 10 | SC0001GP | 2666 | ++-+--++--+ this other one gives me problems with the sum(), wrong result. SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y') date1, SUM(ordini.totale) sum1, SUM(ordini.quantita) sum2, ordini.cod, ordini.fromprev, carello.numprev, DATE_FORMAT(carello.dataordine,'%d-%m-%Y') date2 FROM ordini, carello WHEREordini.numordine = 2302 AND carello.numprev = ordini.fromprev GROUP BY ordini.numordine, carello.numprev; ++--+--+-+--+-+- ---+ | date1 | sum1 | sum2 | cod | fromprev | numprev | date2 | ++--+--+-+--+-+- ---+ | 29-05-2003 | 3491.984 | 80 | SC0001GP| 2666 |2666 | 28-05-2003 | ++--+--+-+--+-+- ---+ Help is required. I run Mysql 4.0.13 Thanks Patrizio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anyone had a chance to try an Opteron yet?
The G5 may not be all Apple says it is, but then, nothing is what it seems when it comes to CPU's and performance tests. That said, if anyone who wants to buy me one when they come out, I won't complain. Interesting article. get's a bit numberish though. http://www.theregister.co.uk/content/39/31405.html Keith Curtis Maurand [EMAIL PROTECTED] 06/26/03 04:03PM I'd be interested to see what happens when MySQL gets run on a G5. SPECint and SPECfp numbers look very good. there is an 8GB RAM limit on it, though. Curtis On Tue, 24 Jun 2003, Jeremy Zawodny wrote: On Tue, Jun 24, 2003 at 05:37:24PM -0400, Curtis Maurand wrote: Linux compiled on an opteron and targeted for it will lose the 2-gig process limit. the 2-gig number is based on a 32 bit integer. You now would a have 64 bit integer (5 Quintillion as an unsigned integer. :-)). Imagine the 10GB database in memory, plus the temp and heap tables and the indexes. Lets not forget about the ability to have large heap tables, too. Life gets very interesting in the 64 bit space, especially since IA64's aren't exactly plentiful. As soon as I can afford one, I'm buying one. I'm very interesting. Yeah, the operton should kick major ass with MySQL and sufficient memory. :-) Jeremy -- -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Pratices for mySQL Backups in Enterprise
It is a perl program, so you can look at it and see. SAQIB wrote: mysqlhotcopy does your locking for you. So running ./mysqlhostcopy dbase /path/to/backup/dir is perfectly safe while database operations (selct, insert, update etc) are being performed? --- Saqib Ali http://www.xml-dev.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Pratices for mySQL Backups in Enterprise
On 26 Jun 2003 at 12:24, SAQIB wrote: mysqlhotcopy does your locking for you. So running ./mysqlhostcopy dbase /path/to/backup/dir is perfectly safe while database operations (selct, insert, update etc) are being performed? Yes, but the inserts and updates will all be blocked until after the copying is done. If your database is big enough and active enough that locking it during the copying time is unacceptable, consider setting up a replication server and backing that up instead. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sum() problems - I don't understand
On 26 Jun 2003 at 15:07, gerald_clark wrote: The second query contains a join and may have many times more rows in the result set. [snip] ++-+--++--+ | date1 | sum1| sum2 | cod| fromprev | ++-+--++--+ | 29-05-2003 | 436.498 | 10 | SC0001GP | 2666 | ++-+--++--+ [snip] | date1 | sum1 | sum2 | cod | fromprev | numprev | date2 | ++--+--+-+--+-+-- +--- ---+ | 29-05-2003 | 3491.984 | 80 | SC0001GP| 2666 |2666 | 28-05-2003 | ++--+--+-+--+-+-- In fact it appears that for this query there are 8 rows in 'carello' corresponding to each row in 'ordini', so the sums are multiplied by 8. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need your help in a search query
SELECT * FROM ardata WHERE title REGEXP '.*2000.*' change that into parameter, but if you want to search on multiple words as OR, youll have to split them and do separate ORs on tile REGEXP per word, or rewrite this one something like .*(2000)|(leagus).* perhaps, not sure if grouping is suported by regexp in mysql though You could try SOUNDEX(str) http://www.mysql.com/doc/en/String_functions.html which might do what you are looking for HTH Peter -Original Message- From: Tom Johnson [mailto:[EMAIL PROTECTED] Sent: 26 June 2003 14:23 To: [EMAIL PROTECTED] Subject: Need your help in a search query Hi MySQL Experts, I am pretty new at this and need your help in figuring out if it is possible to create a query to search for words in a given field. What I am trying to do is allow a user to enter words to search for a given title in the database. For example, a person is looking up the title 20,000 Leagues Under the Sea but is entering only 2 as the search criteria. When I run the following query, I get no results. The problem is with the comma in 20,000. Any suggestions on how to find the title even though the user only entered 2??? I am running this on the web using PHP and MySQL. Possible someone knows of a good search script in PHP that might help also? Here is the query I was trying to run: SELECT * FROM ardata WHERE title LIKE '%user_input%'; I am using MySQL 4.0.13 and PHP 4.3.2 Thanks for helping! Tom Johnson -- 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: mysql.sock doesn't exist
Well, the find / -name mysql.sock didn't return anything at all so this *is* an odd predicament. Would it work if someone with a similar version e-mailed me a copy of their mysql.sock file? Kevin John Nichel wrote: It doesn't look as if you have any conflicting MySQL versions. When the server is running, have you looked for the socket (someone may have already asked you this, but I'm running out of ideas)? find / -name mysql.sock -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sum() problems - I don't understand
What is the purpose of the join? I think I know what you are trying to do so make the first query a sub query and join the subquery to the carello table. Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Thu, 26 Jun 2003, gerald_clark wrote: Why would you expect they should have the same results? The second query contains a join and may have many times more rows in the result set. PaT! wrote: Dear All, I have these two queries which are supposed to return the same results for the fields with the same name (ex query 1 sum1 = query 2 sum1): The first query gives me back the correct result for the sum(): SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y') date1, SUM(ordini.totale) sum1, SUM(ordini.quantita) sum2, ordini.cod, ordini.fromprev FROMordini WHERE ordini.numordine = 2302 GROUP BYordini.numordine; ++-+--++--+ | date1 | sum1| sum2 | cod| fromprev | ++-+--++--+ | 29-05-2003 | 436.498 | 10 | SC0001GP | 2666 | ++-+--++--+ this other one gives me problems with the sum(), wrong result. SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y') date1, SUM(ordini.totale) sum1, SUM(ordini.quantita) sum2, ordini.cod, ordini.fromprev, carello.numprev, DATE_FORMAT(carello.dataordine,'%d-%m-%Y') date2 FROM ordini, carello WHEREordini.numordine = 2302 AND carello.numprev = ordini.fromprev GROUP BY ordini.numordine, carello.numprev; ++--+--+-+--+-+- ---+ | date1 | sum1 | sum2 | cod | fromprev | numprev | date2 | ++--+--+-+--+-+- ---+ | 29-05-2003 | 3491.984 | 80 | SC0001GP| 2666 |2666 | 28-05-2003 | ++--+--+-+--+-+- ---+ Help is required. I run Mysql 4.0.13 Thanks Patrizio -- 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: Best Pratices for mySQL Backups in Enterprise
Is this better than using mysqldump? Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Thu, 26 Jun 2003, SAQIB wrote: mysqlhotcopy does your locking for you. So running ./mysqlhostcopy dbase /path/to/backup/dir is perfectly safe while database operations (selct, insert, update etc) are being performed? --- Saqib Ali http://www.xml-dev.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]
can you insert null?
Hello all, Does anyone know if you can put the value(not the string) NULL? Something like UPDATE TABLE some_table SET field = NULL Thanks, Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can you insert null?
At 13:48 -0700 6/26/03, Bill2 wrote: Hello all, Does anyone know if you can put the value(not the string) NULL? Something like UPDATE TABLE some_table SET field = NULL Sure. If the column isn't defined as NOT NULL, anyway. Why? Did you try this and get results you didn't expect? Thanks, Bill -- 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: can you insert null?
your statement will do just that, but make sure the field has no default value and allows NULL - Original Message - From: Bill2 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 1:48 PM Subject: can you insert null? Hello all, Does anyone know if you can put the value(not the string) NULL? Something like UPDATE TABLE some_table SET field = NULL Thanks, Bill -- 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: Best Pratices for mySQL Backups in Enterprise
mysqldump creates text files containing insert statements that recreate a table and repopulate it with data. They are somewhat portable across database servers and human editable if necessary. They take up less space than the original table because they do not contain indices (only the statements that would create the indices). mysqldump is nice b/c it works for both InnoDB and MyISAM mysqlhotcopy makes a copy of the actual data files in your database. It is much faster than mysqldump, but the resulting backup is larger b/c it contains indices (unless you use the option to turn them off). Recovery is quicker as the tables exist in the backup directory in full MyISAM table form. With mysqldump you need to actually execute the dump files which can take a while for large tables. mysqlhotcopy does not work with InnoDB. --Ware Adams Jake Johnson wrote: Is this better than using mysqldump? mysqlhotcopy does your locking for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re-Informations about libmysqlD.dll (embedded)
Hi, I would like to know if there are some websites, references (except the manual ;) ), exemples of using the libmysql, especially from windows. I get crazy trying to call the dll from a C# environment !! I get nothing else than a NullReferenceException ???!!!??? I read that for using the libmysqld.dll under windows, I had to recompile it myself, is it right ? Did someone already try to invoke the libmysl.dll or the libmysql.dll from a .Net environment like C# or VB .Net ? For the moment, I see that core lab had a mysql .Net provider which can support the embedded option, so it's possible but I want to achieve it myself ! link: MySQLDirect .NET 1.60 - http://www.crlab.com/(see the news) Thanks for any help Wavyx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can you insert null?
At 14:20 -0700 6/26/03, danchik wrote: your statement will do just that, but make sure the field has no default value and allows NULL Why would it matter if it has a default value? - Original Message - From: Bill2 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 1:48 PM Subject: can you insert null? Hello all, Does anyone know if you can put the value(not the string) NULL? Something like UPDATE TABLE some_table SET field = NULL Thanks, Bill -- 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: can you insert null?
hm? i just did a little test on mysql 3.23.56-nt, and had no problems setting a null value on a null column having a default value other than null. it turned to be null, rather than the default value. ··· yves at unclassified.de -Ursprüngliche Nachricht- Von: danchik [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Donnerstag, 26. Juni 2003 23:20 Betreff: Re: can you insert null? your statement will do just that, but make sure the field has no default value and allows NULL - Original Message - From: Bill2 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 1:48 PM Subject: can you insert null? Hello all, Does anyone know if you can put the value(not the string) NULL? Something like UPDATE TABLE some_table SET field = NULL Thanks, Bill -- 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: Problem setting/activating password
mysqladmin -uroot -ppassword reload Curtis On Tue, 24 Jun 2003, Riaan Oberholzer wrote: Hi, I am created a user by using: GRANT ALL ON *.* TO username@% IDENTIFIED BY password I did this from the mysql command line tool, logged in as root. The command succeeds and the new user is created in the mysql.user table, but the password does not work. I can only use this new user if I use a empty string password (no password). I also tried to do the SET PASSWORD afterwords, no luck. I then tried the Windows GUI / Administration to grant access and then set the password, but again, only an empty string password is accepted. I am using mysql 4.0.13. How do I actually get the password validation to be activated and instruct the server that the new user must provide his password? __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- -- 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]
Re: Best Pratices for mySQL Backups in Enterprise
Cool, Thanks a ton. I think I will stick with mysqldump for a while until my tables get too large. Currently my backups only take a few seconds. Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Thu, 26 Jun 2003, Ware Adams wrote: mysqldump creates text files containing insert statements that recreate a table and repopulate it with data. They are somewhat portable across database servers and human editable if necessary. They take up less space than the original table because they do not contain indices (only the statements that would create the indices). mysqldump is nice b/c it works for both InnoDB and MyISAM mysqlhotcopy makes a copy of the actual data files in your database. It is much faster than mysqldump, but the resulting backup is larger b/c it contains indices (unless you use the option to turn them off). Recovery is quicker as the tables exist in the backup directory in full MyISAM table form. With mysqldump you need to actually execute the dump files which can take a while for large tables. mysqlhotcopy does not work with InnoDB. --Ware Adams Jake Johnson wrote: Is this better than using mysqldump? mysqlhotcopy does your locking for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can you insert null?
Yes, that works for Teradata and Oracle as well. Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Thu, 26 Jun 2003 [EMAIL PROTECTED] wrote: hm? i just did a little test on mysql 3.23.56-nt, and had no problems setting a null value on a null column having a default value other than null. it turned to be null, rather than the default value. ··· yves at unclassified.de -Ursprüngliche Nachricht- Von: danchik [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Donnerstag, 26. Juni 2003 23:20 Betreff: Re: can you insert null? your statement will do just that, but make sure the field has no default value and allows NULL - Original Message - From: Bill2 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 1:48 PM Subject: can you insert null? Hello all, Does anyone know if you can put the value(not the string) NULL? Something like UPDATE TABLE some_table SET field = NULL Thanks, Bill -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql.sock doesn't exist
I am having a similar problem. I looked at another Linux machine that has MySQL 4.0.13 running on it and the file /var/lib/mysql/mysql.sock is there but it is empty. So I created the file, chmod to 777 and then tried to light up /usr/bin/mysqld_safe and it is giving the same error message you are getting...Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2). Further examination shows that the file I manually created was blown away after trying to light it up. So now that makes two of us with the same problem, if I figure it out I will be sure to let you know, please if you do figure it out let me know too. On Thursday, June 26, 2003, at 01:35 PM, Kevin H. Phillips wrote: Well, the find / -name mysql.sock didn't return anything at all so this *is* an odd predicament. Would it work if someone with a similar version e-mailed me a copy of their mysql.sock file? Kevin John Nichel wrote: It doesn't look as if you have any conflicting MySQL versions. When the server is running, have you looked for the socket (someone may have already asked you this, but I'm running out of ideas)? find / -name mysql.sock -- 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]
Speed Up Insert Query Results
Hello All,
Could somebody please enlighten me as to why it takes nearly 2 hours to put 8 rows
of data into my table?
The insert commands I use are:
insert into mytab2
select y03m02_Pt.person, y03m02_Acdt.place, y03m02_Acdt.charge_id, y03m02_Acdt.disch,
y03m02_Pt.age, y03m02_Pt.sex, y03m02_Pt.di, y03m02_Pt.dr_id, y03m02_Pt.drty,
y03m02_Acdt.U, y03m02_Acdt.C, y03m02_Acdt.billing_desc
from y03m02_Acdt, y03m02_Acmt, y03m02_Pt
where
match (billing_desc) against ('search')
AND (U not like 2__)
AND room=0
AND (billing_desc not like %amplifi%)
AND (billing_desc not like %PCR%)
AND (billing_desc not like %western%)
AND C=?
AND y03m02_Pt.person=y03m02_Acmt.person
AND y03m02_Acmt.place=y03m02_Acdt.place
AND y03m02_Acmt.charge_id=y03m02_Acdt.charge_id;
and
#87903
insert into mytab2
select y03m02_Pt.person, y03m02_Acdt.place, y03m02_Acdt.charge_id, y03m02_Acdt.disch,
y03m02_Pt.age, y03m02_Pt.sex, y03m02_Pt.di, y03m02_Pt.dr_id, y03m02_Pt.drty,
y03m02_Acdt.U, y03m02_Acdt.C, y03m02_Acdt.billing_desc
from y03m02_Acdt, y03m02_Acmt, y03m02_Pt
where
C=87903
AND y03m02_Pt.person=y03m02_Acmt.person
AND y03m02_Acmt.place=y03m02_Acdt.place
AND y03m02_Acmt.charge_id=y03m02_Acdt.charge_id;
The first one runs in about 12 mins, and inserts close to 50 rows, which is reasonable
(especially given that it's searching some 60,000,000 rows to find the data to insert.
However, the second command takes between 2 and 3 hours each time.
I'm running MySQL 4.0.13 on a Windows 2000 server with plenty of disk space.
So, is there anything I can do, or should I just go get a pot or two of coffee and
start inserting all the data by hand?
-Charlie
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Pratices for mySQL Backups in Enterprise
On 26 Jun 2003 at 17:16, Ware Adams wrote: mysqldump creates text files containing insert statements that recreate a table and repopulate it with data. They are somewhat portable across database servers and human editable if necessary. They take up less space than the original table because they do not contain indices (only the statements that would create the indices). The dump file will be larger than the MyISAM data file for the original table -- especially if you have many non-text columns (dates, numbers, ENUM columns, etc.). In some cases, when you have large indexes, the index file will be large enough that it and the data file combined will be larger than the dump file, but in some cases it won't be. I wouldn't consider the difference in size, whichever way it goes, to be significant in deciding between backup methods. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error message
Steven Why do you include the field name in your FROM part ? The syntax is SELECT fields FROM table I guess that could be the reason. Try with : SELECT * FROM `traffic` LIMIT 132947 Freddie -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Donnerstag, 26. Juni 2003 20:07 To: Steven Dowd; [EMAIL PROTECTED] Sounds like a corrupt table, try REPAIR TABLE: http://www.mysql.com/doc/en/REPAIR_TABLE.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Steven Dowd [mailto:[EMAIL PROTECTED] Sent: Thursday, June 26, 2003 11:13 AM To: [EMAIL PROTECTED] Subject: error message can someone help me with this error, with my database in MySQL 3.23.49 SQL-query : SELECT * FROM `traffic`.`trafficdb` LIMIT 132947 MySQL said: Got error 127 from table handler have I lost the data, or is there something odd happened which i should be able to fix? Steven Dowd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [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: Speed Up Insert Query Results
On 26 Jun 2003 at 17:45, Charles Vos wrote: Could somebody please enlighten me as to why it takes nearly 2 hours to put 8 rows of data into my table? It's not the INSERT that's taking so long -- it's the SELECT. Try the SELECT alone, and it will probably take the same amount of time. Do an EXPLAIN on each of your SELECT queries and compare the use of indexes to see why there's such a difference. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1044
I keep getting this error when I try to create a new database: mysql create database cheese_catalog; ERROR 1044: Access denied for user: '@localhost' to database'cheese_catalog' -- Ed Kiefer Advantage Mail Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can you insert null?
was the field type varchar? because it seems that no default varchars set the NULL or (NULL) as a literal (NULL) not a binary 0 for some reason. - Original Message - From: [EMAIL PROTECTED] To: danchik [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 2:31 PM Subject: Re: can you insert null? hm? i just did a little test on mysql 3.23.56-nt, and had no problems setting a null value on a null column having a default value other than null. it turned to be null, rather than the default value. ··· yves at unclassified.de -Ursprüngliche Nachricht- Von: danchik [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Donnerstag, 26. Juni 2003 23:20 Betreff: Re: can you insert null? your statement will do just that, but make sure the field has no default value and allows NULL - Original Message - From: Bill2 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 1:48 PM Subject: can you insert null? Hello all, Does anyone know if you can put the value(not the string) NULL? Something like UPDATE TABLE some_table SET field = NULL Thanks, Bill -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multiple Joins and denormalizing
Actually, denormalizing aside, are multiple Joins or statements like... SELECT lastName, location, model, make FROM people, make, models, machines WHERE people.peopleID = machines.peopleID AND make.makeID = models.makeID AND models.modelID = machines.modelID ...common? I guess that's actually called a multiple select statement, not a multiple Join. (?) You wouldn't use those terms interchangeably would you? Thanks, Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can you insert null?
I take that back about defaults, worked fine on all types... the earlier test i did on varchar after inserting NULL i brought the field up in edit mode and aparently the editor converted it to literal (NULL) from NULL (maybe it thought to be smart with varchars) where as the actual stored value was NULL - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: danchik [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 2:31 PM Subject: Re: can you insert null? At 14:20 -0700 6/26/03, danchik wrote: your statement will do just that, but make sure the field has no default value and allows NULL Why would it matter if it has a default value? - Original Message - From: Bill2 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 1:48 PM Subject: can you insert null? Hello all, Does anyone know if you can put the value(not the string) NULL? Something like UPDATE TABLE some_table SET field = NULL Thanks, Bill -- 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: Speed Up Insert Query Results
I ran the SELECT without the INSERT earlier today and it ran considerably
faster (at least 4 times), so I didn't think that it would be too bad to insert the
data.
Would it be faster to combine the two SELECT statements into:
insert into mytab2
select y03m02_Pt.person, y03m02_Acdt.place, y03m02_Acdt.charge_id, y03m02_Acdt.disch,
y03m02_Pt.age, y03m02_Pt.sex, y03m02_Pt.di, y03m02_Pt.dr_id, y03m02_Pt.drty,
y03m02_Acdt.U, y03m02_Acdt.C, y03m02_Acdt.billing_desc
from y03m02_Acdt, y03m02_Acmt, y03m02_Pt
where
(
(
match (billing_desc) against ('search')
AND (U not like 2__)
AND room=0
AND (billing_desc not like %amplifi%)
AND (billing_desc not like %PCR%)
AND (billing_desc not like %western%)
AND C=?
)
(
OR C=87903
)
)
AND y03m02_Pt.person=y03m02_Acmt.person
AND y03m02_Acmt.place=y03m02_Acdt.place
AND y03m02_Acmt.charge_id=y03m02_Acdt.charge_id;
-Original Message-
From: Keith C. Ivey [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 26, 2003 5:54 PM
To: [EMAIL PROTECTED]
Cc: Charles Vos
Subject: Re: Speed Up Insert Query Results
On 26 Jun 2003 at 17:45, Charles Vos wrote:
Could somebody please enlighten me as to why it takes nearly 2
hours to put 8 rows of data into my table?
It's not the INSERT that's taking so long -- it's the SELECT. Try
the SELECT alone, and it will probably take the same amount of time.
Do an EXPLAIN on each of your SELECT queries and compare the use of
indexes to see why there's such a difference.
--
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can you insert null?
hm, no, it works fine with int and varchar(40) -Ursprüngliche Nachricht- Von: danchik [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Freitag, 27. Juni 2003 00:01 Betreff: Re: can you insert null? was the field type varchar? because it seems that no default varchars set the NULL or (NULL) as a literal (NULL) not a binary 0 for some reason. - Original Message - From: [EMAIL PROTECTED] To: danchik [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 2:31 PM Subject: Re: can you insert null? hm? i just did a little test on mysql 3.23.56-nt, and had no problems setting a null value on a null column having a default value other than null. it turned to be null, rather than the default value. ··· yves at unclassified.de -Ursprüngliche Nachricht- Von: danchik [EMAIL PROTECTED] An: [EMAIL PROTECTED] Gesendet: Donnerstag, 26. Juni 2003 23:20 Betreff: Re: can you insert null? your statement will do just that, but make sure the field has no default value and allows NULL - Original Message - From: Bill2 [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 1:48 PM Subject: can you insert null? Hello all, Does anyone know if you can put the value(not the string) NULL? Something like UPDATE TABLE some_table SET field = NULL Thanks, Bill -- 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] -- 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]
group by clause
hi there I wrote this querie : select pop, prot, sw_or_local, sum(qtd_porta) as soma from clientes where status'C' group by pop,prot,sw_or_local And I have this result : +++-++ | pop| prot | sw_or_local | sum | +++-++ |BHE | R2 | local50 | |BHE | R2 | local 12 | +++-++ . . . . . Have you see,? I would like something like that: | pop| prot | sw_or_local | sum | +++-++ |BHE | R2 | local62 Is my group by querie wrong...?
Re: can you insert null?
On 26 Jun 2003 at 15:01, danchik wrote: was the field type varchar? because it seems that no default varchars set the NULL or (NULL) as a literal (NULL) not a binary 0 for some reason. You're confusing various meanings of null. NULL in SQL has nothing to do with binary 0 bytes (which would be represented as '\0' in MySQL SQL statements). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group by clause
Fabio Bernardo wrote: select pop, prot, sw_or_local, sum(qtd_porta) as soma from clientes where status'C' group by pop,prot,sw_or_local And I have this result : +++-++ | pop| prot | sw_or_local | sum | +++-++ |BHE | R2 | local50 | |BHE | R2 | local 12 | +++-++ It looks to me like a data problem, where some rows have leading tabs or spaces before R2 in the prot column and others don't. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
URGENT problem with mysql processes
Major problem! I've installed mysql-standard 4.0.13 (from binary) a new DB server, it is - quad-xeon (500mhz each cpu) - 3 gig RAM - Linux Redhat 7.3 When I start up mysql, processes looking like the following begin to pile up until it kills the server: | 106 | unauthenticated user | 192.168.1.59:1925 | NULL | Connect | NULL | login | NULL | | 115 | unauthenticated user | 192.168.1.53:2041 | NULL | Connect | NULL | login | NULL | | 118 | unauthenticated user | 192.168.1.56:4172 | NULL | Connect | NULL | login | NULL | | 119 | unauthenticated user | 192.168.1.56:4173 | NULL | Connect | NULL | login | NULL | I need to fix this immediately as my site cannot run ! Thanks in advance. I've seen this exact same situation happen to others (posted on various boards), however never saw any solutions ;/ -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL client - server side help menu
Hi MySQL Fans, I understood that within the mysql command line client there exist two help pages: a client help page - accessable with help a server help page accessable with help all I found that the file (seems like a table within the mysql privilege database) was not included in the 4.1 alpha (rpm) download. In Redhats distributions (7.2 and 9.0) it also is not mentioned in the help menu. Does anybody have the server side help topic installed (mysql.help_topic) or knows where I could receive it from (Hello MySQL AB ;-) ? mysql help all ERROR 1146: Table 'mysql.help_topic' doesn't exist Best regards -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Practices for mySQL Backups in Enterprise
Hi Subhakar, I would be interested to know what you mean with roll forward ? In case you have another backup let's say @10AM you could use this one, but if you dont have another backup where do you want to do a roll forward from ?? Do I miss something here ?? Best regards Nils Valentin Tokyo/Japan 2003 6 27 09:03Subhakar Burri : Can I roll forward if I do backups using Mysqldump? Say, I did backups using Mysqldump @ 8:00 AM and my instance crashed @ 2:00 PM. I can restore the tables (both Innodb and MyISAM tables) from my 8:00AM backup, but can I roll forward the data that changed after 8:00 AM or do I lose the data after 8:00 AM. Pls clarify... a link to relevant information would be just fine too... Thankx in advance SB -Original Message- From: Keith C. Ivey [mailto:[EMAIL PROTECTED] Sent: Thursday, June 26, 2003 2:49 PM To: [EMAIL PROTECTED] Subject: Re: Best Pratices for mySQL Backups in Enterprise On 26 Jun 2003 at 17:16, Ware Adams wrote: mysqldump creates text files containing insert statements that recreate a table and repopulate it with data. They are somewhat portable across database servers and human editable if necessary. They take up less space than the original table because they do not contain indices (only the statements that would create the indices). The dump file will be larger than the MyISAM data file for the original table -- especially if you have many non-text columns (dates, numbers, ENUM columns, etc.). In some cases, when you have large indexes, the index file will be large enough that it and the data file combined will be larger than the dump file, but in some cases it won't be. I wouldn't consider the difference in size, whichever way it goes, to be significant in deciding between backup methods. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Pratices for mySQL Backups in Enterprise
I'd instead setup a 2nd backup server that's a slave to the master, replicates all the time, keeps in sync. At X time, stop replication/mysql, backup data to tape .. restart mysql and it will catch up/re sync back to master.. On Thu, 26 Jun 2003, SAQIB wrote: mysqlhotcopy does your locking for you. So running ./mysqlhostcopy dbase /path/to/backup/dir is perfectly safe while database operations (selct, insert, update etc) are being performed? --- Saqib Ali http://www.xml-dev.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: URGENT problem with mysql processes
Hi Keith, The wrong IP address could only avoid a client to connect to the database server. If you have the TCP/IP address specified in /etc/php.ini or for phpmyadmin in config.inc.php or which ever tool you use than of course it will try to connect to this IP address. Best regards Nils Valentin Tokto/Japan 2003 6 27 11:02Keith Bussey : Hrmmmeaning if I do id mysql, that information (group etc..) ? I've halted that server and moved everything back to my original server for now, I had too much downtime. I did notice something else interesting though: The old mahcine's IP is 192.168.1.71 New machine's IP is 192.168.1.79 Now that it's halted, instead of changing the IP back to .71 in my pages/scripts I added .79 to the .71 machine so it has both Now that machine experienced the exact same problem. Switching my code to use .71 again however, and no problems. Could somehow there be a problem with the IP address 192.168.1.79?? It seems very strange, however tomorrow I will try putting the .71 on the new machine and see if it works or not -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
example of using FORCE INDEX
hi there i dont think this will help any further on my full text searching but i would like an example of how to use FORCE INDEX , there is no example on the mysql site and the hint is misleading i am getting sql errors , lemme know thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
force index
sorry i worked it out put it after the table , i got some interesting results Queried The Database 1 times for 9.9892050027847 seconds +---+--+--+--+-++--+-+ | table | type | possible_keys| key | key_len | ref | rows | Extra | +---+--+--+--+-++--+-+ | sl| fulltext | libraryID,locations,shotlist | shotlist | 0 | |1 | Using where | | p | eq_ref | PRIMARY,programID| PRIMARY | 1 | sl.programID |1 | | | r | eq_ref | PRIMARY,reporterID | PRIMARY | 1 | sl.reporterID |1 | | | tt| eq_ref | PRIMARY,tape_typeID | PRIMARY | 1 | sl.tape_typeID |1 | | +---+--+--+--+-++--+-+ Queried The Database 1 times for 9.9337540864944 seconds +---+--+-+--+-++--+-+ | table | type | possible_keys | key | key_len | ref| rows | Extra | +---+--+-+--+-++--+-+ | sl| fulltext | libraryID,shotlist | shotlist | 0 ||1 | Using where | | p | eq_ref | PRIMARY,programID | PRIMARY | 1 | sl.programID |1 | | | r | eq_ref | PRIMARY,reporterID | PRIMARY | 1 | sl.reporterID |1 | | | tt| eq_ref | PRIMARY,tape_typeID | PRIMARY | 1 | sl.tape_typeID |1 | | +---+--+-+--+-++--+-+ the first was using the force index, the second was without so is adding to force to use and index going to make it slower ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
load data
I have this CREATE TABLE `dictionary` ( `id` int(11) NOT NULL auto_increment, `word` text NOT NULL, `def` text NOT NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM AUTO_INCREMENT=1 ; I have a huge text file, that is in the format of stringtabstringreturn I want to load this into mysql, but I want to skip the id field and let that auto populate with the auto increment value. What is the syntax for this? -- - 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]
