RE: MySQL in read-only mode
This will be not that easy as there is a large array of different users accessing the DBA at different time during the day/night. CL t 11:58 AM 8/3/2007, Edward Kay wrote: > -Original Message- > From: Clyde Lewis - DBA [mailto:[EMAIL PROTECTED] > Sent: 03 August 2007 16:55 > To: MySQL List > Subject: Re: MySQL in read-only mode > > > The idea here is to perform this change without restarting the > server. Is that possible? I know that by updating the config file, > then restarting will allow the database to be in read-only mode, but > need to know if it is possible without restarting. > > Thanks, > CL > At 11:46 AM 8/3/2007, Clyde Lewis - DBA wrote: > >Is this possible with MYSQL using the innodb engine? If so, what is > >the command/process to make this happen. > > Can't you just remove the INSERT/UPDATE/DELETE priviledges from the user(s) concerned? Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] ~ Clyde Lewis Database Administrator General Parts International Inc. / CARQUEST P.O. Box 26006 Raleigh, NC 27611 Office: (919) 227-5100 Mobile: (757) 952-7331 Fax: (919) 573-2280 E-mail: [EMAIL PROTECTED]
Re: MySQL in read-only mode
The idea here is to perform this change without restarting the server. Is that possible? I know that by updating the config file, then restarting will allow the database to be in read-only mode, but need to know if it is possible without restarting. Thanks, CL At 11:46 AM 8/3/2007, Clyde Lewis - DBA wrote: Is this possible with MYSQL using the innodb engine? If so, what is the command/process to make this happen. Thanks in advance, ~ Clyde Lewis Database Administrator ~ Clyde Lewis Database Administrator
MySQL in read-only mode
Is this possible with MYSQL using the innodb engine? If so, what is the command/process to make this happen. Thanks in advance, ~ Clyde Lewis Database Administrator
Multiple binary log files question
Guys, I would like to know if there is a way to have individual databases under the same instance or server write to separate binary log files. The idea is to have multiple binary log file for each database on the same server. The problem that I experiencing is sorting through the binary log file and not knowing which database the changes are associated with while attempting to reapply the changes. Thanks in advance ~ Clyde Lewis Database Administrator
Re: Reapply bin-log question( Help Please)
Thanks a bunch for the suggested workaround. This method was exactly what I was looking for. Thanks again. CL At 01:33 AM 6/20/2007, Ananda Kumar wrote: One possible way is to spool the contents of bin-log into a file. mysqlbinlog oca-bin.000554 > binlog_sql.sql. This will give you all the data present in oca-bin.000554. Then you can set the foreign key check to "0" at the session level and then apply the binlog_sql.sql. Comment our everything (all set command any other things) but not the sql's source binlog_sql.sql. set foreign key check back to "1". or from binlog_sql.sql, check the insert or update that is giving "foreign key fail" error, know the position just before this sql and recovery till that position and then recovery from position after this sql still till the end of file. regards anandkl On 6/20/07, Clyde Lewis - DBA <[EMAIL PROTECTED]> wrote: Guys, I'm attempting to reapply a number of bin-log files in a effort to restore all changes that was made to a database. I'm performing the following command, but continue to get a foreign key constraint error message when doing so. Has anyone ever ran into this issue, and if so, what is a potential workaround? A possible solution is to set foreign key check to "0", but this from what I understand is done at the session level and cannot be applied in my situation. Any help would be greatly appreciated. mysqlbinlog oca-bin.000554 | mysql -h -u -p oca ERROR 1452 (23000) at line 125: Cannot add or update a child row: a foreign key constraint fails (`oca/invbodytax`, CONSTRAINT `invbodytax_ibfk_1` FOREIGN KEY (`inbtStore`, `inbtActNumber`, `inbtRef`, `inbtItem`) REFERENCES `invbody` (`inbStore`, `inbActNumber`, `inbRef`, `inbItem`) ON DEL) Thanks in advance, ~ Clyde Lewis Database Administrator ~ Clyde Lewis Database Administrator
Reapply bin-log question( Help Please)
Guys, I'm attempting to reapply a number of bin-log files in a effort to restore all changes that was made to a database. I'm performing the following command, but continue to get a foreign key constraint error message when doing so. Has anyone ever ran into this issue, and if so, what is a potential workaround? A possible solution is to set foreign key check to "0", but this from what I understand is done at the session level and cannot be applied in my situation. Any help would be greatly appreciated. mysqlbinlog oca-bin.000554 | mysql -h -u -p oca ERROR 1452 (23000) at line 125: Cannot add or update a child row: a foreign key constraint fails (`oca/invbodytax`, CONSTRAINT `invbodytax_ibfk_1` FOREIGN KEY (`inbtStore`, `inbtActNumber`, `inbtRef`, `inbtItem`) REFERENCES `invbody` (`inbStore`, `inbActNumber`, `inbRef`, `inbItem`) ON DEL) Thanks in advance, ~~~~~ Clyde Lewis Database Administrator
Query question
Guys, I have the following table that contains some information about a cars. I'm trying to write a query to determine: the number of make(name of car), number of models per make(name of car) and the average number of models/make(name of car) sold in a particular period. The two queries below can provide me with what I need, but am stumped on how to combine the results. Any help would be greatly appreciated. table name cars: columns: make model sold_date select make,count(make) from cars where make in(Nissan, Toyota, Honda) group by make select model,count(model) from cars where make in(Nissan, Toyota, Honda) group by model -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Benchmarking
Alex, Thanks a bunch for the insight and for proving the links to the following benchmarking tools. Unfortunately, business is requiring that each database live in it's own instance, so it sounds like moving in the direction of having multiple servers and spreading the data around would be the best idea. Again, thanks. CL At 06:39 PM 3/15/2007, Alex Greg wrote: On 3/14/07, Clyde Lewis <[EMAIL PROTECTED]> wrote: System Configuration: Sun Microsystems sun4u Sun Fire E2900 System clock frequency: 150 MHZ Memory size: 65536 Megabytes CPU: 12 @ 1200 MHz I'm looking for a tool that will allow us to determine the max number of databases that can run in a single instance of MySQL on a pretty beefy server( Spec above). In total we will have about ~40 MySQL instances running on this server. Each instance of MySQL, there will have between 30-60 individual databases supporting an OLTP application. I know that there are no know internal limits that MySQL have regarding the number of databases that can be created, but I would like get my hands on a tool that can simulate the number of databases and identify where we would potentially run into performance issues. As I mentioned above, your performance issues are going to come not from the number of databases, but from (primarily) how well-designed your database tables and queries are, and (secondly) how you configure the mysql server(s). One important factor to bear in mind is that with 40 separate MySQL instances on the single 64GB server, you will have a maximum 1.6GB of RAM per instance (excluding memory used by the O/S and other applications). This will have to be divided up between the various memory buffers (key_buffer, innodb_buffer_pool, etc.) allocated by each mysql process, so you might want to reconsider if you really need to run 40 separate mysql processes, or whether all the databases can live in the same MySQL instance and thus probably make better use of the available RAM. With regards to stress-testing and benchmarking, two popular tools for benchmarking MySQL servers are: Super Smack: http://vegan.net/tony/supersmack/ Sysbench: http://sysbench.sourceforge.net/ We need to determine whether to have multiple servers to support the ~40 instances or have all ~40 instances on the same machine. Any help of ideas would be greatly appreciated with this decision. I would be inclined to have separate machines, rather than put everything on one huge server. By spreading the data around, you are reducing the risk if the one mega-machine were to become unavailable, and also reducing resource contention (on the disks, CPU, RAM etc.). -- Alex *** Clyde Lewis Database Administrator General Parts, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Benchmarking
Guys, System Configuration: Sun Microsystems sun4u Sun Fire E2900 System clock frequency: 150 MHZ Memory size: 65536 Megabytes CPU: 12 @ 1200 MHz I'm looking for a tool that will allow us to determine the max number of databases that can run in a single instance of MySQL on a pretty beefy server( Spec above). In total we will have about ~40 MySQL instances running on this server. Each instance of MySQL, there will have between 30-60 individual databases supporting an OLTP application. I know that there are no know internal limits that MySQL have regarding the number of databases that can be created, but I would like get my hands on a tool that can simulate the number of databases and identify where we would potentially run into performance issues. We need to determine whether to have multiple servers to support the ~40 instances or have all ~40 instances on the same machine. Any help of ideas would be greatly appreciated with this decision. Thanks in advance, *** Clyde Lewis Database Administrator General Parts, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Gaining statistics from MySQL
I'm looking to find a way to determine the number of transactions that a particular database is processing each min/hour/day/month/year and would like to know of a known MySQL best practices for determining this information. I'm hoping that this can be determined at the database level, but am not currently aware of how to move for forward. Release: 4.1.20 OS: Solaris 10 Thanks in advance, *** Clyde Lewis Database Administrator General Parts, Inc. 919-227-5100 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[JOB] MySQL DBA, Raleigh, NC
General duties of the position: Responsible for independently performing the complex technical operations/tasks or portions of the larger projects such as upgrading database or writing code/scripts to resolve complex business problems. Troubleshoot the technical issues by interfacing with appropriate technical & functional stakeholders. Analyze the information relevant to the current issue and make well thought recommendations towards resolutions. Responsibilities (tasks): * Independently develop code/scripts to automate existing DBA functions or create new functionalities for a particular business application. * Provide DBA related inputs to the project teams and adhere to the assigned project expectations/schedules. * Appropriately manage his/her priorities to provide effective DBA support to the existing and prospective customers. * Work and communicate effectively with other DBAs, project managers and business representatives on a given issue and/or to develop a healthy working relationship. * Provide consultation from the DBA perspective, when necessary, to the various project teams. * Understand the business side of the technical operations and recommend ways to effectively use the DBA technologies to optimize the business. * Demonstrate flexibility in adapting to the changing database technologies and business needs. * Possess the advance DBA skills or knowledge to be able to perform assigned tasks. Experience/skills Required: * Three years of experience in administering MySQL databases. This includes backup/restore, performance tunning, database duplicating etc. * Well versed in unix shell scripting. * Ability to troubleshoot complex technical problems. * Strong analytical skills to troubleshoot and be able to resolve complex technical issues. * Ability to effectively work in a fast paced team environment. * Effective communication skills both oral and written. * Proficiency in programming languages Java, shell scripting, perl, php etc. will be huge plus If you are interested in this position please submit your resume, salary requirements, and a paragraph highlighting your skills/experience as it pertains to this job to [EMAIL PROTECTED] *** Clyde Lewis Database Administrator General Parts, Inc.
RE: Find foreign key for a table
Will the output from the show command also provide a list of other dependent tables? From what I've seen with the "show create table " command, it only provide keys associated with that table , but other dependent tables. Thanks so much. At 01:18 PM 11/14/2006, Howard Hart wrote: show create table ? -Original Message----- From: Clyde Lewis [<mailto:[EMAIL PROTECTED]>mailto:[EMAIL PROTECTED] Sent: Tue 11/14/2006 10:16 AM To: MySQL List Subject: Find foreign key for a table Hello, How can I find the foreign keys for a table? I would like to be able to find, for any table, the columns that are foreign keys and what tables/columns the foreign keys are related to. Basically what I am trying to do is this. My developers want to allow logical deletes of data. I don't want them to be able to logically delete a record that has related records. Thanks in advance -- MySQL General Mailing List For list archives: <http://lists.mysql.com/mysql>http://lists.mysql.com/mysql To unsubscribe: <http://lists.mysql.com/[EMAIL PROTECTED]>http://lists.mysql.com/[EMAIL PROTECTED]
Find foreign key for a table
Hello, How can I find the foreign keys for a table? I would like to be able to find, for any table, the columns that are foreign keys and what tables/columns the foreign keys are related to. Basically what I am trying to do is this. My developers want to allow logical deletes of data. I don't want them to be able to logically delete a record that has related records. Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Swapping problem
A little confused as to why MySql is swapping when there is more than enough real memory still available on the server. I'm having a serious problem with a production server running out of swap space and in turn causing mysql to crash with the following error message. According to the TOP report, there is more than 14G available to the server. Error: Version: '4.1.11-max-log' socket: '/export/ctrl/mysqladmin/mysql.sock' port: 3306 MySQL Community Edition - Experimental (GPL) 061013 12:39:47 InnoDB: Error: cannot allocate 73919464 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 595973856 bytes. Operating system errno: 11 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We keep retrying the allocation for 60 seconds... 061013 12:39:50 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... Top on server: load averages: 0.11, 0.12, 0.13 11:37:55 140 processes: 138 sleeping, 1 stopped, 1 on cpu CPU states: 99.1% idle, 0.2% user, 0.6% kernel, 0.0% iowait, 0.0% swap Memory: 32G real, 14G free, 30G swap in use, 145M swap free PID USERNAME LWP PRI NICE SIZE RES STATETIMECPU COMMAND 2556 mysql 19 590 954M 735M sleep 23.5H 0.14% mysqld 20373 mysql 31 590 954M 706M sleep 78:23 0.13% mysqld 27559 mysql 47 590 1011M 990M sleep 26.6H 0.09% mysqld 2049 mysql 18 590 943M 701M sleep 16.8H 0.07% mysqld 2810 mysql 26 590 947M 754M sleep 766:37 0.05% mysqld 2370 mysql 24 590 963M 825M sleep 31.7H 0.01% mysqld 29472 mysql 1 590 3144K 2024K cpu/11 0:00 0.01% top 2432 mysql 20 590 952M 732M sleep 800:57 0.01% mysqld 22967 mysql 31 590 934M 139M sleep5:02 0.01% mysqld 2742 mysql 18 590 947M 722M sleep 17.6H 0.00% mysqld 2494 mysql 20 590 945M 728M sleep 782:58 0.00% mysqld 2880 mysql 26 590 972M 828M sleep 910:23 0.00% mysqld Any help would be greatly appreciated. Thanks in advance, CL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Read_only Mode
The idea is to prevent all users from applying changes to the system. Not just a single user. I'm trying to find something similar to Oracle's Read-only mode option during startup. Thanks, CL At 02:38 PM 6/28/2006, João Cândido de Souza Neto wrote: Why you don´t create a user with just select right and use him? "Clyde Lewis" <[EMAIL PROTECTED]> escreveu na mensagem news:[EMAIL PROTECTED] > All, > > Does MySQL have an option where the database can startup in READ-ONLY > mode? The idea is to have the server running with users connected, but > now allowing any updates to me applied to the database. I've looked > through the documentation, but was not able to find such a feature. If > someone can point me in the right direction, it would be greatly > appreciated. > > Thanks in advance, > > *** > Clyde Lewis > Database Administrator > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] *** Clyde Lewis Database Administrator General Parts, Inc. 919-227-5100
MySQL Read_only Mode
All, Does MySQL have an option where the database can startup in READ-ONLY mode? The idea is to have the server running with users connected, but now allowing any updates to me applied to the database. I've looked through the documentation, but was not able to find such a feature. If someone can point me in the right direction, it would be greatly appreciated. Thanks in advance, *** Clyde Lewis Database Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cannot kill Query process
I have a process that has been running for more than 7080 seconds and have sent a kill signal to the process more than 30 minutes ago. kill query id#. Is there something I'm missing? Show full processlist: 3294878 usergpiexp01sea 7207 Killed UPDATE parts SET prtStocked = (CASE WHEN (prtstkqty = 0 AND prtordpnt = 0) THEN 'N' ELSE 'Y' 3303487domdef exapps3:43461sea31 Query UPDATE parts SET prtStkQty = prtStkQty - (1/POWER(10, (0-prtVQ))), prtRsvQty = prtRsvQty + (1 3303490domdef exapps2:36706sea30 Query UPDATE parts SET prtStkQty = prtStkQty - (1/POWER(10, (0-prtVQ))), prtRsvQty = prtRsvQty + (1 3303493domdef exapps2:36709sea30 Query UPDATE parts SET prtStkQty = prtStkQty - (1/POWER(10, (0-prtVQ))), prtRsvQty = prtRsvQty + (1 3303494domdef exapps3:43484sea28 Query UPDATE parts SET prtStkQty = prtStkQty - (1/POWER(10, (0-prtVQ))), prtRsvQty = prtRsvQty + (1 3303498domdef exapps3:43528sea24 Query UPDATE parts SET prtStkQty = prtStkQty - (1/POWER(10, (0-prtVQ))), prtRsvQty = prtRsvQty + (1 3303502domdef exapps3:43566sea20 Query UPDATE parts SET prtHstCur = prtHstCur - (1/POWER(10, (0-prtVQ))), prtHstYTD = prtHstYTD - (1 3303503domdef exapps2:36797sea20 Query UPDATE parts SET prtStkQty = prtStkQty - (1/POWER(10, (0-prtVQ))), prtRsvQty = prtRsvQty + (1 3303504domdef exapps2:36808sea19 Query UPDATE parts SET prtStkQty = prtStkQty - (1/POWER(10, (0-prtVQ))), prtRsvQty = prtRsvQty + (1 3303505domdef exapps2:36809sea19 Query UPDATE parts SET prtStkQty = prtStkQty - (2/POWER(10, (0-prtVQ))), prtRsvQty = prtRsvQty + (2 3303506domdef exapps2:36820sea18 Query UPDATE parts SET prtStkQty = prtStkQty - (6/POWER(10, (0-prtVQ))), prtRsvQty = prtRsvQty + (6 3303382domdef exapps2:35930sea15 Query UPDATE pobdy LEFT JOIN parts ON pobPNID = prtPNID SET pobRecvQty = CASE WHEN (prtStkQty < 0) Thanks in advance *** Clyde Lewis Database Administrator General Parts, Inc. 919-227-5100
Re: Show row number
Excellent explanation. Thanks again. CL At 02:00 PM 1/25/2006, [EMAIL PROTECTED] wrote: Clyde Lewis <[EMAIL PROTECTED]> wrote on 01/25/2006 01:41:30 PM: > Is there a command in mysql that will return the row number. I > tried rownum and rownum() > No, "rows" do not exist in the base data of a MySQL database. They are called "records" and records may be stored in any order. Within some MySQL storage engines (particularly InnoDB), more than one of any "record" may exist in the database at the same time (depending on transaction activity) or in more than one place (NDB). Within the results of a SELECT query, the individual row number is only important to the client. You need to use whatever number your client library provides for you.( ex: in ADO you would use the Recordset.AbsolutePosition property) There is no internal "record number" that is exposed through any interface to any client. Now, there is a pointer to each record but that is not useful information to any system except the database server itself and cannot be used to query for a particular record because it is not part of the data but it is part of the metadata associated with each record. Records can move around within a database file (changing their pointers) so long as the data pointed to does not change. Shawn Green Database Administrator Unimin Corporation - Spruce Pine *** Clyde Lewis Database Administrator General Parts, Inc. 919-227-5100
Show row number
Is there a command in mysql that will return the row number. I tried rownum and rownum()
General logging vs. bin logging
Hello, I'm trying to get a handle on who is making what changes in my database, but cannot seem to find user/account info in the bin-logs besides the actual update that was made. Is there a way in which MySQL logs not only the changes, but also the user account that updated a record in the database and how is this enabled? Any help would be greatly appreciated. Thanks in advance. CL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy database other than mysqldump
You could copy the entire mysql directory structure to an isolated area on disk. This will involve stopping the instance though. Luke Vanderfluit wrote: Hi. I'm using 4.0.18 and for legacy reasons I can't upgrade that. However I'm upgrading the database to a new schema. I want to copy the database before I make changes to the schema, so that if anything goes wrong, I can revert back. I have a dump file of the database but that is around 5GIG, so takes around an hour to restore. Is there a quicker way to copy a database other than to do a dump. Thanks. Kind regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Verify DML(Mysterious DROP TABLE command in production)
I did check the binlog and saw that the command ran along with the timestamp. Is there a way to figure out who ran the command? [EMAIL PROTECTED] wrote: Clyde Lewis <[EMAIL PROTECTED]> wrote on 09/26/2005 02:44:40 PM: Guys, Our production database had a table that was suddently dropped "a mystery". I need to find out if there is a way to find the user account that performed this function. No one is taking ownership and I need to know if there is a way to find out. Thanks in advance Have you checked your binlogs? Each command is listed along with the user account that was used to execute it and a timestamp for proper replication. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Verify DML(Mysterious DROP TABLE command in production)
Guys, Our production database had a table that was suddently dropped "a mystery". I need to find out if there is a way to find the user account that performed this function. No one is taking ownership and I need to know if there is a way to find out. Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql to Oracle migration
Does anyone know of a straght forward approach to migrate a mysql(4.1.11) Schema to Oracle(9i release 2). Also, please provide any best practices. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stopping drop index command on table
What are the consequences of cancelling(stopping) the drop index on table command if any at all? The process is running on a 9GB table and need to stop the process (dev environment). Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb thread concurrency size
Hey guys, I'm looking for any best practices or a formula that is commonly used in setting the value for thread concurrency in the configuration file. I have 24 instances running on a sun 2900 server with 32GB or ram. Here is a sample of my configuration file. #use this line mfor mysql 4.1 old-passwords server-id = 2216 net_buffer_length=65536 net_read_timeout=120 net_write_timeout=180 key_buffer=64M max_allowed_packet=1M table_cache=2048 sort_buffer=1M record_buffer=1M myisam_sort_buffer_size=16M max_connections=2500 thread_cache=8 # Try number of CPU's*2 thread_concurrency=4 query_cache_size=256M query_cache_limit=128K #only availble in 4.1 innodb_file_per_table innodb_buffer_pool_size=500M innodb_additional_mem_pool_size=25M innodb_log_archive=0 innodb_log_files_in_group=3 innodb_log_file_size=100M innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=2 innodb_file_io_threads=4 innodb_lock_wait_timeout=30 #innodb_flush_method=fdatasync #innodb_fast_shutdown=1 innodb_thread_concurrency=5 transaction-isolation = READ-UNCOMMITTED [mysqld140] bind-address=xxx.xxx.xxx.xxx old-passwords mysqld=/usr/local/mysql/bin/mysqld_safe pid-file=/p01/abq/mysqladmin/abq_pid basedir=/usr/local/mysql datadir=/p01/abq/mysqldata socket=/p01/abq/mysqladmin/mysql.sock port=3306 local-infile=1 user=mysql tmpdir = /tmp/abq/ log = /p01/abq/mysqllogs log-bin = /p01/abq/mysqllogs/abq-bin log-err = /p01/abq/mysqllogs/abq.err log-slow-queries = /p01/abq/mysqllogs/abq_slow_query.log innodb_file_per_table set-variable = innodb_buffer_pool_size=500M set-variable = innodb_additional_mem_pool_size=25M innodb_data_home_dir = /p01/abq/mysqldata/innodb/ innodb_data_file_path = ibdata1_abq:100M:autoextend:max:4096M #.._log_arch_dir must be the same #as .._log_group_home_dir innodb_log_group_home_dir = /p01/abq/mysqladmin/iblogs innodb_log_arch_dir = /p01/abq/mysqladmin/iblogs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index update process 20+ hrs
tWarrantyMsg` int(10) unsigned NOT NULL default '0',
`prtWarrantyLength` smallint(5) unsigned NOT NULL default '0',
`prtWarrantyUnit`
enum('NO','MO','DA','YR','WK','HR','LT','LL','ML','TD') NOT NULL default
'NO',
`prtWarrantyMsg2` int(10) unsigned NOT NULL default '0',
`prtWarrantyLength2` smallint(5) unsigned NOT NULL default '0',
`prtWarrantyUnit2`
enum('NO','MO','DA','YR','WK','HR','LT','LL','ML','TD') NOT NULL default
'NO',
`prtInvMod` char(2) NOT NULL default 'S',
`prtBelowCostMargin` int(5) unsigned NOT NULL default '0',
`prtUseBelowCostMargin` enum('N','Y') NOT NULL default 'N',
`prtAboveCostMargin` int(5) unsigned NOT NULL default '0',
`prtUseAboveCostMargin` enum('N','Y') NOT NULL default 'N',
`prtForcedQty` enum('N','Y') NOT NULL default 'N',
`prtIsRestricted` enum('N','Y') NOT NULL default 'N',
`prtHazmatCode` char(3) NOT NULL default '',
`prtShipAnywhere` enum('N','Y') NOT NULL default 'Y',
`prtCInBound` int(11) NOT NULL default '0',
`prtPartTax` char(4) NOT NULL default '',
`prtCoreTax` char(4) NOT NULL default '',
`prtStateFee` char(4) NOT NULL default '',
`prtCIDFee` char(4) NOT NULL default '',
`prtOtherFee` char(4) NOT NULL default '',
`prtDepth` char(18) NOT NULL default '',
`prtHeight` char(9) NOT NULL default '',
`prtWidth` char(9) NOT NULL default '',
`prtMSDS` int(10) unsigned NOT NULL default '0',
`prtRetailCatType` char(3) NOT NULL default '',
`prtLogicOper` enum('N','F','B','L','1') NOT NULL default 'N',
`prtSubType` enum('NONE','TIRE','BATT','PKG','SMPT') NOT NULL default
'NONE',
`prtWrtyTypeID` int(10) unsigned NOT NULL default '0',
`prtSourceCode` char(3) NOT NULL default '',
`prtShopPart` enum('N','Y') NOT NULL default 'N',
`prtPkgGroup` char(10) NOT NULL default '',
`prtPkgSubGroup` char(10) NOT NULL default '',
`prtDisclaimID` int(10) unsigned NOT NULL default '0',
`prtQualifier` char(10) NOT NULL default '',
`prtStatus` enum('ACTIVE','VOID','CHANGEOVER') default NULL,
`prtDCPop` char(5) NOT NULL default '',
`prtCOnOrder` int(10) unsigned NOT NULL default '0',
`prtIsDisplay` enum('N','Y') NOT NULL default 'N',
`prtVolProtect` int(10) unsigned NOT NULL default '0',
`prtDNM` enum('N','Y') NOT NULL default 'N',
`prtLst2Year` int(11) NOT NULL default '0',
`prtLst3Year` int(11) NOT NULL default '0',
`prtDoNotReturn` enum('N','Y') NOT NULL default 'N',
`prtSalesHistTot` int(10) NOT NULL default '0',
`prtSalesHistPrd` smallint(5) NOT NULL default '0',
PRIMARY KEY (`prtPNID`),
UNIQUE KEY `prtUPN` (`prtPN`,`prtStore`,`prtMfg`),
KEY `prtSort` (`prtSort`),
KEY `sku1` (`prtSKU`,`prtStore`),
KEY `prtIPN` (`prtStripped`,`prtStore`,`prtMfg`),
KEY `prtDESC` (`prtDesc`,`prtStore`),
KEY `prtWrtyTypeID` (`prtWrtyTypeID`),
KEY `prtStore` (`prtStore`,`prtMfg`),
CONSTRAINT `parts_f1` FOREIGN KEY (`prtStore`) REFERENCES `cofile`
(`coStore`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Parts Table'
mysql> SHOW VARIABLES LIKE '%buff%';
+---+--+
| Variable_name | Value|
+---+--+
| bdb_log_buffer_size | 1048576 |
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_awe_mem_mb | 0|
| innodb_buffer_pool_size | 52428800 |
| innodb_log_buffer_size| 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 67108864 |
| myisam_sort_buffer_size | 16777216 |
| net_buffer_length | 65536|
| preload_buffer_size | 32768|
| read_buffer_size | 1044480 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 1048568 |
+---+--+
13 rows in set (0.00 sec)
mysql> SHOW TABLE STATUS LIKE 'parts';
+---++-++-++-+-+--+---++-+-++---+-
Index update process 20+ hrs
Guys, I have a huge table to which I'm attempting to update the foreign key and index. It is taking me more than 20 hrs to complete the process and would like to know if someone can point me in the right direction. Please let me know of any additional information that I should provide. mysql> show index from parts; +---++---+--+---+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++---+--+---+---+-+--++--++-+ | parts | 0 | PRIMARY |1 | prtPNID | A | 3921279 | NULL | NULL | | BTREE | | | parts | 0 | prtUPN|1 | prtPN | A | 326773 | NULL | NULL | | BTREE | | | parts | 0 | prtUPN|2 | prtStore | A | 3921279 | NULL | NULL | | BTREE | | | parts | 0 | prtUPN|3 | prtMfg| A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | prtSort |1 | prtSort | A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | sku1 |1 | prtSKU| A | 18 | NULL | NULL | | BTREE | | | parts | 1 | sku1 |2 | prtStore | A | 18 | NULL | NULL | | BTREE | | | parts | 1 | prtIPN|1 | prtStripped | A | 230663 | NULL | NULL | | BTREE | | | parts | 1 | prtIPN|2 | prtStore | A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | prtIPN|3 | prtMfg| A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | prtDESC |1 | prtDesc | A | 301636 | NULL | NULL | | BTREE | | | parts | 1 | prtDESC |2 | prtStore | A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | prtWrtyTypeID |1 | prtWrtyTypeID | A | 18 | NULL | NULL | | BTREE | | | parts | 1 | prtStore |1 | prtStore | A | 18 | NULL | NULL | | BTREE | | | parts | 1 | prtStore |2 | prtMfg| A | 46132 | NULL | NULL | | BTREE | | +---++---+--+---+---+-+--++--++-+ mysql> explain select * from parts; ++-+---+--+---+--+-+--+-+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+---+--+-+--+-+---+ | 1 | SIMPLE | parts | ALL | NULL | NULL |NULL | NULL | 3921279 | | ++-+---+--+---+--+-+--+-+---+ 1 row in set (0.00 sec) Files from mysqldata directory -rw-rw 1 mysqlmysql18K Aug 25 17:24 parts.frm -rw-rw 1 mysqlmysql 8.9G Aug 29 12:32 parts.ibd DB MySQL: 4.1.11 OS: Solaris 9 Hardware: SUN 2900; 32GB RAM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple my.cnf files
Does anyone have any experience using multiple my.cnf files on a single box? If so, how is the my.cnf specified during startup and shutdown? I need multiple my.cnf files to test ibbackup software because it does not currently support using mysqld_multi. CL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import problem
I used mysqldump with the -t option to only dump the raw data. I'm in the process of importing the dump to a new database with seed data loaded. I'm attempting to override the existing dataset but continue to get the following message when the import encounters a duplicate entry: Duplicate entry '0' for key 1. and bombs out each time. Ss there an option that I can add to allow the load to proceed. Thanks in advance. CL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple database backup solution
I'm in need of a hotbackup solution that supports multiple instances on the the same server. Currently, I'm dumping the databases nightly and would like to know if anyone know of an alternative. I've tested innodb, but have not had any success getting it to work with more than one group(mysqld[GNR]) in my my.cnf file. Any advise would be greatly appreciated. Thanks in advance. *** CL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ibbackup question
I'm in the process of testing this backup utility and need to know if anyone have any experience using it as their primary backup solution. Any help would be greatly appreciated. Thanks in advance. *** Clyde Lewis DBA General Parts (919)227-5100 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ibbackup and GNR
I've been searching for a decent backup utility and is currently in the process of testing ibbackup from www.innodb.com and is presently stuck. I've tried running the script but it will only parse the first GNR in my my.cnf file. The my.cnf file has five group numbers which sets up five databases. I'm still testing and have not yet purchased support, but thought that I could get some feedback from this list. Any help would be greatly apprecciated in helping me move forward with this testing. *** Clyde Lewis DBA General Parts (919)227-5100 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Binary Log and Load Data Local
The manual does not give any warnings about using "load data local" when applying changes via the binary log. I am having trouble getting my head around how this would work. IE if you update a table via "load data local" then how can you replicate these changes using the binary log, because when you try to run the SQL statements to replicate the changes of the table, you probably won't have that file local on your computer. (ie the original load data local could have been done from any computer on the network or even through the Internet) Does this mean replication servers can't be EXACTLY kept up to date if you use "load data local"? I can also see possible issues even if you don't use local - for example: A job uses "load data infile" that loads some data from the server into the table, then as a part of the clean up process deletes the input file. Now if you wanted to reproduce these changes from the binary log (eg after a file restore) the SQL statement that tries to run the "load data infile" will fall over because the file is no longer on the server. I would therefore think using "load data infile" local or otherwise has HUGE implications when you are looking at a backup strategy. Am I missing some thing here? Are there any work arounds, or should it be said that if you want EXACT replication of data DON'T use "Load data infile"? Thanks Clyde England - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Windows Users FeedBack
Hi, I have installed Version 4.07 gamma on a windows 2000 server. Install went like a dream and I was up and running within a few minutes. I have run several tests of my own using Delphi and Zeos Data base controls - again all this works perfectly. ( I get nervous when things work this well !) I have been reading the list and come to the conclusion that MySql is used very much in a Linux/Unix web environment. I am looking at converting over our company legacy systems (debtors, creditors, assets, etc) to MySql. (we are a small company that would only have a maximum of 40 users logged on to the data base) Though everything I have tried works fine - this is only in a test environment and not a production one. Speed and Usability are a given, my only concern now is stability. So my questions basically are: 1. Am I on the wrong track here using MySql/Windows for these types of applications. 2. Are there other users out there with similar usage 3. Do windows users find MySql stable - Good or bad comments here please Perhaps this should be another thread, but would anyone have some indication as to what % of MySql installations are Windows based - my gut felling is that it would be lucky to be 5% but I would sure like to know if someone has some statistical data on this. Thanks Clyde England - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
When are MyIsam Tables physically updated?
Hi, I am just trying to get a handle on when tables are physically written to disk. (ignoring whatever operating system level caching that may be going on) IE If you do some updates/inserts to a MyIsam table and then have a power failure will you ALWAYS loose those updates (Even if the power failure is after say 5 minutes of inactivity on a table, assuming no intervening FLUSH command), or does the server do a write to disk based on some algorithm (eg low activity or Number of updates)? I understand the operating system may do its own caching and hence have a bearing on this - but at this stage I just need to know how/when MySql Physically writes the tables to disk. Thanks Clyde England - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Merge Table weird result - bug?
Hi, I am using windows 2000 server and MySql 4.07gamma. I have a merge table, combining 2 tables. The tables both have only one index (non unique) which consist of 2 fields (both these fields are defined as char but in reality they only have integer values in them. Index Field 1 is char(1) and index field2 is char(3). Queries over the the individual tables produce results as expected however queries over the merge table often give an empty result set when they should not. The only way I can get the correct result in these cases on the merge file is to enter the second part of the where clause as Integer (ie without the quotes) IE select * from table1 where field1 = '2' and field2 = '100' > 50 records select * from table2 where filed1 = '2' and field2 = '100' > 30records Now the problem select * from MergeTable where field1 = '2' and field2 = '100' > 0 records ie empty result set BUT select * from MergeTable where field1 = '2' and field2 = 100 > 80 records (the correct result) IE by using field2 = 100 I get the correct result but not when I use field2 = '100' ( using the quotes) As field2 is defined as char I would have thought using the quotes is the correct syntax (and this works perfectly with the individual tables) As the query works fine on the individual tables I am sure the problem is with the merge table. To make sure the field definitions were 100% correct for the merge table I used mysqldump and then cut and paste. This was also the method I used to create table2 from table1, so I am quite sure all the table definitions are the same. Could this be a bug? Am I missing something here? Thanks Clyde England PS I get the feeling it is the index that is part of the problem, because if I remove the index and re-create the files, all is OK - but of course now much slower. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Lock Tables Query
Hi, As per a previous thread I have found that when you use "lock Tables" MySql will wait indefinitely for the lock - No timeout or error message. Therefore let me explain my question. Scenario: User 1 locks files for a long running job. (write lock that prevents any access to the files) User 2 logs on, then try's to lock or use these files but can't because user 1 already has the lock. (even a simple "select * from xxx" will wait forever) The program for user 2 will just appear to hang. How do I give feed back to user 2 to say something like: "Unable to lock/use files after waiting 30 seconds, try again later" or whatever. IE I would like the program to be able to return control after a specific amount of time if the lock is not satisfied in order to give feedback to the user, rather than the the program just wait and appearing to hang. Better still if there is a system variable or something I can check first to see if the file is locked - but I can't seem to find this in the docs. Most other databases I have used have a timeout value (like the record lock for innodb) so I am having trouble dealing with this scenario. Any Ideas. Thanks Clyde England - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Lock Tables Timeout value?
*** REPLY SEPARATOR *** On 11/01/2003 at 1:29 PM Mark wrote: >Gee, I hope there is no such thing as a timeout value for "Lock Tables". >:) >If two of my programs decide that one of them needs to wait for the other, >however long that may take, then I hope MySQL honors that chosen symbiosis. >I hope it behaves like a Perl flock(): it just waits, and waits, and >waits -- and that is how I want it. :) H'mm. If this is so then how do you give feed back to a user when files are locked. Scenario: User 1 locks files for a long running job. User 2 logs on, then try's to lock files but can't because user 1 already has the lock. If there is no timeout value then the program for user 2 will just appear to hang. How do I give feed back to user 2 to say something like: "Unable to lock files after waiting 60 seconds, try again later" or whatever. IE I would like the program to be able to return control after a specific amount of time if the lock is not satisfied (I would think MySql would give an error message) in order to give feedback to the user, rather than the the program just wait and appearing to hang. This behavior occurs with record locks using InnoDB files. I would have thought a similar approach would have applied to File locks? Thanks Clyde England - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Lock Tables Timeout value?
Hi, I have searched the docs but can't seem to find information on the time out value for "Lock Tables" (probably just me) When using "Lock Tables" how long does MySql wait before giving up if it can't get a lock? Is there any way of changing this time out value? Thanks Clyde England - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select * from names where words(name) = 3
Thanks Dan,
It never occured to me to use regular expressions. (I'm still new to Mysql)
You have opened up a whole new world to me :-)
The tables I am working with only have a few thousand records in so
performance is not a big issue and the reg expression works just fine (the
response is near instant on my now aging P500 256mb PC)
Thanks again.
Clyde England
*** REPLY SEPARATOR ***
On 8/01/2003 at 11:45 PM Dan Nelson wrote:
>In the last episode (Jan 09), Clyde England said:
>> I have a database of names and would like to do a selection based on
>> the number or words in a name
>>
>> eg the name "Peter Smith" has 2 words
>> the name "Peter John Smith" has 3 words
>> the name "Peter John Fred Smith" has 4 words
>>
>> IE I would like to select all names where there are 3 words in it for
>> instance. If there were such a function as words(string) which
>> returned the number of words in a string then the simple select
>> syntax would be:
>>
>> select * from names where words(name) = 3
>>
>> Of course in MySql there is no such function (that I am aware of) -
>> so any ideas how I can achieve this result.
>
>Easy (although not all that fast) way:
>
>select * from names where name regexp "^[^ ]*( [^ ]*){2}$";
>+--+
>| name |
>+--+
>| Peter John Smith |
>+--+
>
>The '2' in the regex is how many spaces are in the name. 0 = single
>word, 1 = 2 words, etc. Exercise to the reader: make it work correctly
>with runs of spaces, and handle tabs and other whitespace characters.
>
>Fast way:
>
>Write a UDF the implements your WORDS() function; this will be quite a
>bit faster than the regex.
>
>Fastest way:
>
>Write the UDF, add another column to your table called `words`, index
>it, and update it when you update your `name` field. Use that column
>in your queries.
>
>--
> Dan Nelson
> [EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Select * from names where words(name) = 3
Hi All, Not sure how to do this (or even if you can) but thought I would ask anyway. I have a database of names and would like to do a selection based on the number or words in a name eg the name "Peter Smith" has 2 words the name "Peter John Smith" has 3 words the name "Peter John Fred Smith" has 4 words (sorry if this is too obvious but I am just trying to make myself clear) So the selections I would like to do are based on the number of words in the name. IE I would like to select all names where there are 3 words in it for instance. If there were such a function as words(string) which returned the number of words in a string then the simple select syntax would be: select * from names where words(name) = 3 Of course in MySql there is no such function (that I am aware of) - so any ideas how I can achieve this result. Thanking You Clyde Engalnd PS: one obvious solution is to add another field to the table eg NumWords then write code to maintain this field with the correct nubmer of words in a name. I was trying to avoid this and hoping to be able to just use native MySql functions. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to Unlock a row?
Clyde, - Original Message - From: ""Clyde"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Saturday, January 04, 2003 11:47 AM Subject: How to Unlock a row? > Hi, > > Using MySql with InnoDB files you can lock rows for update. > Eg select * from customer where cusomerID=1 for update > > I assume these locks are release when the the records selected are actually updated >no. Does this still hold true if MySql is running in autocommit mode? And therefore does the query "select * from customer where cusomerID=1 for update" only make sense to use if autocommit is OFF? IE if Autocommit is on, and you run this query, are the records still locked, or has an implicit COMMIT been issued because we are running in autocommit mode - thus releasing the record lock. Thanks Clyde - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to Unlock a row?
Hi, Using MySql with InnoDB files you can lock rows for update. Eg select * from customer where cusomerID=1 for update I assume these locks are release when the the records selected are actually updated (or the connection is terminated). But how do you release these record locks if you do not want to go ahead with the update. Scenario: User selects records to update. User changes mind (clicks on cancel button) Now we have locked rows that need to be unlocked. How to do this? Thanks Clyde - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to get the correct result -- Thrid Time --
On Sat, 20 Oct 2001, Adrian D'Costa wrote:
| Date: Sat, 20 Oct 2001 12:04:53 +0530 (IST)
| From: Adrian D'Costa <[EMAIL PROTECTED]>
| To: Clyde Jones <[EMAIL PROTECTED]>
| Cc: [EMAIL PROTECTED]
| Subject: Re: how to get the correct result -- Thrid Time --
|
| On Fri, 19 Oct 2001, Clyde Jones wrote:
|
| To me they are same, but since there is a space, they are not. What I
| want is just one name
|
| >
| > If you want to get only unique, non-blank results then
| >
| > SELECT DISTINCT
| > replace(replace(nome_hotel,\n,""), " ", " ")
| > from hotel
|
| The problem is that the space (" ") will match if there are two
| spaces. I am not sure how may are there.
|
| Adrian
Did you try that? replace should recursively replace ALL double spaces,
if it is a problem just run and rerun the query
UPDATED hotel
set nome_hotel replace(nome_hotel, " ", " ")
until all the double spaces are gone.
On your entry form you will want to do this so the data goes in the way
you want it, not the way it was entered.
Clyde
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
- Benjamin Franklin
Clyde Jones - http://www.clydec.net
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to get the correct result -- Thrid Time --
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 OK it looks like you have gotten rid of your excess carriage returns, but now you need to remove the excess spaces. did you try nesting the functions? try this select replace(replace(nome_hotel,\n,""), " ", " ") as nome d'hotel from hotel group by nome_hotel limit 10; you can update your entire database and remove the excess spaces and returns by doing the following update hotel set nome_hotel to replace(replace(nome_hotel,\n,""), " ", " ") HTH On Fri, 19 Oct 2001, Adrian D'Costa wrote: | Date: Fri, 19 Oct 2001 10:43:31 +0530 (IST) | From: Adrian D'Costa <[EMAIL PROTECTED]> | To: Clyde Jones <[EMAIL PROTECTED]> | Subject: Re: how to get the correct result -- Thrid Time -- | | Hi, | | Thanks. It gave an error but it work after adding the "\n". Now the | result is: | | +--+-+ | | id | replace(nome_hotel,"\n","") | | +--+-+ | | 3825 | | | | 3827 | | | | 3391 | Abi d'Oru | | | 5208 | Abou Nawas Djerba | | | 3063 | Abou Nawas Djerba | | | 1252 | Adams Beach | | | 9757 | Aegean Village | | | 7973 | Aegean | | | 8917 | Aegean Village | | | 8122 | Aegeon | | +--+-+ | | If you notice id 5208 and 3063 are the same so also with 9757 and 7973. I | tried using trim it is the same. Any pointers. | | Thanks | | Adrian | | On Thu, 18 Oct 2001, Clyde Jones wrote: | | > the doc page is here | > http://www.mysql.com/doc/S/t/String_functions.html | > | > try | > | > select replace(nome_hotel,\n,"") | > from hotel | > group by nome_hotel limit 10; | > | > On Thu, 18 Oct 2001, Adrian D'Costa wrote: | > | > | Date: Thu, 18 Oct 2001 10:04:03 +0530 (IST) | > | From: Adrian D'Costa <[EMAIL PROTECTED]> | > | To: Mysql Mailing List <[EMAIL PROTECTED]> | > | Subject: how to get the correct result -- Thrid Time -- | > | | > | Hi, | > | | > | I have some records that I need to group by a field. This is easy using | > | group by in the sql statement. What I happening is that I get some data | > | in html format that I have written a script that will extract the data I | > | require an dump it into a table. Everything working fine. The problem is | > | that when the data in entered into the table some fields enter with the | > | new line (\n). So when I use the group by command below is the result. | > | | > | mysql> select nome_hotel from hotel group by nome_hotel limit 10; | > | | Abou | > | Nawas Djerba | | > | | Abou Nawas Djerba| | > | | Adams Beach | | > | | Aegean | > | Village | | > | | Aegean | | > | | Aegean Village | | > | | > | This what I don't want since "Abou | > | Nawas Djerba" and "Abou Nawas Djerba" are the | > | same. How do I get rid of the space. I tried trim, rtrim nothing works. | > | | > | Second, using the same data I get some of the hotel names in CAPS and I | > | need to convert it to lower and then group by. How do I get these two | > | done? | > | | > | Thanks | > | | > | Adrian | > | | > | > | - -- "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." - Benjamin Franklin Clyde Jones - http://www.clydec.net -BEGIN PGP SIGNATURE- Version: PGP 6.5.8 iQA/AwUBO9ArzkP80ESqUED/EQJ3FwCgoFMh4HgKtwNlgwNK0IflH5VHkkAAn0HP pO6A3kYZgTTUN/uFgz54J57A =TgdS -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to get the correct result -- Thrid Time --
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Sorry, I forgot to answer your question about similar rows. Rows 5208 and 3063 are NOT the same (row 5208 has spaces and returns), besides your query asked for ALL rows not just the unique ones. Remember, computers only give you what you asked for, not what you want. If you want to get only unique, non-blank results then SELECT DISTINCT replace(replace(nome_hotel,\n,""), " ", " ") from hotel WHERE nome_hotel NOT NULL or trim(nome_hotel) NOT LIKE "" order by nome_hotel limit 10; On Fri, 19 Oct 2001, Adrian D'Costa wrote: | Date: Fri, 19 Oct 2001 10:43:31 +0530 (IST) | From: Adrian D'Costa <[EMAIL PROTECTED]> | To: Clyde Jones <[EMAIL PROTECTED]> | Subject: Re: how to get the correct result -- Thrid Time -- | | Hi, | | Thanks. It gave an error but it work after adding the "\n". Now the | result is: | | +--+-+ | | id | replace(nome_hotel,"\n","") | | +--+-+ | | 3825 | | | | 3827 | | | | 3391 | Abi d'Oru | | | 5208 | Abou Nawas Djerba | | | 3063 | Abou Nawas Djerba | | | 1252 | Adams Beach | | | 9757 | Aegean Village | | | 7973 | Aegean | | | 8917 | Aegean Village | | | 8122 | Aegeon | | +--+-+ | | If you notice id 5208 and 3063 are the same so also with 9757 and 7973. I | tried using trim it is the same. Any pointers. | | Thanks | | Adrian | | On Thu, 18 Oct 2001, Clyde Jones wrote: | | > the doc page is here | > http://www.mysql.com/doc/S/t/String_functions.html | > try | > | > select replace(nome_hotel,\n,"") | > from hotel | > group by nome_hotel limit 10; | > - -- "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." - Benjamin Franklin Clyde Jones - http://www.clydec.net -BEGIN PGP SIGNATURE- Version: PGP 6.5.8 iQA/AwUBO9At+0P80ESqUED/EQICnwCgvVdsWfN0Nr4/HnRoa7/5UKCuYggAoOB1 g4svPHDm8QFnMXAAdE2RDYce =ONgB -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Installing Mysql GUI
Hello, I was able to download the .gz file, but was not able to go any further with the installation. Is there an installation guide available online. Looking forward to using this tool. * Clyde R. Lewis Database Manager Mail Stop 400/DL NASA Langley Research CenterHampton, VA 23681 Office of Education (Phone)757-864-8019 Mail Stop 400/DL (Fax)757-864-9701 ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
