Re: Default character set error
Hi! On Jul 31, Kittiphum Worachat wrote: Hi. With MySQL 4.1.0 alpha when I try to set default character set to tis620 MySQL start OK but have crash every time when perform to tun query (with default_character_set=latin1 it never got error) and with MySQL 4.0.14 it can do with out any error. On what query does it crash ? Could you go to bugs.mysql.com and submit a bugreport about it ? Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqldaemon not starting
Hi , I am this error while running mysqld_safe # ./bin/mysqld_safe --user=mysql [1] 19413 # Starting mysqld daemon with databases from /export/home/siptech/vidhya/ifmonitor/mysql/data 030728 12:17:49 mysqld ended The daemon is not starting . what could be the problem thanks Vidhya. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems while compiling mysql on HP-UX 11.11
(B[EMAIL PROTECTED](B $B%D%?%M%9([EMAIL PROTECTED](B (B (BHello, (B (BI am trying to install MySQL 3.23.55 on HP-UX 11.11 (B (BWhen I ran ./configure, I got the following messages : (B (B Configure: WARNING: sys/socket.h: present but cannot be compiled (B Configure: WARNING: sys/socket.h: check for missing prerequisite headers? (B Configure: WARNING: sys/socket.h: proceeding with the preprocessor's result (B (B/* The messages above also appeared for sys/stream.h */ (B (B checking sys/socket.h usability... no (B checking sys/socket.h presence... yes (B checking for sys/socket.h... yes (B (B/* The messages above also appeared for other *.h files */ (B (B (BThank you very much. (B $B0J>e!"$h$m$7$/$*4j$$$7$^$9!#(B (B (B (BDennis Francis B. Tutanes (BProject Leader (BTsukiden Software Technologies, Inc. (Bmailto:[EMAIL PROTECTED] (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A new charset without recompiling
Can I put a new charset or modify an existing one without recompiling MySQL? If yes, please tell me how. Thanks Anticipated, Iulian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problem
Beacuse of this type of mail , i got listed on spam bag for using the mailing lists inform the linux comunity of my site , wich is a free service. If see this kind of junk on any mailing list ill make it my personal mission to have you domain fried Don't post unless it's goning to help the opens source comunity ... - Original Message - From: Jonathan Fowler [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 31, 2003 4:34 AM Subject: Re: replication problem www.kapadiaz.com = download free movies songs and also free 5 gb hosting -- 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: mysqlhotcopy problem
On Wed, 2003-07-30 at 17:40, Vladimir Shiray wrote: How can I make hotcopy of my database if it have many tables (more than 1000) ??? mysqlhotcopy failed when it try to lock all tables ... How many tables can I lock at one time ? Can I increase this limit ? Hi, You probably ran out of file handles. The available file handles can be increased by modifying the table_cache and max_connections variables in my.cnf, eg. set-variable = table_cache=3000 You might need to tinker with /proc/sys/fs/file-max if you hit your operating system's hard limit. regards, Martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting Duplicate Records
Kim Mackey wrote: Group, I have been working on a project for a while now trying to figure out how to remove duplicate records from a single table using a query. To complicate matters, what constitutes a duplicate record is a match on several fields, but not all fields. I have been successful in matching all duplicates based on the fields I'm interested in, but this only returns the duplicate records. I want a query that will return all records from the table, but only once if certain fields are the same. The table structure is basically: User Code Date Last Name First Name Address City State ZIP and then some other fields I don't want to use the values in the other fields to determine if the record duplicates another. I'm not concerned about which one of the duplicate records I keep because I will make a backup copy of this table before removing the duplicates, so I will still have all the data from the records deleted. Later I will redesign the tables for this database and link back in the data from the other fields. So I need a method that will output one and only one record from the table in which the data in the above mentioned fields are the same. Again, even if the data in the remaining fields are different I just want one of the records, and I don't care which one. CREATE TABLE `new_table` SELECT * FROM `old_table` GROUP BY [your_fields_you_want_unique]; -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advice wanted on Data Structure
-Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: 30 July 2003 16:42 To: [EMAIL PROTECTED] Subject: Advice wanted on Data Structure I have a question that I hope I can explain well enough: I am trying to figure out a data structure for an inventory system. The system contains: Items Kits(Made from a collection of Items and/or other Kits) Now my question is: Is there any way to structure this in a database so that I could run a single query to get the contents of a kit, even though it contains other kits? My problems occur when I try to create the tables as so: ITEMS:KIT_CONTENTSKITS KIT_IDKIT_ID ITEM_ID-ITEM_ID Description Description Price Etc This is a classic problem known as a Bill of Materials explosion and unfortunately relational databases don't handle it very well. Storage is easy(ish). Fundamentally you have a recursive many to many relationship between components, resolved as Component: Component_Link id ---|---assembly_id name|---subcomponent_id That is 2 foreign keys back to the same master table, if the diagram isn't clear. In OO terms, both item and kit are subclasses of component. There are may ways to implement that in a relation database, but the simplest is to store them as a single table with a type field. Retrieval is harder. To get the contents of an assembly (kit), select * from component as assembly, component as subcomponent, component_link where assembly.name=? and component_link.assembly_id=assembly_id and subcomponent.id= componentLink.subcomponent_id BUT, this only goes down to one level which may be enough for most purposes, but for stock monitoring (e.g. I've sold 10 of kit ZZA102, what effect does that have on my item stocks), you need to do it recursively. With mysql ( and most other DBMS) the only alternative is to do the recursion in a program - ie get all first level children foreach get next level foreach get next level etc Oracle has an excellent CONNECT BY extension to standard sql which does this brilliantly, and I believe mysql AB are planning to imlpement it sometime. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldaemon not starting
Vidhya CS [EMAIL PROTECTED] wrote: I am this error while running mysqld_safe # ./bin/mysqld_safe --user=mysql [1] 19413 # Starting mysqld daemon with databases from /export/home/siptech/vidhya/ifmonitor/mysql/data 030728 12:17:49 mysqld ended The daemon is not starting . what could be the problem thanks Look into error log file. -- 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: Transactions
I'm reticent to consume any more of this lists bandwidth and trust this will end the thread, but here is my point. There are many 'gotchas' to consider when developing an application that uses any dynamic file structure from simple flat ascii files to engorged dbms's. Add multi-user and multi-tasking to the mix and you have a tiger by the tail. The bottom line is, it is the responsibility of the programmer to ensure that every tool they use is safely and correctly implemented. Transactions or commit and rollback levels help, but they are only a small piece of the package. It is dangerous to assume that because referential integrity has been maintained, that the data is as intended. Any application that allows its data to be manipulated in other than a read-only status is responsible for its integrity. Forgive me if I'm missing the point here I have only just finished a database programming course in university and do not have extensive real practice. However I was tought that databases are responsible (among other things) of data integrity and consistency. Yhe client's job is to add data and ask for data. The rest should be database's job. Database ensures there are no violation of business rules (constraints, foreign keys) and that full data not just part of it will be added (transactions). Of course a client must be programmed in a way to make use of these features. But all the things you were talking about really are part of RDBMS definition. appropriate result to the client. You certainly would not want to lock a region or even a row from a stateless client. If two people access the same record/row at the same time and change non-key information but commit the changes sequentially 2 seconds apart the referential integrity could be correct, but the data is not as the first person intended. In this scenario, the programmer needs to provide a mechanism to advise the first client that his changes were overwritten, thereby turning a stateless event into a stateful one and maintaining control of the application. This situation is one of the main reasons you want to use a database. It is database's job to handle multiple simultaneous accesses on the same data/row. This is not an easy task, there are several different locking mechanisms to implement this and different types of transactions for end users/client applications. Database creators have spent a lot of time on implementing these things so it is probably quite wise not to try to invent a bicycle here. A robust file handler or dbms is a wonderful tool but it is only a tool and does not relieve a programmer of their responsibility. That's my point. It is not the APPLICATION programmers responsibility to handle simultaneous accesses to the same data, to ensure data integrity. The application/database user only sends data to database and asks for data. The rest is the job of RDBMS. Thats the whole point of using a RDBMS imho. Kaarel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help to structure query without using subselects
Hi List Can anyone help me to do the following without the need of subselects: I have a table that contains rows where mostly, the only common field is the name. The rest of the data fields, even though they are related to the name, differs from row to row ( they are things like the address etc, which could have changed on the next entry for the same name) What I'm trying to get out of the table is a list of all the names ( with the duplicates pruned out by DISTINCT) and other collumns for the row with the highest id. This works: select distinct name, max(id) as mid from table group by name but it only returns the name and the highest id for such a name. if I try this: select distinct name, max(id) as mid, anything_else from table group by name it returns the FIRST anything_else, and not the value in the last row for that name ( as I would like it to) Any help? mysql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bug or some mistake ???
hello I have a OpenBSD3.3 box with mysql-server 4.0.13. the mysql-server runs normally at the beginnig . Trouble come up these days that the mysqld server will down . There's error messages appers that lost connection during query when the client connect to it. There's errors in .err log files like this: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. What's matter with mysql-server or openbsd3.3? mysql-server runs perfect on FreeBSD or RedHat instead. How to fix it ? thanks, any idea appreciated powercola [EMAIL PROTECTED] 2003-07-29 = _._ _,-'`-._ = = (,-.`._,'( |\`-/| = = `-.-' \ )-`( , o o) = = `-\`_`'- = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bug or some mistake ???
Hi, i already have this problem with MySQL 4.0.13 (max) on RedHat, with the same message in .err file. Does you enable QUERY_CACHE in your configuration ? Thierno 6C -Original Message- From: powercola [mailto:[EMAIL PROTECTED] Sent: jeudi 31 juillet 2003 09:47 To: [EMAIL PROTECTED] Subject: bug or some mistake ??? hello I have a OpenBSD3.3 box with mysql-server 4.0.13. the mysql-server runs normally at the beginnig . Trouble come up these days that the mysqld server will down . There's error messages appers that lost connection during query when the client connect to it. There's errors in .err log files like this: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. What's matter with mysql-server or openbsd3.3? mysql-server runs perfect on FreeBSD or RedHat instead. How to fix it ? thanks, any idea appreciated powercola [EMAIL PROTECTED] 2003-07-29 = _._ _,-'`-._ = = (,-.`._,'( |\`-/| = = `-.-' \ )-`( , o o) = = `-\`_`'- = -- 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: Deleting Duplicate Records
The cleanest way is to add a column (ukey) to the table which will uniquely identify each record. This will help you in the following phases too. Let's say the name of your table is 'focus_table'. 1.adding the unique key 'ukey': ALTER TABLE 'focus_table' ADD COLUMN ukey BIGINT(12) AUTO_INCREMENT PRIMARY KEY; *Now you know what records are remaining after deleting the duplicates and the table will have the same structure. 2.backup your table 3.select the duplicates: CREATE TEMPORARY TABLE dupls SELECT * FROM 'focus_table' GROUP BY field1,field2,... ; *field1,field2,... is the list of the fields which give the duplicates. 4.empty the table: DELETE FROM 'focus_table'; 5.fill it with unique records: INSERT INTO 'focus_table' SELECT * FROM dupls; - Original Message - From: Cybot [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 31, 2003 11:41 AM Subject: Re: Deleting Duplicate Records Kim Mackey wrote: Group, I have been working on a project for a while now trying to figure out how to remove duplicate records from a single table using a query. To complicate matters, what constitutes a duplicate record is a match on several fields, but not all fields. I have been successful in matching all duplicates based on the fields I'm interested in, but this only returns the duplicate records. I want a query that will return all records from the table, but only once if certain fields are the same. The table structure is basically: User Code Date Last Name First Name Address City State ZIP and then some other fields I don't want to use the values in the other fields to determine if the record duplicates another. I'm not concerned about which one of the duplicate records I keep because I will make a backup copy of this table before removing the duplicates, so I will still have all the data from the records deleted. Later I will redesign the tables for this database and link back in the data from the other fields. So I need a method that will output one and only one record from the table in which the data in the above mentioned fields are the same. Again, even if the data in the remaining fields are different I just want one of the records, and I don't care which one. CREATE TABLE `new_table` SELECT * FROM `old_table` GROUP BY [your_fields_you_want_unique]; -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.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: Problem with adding a new user by non root user with GRANT OPTION
Victoria Reznichenko [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Marcin Blazowski [EMAIL PROTECTED] wrote: Marcin Blazowski [EMAIL PROTECTED] wrote: I've created a database and a user with ALL PRIVILEGES and GRANT OPTIONS To be able give permissions to other users you should have UPDATE privilege and GRANT option. 2. Why everything works like I expect (I can grant privileges with the way described above, User doesn't have UPDATE privilege) on my MySQL compiled from sources on RedHat? Could you be more detailed? Show me an example, i.e. what permissions does user have? Now, I'm not sure if it worked OK. I've moved to 4.0.14 and it don't. I have the same problem but I've discoverd some new feature (Ed Brown send a news to this group at 19-07-2003 16:57, subject: creating db admins). Like Ed Brown I can't grant my privileges to other user and setting a password for him. Below is the long example what I do: mysql select user, grant_priv, insert_priv, update_priv from user; +--++-+-+ | user | grant_priv | insert_priv | update_priv | +--++-+-+ | root | Y? | Y?? | Y?? | | root | Y? | Y?? | Y?? | +--++-+-+ 2 rows in set (0.00 sec) mysql show databases; +--+ | Database | +--+ | mysql??? | +--+ 1 row in set (0.00 sec) mysql grant all privileges on temp.* to [EMAIL PROTECTED] identified by 'haslo' w ith grant option; Query OK, 0 rows affected (0.05 sec) mysql flush privileges; Query OK, 0 rows affected (0.02 sec) mysql select user, grant_priv, insert_priv, update_priv, host from user; +---++-+-+---+ | user? | grant_priv | insert_priv | update_priv | host? | +---++-+-+---+ | root? | Y? | Y?? | Y?? | localhost | | root? | Y? | Y?? | Y?? | % | | blazo | N? | N?? | N?? | localhost | +---++-+-+---+ 3 rows in set (0.00 sec) mysql select user, db, grant_priv from db; +---+--++ | user? | db?? | grant_priv | +---+--++ | blazo | temp | Y? | +---+--++ 1 row in set (0.00 sec) mysql exit Bye C:\mysql\binmysql -u blazo -p Enter password: * Welcome to the MySQL monitor.? Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.0.14-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql create database temp; Query OK, 1 row affected (0.01 sec) mysql grant select on temp.* to [EMAIL PROTECTED] identified by 'haslo'; --- ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'mysql' mysql grant select on temp.* to [EMAIL PROTECTED]; -- Query OK, 0 rows affected (0.00 sec) mysql exit Bye C:\mysql\binmysql -u root Welcome to the MySQL monitor.? Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.0.14-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use mysql Database changed mysql select user, host, grant_priv, update_priv, insert_priv from user; ++---++-+-+ | user?? | host? | grant_priv | update_priv | insert_priv | ++---++-+-+ | root?? | localhost | Y? | Y?? | Y?? | | root?? | % | Y? | Y?? | Y?? | | blazo2 | localhost | N? | N?? | N?? | | blazo? | localhost | N? | N?? | N?? | ++---++-+-+ 4 rows in set (0.01 sec) mysql select user, grant_priv, db from db; +++--+ | user?? | grant_priv | db?? | +++--+ | blazo? | Y? | temp | | blazo2 | N? | temp | +++--+ 2 rows in set (0.00 sec) So, Why the first of the matched lines don't work and the second works OK. Why I can't create user with a password set? You said that the user must have UPDATE privilege on mysql.* to grant his rights to other user. How can I give him that right and be sure that his is not able to change root (or any other user) password? Thanks a lot for answer. Marcin Blazowski -- Marcin Blazowski Incenti SA tel. +48 71 370 74 50 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication question
GlacierI have connect my web server (master) to my local server (slave) through dial-up and replicate 2 databases. a) Is there a log file describing the replications that happend (if any)? b) how do I know when to stop the dial-up so not to stop any data transfer through repliication? Thanx Nikos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show variables
Primaria Falticeni [EMAIL PROTECTED] wrote: How can I see the value of master-connect-retry? show variables doesn't work on it. Use SHOW SLAVE STATUS command: http://www.mysql.com/doc/en/SHOW_SLAVE_STATUS.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: utf8 in 4.1 for Windows
Jon Hancock [EMAIL PROTECTED] wrote: I have read in past postings that UTF8 is not yet working with MySQL 4.1 on the Windows build. Can anyone verify this? Yes, utf8 was not compiled. It will be fixed in the next release. If it is not yet working, can anyone give an estimate when a build will be available that should work? -- 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: show variables
Thank you very much! 1. Is it a way to grow the speed of the replication out of the master-connect-retry variable? 2. I backup the databases. Assuming that the last relay log file is sql-relay-bin.005 and the last bin log file is sql-bin.007, can I simply delete the files from sql-relay-bin.001 to sql-relay-bin.004 and from sql-bin.001 to sql-bin.006? If not, how can I escape by these logs in an elegant way? Respectfully Yours, Iulian - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 31, 2003 1:16 PM Subject: Re: show variables Primaria Falticeni [EMAIL PROTECTED] wrote: How can I see the value of master-connect-retry? show variables doesn't work on it. Use SHOW SLAVE STATUS command: http://www.mysql.com/doc/en/SHOW_SLAVE_STATUS.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]
Constraints under 3.23.55-nt
Hello programmers: I use Version DBMS MySQL 3.23.55 runing under Windows 2000. I have coded this from a textbook; === C:\mysql\bintype constraint.sql USE company; DROP TABLE IF EXISTS EMPLOYEE; CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSNCHAR(9), DNO INT NOT NULL DEFAULT 1, CONSTRAINT EMPPK PRIMARY KEY (SSN), CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT EMPDEPTFK FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET DEFAULT ON UPDATE CASCADE) TYPE = InnoDB; DESCRIBE EMPLOYEE; C:\mysql\bin === This is what happens : === C:\mysql\binmysql -vvvconstraint.sql -- DROP TABLE IF EXISTS EMPLOYEE -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSNCHAR(9), DNO INT NOT NULL DEFAULT 1, CONSTRAINT EMPPK PRIMARY KEY (SSN), CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT EMPDEPTFK FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET DEFAULT ON UPDATE CASCADE) TYPE = InnoDB -- Query OK, 0 rows affected (0.00 sec) -- DESCRIBE EMPLOYEE -- +--+---+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+---+ | FNAME| varchar(15) | | | | | | MINIT| char(1) | YES | | NULL| | | LNAME| varchar(15) | | | | | | SSN | varchar(9)| | PRI | | | | BDATE| date | YES | | NULL| | | ADDRESS | varchar(30) | YES | | NULL| | | SEX | char(1) | YES | | NULL| | | SALARY | decimal(10,2) | YES | | NULL| | | SUPERSSN | varchar(9)| YES | | NULL| | | DNO | int(11) | | | 1 | | +--+---+--+-+-+---+ 10 rows in set (0.00 sec) Bye C:\mysql\bin === My problem is, I want some feedback from MySQL that my versions actually supports those statements, including innodB and all additional constraint information, Describe does not give me feedback about foreign keys and referential triggered actions. Is InnoDB the correct table type for properly supporting referential triggered actions, please ? If not, what can I do instead ? Upgrade to some newer MySQL version please ? Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting unique values:K@N@!:
Alec Smith [EMAIL PROTECTED] wrote: I have two tables as below: CREATE TABLE domain_types ( type_id INT(4) NOT NULL AUTO_INCREMENT, name VARCHAR(10) UNIQUE NOT NULL, description VARCHAR(75), PRIMARY KEY(type_id) ) TYPE=INNODB COMMENT=Types of domains we store; CREATE TABLE domains ( domain_id INT(6) NOT NULL AUTO_INCREMENT, domain VARCHAR(50) UNIQUE NOT NULL, type_id INT(4) NOT NULL, PRIMARY KEY(domain_id) ) TYPE=INNODB COMMENT=Domains; I can get the below result easily: mysql select distinct t.name,d.domain from domain_types t, domains d WHERE t.type_id=d.type_id ORDER BY t.type_id; +--++ | name | domain | +--++ | hostdom1 | abc123.com | | hostdom1 | abc124.com | | hostdom1 | abc125.com | | hostdom2 | abc127.com | | hostdom2 | abc126.com | | hostdom3 | abc128.com | | hostdom4 | abc129.com | | hostdom4 | abc130.com | +--++ 8 rows in set (0.01 sec) But what I really need is a result like +--++ | name | domain | +--++ | hostdom1 | abc123.com | | hostdom2 | abc127.com | | hostdom3 | abc128.com | | hostdom4 | abc129.com | +--++ where only the t.name and d.domain pair with the highest domain_id for each type_id are given. Is there a way to do this without resulting to seperate SQL queries for each entry in the domain_types table? I'm using MySQL 4.0.14 on FreeBSD 5.1. Use SELECT ... GROUP BY name. DISTINCT is applied to the all columns in the select list. -- 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]
I Specify explicitly TYPE = InnoDB But My SQL gives me TYPE=MyISAM ?
Dear MySQL developers, Could some experienced Database developer please take a look at this ? It is supposed to be plain SQL2. How can it be coded under MySQL Especially all referential triggered actions. According to the manual : 7.5.5.2 Foreign Key Constraints Starting from version 3.23.43b InnoDB features foreign key constraints. InnoDB is the first MySQL table type which allows you to define foreign key constraints to guard the integrity of your data. Yours Sincerely Morten Gulbrandsen === -- DROP TABLE IF EXISTS EMPLOYEE -- Query OK, 0 rows affected (0.01 sec) -- CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSNCHAR(9), DNO INT NOT NULL DEFAULT 1, CONSTRAINT EMPPK PRIMARY KEY (SSN), CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT EMPDEPTFK FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET DEFAULT ON UPDATE CASCADE) TYPE = InnoDB -- Query OK, 0 rows affected (0.00 sec) -- DROP TABLE IF EXISTS DEPARTMENT -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE DEPARTMENT ( DNAME VARCHAR(15) NOT NULL, DNUMBER INT NOT NULL, MGRSSN CHAR(9) NOT NULL DEFAULT '88866', MGRSTARTDATEDATE, CONSTRAINT DEPTPK PRIMARY KEY (DNUMBER), CONSTRAINT DEPTSK UNIQUE (DNAME), CONSTRAINT DEPTMGRFK FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET DEFAULT ON UPDATE CASCADE ) TYPE = InnoDB -- Query OK, 0 rows affected (0.00 sec) -- DROP TABLE IF EXISTS DEPT_LOCATIONS -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE DEPT_LOCATIONS ( DNUMBER INT NOT NULL, DLOCATION VARCHAR(15) NOT NULL, PRIMARY KEY (DNUMBER, DLOCATION), FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE = InnoDB -- Query OK, 0 rows affected (0.00 sec) -- DROP TABLE IF EXISTS PROJECT -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE PROJECT ( PNAME VARCHAR(15) NOT NULL, PNUMBER INT NOT NULL, PLOCATION varchar(15), DNUMint NOT NULL, PRIMARY KEY (PNUMBER), UNIQUE (PNAME), FOREIGN KEY (DNUM) REFERENCES DEPARTMENT (DNUMBER)) TYPE = InnoDB -- Query OK, 0 rows affected (0.02 sec) -- DROP TABLE IF EXISTS WORKS_ON -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE WORKS_ON ( ESSNCHAR(9) NOT NULL, PNO INT NOT NULL, HOURS DECIMAL(3,1)NOT NULL, PRIMARY KEY (ESSN, PNO), FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY (PNO) REFERENCES PROJECT (PNUMBER))TYPE = InnoDB -- Query OK, 0 rows affected (0.00 sec) -- DROP TABLE IF EXISTS DEPENDENT -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE DEPENDENT ( ESSNCHAR(9) NOT NULL, DEPENDENT_NAME VARCHAR(15) NOT NULL, SEX CHAR, BDATE DATE, RELATIONSHIPVARCHAR(8), PRIMARY KEY (ESSN, DEPENDENT_NAME), FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN))TYPE = InnoDB -- Query OK, 0 rows affected (0.00 sec) -- SHOW CREATE TABLE EMPLOYEE -- +--+ --+ | Table| Create Table | +--+ --+ | EMPLOYEE | CREATE TABLE `employee` ( `FNAME` varchar(15) NOT NULL default '', `MINIT` char(1) default NULL, `LNAME` varchar(15) NOT NULL default '', `SSN` varchar(9) NOT NULL default '', `BDATE` date default
Re: I Specify explicitly TYPE = InnoDB But My SQL gives me TYPE=MyISAM ?
Hi, On Thu, Jul 31, 2003 at 02:46:06PM +0200, Morten Gulbrandsen wrote: According to the manual : 7.5.5.2 Foreign Key Constraints Starting from version 3.23.43b InnoDB features foreign key constraints. InnoDB is the first MySQL table type which allows you to define foreign key constraints to guard the integrity of your data. TYPE = InnoDB only works if MySQL is compiled with InnoDB support, which is the default in binaries from MySQL. If you compile it yourself you need to configure --with-innodb . 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: Constraints under 3.23.55-nt
Morten Gulbrandsen [EMAIL PROTECTED] wrote: I use Version DBMS MySQL 3.23.55 runing under Windows 2000. I have coded this from a textbook; === C:\mysql\bintype constraint.sql USE company; DROP TABLE IF EXISTS EMPLOYEE; CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSNCHAR(9), DNO INT NOT NULL DEFAULT 1, CONSTRAINT EMPPK PRIMARY KEY (SSN), CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT EMPDEPTFK FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET DEFAULT ON UPDATE CASCADE) TYPE = InnoDB; DESCRIBE EMPLOYEE; C:\mysql\bin === This is what happens : === C:\mysql\binmysql -vvvconstraint.sql -- DROP TABLE IF EXISTS EMPLOYEE -- Query OK, 0 rows affected (0.00 sec) -- CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10,2), SUPERSSNCHAR(9), DNO INT NOT NULL DEFAULT 1, CONSTRAINT EMPPK PRIMARY KEY (SSN), CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT EMPDEPTFK FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET DEFAULT ON UPDATE CASCADE) TYPE = InnoDB -- Query OK, 0 rows affected (0.00 sec) -- DESCRIBE EMPLOYEE -- +--+---+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+---+ | FNAME| varchar(15) | | | | | | MINIT| char(1) | YES | | NULL| | | LNAME| varchar(15) | | | | | | SSN | varchar(9)| | PRI | | | | BDATE| date | YES | | NULL| | | ADDRESS | varchar(30) | YES | | NULL| | | SEX | char(1) | YES | | NULL| | | SALARY | decimal(10,2) | YES | | NULL| | | SUPERSSN | varchar(9)| YES | | NULL| | | DNO | int(11) | | | 1 | | +--+---+--+-+-+---+ 10 rows in set (0.00 sec) Bye C:\mysql\bin === My problem is, I want some feedback from MySQL that my versions actually supports those statements, including innodB and all additional constraint information, Describe does not give me feedback about foreign keys and referential triggered actions. Is InnoDB the correct table type for properly supporting referential triggered actions, please ? If not, what can I do instead ? Upgrade to some newer MySQL version please ? You can see foreign key definitions using SHOW CREATE TABLE or SHOW TABLE STATUS statements. ON UPDATE CASCADE works since 4.0.8. -- 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]
AW: I Specify explicitly TYPE = InnoDB But My SQL gives me TYPE=MyISAM ?
Dear Fred, according to what I experience, I do definitely run the default binaries, perhaps the daemon mysqld has to be started with an option in order to support InnoDB ? How can this be investigated ? C:\mysql\binmysqld -v mysqld Ver 3.23.55-max-debug for Win95/Win98 on i32 C:\mysql\binmysql -v Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 264 to server version: 3.23.55-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql C:\mysql\binver Microsoft Windows 2000 [Version 5.00.2195] This seems fine to me, I believe I have the default binaries. If InnoDB is not supported in MySQL, then I would have expected a warning, Or an error message. As we all can see, no warning or error is issued, perhaps another error level can give me more feedback ? Yours Sincerely Morten Gulbrandsen -Ursprüngliche Nachricht- Von: Fred van Engen [mailto:[EMAIL PROTECTED] TYPE = InnoDB only works if MySQL is compiled with InnoDB support, which is the default in binaries from MySQL. If you compile it yourself you need to configure --with-innodb . 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
WHERE x IN (SELECT x ...
I believe the multiple 'SELECT' statements will not be included until version 4.1 is released? If this is the case, is there a crude workaround method of attempting to perform the following until such a time as it is? SELECT COUNT(*) FROM messages WHERE forum_id IN (SELECT forum_id FROM forums WHERE team_no = 400) Many thanks Gary
RE: WHERE x IN (SELECT x ...
If this is the case, is there a crude workaround method of attempting to perform the following until such a time as it is? SELECT COUNT(*) FROM messages WHERE forum_id IN (SELECT forum_id FROM forums WHERE team_no = 400) select count(message_id) -- assuming you have a non-null id column from messages m inner join forums f on m.forum_id=f.forun_id where team_id=400 This will give the wrong result if a message can appear in more than one forum. In that case use count distinct(message_id) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I Specify explicitly TYPE = InnoDB But My SQL gives me TYPE=MyISAM ?
Morten, On Thu, Jul 31, 2003 at 03:27:51PM +0200, Morten Gulbrandsen wrote: according to what I experience, I do definitely run the default binaries, perhaps the daemon mysqld has to be started with an option in order to support InnoDB ? How can this be investigated ? Well, I never used InnoDB, but have it compiled because I intend to do so sometime. What I see in the error log (hostname.err on Unix et.al.) is this: 030728 05:26:17 mysqld started Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add to the [mysqld] section, for example, innodb_data_file_path = ibdata1:10M:autoextend But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 2 at http://www.innodb.com/ibman.html /opt/mysql-3.23.56/libexec/mysqld: ready for connections So, look for something similar in your log. If it is there, then make sure that you have innodb configured in my.cnf. 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: I Specify explicitly TYPE = InnoDB But My SQL gives me TYPE=MyISAM ?
Morten Gulbrandsen [EMAIL PROTECTED] wrote: according to what I experience, I do definitely run the default binaries, perhaps the daemon mysqld has to be started with an option in order to support InnoDB ? How can this be investigated ? C:\mysql\binmysqld -v mysqld Ver 3.23.55-max-debug for Win95/Win98 on i32 C:\mysql\binmysql -v Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 264 to server version: 3.23.55-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql C:\mysql\binver Microsoft Windows 2000 [Version 5.00.2195] This seems fine to me, I believe I have the default binaries. If InnoDB is not supported in MySQL, then I would have expected a warning, Or an error message. As we all can see, no warning or error is issued, perhaps another error level can give me more feedback ? See output of SHOW VARIABLES LIKE have_innodb. -- 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]
AW: I Specify explicitly TYPE = InnoDB But My SQL gives me TYPE=MyISAM ?
Hi Programmers, after this legal statement ALTER TABLE EMPLOYEE TYPE = InnoDB; I get Warnings: 0 and still MySQL claims to have to use Type = MyISAM, Which has never supported Foreign Key constraints. There must be a workaround for this, My queries are unambiguous, and MySQL does not obey. Is this fixed in later releases, please ? How can I increase the levels and sensitivity of the warnings, please ? Yours Sincerely Morten Gulbrandsen === C:\mysql\bintype constraint.sql USE company; DESCRIBE EMPLOYEE; SHOW CREATE TABLE EMPLOYEE; ALTER TABLE EMPLOYEE TYPE = InnoDB; SHOW CREATE TABLE EMPLOYEE; SHOW TABLE STATUS FROM company LIKE EMPLOYEE C:\mysql\binmysql -vvv Constraint.sqlout.txt C:\mysql\bintype out.txt -- DESCRIBE EMPLOYEE -- +--+---+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+---+ | SUPERSSN | varchar(9)| YES | | NULL| | | DNO | int(11) | | | 1 | | | SSN | varchar(9)| | PRI | | | | FNAME| varchar(15) | | | | | | MINIT| char(1) | YES | | NULL| | | LNAME| varchar(15) | | | | | | BDATE| date | YES | | NULL| | | ADDRESS | varchar(30) | YES | | NULL| | | SEX | char(1) | YES | | NULL| | | SALARY | decimal(10,2) | YES | | NULL| | +--+---+--+-+-+---+ 10 rows in set (0.00 sec) -- SHOW CREATE TABLE EMPLOYEE -- +--+ --+ | Table| Create Table | +--+ --+ | EMPLOYEE | CREATE TABLE `employee` ( `SUPERSSN` varchar(9) default NULL, `DNO` int(11) NOT NULL default '1', `SSN` varchar(9) NOT NULL default '', `FNAME` varchar(15) NOT NULL default '', `MINIT` char(1) default NULL, `LNAME` varchar(15) NOT NULL default '', `BDATE` date default NULL, `ADDRESS` varchar(30) default NULL, `SEX` char(1) default NULL, `SALARY` decimal(10,2) default NULL, PRIMARY KEY (`SSN`) ) TYPE=MyISAM | +--+ --+ 1 row in set (0.00 sec) -- ALTER TABLE EMPLOYEE TYPE = InnoDB -- Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 -- SHOW CREATE TABLE EMPLOYEE -- +--+ --+ | Table| Create Table | +--+ --+ | EMPLOYEE | CREATE TABLE `employee` ( `SUPERSSN` varchar(9) default NULL, `DNO` int(11) NOT NULL default '1', `SSN` varchar(9) NOT NULL default
RE: Finding Table and database size
Ok, if this is the wrong mysql list could someone tell me which list would best be able to handle my question if no one on this list knows how to get the table and database sizes? TIA Dan Ok at least could someone tell me if the SHOW TABLE STATUS fields 'Data_length' and 'Index_length' are in bytes and added together would make the disk size that that table is using up? Thanks a bunch Dan -Original Message- From: Dan Muey Sent: Wednesday, July 30, 2003 4:24 PM To: [EMAIL PROTECTED] Subject: RE: Finding Table and database size Is this even possible with mysql then? Howdy List! A couple questions about finding the disk space used by a table/database: 1) First how can I get the size a table is taking up on disk? Is it the 'Data_length' field in: SHOW TABLE STATUS FROM db_name LIKE 'wild';?? If so how can I do a query like the one above and just get Data_length returned? Otherwise what query can I do to find the disk size of a table? 2) Is there a simple query to get the disk size of a database and all of it's tables? Or do I need to do the method I use in question one on each table in the db and add them up? TIA Dan -- 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/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: bug or some mistake ???
hi No,I installed Mysql server (not max) by default. === 2003-07-31 09:56:00 you have composed=== Hi, i already have this problem with MySQL 4.0.13 (max) on RedHat, with the same message in .err file. Does you enable QUERY_CACHE in your configuration ? Thierno 6C -Original Message- From: powercola [mailto:[EMAIL PROTECTED] Sent: jeudi 31 juillet 2003 09:47 To: [EMAIL PROTECTED] Subject: bug or some mistake ??? hello I have a OpenBSD3.3 box with mysql-server 4.0.13. the mysql-server runs normally at the beginnig . Trouble come up these days that the mysqld server will down . There's error messages appers that lost connection during query when the client connect to it. There's errors in .err log files like this: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. What's matter with mysql-server or openbsd3.3? mysql-server runs perfect on FreeBSD or RedHat instead. How to fix it ? thanks, any idea appreciated powercola [EMAIL PROTECTED] 2003-07-29 = _._ _,-'`-._ = = (,-.`._,'( |\`-/| = = `-.-' \ )-`( , o o) = = `-\`_`'- = -- 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] = = = = = = = = = = = = = = = = = = = = thanks powercola [EMAIL PROTECTED] 2003-07-31 = _._ _,-'`-._ = = (,-.`._,'( |\`-/| = = `-.-' \ )-`( , o o) = = `-\`_`'- = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: I Specify explicitly TYPE = InnoDB But My SQL gives me TYPE=MyISAM ?
mysql SHOW VARIABLES LIKE have_innodb; +---+--+ | Variable_name | Value| +---+--+ | have_innodb | DISABLED | +---+--+ 1 row in set (0.02 sec) mysql === Goodygood, Victoria !!! Thank you Sir, This saves my keyboard, now please, how can I enable this fundamental Value have_innodb? Yours sincerely Morten Gulbrandsen -Ursprüngliche Nachricht- Von: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 31. Juli 2003 16:01 An: [EMAIL PROTECTED] Betreff: Re: I Specify explicitly TYPE = InnoDB But My SQL gives me TYPE=MyISAM ? Morten Gulbrandsen [EMAIL PROTECTED] wrote: according to what I experience, I do definitely run the default binaries, perhaps the daemon mysqld has to be started with an option in order to support InnoDB ? How can this be investigated ? C:\mysql\binmysqld -v mysqld Ver 3.23.55-max-debug for Win95/Win98 on i32 C:\mysql\binmysql -v Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 264 to server version: 3.23.55-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql C:\mysql\binver Microsoft Windows 2000 [Version 5.00.2195] This seems fine to me, I believe I have the default binaries. If InnoDB is not supported in MySQL, then I would have expected a warning, Or an error message. As we all can see, no warning or error is issued, perhaps another error level can give me more feedback ? See output of SHOW VARIABLES LIKE have_innodb. -- 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]
Help!!! Need to backup mysql using - MYSQL Second Edition?
Hello again, Still a newbie, and running mysql on windows 2000 Advanced Server. I am trying to backup mysql and was confused as to which command to use. I see the following options mysqldump mysqlhotcopy Now the confusion is really in the syntax used in the MYSQL Second Edition. I looked on page 718 and I am thinking that the command is for a Unix system. Since I am running on Windows, do I need to first create the directory where I want mysql to be backed up? And secondly, it is recommended that I do not backup in the same volume, so what exactly is the command for me to use, if mysql resides on C:\ and I want to back it up in the D:\? I should tell you that my PC is still a standalone, and I am planning to take it online soon. So the backup is a local backup. Thank you all in advance
standardized naming system ?
Hello list, is there a common naming system for db objects ? Like: 1) Tables: mytable, tblmytable, tbl_mytable 2) Indices: idx_anindex 3) Columns: int_somenumber, date_lastupdate 4) id for the numerical primary key e.g. table customers.id and then for referencing foreign keys table addresses : addresses.customer_id or addresses.customer_fk OK, I know I could name them the way I want but perhaps there is some kind of common sense in this regard ? ... A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL on Windows
Hi, I'm sure I'm missing something simple. I've searched the doc but can't find an answer. I downloaded and installed MySQL for Windows this morning, V4.1. Unzipped, installed and started the server as a service, no problems. Installed the ODBC driver. A-OK But, the DOC tells me I have to run mysql_install_db.sh. So, the question is, how am I supposed to run shell scripts on Windows? Thanks Arnie Mauer Application Architect Valassis Relationship Marketing Systems, LLC A Valassis Company 6 Armstrong Rd, Shelton CT 06484 Tel 1-203-225-1359 Fax 1-203-925-0188 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL on Windows
This script is run on *nix machines only. It installs the mysql and test databases into mysql. Windows installation does this by default. BTW, these *.sh are BASh scripts. Scott -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2003 10:50 AM To: [EMAIL PROTECTED] Subject: MySQL on Windows Hi, I'm sure I'm missing something simple. I've searched the doc but can't find an answer. I downloaded and installed MySQL for Windows this morning, V4.1. Unzipped, installed and started the server as a service, no problems. Installed the ODBC driver. A-OK But, the DOC tells me I have to run mysql_install_db.sh. So, the question is, how am I supposed to run shell scripts on Windows? Thanks Arnie Mauer Application Architect Valassis Relationship Marketing Systems, LLC A Valassis Company 6 Armstrong Rd, Shelton CT 06484 Tel 1-203-225-1359 Fax 1-203-925-0188 [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: Finding Table and database size
Hello Dan, Probably the reason that no-one has replied to you is that on-one feels that they have anything helpful to add to what you already know. A quick check on my system shows that the data and index sizes reported by SHOW TABLE STATUS are the same as the byte sizes of the MYD and MYI files - which is a clue that they may represent the table sizes in bytes. Whether this also applies to InnoDB tables, I have no idea. SImply checking the manual page http://www.mysql.com/doc/en/SHOW_TABLE_STATUS.html will show that there is no syntax for selecting only a few columns from the SHOW STATUS. The fact that no-one else has replied suggests that there is no alternative command. However, unless your number of tables is prodigious, the extra data transferred is hardly likely to be prodigious. If you phrase your question in the style please tell me more, then you must accept that silence means that there is no more to tell. If this list cannot answer your question, your only alternative is to purchase support from MySQL AB (always a good idea) , and then enquire of the fount of all (MySQL) knowledge. Alec |-+ | | Dan Muey | | | [EMAIL PROTECTED]| | | .com| | || | | 31/07/2003 15:15 | | || |-+ --| | | | To: [EMAIL PROTECTED] | | cc: | | Subject: RE: Finding Table and database size | --| Ok, if this is the wrong mysql list could someone tell me which list would best be able to handle my question if no one on this list knows how to get the table and database sizes? TIA Dan Ok at least could someone tell me if the SHOW TABLE STATUS fields 'Data_length' and 'Index_length' are in bytes and added together would make the disk size that that table is using up? Thanks a bunch Dan -Original Message- From: Dan Muey Sent: Wednesday, July 30, 2003 4:24 PM To: [EMAIL PROTECTED] Subject: RE: Finding Table and database size Is this even possible with mysql then? Howdy List! A couple questions about finding the disk space used by a table/database: 1) First how can I get the size a table is taking up on disk? Is it the 'Data_length' field in: SHOW TABLE STATUS FROM db_name LIKE 'wild';?? If so how can I do a query like the one above and just get Data_length returned? Otherwise what query can I do to find the disk size of a table? 2) Is there a simple query to get the disk size of a database and all of it's tables? Or do I need to do the method I use in question one on each table in the db and add them up? TIA Dan -- 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/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: Deleting Duplicate Records
To select records only once try something like select * from your_table group by field1, field2, .. Regards, Paul Kim Mackey wrote: Group, I have been working on a project for a while now trying to figure out how to remove duplicate records from a single table using a query. To complicate matters, what constitutes a duplicate record is a match on several fields, but not all fields. I have been successful in matching all duplicates based on the fields I'm interested in, but this only returns the duplicate records. I want a query that will return all records from the table, but only once if certain fields are the same. The table structure is basically: User Code Date Last Name First Name Address City State ZIP and then some other fields I don't want to use the values in the other fields to determine if the record duplicates another. I'm not concerned about which one of the duplicate records I keep because I will make a backup copy of this table before removing the duplicates, so I will still have all the data from the records deleted. Later I will redesign the tables for this database and link back in the data from the other fields. So I need a method that will output one and only one record from the table in which the data in the above mentioned fields are the same. Again, even if the data in the remaining fields are different I just want one of the records, and I don't care which one. Thanks for your help Kim Mackey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding Table and database size
Dan What you may be looking for (and I had to hunt around to find it myself!) is myisamchk -eis table_name Hope that helps - just because you don't get an answer doesn't mean we don't care - it probably means we don't know. Terry --Original Message- Ok, if this is the wrong mysql list could someone tell me which list would best be able to handle my question if no one on this list knows how to get the table and database sizes? TIA Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help!!! Need to backup mysql using - MYSQL Second Edition?
Hi, when you run this backup command, it wait that you provide a password (it is as if it were spirit to treat the command) without prompt; so just after you press enter key to run the backup command, type here you password. Also you can place directly the password in the command like this : mysqldump -u username -pyour_password --opt --databases --complete-insert --flush-logs mysql d:\path\to\backup\filename.sql NOTE : -p and your password are stuck and that the --databases will take care of all the databases? yes , it will backup all databases. Hope this run -Original Message- From: Ola Ogunneye [mailto:[EMAIL PROTECTED] Sent: jeudi 31 juillet 2003 15:15 To: [EMAIL PROTECTED] Subject: RE: Help!!! Need to backup mysql using - MYSQL Second Edition? Hi, sorry to bother you, but from what you recommended, am I typing everything just the way you typed it out except for the username which would be my username, or are there other variables in there that I need to change to something else? In essence, if my username is ola, is all I am now doing is changing the username to ola or do I need to provide the password for user ola, I also have created 3 databases apart from the default test and mysql (5 Databases in all), I take it that I do not have to specify each by its name, and that the --databases will take care of all the databases? The user ola has a password being the root user, do I put in the password somewhere as well or is mysql going to promt me for the passsword? Is all I am typing the following? C:\mysql\bin\mysqldump -u ola -p --opt --databases --complete-insert --flush-logs mysql d:\path\to\backup\filename.sql Thanks in advance Ola Thierno Cissé [EMAIL PROTECTED] 07/31/03 11:03AM hi, Still a newbie, and running mysql on windows 2000 Advanced Server. I am trying to backup mysql and was confused as to which command to use. I see the following options use mysqldump . If you want backup whole database(s) , proceed to next : mysqldump -u username -p --opt --databases --complete-insert --flush-logs mysql d:\path\to\backup\filename.sql do I need to first create the directory where I want mysql to be backed up? you can do this, and give this directory in the path. So the backup is a local backup anyway Thierno 6C -Original Message- From: Ola Ogunneye [mailto:[EMAIL PROTECTED] Sent: jeudi 31 juillet 2003 14:21 To: [EMAIL PROTECTED] Subject: Help!!! Need to backup mysql using - MYSQL Second Edition? Hello again, Still a newbie, and running mysql on windows 2000 Advanced Server. I am trying to backup mysql and was confused as to which command to use. I see the following options mysqldump mysqlhotcopy Now the confusion is really in the syntax used in the MYSQL Second Edition. I looked on page 718 and I am thinking that the command is for a Unix system. Since I am running on Windows, do I need to first create the directory where I want mysql to be backed up? And secondly, it is recommended that I do not backup in the same volume, so what exactly is the command for me to use, if mysql resides on C:\ and I want to back it up in the D:\? I should tell you that my PC is still a standalone, and I am planning to take it online soon. So the backup is a local backup. Thank you all in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Replication Issue?
Hi all, My slave has been working fine up until now. Here is the error message. 030730 11:32:28 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'SQLServer1-bin.034' at position 77571524 ERROR: 1062 Duplicate entry '120816' for key 1 030730 11:32:28 Slave: error running query 'INSERT INTO Assignments ( . Key 1 is CaseID Database changed mysql select CaseID from Assignments Order by CaseID DESC Limit 3; ++ | CaseID | ++ | 120815 | | 120814 | | 120813 | ++ 3 rows in set (0.02 sec) mysql DELETE FROM Assignments WHERE CaseID = 120816; Query OK, 0 rows affected (0.04 sec) If I try to restart the slave I get the error all over again. Anyone have any Ideas Roger --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.504 / Virus Database: 302 - Release Date: 7/24/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help!!! Need to backup mysql using - MYSQL Second Edition?
Hi, rapid correction !! in the backup command, retrieve the word mysql placed just after --flush-logs . Thierno 6C -Original Message- From: Thierno Cissé [mailto:[EMAIL PROTECTED] Sent: jeudi 31 juillet 2003 15:40 To: 'Ola Ogunneye' Cc: [EMAIL PROTECTED] Subject: RE: Help!!! Need to backup mysql using - MYSQL Second Edition? Hi, when you run this backup command, it wait that you provide a password (it is as if it were spirit to treat the command) without prompt; so just after you press enter key to run the backup command, type here you password. Also you can place directly the password in the command like this : mysqldump -u username -pyour_password --opt --databases --complete-insert --flush-logs mysql d:\path\to\backup\filename.sql NOTE : -p and your password are stuck and that the --databases will take care of all the databases? yes , it will backup all databases. Hope this run -Original Message- From: Ola Ogunneye [mailto:[EMAIL PROTECTED] Sent: jeudi 31 juillet 2003 15:15 To: [EMAIL PROTECTED] Subject: RE: Help!!! Need to backup mysql using - MYSQL Second Edition? Hi, sorry to bother you, but from what you recommended, am I typing everything just the way you typed it out except for the username which would be my username, or are there other variables in there that I need to change to something else? In essence, if my username is ola, is all I am now doing is changing the username to ola or do I need to provide the password for user ola, I also have created 3 databases apart from the default test and mysql (5 Databases in all), I take it that I do not have to specify each by its name, and that the --databases will take care of all the databases? The user ola has a password being the root user, do I put in the password somewhere as well or is mysql going to promt me for the passsword? Is all I am typing the following? C:\mysql\bin\mysqldump -u ola -p --opt --databases --complete-insert --flush-logs mysql d:\path\to\backup\filename.sql Thanks in advance Ola Thierno Cissé [EMAIL PROTECTED] 07/31/03 11:03AM hi, Still a newbie, and running mysql on windows 2000 Advanced Server. I am trying to backup mysql and was confused as to which command to use. I see the following options use mysqldump . If you want backup whole database(s) , proceed to next : mysqldump -u username -p --opt --databases --complete-insert --flush-logs mysql d:\path\to\backup\filename.sql do I need to first create the directory where I want mysql to be backed up? you can do this, and give this directory in the path. So the backup is a local backup anyway Thierno 6C -Original Message- From: Ola Ogunneye [mailto:[EMAIL PROTECTED] Sent: jeudi 31 juillet 2003 14:21 To: [EMAIL PROTECTED] Subject: Help!!! Need to backup mysql using - MYSQL Second Edition? Hello again, Still a newbie, and running mysql on windows 2000 Advanced Server. I am trying to backup mysql and was confused as to which command to use. I see the following options mysqldump mysqlhotcopy Now the confusion is really in the syntax used in the MYSQL Second Edition. I looked on page 718 and I am thinking that the command is for a Unix system. Since I am running on Windows, do I need to first create the directory where I want mysql to be backed up? And secondly, it is recommended that I do not backup in the same volume, so what exactly is the command for me to use, if mysql resides on C:\ and I want to back it up in the D:\? I should tell you that my PC is still a standalone, and I am planning to take it online soon. So the backup is a local backup. Thank you all in advance -- 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: Help!!! Need to backup mysql using - MYSQL Second Edition?
hi, the sign remains in the command , so you will have : C:\mysql\bin\mysqldump -u ola -p --opt --databases --complete-insert --flush-logs d:\path\to\backup\filename.sql The sign will redirect output into file. Thierno 6C -Original Message- From: Ola Ogunneye [mailto:[EMAIL PROTECTED] Sent: jeudi 31 juillet 2003 15:52 To: [EMAIL PROTECTED] Subject: RE: Help!!! Need to backup mysql using - MYSQL Second Edition? Thank you once again, so the correct thing is: C:\mysql\bin\mysqldump -u ola -p --opt --databases --complete-insert --flush-logs d:\path\to\backup\filename.sql Or does the sign come out as well? Thanks Ola Thierno Cissé [EMAIL PROTECTED] 07/31/03 11:48AM Hi, rapid correction !! in the backup command, retrieve the word mysql placed just after --flush-logs . Thierno 6C -Original Message- From: Thierno Cissé [mailto:[EMAIL PROTECTED] Sent: jeudi 31 juillet 2003 15:40 To: 'Ola Ogunneye' Cc: [EMAIL PROTECTED] Subject: RE: Help!!! Need to backup mysql using - MYSQL Second Edition? Hi, when you run this backup command, it wait that you provide a password (it is as if it were spirit to treat the command) without prompt; so just after you press enter key to run the backup command, type here you password. Also you can place directly the password in the command like this : mysqldump -u username -pyour_password --opt --databases --complete-insert --flush-logs mysql d:\path\to\backup\filename.sql NOTE : -p and your password are stuck and that the --databases will take care of all the databases? yes , it will backup all databases. Hope this run -Original Message- From: Ola Ogunneye [mailto:[EMAIL PROTECTED] Sent: jeudi 31 juillet 2003 15:15 To: [EMAIL PROTECTED] Subject: RE: Help!!! Need to backup mysql using - MYSQL Second Edition? Hi, sorry to bother you, but from what you recommended, am I typing everything just the way you typed it out except for the username which would be my username, or are there other variables in there that I need to change to something else? In essence, if my username is ola, is all I am now doing is changing the username to ola or do I need to provide the password for user ola, I also have created 3 databases apart from the default test and mysql (5 Databases in all), I take it that I do not have to specify each by its name, and that the --databases will take care of all the databases? The user ola has a password being the root user, do I put in the password somewhere as well or is mysql going to promt me for the passsword? Is all I am typing the following? C:\mysql\bin\mysqldump -u ola -p --opt --databases --complete-insert --flush-logs mysql d:\path\to\backup\filename.sql Thanks in advance Ola Thierno Cissé [EMAIL PROTECTED] 07/31/03 11:03AM hi, Still a newbie, and running mysql on windows 2000 Advanced Server. I am trying to backup mysql and was confused as to which command to use. I see the following options use mysqldump . If you want backup whole database(s) , proceed to next : mysqldump -u username -p --opt --databases --complete-insert --flush-logs mysql d:\path\to\backup\filename.sql do I need to first create the directory where I want mysql to be backed up? you can do this, and give this directory in the path. So the backup is a local backup anyway Thierno 6C -Original Message- From: Ola Ogunneye [mailto:[EMAIL PROTECTED] Sent: jeudi 31 juillet 2003 14:21 To: [EMAIL PROTECTED] Subject: Help!!! Need to backup mysql using - MYSQL Second Edition? Hello again, Still a newbie, and running mysql on windows 2000 Advanced Server. I am trying to backup mysql and was confused as to which command to use. I see the following options mysqldump mysqlhotcopy Now the confusion is really in the syntax used in the MYSQL Second Edition. I looked on page 718 and I am thinking that the command is for a Unix system. Since I am running on Windows, do I need to first create the directory where I want mysql to be backed up? And secondly, it is recommended that I do not backup in the same volume, so what exactly is the command for me to use, if mysql resides on C:\ and I want to back it up in the D:\? I should tell you that my PC is still a standalone, and I am planning to take it online soon. So the backup is a local backup. Thank you all in advance -- 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]
Any Issues with 3.23.56 and 4.0.13 using mysqld_multi on one server
Is it possible to run 3.23.43 and 4.0.13 on the same server? I don't see anything in the documentation that should prohibit this as long as the installations are in different directories, use different sockets/ports and data directories. Can this be done utilizing mysqld_multi and if so are there any special considerations? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding Table and database size
Excellent Terry Thanks I'll into that a little more. Basically I'm trying to make the most efficient function I can That will return the size of a table and the size of a database, so I can put it in aPelr module and make it avaialable to all. Thanks Dan -Original Message- From: Terry Riley [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2003 10:16 AM To: [EMAIL PROTECTED] Subject: RE: Finding Table and database size Dan What you may be looking for (and I had to hunt around to find it myself!) is myisamchk -eis table_name Hope that helps - just because you don't get an answer doesn't mean we don't care - it probably means we don't know. Terry --Original Message- Ok, if this is the wrong mysql list could someone tell me which list would best be able to handle my question if no one on this list knows how to get the table and database sizes? TIA Dan -- 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: Finding Table and database size
Hello Dan, Probably the reason that no-one has replied to you is that on-one feels that they have anything helpful to add to what you already know. A quick check on my system shows that the data and index sizes reported by SHOW TABLE STATUS are the same as the byte sizes of the MYD and MYI files - which is a clue that they may represent the table sizes in bytes. Whether this also applies to InnoDB tables, I have no idea. Excellent SImply checking the manual page http://www.mysql.com/doc/en/SHOW_TABLE_STATUS.html will show that there is no syntax for selecting only a few columns from the SHOW STATUS. The fact that no-one else has replied suggests that there is no alternative command. However, unless your number of tables is prodigious, the extra data transferred is hardly likely to be prodigious. I figured that, I'll just have to loop around in my function and add it all up. If you phrase your question in the style please tell me more, then you must accept that silence means that there is no more to tell. If this list cannot answer your question, your only alternative is to purchase support from MySQL AB (always a good idea) , and then enquire of the fount of all (MySQL) knowledge. Alec Right on, Thanks Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
calender table - time column?
I am currently in the process of building a calender/date MySQL table that will be searched for available dates for holiday tours? I want to use a time column as there will be a restriction on places for up to 3 days so this will have to be taken into account. Has anyone done something like this? Cheers Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: bug or some mistake ???
Hi, Ok , i complete my answer about the MySQL version. I have used all three versions 4.0.13 (debug - max - standard) for Linux RedHat. And all theses versions have indicated the same message : mysqld got signal 11. Has a procedure to , may be, find what goes wrong in section : E.1.4 Using a Stack Trace of the MySQL reference manual; it documents MySQL up to Version 4.1.0-alpha. I ask you question to know if you're using QUERY CACHE feature, can you light me ? Hope this help Thierno 6C -Original Message- From: powercola [mailto:[EMAIL PROTECTED] Sent: jeudi 31 juillet 2003 14:27 To: Thierno Ciss| Cc: [EMAIL PROTECTED] Subject: Re: bug or some mistake ??? hi No,I installed Mysql server (not max) by default. === 2003-07-31 09:56:00 you have composed=== Hi, i already have this problem with MySQL 4.0.13 (max) on RedHat, with the same message in .err file. Does you enable QUERY_CACHE in your configuration ? Thierno 6C -Original Message- From: powercola [mailto:[EMAIL PROTECTED] Sent: jeudi 31 juillet 2003 09:47 To: [EMAIL PROTECTED] Subject: bug or some mistake ??? hello I have a OpenBSD3.3 box with mysql-server 4.0.13. the mysql-server runs normally at the beginnig . Trouble come up these days that the mysqld server will down . There's error messages appers that lost connection during query when the client connect to it. There's errors in .err log files like this: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. What's matter with mysql-server or openbsd3.3? mysql-server runs perfect on FreeBSD or RedHat instead. How to fix it ? thanks, any idea appreciated powercola [EMAIL PROTECTED] 2003-07-29 = _._ _,-'`-._ = = (,-.`._,'( |\`-/| = = `-.-' \ )-`( , o o) = = `-\`_`'- = -- 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] = = = = = = = = = = = = = = = = = = = = thanks powercola [EMAIL PROTECTED] 2003-07-31 = _._ _,-'`-._ = = (,-.`._,'( |\`-/| = = `-.-' \ )-`( , o o) = = `-\`_`'- = -- 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: Deleting Duplicate Records
At 02:32 PM 7/30/2003, you wrote: Group, I have been working on a project for a while now trying to figure out how to remove duplicate records from a single table using a query. To complicate matters, what constitutes a duplicate record is a match on several fields, but not all fields. I have been successful in matching all duplicates based on the fields I'm interested in, but this only returns the duplicate records. I want a query that will return all records from the table, but only once if certain fields are the same. The table structure is basically: User Code Date Last Name First Name Address City State ZIP and then some other fields I don't want to use the values in the other fields to determine if the record duplicates another. I'm not concerned about which one of the duplicate records I keep because I will make a backup copy of this table before removing the duplicates, so I will still have all the data from the records deleted. Later I will redesign the tables for this database and link back in the data from the other fields. So I need a method that will output one and only one record from the table in which the data in the above mentioned fields are the same. Again, even if the data in the remaining fields are different I just want one of the records, and I don't care which one. Thanks for your help Kim Mackey Kim, Here are a few solutions on the web: http://www.databasejournal.com/features/mysql/article.php/10897_2201621_3 http://15seconds.com/issue/011009.htm http://www.faqchest.com/prgm/mysql-l/mysql-03/mysql-0302/mysql-030222/mysql03021508_15076.html http://www.experts-exchange.com/Databases/Mysql/Q_20610046.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: unique identifier using mysql_connect
I am opening 4 different databases and was hoping when I do a mysql_query I could just grab the connection id rather than reconnecting everytime I want to query a new database. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 30, 2003 9:08 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: unique identifier using mysql_connect At 12:43 -0700 7/30/03, Grant Cooper wrote: Can I get a unique identifier using the mysql_connect command by connecting to the same host, same user but different database during the life time of one script? I currently do a $conn_id = mysql_connect ($host_name, $user_name, $password); Then call a, mysql_select_db ($db_name,$conn_id) But I always get the same identifier. Why is this a problem? This is fairly typical behavior for PHP (which is what it looks like you're using). -- 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]
MySQL 4.1 Question
Anyone know when it will be considered production stable? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldaemon not starting
Victoria, I just had this problem solved for me by the list. It was permissions on the mysql/data directory. See the topic Running mysql (complete newbie). HTH Peter On Thu, 2003-07-31 at 08:50, Victoria Reznichenko wrote: Vidhya CS [EMAIL PROTECTED] wrote: I am this error while running mysqld_safe # ./bin/mysqld_safe --user=mysql [1] 19413 # Starting mysqld daemon with databases from /export/home/siptech/vidhya/ifmonitor/mysql/data 030728 12:17:49 mysqld ended The daemon is not starting . what could be the problem thanks Look into error log file. -- 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]
max clients
Can mysql handle 5000 concurrent webusers sending queries to the db through a web search engine. Is it possible (with a very big server/hw) ? Thanks. Roberto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
max clients
Can mysql handle 5000 concurrent webusers sending queries to the db through a web search engine. Is it possible (with a very big server/hw) ? Thanks. Roberto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max clients
I'm not sure you'd want to do that way... Perhaps 5+ replicated boxes from a master that share the queries equally (hardware load balancer).. Might be cheaper in hardware than buying some heavy horsepower box.. On Thu, 31 Jul 2003, NEWMEDIAPLAN wrote: Can mysql handle 5000 concurrent webusers sending queries to the db through a web search engine. Is it possible (with a very big server/hw) ? Thanks. Roberto -- 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_history file?
We're just getting started with MySQL on Linux; I was browsing my home directory and found a file called .mysql_history. It contains a history of all mysql commands I've entered - including logging on, setting passwords for users, etc. Sure enough, other users of mysql have the same file. Questions: What are these files, are they mandatory, or can I turn off this logging? Do we need to keep an eye on growth of this files, and truncate or archive them? Most importantly - yikes, seems scary to have a clear text file with a record of Mysql username and password create commands. Any thoughts on this?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: max clients
I was considering different boxes. But I'm courious to know if anyone here knows the possibility we have with mysql... just to foresee the crash. Just a software matter assuming we have a very big server :) I saw max_connections and things like this. What's the real limit, tested? |-Original Message- |From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] |Sent: Thursday, July 31, 2003 8:40 PM |To: NEWMEDIAPLAN |Cc: [EMAIL PROTECTED] |Subject: Re: max clients | | |I'm not sure you'd want to do that way... Perhaps 5+ replicated boxes from |a master that share the queries equally (hardware load balancer).. Might |be cheaper in hardware than buying some heavy horsepower box.. | | | |On Thu, 31 Jul 2003, NEWMEDIAPLAN wrote: | | Can mysql handle 5000 concurrent webusers sending queries to the |db through | a web search engine. | Is it possible (with a very big server/hw) ? | Thanks. Roberto | | | | -- | 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_history file?
it's like bash_history.. command history (used with up/back key mostly) You want to get rid of it for good: ln -sf /dev/null .mysql_history On Thu, 31 Jul 2003, Jean Hagen wrote: We're just getting started with MySQL on Linux; I was browsing my home directory and found a file called .mysql_history. It contains a history of all mysql commands I've entered - including logging on, setting passwords for users, etc. Sure enough, other users of mysql have the same file. Questions: What are these files, are they mandatory, or can I turn off this logging? Do we need to keep an eye on growth of this files, and truncate or archive them? Most importantly - yikes, seems scary to have a clear text file with a record of Mysql username and password create commands. Any thoughts on this?? -- 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: max clients
Hopefully jeremyz will toll in.. he's probably hit it before ;) On Thu, 31 Jul 2003, NEWMEDIAPLAN wrote: I was considering different boxes. But I'm courious to know if anyone here knows the possibility we have with mysql... just to foresee the crash. Just a software matter assuming we have a very big server :) I saw max_connections and things like this. What's the real limit, tested? |-Original Message- |From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] |Sent: Thursday, July 31, 2003 8:40 PM |To: NEWMEDIAPLAN |Cc: [EMAIL PROTECTED] |Subject: Re: max clients | | |I'm not sure you'd want to do that way... Perhaps 5+ replicated boxes from |a master that share the queries equally (hardware load balancer).. Might |be cheaper in hardware than buying some heavy horsepower box.. | | | |On Thu, 31 Jul 2003, NEWMEDIAPLAN wrote: | | Can mysql handle 5000 concurrent webusers sending queries to the |db through | a web search engine. | Is it possible (with a very big server/hw) ? | Thanks. Roberto | | | | -- | 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]
Mysqlcc vs Phpmyadmin
Hello again people, Can someone tell me if mysqlcc does the same thing as phpmyadmin. Thank you all Ola
Re: max clients
The answer (like most of these) is, it depends. You really need to benchmark your application to know how often a user is going to actually hit your database server, and how many connections the application creates for each user. I've got a server that handles the databases for 3000 users hitting web apps on several different web servers without blinking (dual P4 Xeon, 2 GB of memory). It runs at worst 75% idle. On the other hand I've seen applications where 100 users would bring this server to it's knees if it were serving the database for it. It all depends on usage and you can't get from users to database usage directly. I would pile up some hardware your not using at the moment set things up and write some scripts to simulate actual usage of your application. Load the server with some real data, especially in terms of quantity of rows, and see what it handles. Measure performance and system usage metrics and go from there. -- Michael Conlen NEWMEDIAPLAN wrote: Can mysql handle 5000 concurrent webusers sending queries to the db through a web search engine. Is it possible (with a very big server/hw) ? Thanks. Roberto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlcc LIMIT
mysqlcc has a limit of 1000 on all queries I run. I can understand this for SELECT queries, but it makes no sense for INSERT... SELECT queries. Is there a way to override this limit? -Jackson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
TABLE STATUS
I was wondering if anybody wth expereince with other tables types than MyIsam would be able to fill in a few blanks for me: I'm trying to find what file extentions are associated with what table types. And which file types are associated with the Field's listed below of a SHOW TABLE STATUS. Or if those fields otherwise give you the size of the table plus things that are not inluded in that query, like the .frm files. My areas of ignorance is marked with question marks: Thanks Dan +--+ |http://www.mysql.com/doc/en/SHOW_TABLE_STATUS.html| +--+ Field MyIsam Merge ISAM HEAP InnoDB BDB/BerkelyDB -+---+--+--+-+--+---+ Data_length + .MYD + ?? + .ISM + ?? + ?? + ?? + Index_length + .MYI + ?? + .ISD + ?? + ?? + ?? + -+--+ Other files not included in SHOW TABLE STATUS query-+ + .frm + .frm + .frm +.frm?+.frm??+ .frm?? + + + .MRG + + + + + + + + + + + + + + + + + + + + + + + + + + + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqld is keep crashing
This is what I bumped into now 030731 15:39:50 mysqld started 030731 15:39:52 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 3491794745 InnoDB: Doing recovery: scanned up to log sequence number 0 3491800490 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 32 row operations to undo InnoDB: Trx id counter is 0 3532544 030731 15:39:53 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 3532191, 32 rows to undoInnoDB: Error: trying to access page number 2650767236 in space 0 InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10 030731 15:39:59 InnoDB: Assertion failure in thread 1 in file fil0fil.c line 1176 InnoDB: Failing assertion: 0 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=2093056 030731 15:39:59 mysqld ended Anyone would know how I can fix it I am using my-huge.cnf . I have 4 450Mhz with 4 gig ram -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysqlcc vs Phpmyadmin
No. Ones a client application the other is a web app. The client application has SQL completion, the web app does not. ---Original Message- --From: Ola Ogunneye [mailto:[EMAIL PROTECTED] --Sent: Thursday, July 31, 2003 11:52 AM --To: [EMAIL PROTECTED] --Subject: Mysqlcc vs Phpmyadmin -- --Hello again people, -- --Can someone tell me if mysqlcc does the same thing as phpmyadmin. -- --Thank you all -- --Ola -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysqlcc vs Phpmyadmin
Contents are Direct Alliance Corporation CONFIDENTIAL - - For writing long sql statements I prefer mysqlcc, and the tab feature is nice. - Phpmyadmin also has some handy feature. Most of it is preference... If learn to use both then all the better... -Original Message- From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2003 1:15 PM To: 'Ola Ogunneye'; [EMAIL PROTECTED] Subject: RE: Mysqlcc vs Phpmyadmin No. Ones a client application the other is a web app. The client application has SQL completion, the web app does not. ---Original Message- --From: Ola Ogunneye [mailto:[EMAIL PROTECTED] --Sent: Thursday, July 31, 2003 11:52 AM --To: [EMAIL PROTECTED] --Subject: Mysqlcc vs Phpmyadmin -- --Hello again people, -- --Can someone tell me if mysqlcc does the same thing as phpmyadmin. -- --Thank you all -- --Ola -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] This message is for the designated recipient(s) only and contains Direct Alliance Corporation privileged and confidential information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of this email is prohibited.
ARGGH! - User password problems
All, I've been attempting to assign a password to a user with the following statement: UPDATE mysql.user SET password=PASSWORD('foo') WHERE user = 'acc_name'; I get the typical: Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 However, when the user attempts the login using the password they are unable (access is denied to acc_name with password yes). If they attempt to login without a password, they are successful - WTF!. There is only 1-entry in the user table for this user so I didn't specify a host. Next, for the same user account, if I try to login from a remote machine (via ODBC) I am denied access (event with the blank password!). The user has the following host '%' (no other hosts present), yet I receive an error that reads: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) HELP before I lose my mind and start pretending I am a glass of orange juice. Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ARGGH! - User password problems
Hi Did you remember to flush??? - Original Message - From: Adam Fortuno KOVICK [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: July 31, 2003 4:33 PM Subject: ARGGH! - User password problems All, I've been attempting to assign a password to a user with the following statement: UPDATE mysql.user SET password=PASSWORD('foo') WHERE user = 'acc_name'; I get the typical: Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 However, when the user attempts the login using the password they are unable (access is denied to acc_name with password yes). If they attempt to login without a password, they are successful - WTF!. There is only 1-entry in the user table for this user so I didn't specify a host. Next, for the same user account, if I try to login from a remote machine (via ODBC) I am denied access (event with the blank password!). The user has the following host '%' (no other hosts present), yet I receive an error that reads: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) HELP before I lose my mind and start pretending I am a glass of orange juice. Regards, Adam -- 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]
study on composite keys and different column types
From going over http://www.mysql.com/doc/en/MySQL_indexes.html (again) http://www.mysql.com/doc/en/Indexes.html (again) And doing some experimentation it seems that when I build a compound index (A key spanning multiple column) of column types String, int, int - sorting the result takes a very long time when compared to int, int, String - type keys Even though there are more distinct values of strings then ints: and it would make sense to have String 1st as the leftmost prefix to avoid yet another standalone index (since it's required for lookup) the results where an order of magnitude faster. Has anyone else come across this situation? Does anyone have an explanation from a code point of view why this is the case? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup conventions
Hi, I'm a new user of MySQL on Linux. I have been an Oracle DBA; and know that anyone familiar with Oracle takes backup procedures very seriously Anyone have recommendations for nightly backup procedures for MySQL (transactional) databases? The literature seems a little ambiguous on this. I did 'turn on' the general, binary and error logs. Does anyone shut down the MySQL daemon regularly? Anyone use mysqldump, or do folks simply use a file system backup? We have tons of databases and tons of tables, so I'm not really considering the lock/flush procedure on tables... Any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max clients
On Thu, Jul 31, 2003 at 02:51:10PM -0400, [EMAIL PROTECTED] wrote: Hopefully jeremyz will toll in.. he's probably hit it before ;) I've been up to 2000 connections on a 1GB FreeBSD box. With some tweaking, I'm sure you could go quite a bit higher. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 21 days, processed 679,989,987 queries (359/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max clients
On Thu, Jul 31, 2003 at 08:36:02PM +0200, NEWMEDIAPLAN wrote: Can mysql handle 5000 concurrent webusers sending queries to the db through a web search engine. Is it possible (with a very big server/hw) ? What OS are you using? And how much hardare? -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 21 days, processed 679,998,992 queries (359/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
connections aren't released?
Hello, I am having some performance difficulties due to the number of connections getting so high. I know that the correct way to solve this is by using connection pooling, but for now I can't figure out why the connections are'nt being released as I expect. I am using Tomcat 4.0, with mm.mysql-2.0.14-bin.jar on Linux 7.2 to connect to a MySQL max 3.23.51 database. I open the connections in a bean with: private void connect () { String url=jdbc:mysql://mysqlserver/database; String username=someusername; String password=somepassword; String driver=org.gjt.mm.mysql.Driver; try { // load db driver Class.forName (driver); // establish net connection to db this.connection=DriverManager.getConnection (url, username, password); } catch (ClassNotFoundException cnfe) { System.err.println (Error loading driver: + cnfe); } catch (SQLException sqle) { System.err.println (Error connection: + sqle); } } public Connection getConnection () { if (this.connection==null) { this.connect (); } return this.connection; } and I close the statements, resultsets and connections with statement.close(); rs.close(); and connection.close; But when I track Threads_connected with show status, I see that the connections aren't released until the session is terminated with session.invalidate(); Any insights? Thanks, Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update Table By ID Ranges
Hi, How do i use the UPDATE statement to update a range of Primary Key id numbers. (let say 100 through 1000). Example, UPDATE testTable set testField='' WHERE test_id in (100:1000); Thanks in advance for any comments, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql and Access
I'm building an integrated system with Access and mySQL. Some company has na internal system based on Access and a web site with a form that saves its variables in a mySQL db. Can I make a system that migrates automatically the data from the mySQL online db for the the local Access db? And how? Can somebody help me? Thanks for now Cheers Voodoo
Re: Update Table By ID Ranges
how about UPDATE testTable set testField='' WHERE test_id = 100 and test_id = 1000; Mike Doanh Tran wrote: Hi, How do i use the UPDATE statement to update a range of Primary Key id numbers. (let say 100 through 1000). Example, UPDATE testTable set testField='' WHERE test_id in (100:1000); Thanks in advance for any comments, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help Please: Substitute Text to replace Stored Procedures for use with MySQL
Hi I have purchased a package that uses ASP and SQL, my webhost provides MYSQL and I have loaded my access database and all seems to be working fine. Except the stored procedures (Queries) the webhost says MYSQL doesn't support stored procedures, as this site confirms (wait for v5.0) but the same effect can be achieved using ASP, an example of what they offer in help is below: SQLQuery = SELECT * FROM tblAPCategories Set RS = gsconnect.Execute(SQLQuery) Do While Not RS.EOF % %=RS(catID)%, etc... This works fine for dumping the contents of these fields to the screen, my problem is that my ASP app calls stored procedures in the following way: sSQL = qdpAPGetMaxCatLevel etc... the variable seems to be called repeatedly after this, I'm assuming sSQL is the variable, the query for this is: SELECT Max(catLevel) AS calcMaxLevel FROM tblAPCategories; The answer is 3 which I can get to using the ASP text suggested, but I can't seem to work out how to replace the call to the stored procedure with the ASP text to make the ASP function as intended, it just crashes out at the point this is called, and believe me I have tried everything, except what works obviously. I even tried creating a table with the same name and typing in the answer just to see if I could further, it knew it was a table and stopped just the same. Can anyone help, if I can crack one I think I can crack the rest, although to be fair this appears to be one of the easier queries. Thanks in advance for anyone's help. Chris
Re: mysql and Access
The simplest way, if you have the ability to make an ODBC connection to the mySQL box, would be to create an ODBC linked table in access and then run an append or make-table query against the linked table, as per your situation requires. I assume that you could write a macro that would run the query on a daily or hourly basis or something. If you're not able to create an ODBC link to the mySQL database, then there will most likely be some manual intervention. It would not be hard to procedure-ize and mostly automate the export and import process, though (with a little help from their ISP). (I'm assuming that they have an ISP that is hosting the box, if they're not hosting the box locally. If they were hosting it locally, they'd most likely be able to create an ODBC connection to it.) So the short answer is: Yes, you can, but if you have to ask, (or if most of what I've written so far went over your head), you'll probably want to get some help doing it. -Cameron Wilhelm On Thursday, July 31, 2003, at 04:41 PM, Voodoo wrote: I'm building an integrated system with Access and mySQL. Some company has na internal system based on Access and a web site with a form that saves its variables in a mySQL db. Can I make a system that migrates automatically the data from the mySQL online db for the the local Access db? And how? Can somebody help me? Thanks for now Cheers Voodoo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How does one get off of this list
I have tried to remove myself from this list, but the unsubscribe function does not function. How does one get off of this list? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULLTEXT Searching
I have to do a catalog search through multiple tables and columns for product model number, description, and name. I realize that doing pattern matching with multiple LIKE statements is slow so I found that FULLTEXT searches is a better alternative. I have added a FULLTEXT index to the tables I'm searching, but I get an unkown error when I run my query: SELECT p2c.categories_id, p.products_id, pd.products_name, p.products_quantity, p.products_image, p.products_bimage, p.products_price, p.products_date_added, p.products_last_modified, p.products_date_available, p.products_status FROM products p, products_description pd, products_to_categories p2c WHERE MATCH(p.products_model,pd.products_name,pd.products_description) AGAINST('pumps') AND p.products_id = pd.products_id AND p.products_id = p2c.products_id ORDER BY pd.products_name; This is how my table structures look: CREATE TABLE `products` ( `products_id` int(11) NOT NULL auto_increment, `products_quantity` int(4) NOT NULL default '0', `products_model` varchar(12) default NULL, `products_image` varchar(64) default 'image_na.gif', `products_bimage` varchar(64) default 'image_na.jpg', `products_price` decimal(15,4) NOT NULL default '0.', `products_date_added` datetime NOT NULL default '-00-00 00:00:00', `products_last_modified` datetime default NULL, `products_date_available` datetime default NULL, `products_weight` decimal(5,2) NOT NULL default '0.00', `products_status` tinyint(1) NOT NULL default '0', `products_tax_class_id` int(11) NOT NULL default '0', `manufacturers_id` int(11) default NULL, `products_ordered` int(11) NOT NULL default '0', PRIMARY KEY (`products_id`), KEY `idx_products_date_added` (`products_date_added`), FULLTEXT KEY `products_model` (`products_model`) ) TYPE=MyISAM AUTO_INCREMENT=928 ; CREATE TABLE `products_description` ( `products_id` int(11) NOT NULL auto_increment, `language_id` int(11) NOT NULL default '1', `products_name` varchar(64) NOT NULL default '', `products_description` text, `products_url` varchar(255) default NULL, `products_viewed` int(5) default '0', PRIMARY KEY (`products_id`,`language_id`), KEY `products_name` (`products_name`), FULLTEXT KEY `products_name_2` (`products_name`,`products_description`) ) TYPE=MyISAM AUTO_INCREMENT=928 ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running mysql (complete newbie)
The permissions should be as follows: /usr/local/mysql/data/ mysql owner 600 is no problem if the linux.err does not exist create one touch linux.err now u can try. I hope it helps. -- Aftab Jahan Subedar Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 81/1-A North Jatrbari Dhaka 1204 Bangladesh sms://+447765341890 sms://+880171859159 http://www.ceobangladesh.com http://www.DhakaStockExchangeGame.com http://www.geocities.com/jahan.geo / Peter Bradley wrote: Hi again Mike, thanks for your help, but no luck there either. the permissions are 755 with mysql as owner and daemon as group cheers Peter On Wed, 2003-07-30 at 18:52, O'K Web Design wrote: Hi Peter It might not be the /tmp directory. This is taken from your original post. I did check to see if mysqld was running, but of course it wasn't. I also tried the perl script run-all-tests, but it just reported mysql wasn't running ('Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)' when connecting to DBI:mysql:database=test;host=test;host=localhost with user: '' password: '') You might want to check the permissions on the /var/lib/mysql directory as well. Mike - Original Message - From: Peter Bradley [EMAIL PROTECTED] To: O'K Web Design [EMAIL PROTECTED] Sent: July 30, 2003 1:03 AM Subject: Re: Running mysql (complete newbie) Hi Mike, Hmmm. Don't think so. /tmp is drwxrwxrwt Cheers Peter On Wed, 2003-07-30 at 18:43, O'K Web Design wrote: Hi Peter It sounds like you have a directory permission problem. I had one just this week. Just make sure that mysql can write to /tmp. Mike - Original Message - From: Peter Bradley [EMAIL PROTECTED] To: msql general mailing list [EMAIL PROTECTED] Sent: July 30, 2003 12:50 AM Subject: Running mysql (complete newbie) Hi guys and gals, I'm a complete newbie to mysql, so please bear with me. Today I downloaded mysql4.0 binary distribution and installed it on my SuSE Linux 8.1 box. I've unzipped it and put it in: /usr/local/mysql-standard-4.0.14-pc-linux-i686 and I've created a symbolic link to give me /usr/local/mysql I then ran scripts/mysql_install_db (as root). The output was: = | |linux:/usr/local/mysql # ./scripts/mysql_install_db |Installing all prepared tables |030730 4:24:13 ./bin/mysqld: Shutdown Complete | | |To start mysqld at boot time you have to copy |support-files/mysql.server |to the right place for your system | |PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! |This is done with: |./bin/mysqladmin -u root password 'new-password' |./bin/mysqladmin -u root -h linux password 'new-password' |See the manual for more instructions. | |NOTE: If you are upgrading from a MySQL = 3.22.10 you should run |the ./bin/mysql_fix_privilege_tables. Otherwise you will not be |able to use the new GRANT command! | |You can start the MySQL daemon with: |cd . ; ./bin/mysqld_safe | |You can test the MySQL daemon with the benchmarks in the 'sql-bench' |directory: |cd sql-bench ; perl run-all-tests | |Please report any problems with the ./bin/mysqlbug script! | |The latest information about MySQL is available on the web at |http://www.mysql.com |Support MySQL by buying support/licenses at https://order.mysql.com | |linux:/usr/local/mysql # | === I then tried to start mysql as a normal user and got: | |[EMAIL PROTECTED]:/usr/local/mysql bin/safe_mysqld --log |[1] 2693 |[EMAIL PROTECTED]:/usr/local/mysql Starting mysqld daemon with databases |from /usr/local/mysql/data |bin/safe_mysqld: line 296: /usr/local/mysql/data/linux.err: Permission |denied |rm: cannot remove `/tmp/mysql.sock': Operation not permitted |bin/safe_mysqld: line 1: /usr/local/mysql/data/linux.err: Permission |denied |tee: /usr/local/mysql/data/linux.err: Permission denied |030730 04:27:40 mysqld ended |tee: /usr/local/mysql/data/linux.err: Permission denied | |== So I tried as root and got: | |linux:/usr/local/mysql # ./bin/safe_mysqld --log |[1] 2740 |linux:/usr/local/mysql # Starting mysqld daemon with databases from |/usr/local/mysql/data |030730 04:30:05 mysqld ended | I did check to see if mysqld was running, but of course it wasn't. I also tried the perl script run-all-tests, but it just reported mysql wasn't running ('Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)' when connecting to DBI:mysql:database=test;host=test;host=localhost with user: '' password: '') Can anyone help? Thanks Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Books advice
Hi, I would like to learn about RDBMS, namely mysql of course, but know really nothing in this area, so have to learn about; RDBMS, SQL and mysql from scratch. In this regard, if anyone knows of any books they think would start me off on the right foot, I would be very grateful. I have done some searching on Amazon, with the following results. 1.Beginning Databases with MySQL by Richard Stones, Neil Matthew 2.MySQL Cookbook by Paul DuBois 3.Managing and Using MySQL by George Reese, et al 4.Inside Relational Databases by Mark Whitehorn, Bill Marklyn 5.Database Design by Ryan K. Stephens, Ronald R. Plew 6.The Practical SQL Handbook: Using SQL Variants by Judith S. Bowman, et al Many thanks in advance for all your help. Fawad
RE: Books advice
Here are two other books that I would recommend, specially the first one: * SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL by Michael J. Hernandez, John L. Viescas Joe Celko's SQL for Smarties: Advanced SQL Programming by Joe Celko -Original Message- From: Fawad Siddiqui [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2003 5:06 PM To: [EMAIL PROTECTED] Subject: Books advice Hi, I would like to learn about RDBMS, namely mysql of course, but know really nothing in this area, so have to learn about; RDBMS, SQL and mysql from scratch. In this regard, if anyone knows of any books they think would start me off on the right foot, I would be very grateful. I have done some searching on Amazon, with the following results. 1.Beginning Databases with MySQL by Richard Stones, Neil Matthew 2.MySQL Cookbook by Paul DuBois 3.Managing and Using MySQL by George Reese, et al 4.Inside Relational Databases by Mark Whitehorn, Bill Marklyn 5.Database Design by Ryan K. Stephens, Ronald R. Plew 6.The Practical SQL Handbook: Using SQL Variants by Judith S. Bowman, et al Many thanks in advance for all your help. Fawad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Books advice
Hi I guess I will plug Paul's books. I have his MySQL book and his MySQL and Perl for the Web. I really enjoyed the second one but the MySQL book was very well written. I also have read Sam's teach yourself MySQL in 21 days which seemed pretty light weight and I never look up anything in it. I also have the MySQL reference manual which is great for really technical type questions. Mike - Original Message - From: Ralph Guzman [EMAIL PROTECTED] To: 'Fawad Siddiqui' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: July 31, 2003 8:23 PM Subject: RE: Books advice Here are two other books that I would recommend, specially the first one: * SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL by Michael J. Hernandez, John L. Viescas Joe Celko's SQL for Smarties: Advanced SQL Programming by Joe Celko -Original Message- From: Fawad Siddiqui [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2003 5:06 PM To: [EMAIL PROTECTED] Subject: Books advice Hi, I would like to learn about RDBMS, namely mysql of course, but know really nothing in this area, so have to learn about; RDBMS, SQL and mysql from scratch. In this regard, if anyone knows of any books they think would start me off on the right foot, I would be very grateful. I have done some searching on Amazon, with the following results. 1.Beginning Databases with MySQL by Richard Stones, Neil Matthew 2.MySQL Cookbook by Paul DuBois 3.Managing and Using MySQL by George Reese, et al 4.Inside Relational Databases by Mark Whitehorn, Bill Marklyn 5.Database Design by Ryan K. Stephens, Ronald R. Plew 6.The Practical SQL Handbook: Using SQL Variants by Judith S. Bowman, et al Many thanks in advance for all your help. Fawad -- 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: help to structure query without using subselects
* Petre Agenbag [...] This works: select distinct name, max(id) as mid from table group by name but it only returns the name and the highest id for such a name. if I try this: select distinct name, max(id) as mid, anything_else from table group by name it returns the FIRST anything_else, and not the value in the last row for that name ( as I would like it to) Try the MAX-CONCAT trick: select distinct name, max(concat(id,'|',anything_else,'|',even_more)) as name_info from table group by name Then you must programatically split the 'name_info' column back to the individual fields, or use SUBSTRING, like in the example in the manual: URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html HTH, -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Books advice
I just ordered this book MySQL The definitive guide to using, programming, and administering MySQL 4 by Paul Dubois I found it more technical than MySQL cook book by Paul Dubois On Thu, 31 Jul 2003, Ralph Guzman wrote: Here are two other books that I would recommend, specially the first one: * SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL by Michael J. Hernandez, John L. Viescas Joe Celko's SQL for Smarties: Advanced SQL Programming by Joe Celko -Original Message- From: Fawad Siddiqui [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2003 5:06 PM To: [EMAIL PROTECTED] Subject: Books advice Hi, I would like to learn about RDBMS, namely mysql of course, but know really nothing in this area, so have to learn about; RDBMS, SQL and mysql from scratch. In this regard, if anyone knows of any books they think would start me off on the right foot, I would be very grateful. I have done some searching on Amazon, with the following results. 1.Beginning Databases with MySQL by Richard Stones, Neil Matthew 2.MySQL Cookbook by Paul DuBois 3.Managing and Using MySQL by George Reese, et al 4.Inside Relational Databases by Mark Whitehorn, Bill Marklyn 5.Database Design by Ryan K. Stephens, Ronald R. Plew 6.The Practical SQL Handbook: Using SQL Variants by Judith S. Bowman, et al Many thanks in advance for all your help. Fawad -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Searching on Two Keys with OR?
Which version is targetted for optimization of OR searching on two keys, that is, select * from sometable where f1 = 123 or f2 = 123, as described in http://www.mysql.com/doc/en/Searching_on_two_keys.html ? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How does one get off of this list
I have tried to remove myself from this list, but the unsubscribe function does not function. How does one get off of this list? I'd tell you to search the friendly archives, but the string unsubscribe is part of every post recorded in the archives. But browsing backwards for several weeks should get you something that helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql And heres's another searchable archive: http://marc.theaimsgroup.com/?l=mysqlr=1w=2 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] (Hmm. That's changed in the recent month or so.) I assume what you've tried is the above link? You should receive a confirmation e-mail, and you need to reply to that. Did you receive the confirmation e-mail? -- Joel Rees, programmer, Systems Group Altech Corporation (Alpsgiken), Osaka, Japan http://www.alpsgiken.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Impossible mysqldump - Please HELP!!!
I am running mysql 4.0.13 on Apache 2.0.47 and PHP 4.3.2. I can log onto my Mysql and I can show databases, and create users and passwords. OS is Windows 2000 Advanced Server. In trying to backup my database, I used the MySQL Second edition syntax and also used something somebody suggested in a previous email, unfortunately I got error code 1064 in both cases. I tried the following: Name of database in this case is candleplace and I got this syntax from MYSQL 2nd Edition. mysql mysqldump --opt candleplace /archive/mysql/candleplace.073103 What I got was: ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump --opt candleplace /archive/mysql/candleplace.073103' at line 1 I also tried backing up the following way after creating dbase directory in my D drive: mysql mysqldump -u ola -p --opt --database --complte-insert --flush-logs d:/dbase/candleplace.2003-7-31 Each time I tried any of the commands to run mysqldump, I never included the ; and I end up getting the - and when I put in the ; then I get the error message. I have tried using mysqlhotcopy and I still got the same result. Is there something that I am doing wrong please point me in the right direction. Thank you all very much Ola A. Ogunneye Senior Accountant Adventist Healthcare - Financial Services 1801 Research Boulevard, Suite 400 Rockville, MD 20850 Tel: 301.315.3274 Fax: 301.315.3265 Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error 1005...
Greetings I have MySQL 4.0.14 running on WinXP. Whenever I create a temporary table from an existing table that has multiple keys ( the temporary table has columns reordered from the original table ) with a query like - create temporary table if not exists `mn`.`sqlyog_23796` ( `entryid` int(6) unsigned NOT NULL auto_increment , `caseid` int(6) unsigned NOT NULL DEFAULT '0' , `litigantid` int(6) unsigned NULL , `litiganttype` char(1) NULL , `counselid` int(6) unsigned NULL , primary key ( entryid ) ,KEY `caseid` ( `caseid` ), FULLTEXT KEY `counselid` ( `counselid` ), FULLTEXT KEY `litigantid` ( `litigantid` )); I get a error - Error No. 1005 Can't creat table 'C:\WINDOWS\TEMP\#sql794_37_2.frm' (errno: 140) Why the above query is not working? Is there anything wrong with the SQL? Karam __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Books advice
Asif, can you paste here the ISBN? Thanks!! Lucas -Mensaje original- De: Asif Iqbal Enviado el: Jueves, 31 de Julio de 2003 22:05 Para: Ralph Guzman CC: 'Fawad Siddiqui'; [EMAIL PROTECTED] Asunto: RE: Books advice I just ordered this book MySQL The definitive guide to using, programming, and administering MySQL 4 by Paul Dubois I found it more technical than MySQL cook book by Paul Dubois -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Books advice
ISBN 0-7357-1212-3 On Fri, 1 Aug 2003, Lucas Fonzalida - Soporte Tecnico wrote: Asif, can you paste here the ISBN? Thanks!! Lucas -Mensaje original- De: Asif Iqbal Enviado el: Jueves, 31 de Julio de 2003 22:05 Para: Ralph Guzman CC: 'Fawad Siddiqui'; [EMAIL PROTECTED] Asunto: RE: Books advice I just ordered this book MySQL The definitive guide to using, programming, and administering MySQL 4 by Paul Dubois I found it more technical than MySQL cook book by Paul Dubois -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Impossible mysqldump - Please HELP!!!
On Thu, 31 Jul 2003, Ola Ogunneye wrote: I am running mysql 4.0.13 on Apache 2.0.47 and PHP 4.3.2. I can log onto my Mysql and I can show databases, and create users and passwords. OS is Windows 2000 Advanced Server. In trying to backup my database, I used the MySQL Second edition syntax and also used something somebody suggested in a previous email, unfortunately I got error code 1064 in both cases. I tried the following: Name of database in this case is candleplace and I got this syntax from MYSQL 2nd Edition. mysql mysqldump --opt candleplace /archive/mysql/candleplace.073103 You run it from shell prompt What I got was: ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump --opt candleplace /archive/mysql/candleplace.073103' at line 1 I also tried backing up the following way after creating dbase directory in my D drive: mysql mysqldump -u ola -p --opt --database --complte-insert --flush-logs d:/dbase/candleplace.2003-7-31 You run it from dos prompt Each time I tried any of the commands to run mysqldump, I never included the ; and I end up getting the - and when I put in the ; then I get the error message. I have tried using mysqlhotcopy and I still got the same result. Is there something that I am doing wrong please point me in the right direction. Thank you all very much Ola A. Ogunneye Senior Accountant Adventist Healthcare - Financial Services 1801 Research Boulevard, Suite 400 Rockville, MD 20850 Tel: 301.315.3274 Fax: 301.315.3265 Email: [EMAIL PROTECTED] -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connection priorisation in Mysql
Hi Environment: RedHat 7.2 MySQL: v 3.23.41 Connections: Perl DBI I have a question if there is any features in mysql 3 /4 to support connection priorisation. Currently we are running some update queries that squeeze all all other connections running on the database. Does any one have any suggests. Regards John Clegg Weta Digital -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR while compiling:
i am using Linux 6.2(Zoot) and MySql 2.23.36and found the following error while compiling the C program using mysql APIcc -o test prog1.c -L /usr/lib/mysql -I /usr/include/mysql -lmysqlclientThe following Error occured :/usr/lib/mysql/libmysqlclient.a(my_compress.o): In function `my_uncompress':my_compress.o(.text+0x97): undefined reference to `uncompress'/usr/lib/mysql/libmysqlclient.a(my_compress.o): In function `my_compress_alloc':my_compress.o(.text+0x12b): undefined reference to `compress'collect2: ld returned 1 exit statusplease help me in understanding the problem. - Want to chat instantly with your online friends? Get the FREE Yahoo!Messenger
Problems Getting XP mysql server going
Mysql is in default directory my.ini - sure looks correct accourding to manual Trying to start frist time using command: C:\mysql\bin\mysqld --standalone Yields this error message: Can't fine messagefile'C:mysqin\share\english\errmsg.sys'aborting Now in C:\mysql\share\english\ there is an errmsg.sys I must be missing something: do I create mysqin and put the errmsg.sys in the directory or how do I change the server start up process to lookin in mysql and not mysqin ? Thanks in Advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]