adding then removing index produces different query results
Can anyone explain the following? I encountered the following very strange behaviour while attempting to optimize a query (more details are provided later on for those interested): 1) execute query takes 2 minutes 2) add index 3) execute same query takes 11 seconds 4) drop index 5) execute same query takes 0.2 seconds and uses a different method of returning results from the original query in 1) 6) restart mysql 7) execute query takes 2 minutes Here's the SQL I used to produce the behaviour: mysql> select phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where phantom_labels.full_name like '%lame%' order by title LIMIT 75, 25; [data omitted for clarity] 25 rows in set (1 min 50.23 sec) mysql> explain SELECT phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id WHERE (phantom_labels.full_name like '%lame%') ORDER BY title ASC LIMIT 75, 25; ++-+--++---+-+-++++ | id | select_type | table| type | possible_keys | key | key_len | ref| rows | Extra | ++-+--++---+-+-++++ | 1 | SIMPLE | phantom_products | ALL| label_id_idx | NULL| NULL| NULL | 787738 | Using filesort | | 1 | SIMPLE | phantom_labels | eq_ref | PRIMARY | PRIMARY | 4 | krad_development.phantom_products.label_id | 1 | Using where | ++-+--++---+-+-+++----+ 2 rows in set (0.00 sec) mysql> create index title_idx on phantom_products(title); Query OK, 777262 rows affected (1 min 58.08 sec) Records: 777262 Duplicates: 0 Warnings: 0 select phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where phantom_labels.full_name like '%lame%' order by title LIMIT 75, 25; 25 rows in set (11.03 sec) mysql> explain select phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where phantom_labels.full_name like '%lame%' order by title LIMIT 75, 25; ++-+--++---+---+-+++-+ | id | select_type | table| type | possible_keys | key | key_len | ref| rows | Extra | ++-+--++---+---+-+++-+ | 1 | SIMPLE | phantom_products | index | label_id_idx | title_idx | 258 | NULL | 785367 | | | 1 | SIMPLE | phantom_labels | eq_ref | PRIMARY | PRIMARY | 4 | krad_development.phantom_products.label_id | 1 | Using where | ++-+--++---+---+-+----++-+ mysql> select phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where phantom_labels.full_name like '%lame%' LIMIT 75, 25; 25 rows in set (0.01 sec) mysql> drop index title_idx on phantom_products; Query OK, 777262 rows affected (53.89 sec) Records: 777262 Duplicates: 0 Warnings: 0 mysql> SELECT phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id WHERE (phantom_labels.full_name like '%lame%') ORDER BY title ASC LIMIT 75, 25; 25 rows in set (0.02 sec) mysql> explain SELECT phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id WHERE (phantom_labels.full_name like '%lame%') ORDER BY title ASC LIMIT 75, 25; ++-+--+--+---+--+-++---+--+ | id | select_type | table| type | possible_keys | key | key_len | ref| rows | Extra | ++-+--+--+---+--+-++---+--+ | 1 | SIMPLE | phantom_labels | ALL | PRIMARY | NULL | NULL
Re: adding then removing index produces different query results
Thanks for the response Chris, although I can't seem to reproduce the problem now, but I'm sure you're right. There's something else strange that I've encountered while trying to optimize this query. I've got two machines, dev and production. After adding the index to the title attribute on the dev machine, my query was reduced from 2 minutes to virtually instantaneous (since the query is sorted by title). But when executing the query on the production machine, the query doesn't use the indexed title attribute. Here's what explain says: DEV MACHINE (mysql 5.1.12-beta-log): mysql> show index from phantom_products; +--++--+--+-+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+-+---+-+--++--++-+ | phantom_products | 0 | PRIMARY |1 | id | A | 759168 | NULL | NULL | | BTREE | | | phantom_products | 1 | label_id_idx |1 | label_id | A | 18075 | NULL | NULL | YES | BTREE | | | phantom_products | 1 | title_idx|1 | title | A | 759168 | NULL | NULL | YES | BTREE | | +--++--+--+-+---+-+--++--+----+-+ 3 rows in set (0.07 sec) mysql> explain SELECT phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where phantom_labels.full_name like '%test%' order by title LIMIT 25, 25; ++-+--++---+---+-+++-+ | id | select_type | table| type | possible_keys | key | key_len | ref| rows | Extra | ++-+--++---+---+-+++-+ | 1 | SIMPLE | phantom_products | index | label_id_idx | title_idx | 258 | NULL | 759168 | | | 1 | SIMPLE | phantom_labels | eq_ref | PRIMARY | PRIMARY | 4 | krad_development.phantom_products.label_id | 1 | Using where | ++-+--++---+---+-+++-----+ 2 rows in set (0.00 sec) PRODUCTION MACHINE (mysql 5.1.22-rc-community) mysql> show index from phantom_products; +--++--+--+-+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+-+---+-+--++--++-+ | phantom_products | 0 | PRIMARY |1 | id | A | 781891 | NULL | NULL | | BTREE | | | phantom_products | 1 | label_id_idx |1 | label_id | A | 48868 | NULL | NULL | YES | BTREE | | | phantom_products | 1 | title_idx|1 | title | A | 781891 | NULL | NULL | YES | BTREE | | +--++--+--+-+---+-+--+----+------++-+ 3 rows in set (0.03 sec) mysql> explain SELECT phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where phantom_labels.full_name like '%test%' order by title LIMIT 25, 25; ++-+--++---+-+-+---+++ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--++---+-+-+---+++ | 1 | SIMPLE | phantom_products | ALL| label_id_idx | NULL| NULL| NULL | 715038 | Using filesort | | 1 | SIMPLE | phantom_labels | eq_ref | PRIMARY | PRIMARY | 4
Re: adding then removing index produces different query results
On 1/24/08, Chris <[EMAIL PROTECTED]> wrote:
>
> mysql mysql wrote:
> > Thanks for the response Chris, although I can't seem to reproduce the
> > problem now, but I'm sure you're right.
> >
> > There's something else strange that I've encountered while trying to
> > optimize this query. I've got two machines, dev and production. After
> > adding the index to the title attribute on the dev machine, my query was
> > reduced from 2 minutes to virtually instantaneous (since the query is
> sorted
> > by title). But when executing the query on the production machine, the
> > query doesn't use the indexed title attribute. Here's what explain
> says:
>
>
> > Now, why is the production machine performing a filesort and including
> ALL
> > the entries? It doesn't seem to be using the indexed title attribute at
> > all. Why would two different machines with the same indexes and schema
> > perform two different queries? Could this be happening because I'm
> running
> > two slightly different versions of mysql?
>
> Normally queries with like '%xxx%' can't be indexes so maybe the
> different version number is playing a part here. Because that string
> ('xxx') can be anywhere in the text, there's no way for the optimizer to
> tell without looking at each row. If you're doing a lot of those sort of
> queries maybe look at full text indexes:
> http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
yeah, I've seen that you can't use an index on like with the wildcard at the
front, but I'm trying to use the index when sorting, not on the where
condition..
Do you have the same data or at least the same amount of data in your
> dev machine? If you don't, you can't compare because the optimizer will
> do different things based on the type & amount of data.
>
> eg adding 3 rows to a table is useless and you'll never notice a
> problem. When you add say 30,000 or 300,000 rows - then you'll really
> notice it.
>
yeah, almost the exact same dataset. Both around 800,000 rows.
I guess the only way to narrow this down is to upgrade both machines to a
more recent version of mysql and see if the problem persists.. Thanks for
the response
How to execute a file in mysql prompt
Hi, I have a file which has Creation of tables and views. How can I run the file in mysql prompt. -mysql __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Any one can drop a table or database
Hi, I am new to mysql. I have installed, created a database and also some created tables. Now any one who can login to the host are able to drop the table or also the database. How do I protect it? -mysql - Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
Change storage location
Hi All, I have newly installed mysql 3.23.54 in Red Hat 9 (The one which was bundled with the OS). And default the data storage file location went to /var/lib/mysql, how do I change the location to /mysql, I have also tried modifying my.cnf it didn't help. Any suggestion. -mysql - Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
Post-install
Just installed mssql and in the end of the installation I receive the message taht now is the time to change root password for the db using mysqladmin using the following synthax: /usr/bin/mysqladmin -u root -p password 'new password' So issued the following command: /usr/bin/mysqladmin -u root -p qazwsx 22 (qazwsx is my current root password in OS, 22 is desired password for sql ) Then I see the prompt: Enter password:(which password?) I enter 22 (I tried qazwsx either) I receive the error message: error: Access denied for user 'root@localhost' (Using password: YES)' - 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: Out of control connections
Hi Jeff. If you are the admin, then maybe you and only one or two others should have the 'Super' privilege. >From the 5.0.18 manual: 13.5.4.16. SHOW PROCESSLIST Syntax SHOW [FULL] PROCESSLIST SHOW PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist statement. If you have the SUPER privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). See Section 13.5.5.3, KILL Syntax. If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field. This statement is very useful if you get the too many connections error message and want to find out what is going on. MySQL reserves one extra connection to be used by accounts that have the SUPER privilege, to ensure that administrators should always be able to connect and check the system (assuming that you are not giving this privilege to all your users). The output of SHOW PROCESSLIST may look like this: Also, what about checking your logs for the rogue connections. Would there be any clues there? What about this also from the 5.0.18 manual: 5.9.4. Limiting Account Resources One means of limiting use of MySQL server resources is to set the max_user_connections system variable to a non-zero value. However, this method is strictly global, and does not allow for management of individual accounts. In addition, it limits only the number of simultaneous connections made using a single account, and not what a client can do once connected. Both types of control are interest to many MySQL administrators, particularly those working for Internet Service Providers. In MySQL 5.0, you can limit the following server resources for individual accounts: The number of queries that an account can issue per hour The number of updates that an account can issue per hour The number of times an account can connect to the server per hour Regards Keith In theory, theory and practice are the same; in practice they are not. To unsubscribe from this list, please see detailed instructions already posted at: http://marc.theaimsgroup.com/?l=php-install&m=114138567814319&w=2 On Fri, 14 Apr 2006, Jeff wrote: > To: mysql@lists.mysql.com > From: Jeff <[EMAIL PROTECTED]> > Subject: Out of control connections > > Hello all, > > I've got a problem where a php web application is intermitantly making > bursts of 500+ connections to a mysql database which then locks out all > other connections. These connection don't apparently do anything query > wise they just use up connections. The problem is when it happens I > can't get a processlist to see what user is causing it due to too many > connection and therefore track down the offending web app. I can do a > netstat but that only confirms which web server the out of controll > connections are coming from. > > The connections then just die off on their own in about a minute. Does > anyone know of any way I can log these connections or some other way of > catching it in progress from myslq so I can figure out which website is > causing it and then go correct the code? > > Thanks, > > Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~ How to install 3 instances of mysql~
Hi Mohammed - yes it is possible to install multiple instances of mysqld on one machine. Under SuSE Linux 9.2 I have had 3 instances of mysqld running. I would suggest the following. Use the generic static pre-compiled distibution. Linux (non RPM package) downloads (platform notes) Linux (x86, glibc-2.2, "standard" is static, gcc) Standard5.0.20 30.3M Pick a mirror MD5: 5b0471380db88b03267bbabde500b7e0 | Signature For each server you want to run, install a copy of the above distribution into a seperate base directory. eg. /usr/local/mysql-5.0.20-srv1 /usr/local/mysql-5.0.20-srv2 /usr/local/mysql-5.0.20-srv3 You will need to install a seperate data directory for each server, as it could corrupt the database if multiple mysqld's are writing to the same database. Each server can be started using a shell script that lives in it's own server directory, in this case; /usr/local/mysql-5.0.20-srv1/bin/start-mysql-5.0.20 contents of start-mysql-5.0.20-srv1 would be something like: #! /bin/sh # # start the MySQL database server srv1 instance /usr/local/mysql-5.0.20-srv1/bin/mysqld \ --defaults-file=/usr/local/mysql-5.0.20-srv1/my.cnf \ --port=7000 \ --socket=/usr/local/mysql-5.0.20-srv1/data/mysql.sock \ --pid=/usr/local/mysql-5.0.20-srv1/data/srv1.pid \ --user=mysql \ --datadir=/usr/local/mysql-5.0.20-srv1 & Each server instance can have it's own my.conf file, such as **you will need to change /usr/local/mysql-5.0.18 to match your own installations** # /usr/local/mysql-/my.cnf # MySQL server configuration file # last updated 2006-03-08 # mysql client program configuration file lives in /etc/my.cnf # # mysqld server configuration options # [mysqld] basedir=/usr/local/mysql-5.0.18 ## use for testing multiple instances of mysqld ## these parameters are normally passed to mysqld ## from the start-mysql-5.0.18 script ## ##basedir=/usr/local/mysql-5.0.18 ##port=7005 ##socket=/usr/local/mysql-5.0.18/data/mysql.sock ##pid-file=/usr/local/mysql-5.0.18/data/laptop.pid ##datadir=/usr/local/mysql-5.0.18/data ##user=mysql server-id=1 #skip-networking skip-name-resolve skip-locking set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = net_buffer_length=8K set-variable = myisam_sort_buffer_size=8M # logging options log=5-0-18.log log-bin=laptop-bin log-error=5-0-18.error-log log-queries-not-using-indexes log-slow-admin-statements log-slow-queries=5-0-18.slow-log log-warnings # # end of mysqld server configuration file # /usr/local/mysql-/my.cnf The /etc/my.cnf can be used to set parameters for all the mysql clients. # /etc/my.cnf # MySQL client program configuration file # last updated 2006-03-05 # mysqld server configuration file lives in # /usr/local/mysql-/my.cnf #--- # mysql client program configuration options #--- [mysql] no-auto-rehash # needed for security - to stops multiple deletes/updates # without a where clause safe-updates # # The following options will be passed to all MySQL clients [client] socket = /var/lib/mysql/mysql.sock port = # [myisamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M # [mysqldump] quick set-variable = max_allowed_packet=16M # # available programs/scripts are: #my_print_defaults mysqladmin #myisamchk mysqlbinlog #myisamlog mysqlbug - n/a #myisampack mysqlcheck #mysql mysqld #mysql_convert_table_format - .plmysqld_multi #mysql_find_rows mysqldump #mysql_fix_privilege_tables n/a mysqlhotcopy - .pl #mysql_install_dbmysqlimport #mysql_setpermission - .pl mysqlshow #mysql_zap mysqltest #mysqlaccess - .pl safe_mysqld #---- # end of mysql client program configurations # /etc/my.cnf To monitor the mysql instances and the parameters passed to each one, you could use a process monitoring tool like: http://www.student.nada.kth.se/~f91-men/qps/ To cleanly shut down
Re: ~ How to install 3 instances of mysql~
On Sat, 15 Apr 2006, Mohammed Abdul Azeem wrote: > To: [EMAIL PROTECTED] > From: Mohammed Abdul Azeem <[EMAIL PROTECTED]> > Subject: Re: ~ How to install 3 instances of mysql~ > > Hello Keith, > > Thank you very much for your guidence. > > This is my existing my.cnf file > > > [mysqld] > port= 3306 > socket = /tmp/mysql.sock > skip-locking > key_buffer = 384M > max_allowed_packet = 1M > table_cache = 512 > sort_buffer_size = 2M > read_buffer_size = 2M > read_rnd_buffer_size = 8M > thread_cache = 8 > query_cache_size = 32M > log_bin_trust_routine_creators = 1 > # Try number of CPU's*2 for thread_concurrency > thread_concurrency = 8 > #specify the storage engine > default-storage-engine = InnoDB > #specify the table type > default-table-type = InnoDB > #enable the full query log > log > #Print warnings to error log file > log_warnings > #specify max connections > max_connections = 30 > #specify max user connections > max_user_connections = 12 > # Uncomment the following if you are using InnoDB tables > innodb_data_home_dir = /mysql-system/mysql/data/ > innodb_data_file_path = ibdata1:10M:autoextend > innodb_log_group_home_dir = /mysql-system/mysql/data/ > innodb_log_arch_dir = /mysql-system/mysql/data/ > # You can set .._buffer_pool_size up to 50 - 80 % > # of RAM but beware of setting memory usage too high > innodb_buffer_pool_size = 384M > innodb_additional_mem_pool_size = 20M > # Set .._log_file_size to 25 % of buffer pool size > innodb_log_file_size = 5242880 > innodb_log_buffer_size = 8M > innodb_flush_log_at_trx_commit = 1 > innodb_lock_wait_timeout = 100 > > The variables key_buffer = 384M, innodb_buffer_pool_size = 384M, > innodb_additional_mem_pool_size = 20M, innodb_log_buffer_size = 8M > > are set for 1 instance. > > My RAM memory is 1GB. Can i have the same values for the above variables > set for all the 3 instances ? if yes, will that not exceed the available > RAM memory. Please help me set the correct values for these variables > for each instance. Well, if you are running multiple instances of mysqld concurrently, then I guess you will have to share the system resorces as well, such as memory and file handles. Note that if you are using qps to monitor processes, for each mysqld you have running, the memory usage will increase appropriately. I only use MyISAM tables the moment, so I cannot help you with your InnoDB table settings. Kind Regards Keith Roberts -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Uninstalling Binary installation
It would be helful if you could tell us what OS you are using please? Your email address suggests you could be running some flavour of Linux? If so, can you tell us what package management system your OS uses? I run SuSE Linux 9.2 pro, which uses the Redhat Package Management system - RPM. Does shell:/ # rpm -qv mysql return anything at all on your system? If your system uses RPM, you should get something like this for the installed packages. karsites:/ # rpm -qv mysql mysql-3.23.x.rpm karsites:/ # Keith In theory, theory and practice are the same; in practice they are not. On Sun, 16 Apr 2006, tuxlinsecure wrote: > To: mysql@lists.mysql.com > From: tuxlinsecure <[EMAIL PROTECTED]> > Subject: Uninstalling Binary installation > > hello list, > > I am a newbie. I have to upgrade mysql 3.23 binary > installtion to mysql 5 in my system to start with. > > I figured out that uninstalling binary 3.23 and then > installing a fresh installation would be a good > option. > since upgrading 3.23 to 4 and 4 to 5 can be a daunting > task. > > Any suggestions to this ? > > Are there any scripts for uninstalling binary > installation.? any suggestions? > > Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
My Left Joins are Doubling the SUM()
My Left Joins are Doubling the SUM() SELECT packageItemID, packageItemName,packageItemPrice ,SUM(packageItemTaxAmount) as packageItemTaxAmount ,SUM(packageCreditAmount) as packageCreditAmount FROM packageItem LEFT JOIN packageCredit ON packageItemID=packageCreditItemID LEFT JOIN packageItemTax ON packageItemTaxItemID=packageItemID AND packageItemTaxActive=1 GROUP BY packageItemID ORDER BY packageItemID packageItem Table packageItemID | packageItemName | packageItmePrice 1 | Delta Hotel | 100.00 packageCredit Table packageCreditID | packageCreditItemID | packageItemType | packageCreditAmount 1 | 1 | Deposit | 25.00 2 | 1 | Balance | 92.00 packageItemTax packageItemTaxID | packageItemTaxItemID | packageItemTaxName | packageItemTaxAmount 1 | 1 | GST | 7.00 2 | 1 | HST | 10.00 The desired result of the query should be: 1 | Delta Hotel | 100.00 | 17.00 | 117.00 But it keeps doubling the tax and the credit amounts and results look like this 1 | Delta Hotel | 100.00 | 34.00 | 234.00 is there a way to execute this query without this happening? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Run Apache/PHP/MySQL from CD?
Well I have wondered about this as well. I guess you could do this, but if you want the MySQL databases to be updateable, then as the CD-ROM is read-only, the databases would have to remain somewhere on the hard drive. If you put the databases on the CD-ROM this would make them read-only and not updateable. It might even be possible to squeeze a minimal Linux distro onto the CD-ROM as well, and make it self-bootable and just running the Linux kernel, Apache, php and MySQL. Regards Keith Roberts In theory, theory and practice are the same; in practice they are not. On Wed, 19 Apr 2006, Jay Blanchard wrote: > To: Jay Paulson <[EMAIL PROTECTED]>, php-general@lists.php.net, > mysql@lists.mysql.com From: Jay Blanchard > <[EMAIL PROTECTED]> Subject: RE: Run Apache/PHP/MySQL > from CD? > > [snip] I have no idea if this is possible or not but is > there a way to run Apache, PHP, and MySQL from a CD? I'd > like it to be possible to run it on Windows, Mac OSX and > *nix. If it is possible could someone point me in the > right direction? [/snip] > > You'd have to have CD's for each OS on which you'd like to > run. You can test this by putting the Apache executable > (or one of the other executables) on a CD and trying to > run it. > > http://www.google.com/search?hl=en&q=run+apache+from+CD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Per query DB stats... ideally for InnoDB
Is this of any use at all?
>From the 5.0.18 manual:
13.5.4.7. SHOW ENGINE Syntax
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW ENGINE displays log or status information about
storage engines. The following statements currently are
supported:
snip
SHOW ENGINE INNODB STATUS
(or SHOW INNODB STATUS)
Both return alot of info on the InnoDB storage engine. Not
sure how to relate this to each SQL query though.
may be of interest too:
13.5.4.16. SHOW PROCESSLIST Syntax
SHOW [FULL] PROCESSLIST
SHOW PROCESSLIST shows you which threads are running. You
can also get this information using the mysqladmin
processlist statement. If you have the SUPER privilege, you
can see all threads. Otherwise, you can see only your own
threads (that is, threads associated with the MySQL account
that you are using). See Section 13.5.5.3, KILL Syntax. If
you do not use the FULL keyword, only the first 100
characters of each statement are shown in the Info field.
This statement is very useful if you get the too many
connections error message and want to find out what is going
on. MySQL reserves one extra connection to be used by
accounts that have the SUPER privilege, to ensure that
administrators should always be able to connect and check
the system (assuming that you are not giving this privilege
to all your users).
Regards
Keith
In theory, theory and practice are the same;
in practice they are not.
On Wed, 19 Apr 2006, Samuel Ziegler wrote:
> To: mysql@lists.mysql.com
> From: Samuel Ziegler <[EMAIL PROTECTED]>
> Subject: Per query DB stats... ideally for InnoDB
>
> Is there any way to retrieve per SQL query stats from MySQL?
> Specifically for my need, the ability to determine the amount of system
> resources required to perform the query, ie CPU, disk usage, etc...
>
> I poked through the docs & did some net searching, but couldn't find
> anything that I could use.
>
> I chatted with someone who thought that InnoDB had had some code added
> to it to start down this path, but that it wasn't exposed to the user
> level at all.
>
> An alternative would be a good method of determining the resource cost
> of a query though an examination of the explain data.
>
> Thanks!
> - Sam
>
>
> --
> 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: Per query DB stats... ideally for InnoDB
Have you taken a look at this Samuel?
http://www.mysql.com/products/tools/administrator/index.html
Check out the demo in the TRH corner.
May be of some use to you.
Regards
Keith
In theory, theory and practice are the same;
in practice they are not.
On Wed, 19 Apr 2006, Samuel Ziegler wrote:
> To: [EMAIL PROTECTED]
> From: Samuel Ziegler <[EMAIL PROTECTED]>
> Subject: Re: Per query DB stats... ideally for InnoDB
>
> Those functions do give useful information, but as you point out, the
> trick is associating that information with a specific query. For my
> use, there will most likely be other SQL activity going on at the same
> time which makes doing a 'SHOW ENGINE ...' before and after the query
> not very useful.
>
> Thanks for the pointers, however.
> - Sam
>
> On Wed, 2006-04-19 at 23:30 +0100, [EMAIL PROTECTED] wrote:
> > Is this of any use at all?
> >
> > From the 5.0.18 manual:
> >
> > 13.5.4.7. SHOW ENGINE Syntax
> >
> > SHOW ENGINE engine_name {LOGS | STATUS }
> >
> > SHOW ENGINE displays log or status information about
> > storage engines. The following statements currently are
> > supported:
> >
> > snip
> > SHOW ENGINE INNODB STATUS
> > (or SHOW INNODB STATUS)
> >
> > Both return alot of info on the InnoDB storage engine. Not
> > sure how to relate this to each SQL query though.
> >
> > may be of interest too:
> >
> > 13.5.4.16. SHOW PROCESSLIST Syntax
> >
> > SHOW [FULL] PROCESSLIST
> >
> > SHOW PROCESSLIST shows you which threads are running. You
> > can also get this information using the mysqladmin
> > processlist statement. If you have the SUPER privilege, you
> > can see all threads. Otherwise, you can see only your own
> > threads (that is, threads associated with the MySQL account
> > that you are using). See Section 13.5.5.3, KILL Syntax. If
> > you do not use the FULL keyword, only the first 100
> > characters of each statement are shown in the Info field.
> >
> > This statement is very useful if you get the too many
> > connections error message and want to find out what is going
> > on. MySQL reserves one extra connection to be used by
> > accounts that have the SUPER privilege, to ensure that
> > administrators should always be able to connect and check
> > the system (assuming that you are not giving this privilege
> > to all your users).
> >
> > Regards
> >
> > Keith
> >
> > In theory, theory and practice are the same;
> > in practice they are not.
> >
> > On Wed, 19 Apr 2006, Samuel Ziegler wrote:
> >
> > > To: mysql@lists.mysql.com
> > > From: Samuel Ziegler <[EMAIL PROTECTED]>
> > > Subject: Per query DB stats... ideally for InnoDB
> > >
> > > Is there any way to retrieve per SQL query stats from MySQL?
> > > Specifically for my need, the ability to determine the amount of system
> > > resources required to perform the query, ie CPU, disk usage, etc...
> > >
> > > I poked through the docs & did some net searching, but couldn't find
> > > anything that I could use.
> > >
> > > I chatted with someone who thought that InnoDB had had some code added
> > > to it to start down this path, but that it wasn't exposed to the user
> > > level at all.
> > >
> > > An alternative would be a good method of determining the resource cost
> > > of a query though an examination of the explain data.
> > >
> > > Thanks!
> > > - Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: describe table : improvement
Using MySQL 5.0.18 mysql> use test1; Database changed mysql> show tables; +-+ | Tables_in_test1 | +-+ | t1 | +-+ 1 row in set (0.00 sec) mysql> show create table t1 \G * 1. row * Table: t1 Create Table: CREATE TABLE `t1` ( `ID` int(11) NOT NULL auto_increment, `set1` varchar(30) default NULL, `col2` char(20) default NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='test1.t1 test table' 1 row in set (0.00 sec) mysql> show full columns from t1 \G * 1. row * Field: ID Type: int(11) Collation: NULL Null: NO Key: PRI Default: NULL Extra: auto_increment Privileges: select,insert,update,references Comment: * 2. row * Field: set1 Type: varchar(30) Collation: latin1_swedish_ci Null: YES Key: Default: NULL Extra: Privileges: select,insert,update,references Comment: * 3. row * Field: col2 Type: char(20) Collation: latin1_swedish_ci Null: YES Key: Default: NULL Extra: Privileges: select,insert,update,references Comment: 3 rows in set (0.00 sec) For some reason the mysql> show full columns from t1 \G command is not returning the Comment field for me. Is there a variable that needs setting to enable this? Regards Keith In theory, theory and practice are the same; in practice they are not. On Thu, 20 Apr 2006, Gilles MISSONNIER wrote: > To: Gabriel PREDA <[EMAIL PROTECTED]>, [EMAIL PROTECTED] > From: Gilles MISSONNIER <[EMAIL PROTECTED]> > Subject: Re: describe table : improvement > > hello, > thank you for your answer, but > > this DO NOT work for me. I use MySQL 4.0.24 [ Linux Debian sarge stable ]. > > the "FULL" argument displays ONLY the Privileges, NOT the Comment, neither > Collation. > > > mysql> CREATE TABLE a_table (a_column CHAR(30) COMMENT > mysql> 'commentaire'); > Query OK, 0 rows affected (0.03 sec) > > mysql> SHOW FULL COLUMNS FROM a_table; > +--+--+--+-+-+---+-+ > | Field| Type | Null | Key | Default | Extra | Privileges > | | > +--+--+--+-+-+---+-+ > | a_column | char(30) | YES | | NULL| | > | select,insert,update,references | > +--+--+--+-+-+---+-+ > 1 row in set (0.03 sec) > > > It seems this is a feature available for a more recent release. > > Is this right ? > > > > > On Wed, 19 Apr 2006, Gabriel PREDA wrote: > > > It is: > > > > SHOW FULL COLUMNS FROM a_table > > > > You will get 2 extra columns: > > > > - Privileges (showing the privileges of the user for that column) > > - Comment (showing a per column comment) > > > > When creating a table you can add a comment using COMMENT keyword: > > > > CREATE TABLE a_table > > ( > > a_column CHAR(30) CHARSET utf8 COMMENT 'Some comment' > > ) ; > > > > Is this... what you needed ? > > > > -- > > Gabriel PREDA > > Senior Web Developer > > > > =_==_==_==_==_==_= > =¯==¯==¯==¯==¯==¯= > Gilles Missonnier > IAP - [EMAIL PROTECTED] > 01 44 32 81 36 > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SPAM] Re: describe table : improvement
Thankyou Gabriel. So how does one set a column comment then? Regards Keith In theory, theory and practice are the same; in practice they are not. On Thu, 20 Apr 2006, Gabriel PREDA wrote: > To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> > From: Gabriel PREDA <[EMAIL PROTECTED]> > Subject: [SPAM] Re: describe table : improvement > > COLUMN COMMENTs are not the same as TABLE COMMENT... > > For TABLE COMMENT you should use: > > SHOW TABLE STATUS LIKE 'table_name' > > *Gilles *(the starter of the thread) wanted COLUMN COMMENTs. > > -- > Gabriel PREDA > Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Older version MySQL
You might like to take a look at smart package manager. It's a wizz at dealing with dependancy conflicts. http://labix.org/smart http://wiki.suselinuxsupport.de/wikka.php?wakka=HowtoSmartPackageManager http://forums.suselinuxsupport.de/index.php?showtopic=21121 HTH Keith In theory, theory and practice are the same; in practice they are not. On Fri, 21 Apr 2006, Nicolas Verhaeghe wrote: > To: 'Mike Blezien' <[EMAIL PROTECTED]>, > 'MySQL List' > From: Nicolas Verhaeghe <[EMAIL PROTECTED]> > Subject: RE: Older version MySQL > > Dependency issue, I would use RPM Package Manager. > > It always solved that type of issue. > > http://www.rpm.org/ > > > -Original Message- > From: Mike Blezien [mailto:[EMAIL PROTECTED] > Sent: Friday, April 21, 2006 3:17 PM > To: MySQL List > Subject: Older version MySQL > > > Hello, > > I am attempting to upgrade one of our older servers running RH 7.3 > w/glibc-2.2.5-44 > > it's currently using 3.23.58 and tried installing 4.0.26 rpm's for linux, > but > won't install due to the following error: > > libc.so.6(GLIBC_2.3) is needed by MySQL-shared-4.0.26-0 > libpthread.so.0(GLIBC_2.3.2) is needed by MySQL-shared-4.0.26-0 > libc.so.6(GLIBC_2.3) is needed by MySQL-shared-compat-4.0.26-0 > libpthread.so.0(GLIBC_2.3.2) is needed by > MySQL-shared-compat-4.0.26-0 > > Now of the RPM's will install. Is there a 4.0.+ version that will install on > > this type of server without have to upgrade alot of other > stuff ?? Or can we > > build it from source, and if so, what is the recommend configure options > used to > build from source ? > > TIA, > Mike(mickalo)Blezien > ======= > Thunder Rain Internet Publishing > Providing Internet Solution that Work === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: describe table : improvement
Thankyou Michael. I'm trying to work my way through the 5.0.18/19 manual, but I seem to have missed that. Regards Keith In theory, theory and practice are the same; in practice they are not. On Sat, 22 Apr 2006, Michael Stassen wrote: > To: [EMAIL PROTECTED] > From: Michael Stassen <[EMAIL PROTECTED]> > Subject: Re: describe table : improvement > > [EMAIL PROTECTED] wrote: > > Thankyou Gabriel. > > > > So how does one set a column comment then? > > > > Regards > > > > Keith > > As part of the column definition, as documented in the manual > <http://dev.mysql.com/doc/refman/5.0/en/create-table.html>. > > column_definition: > col_name type [NOT NULL | NULL] [DEFAULT default_value] > [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] > [COMMENT 'string'] [reference_definition] > > Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Find Most Recent Autoincrement Index Assigned???
>From 'Section 13.5.3 SET Syntax' of the 5.0.18 ref manual: IDENTITY = value The variable is a synonym for the LAST_INSERT_ID variable. It exists for compatibility with other database systems. You can read its value with SELECT @@IDENTITY, and set it using SET IDENTITY. INSERT_ID = value Set the value to be used by the following INSERT or ALTER TABLE statement when inserting an AUTO_INCREMENT value. This is mainly used with the binary log. LAST_INSERT_ID = value Set the value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. Setting this variable does not update the value returned by the mysql_insert_id() C API function. Eg. mysql> select @@last_insert_id; +--+ | @@last_insert_id | +--+ |0 | +--+ 1 row in set (0.04 sec) mysql> select @@insert_id; +-+ | @@insert_id | +-+ | 0 | +-+ 1 row in set (0.00 sec) mysql> select @@identity; ++ | @@identity | ++ | 0 | ++ 1 row in set (0.00 sec) mysql> set @@last_insert_id = 5; Query OK, 0 rows affected (0.00 sec) mysql> select @@last_insert_id; +--+ | @@last_insert_id | +--+ |5 | +--+ 1 row in set (0.00 sec) mysql> select @@insert_id; +-+ | @@insert_id | +-+ | 5 | +-+ 1 row in set (0.00 sec) mysql> select @@identity; ++ | @@identity | ++ | 5 | ++ 1 row in set (0.00 sec) So it appears you can use either of the three variables above to achieve the same effect. Regards Keith On Sat, 22 Apr 2006, Michael Stassen wrote: > To: David T. Ashley <[EMAIL PROTECTED]> > From: Michael Stassen <[EMAIL PROTECTED]> > Subject: Re: How to Find Most Recent Autoincrement Index Assigned??? > > David T. Ashley wrote: > > I'm using PHP, and I sometimes INSERT new records in a table. MySQL > > assigns > > a new autoincrement int field on each INSERT ... nothing surprising > > there. > > It goes 1, 2, 3, etc. > > > > What query can I use to find out what value this int autoincrement > > assigned > > field was? I could of course SELECT based on what was just inserted, > > but > > that seems inefficient. > > > > Thanks for any help, > > Dave. > > LAST_INSERT_ID() > > <http://dev.mysql.com/doc/refman/4.1/en/information-functions.html> > > Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update not working in a script, but going fine running with MySQL Browser
Hi Luis. It sounds like your script may not be initialising those two variables correctly. Are the variables being set properly in your script before sending the insert query to mysql? MySQL will quite happily insert an empty string value, '' into a char field without generating an error message. Try echoing the content of the two variables that are not being set in mysql to screen, just before you send the query to mysql. Does that shed any light on the problem? Keith In theory, theory and practice are the same; in practice they are not. On Thu, 27 Apr 2006, luis perez wrote: > To: mysql@lists.mysql.com > From: luis perez <[EMAIL PROTECTED]> > Subject: Update not working in a script, > but going fine running with MySQL Browser > > Hi guys. > Got a problem when adding records to an existing table, as part of a sql > script. > There are a couple of fields that don't get added as they should > (respaccstopframe and acstpradiusid). > This is the update command: > > insert into temp_s1 (accsesid, respaccstopframe, acstpradiusid, sgsnip, > sgsnip_subnet, sgsnip_last, ggsnip, subip, startime, stoptime) > select r1.radius_acct_session_id, t.frame_number, r1.radius_id, > r1.radius_XXX_Address, substring_index(r1.radius_XXX_Address,".",3), > substring_index(r1.radius_XXX_Address,".",-1),r1.radius_NAS_IP_Address, > r1.radius_Framed_IP_Address, f.frame_time, r1.frame_time > from radius r1, temp_respaccstopframe t, frame f > where r1.frame_number = t.r1_fn and f.frame_number= t.syntstart_number and > t.noacstart=1 and t.started_in >=0; > > I didn't received any error, but fields were not added. I added an update > command for those two fields just right after in the script. > > # no preguntes por que lo hago dos veces...los duendes del sw... > update temp_s1 s1, temp_respaccstopframe t > set s1.respaccstopframe= t.frame_number, s1.acstpradiusid=t.radius_id > where s1.accsesid=t.radius_acct_session_id and > s1.ggsnip=t.radius_NAS_IP_Address and t.noacstart=1 and t.started_in >=0; > > Same result. > Then I run the update from MyQSL Browser and then worked fine...!!? > > I've run same script and update command thru Browser with same table in > both MySQLv4.1 and v5.0. No change. > > I know it sounds crazy, but can anyone add some light on this? > > TIA. > Luis. > > _____ > ¿Estás pensando en cambiar de coche? Todas los modelos de serie y extras > en MSN Motor. http://motor.msn.es/researchcentre/ > > > -- > 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: Have You Seen My CV?
No I have not seen your CV! But I have seen these answers to stopping spam dead in it's tracks. http://www.tmda.net http://spam-stop.com Regards Keith Roberts PS - apologies to anyone on the list that has been getting messages to confirm your emails to [EMAIL PROTECTED] - that was me checking out TMD's challenge/Response on my laptop. I need to set up a filter to allow all messages from the lists I'm on to be accepted without a challenge/response message being sent out. Kind Regards Keith Roberts In theory, theory and practice are the same; in practice they are not. On Tue, 2 May 2006, Rob Munsch wrote: > To: forum mysql > From: Rob Munsch <[EMAIL PROTECTED]> > Subject: Re: Have You Seen My CV? > > Anago Chima wrote: > > > Tired of spam? Yahoo! Mail has the best spam protection around > > > > > Oh the irony. > > -- > Rob Munsch > Solutions For Progress IT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql on Production Servers
Well I would not bother with the source distro as it can take a while to compile, and possibly end up being slower than a pre-compiled version. Personally I would choose between the .tar.gz or the RPM versions. The RPM version is basically self-installing, and also installs the scripts to start the server at boot-up time. You could download and install the RPM binary to get you started, then download the .tar.gz binary to do a custom installation later on if you wanted to run different versions at the same time, eg for testing upgrades before making using them on the live databases. The statically-linked .tar.gz binaries would be the most flexible option as they do not eally on any external libraries at run-time. AFAIK you cannot run two different RPM binary versions at the same time, because you have no control over where the RPM packages are installed. Normally a RPM upgrade will overwrite the previous RPM version - which is OK if you are only doing minor upgrades, and are confident that there will be no problems with the upgrade process. The .tar.gz binaries give you the option to choose where to install the server files. This means you can have different versions of the .tar.gz distro's on one machine, and run each mysql server on the same machine for testing your upgrades, by using a different port, socket pid-file and database (even a snapshot of your live data for testing purposes that you can throw away later). Each mysql version could have it's own unique my.cnf file with just a [mysqld] section and nothing else for that particular version, in it's installation directory. Eg. # /usr/local/mysql-/my.cnf # MySQL server configuration file # last updated 2006-03-08 # mysql client program configuration file lives in /etc/my.cnf #--- # mysqld server configuration options #--- [mysqld] basedir=/usr/local/mysql-5.0.18 ## use for testing multiple instances of mysqld ## these parameters are normally passed to mysqld ## from the start-mysql-5.0.18 script ## ##basedir=/usr/local/mysql-5.0.18 ##port=7005 ##socket=/usr/local/mysql-5.0.18/data/mysql.sock ##pid-file=/usr/local/mysql-5.0.18/data/laptop.pid ##datadir=/usr/local/mysql-5.0.18/data ##user=mysql server-id=1 #skip-networking skip-name-resolve skip-locking set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = net_buffer_length=8K set-variable = myisam_sort_buffer_size=8M # logging options log=5-0-18.log log-bin=laptop-bin log-error=5-0-18.error-log log-queries-not-using-indexes log-slow-admin-statements log-slow-queries=5-0-18.slow-log log-warnings #--- # end of mysqld server configuration file # /usr/local/mysql-/my.cnf As /etc/my.cnf is the first place all mysql client programs seem to look by default (as well as the server, which is why I do not put any configuration section for the server in /etc/my.cnf), your mysql clients could all share one common my.cnf, Eg. # /etc/my.cnf # MySQL client program configuration file # last updated 2006-03-05 # mysqld server configuration file lives in # /usr/local/mysql-/my.cnf #------- # mysql client program configuration options #------- [mysql] no-auto-rehash # needed for security - to stops multiple deletes/updates # without a where clause safe-updates #--- # The following options will be passed to all MySQL clients [client] socket = /var/lib/mysql/mysql.sock port = #--- [myisamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M #--- [mysqldump] quick set-variable = max_allowed_packet=16M #--- # available programs/scripts are: #my_print_defaults mysqladmin #myisamchk mysqlbinlog #myisamlog mysqlbug - n/a #myisampack mysqlcheck #mysql mysqld #mysql_convert_table_format - .plmysqld_multi #mysql_find_rows mysqldump #mysql_fix_privilege_tables n/a mysqlhotcopy - .pl #mysql_install_dbmysqlimport #mysql_setpermission - .pl mysqlshow #mysql_zap mysqltest #mysqlaccess - .pl
Re: Mysql on Production Servers
>On Thu, 4 May 2006, Shivaji S wrote: > > To: [EMAIL PROTECTED], mysql@lists.mysql.com > From: Shivaji S <[EMAIL PROTECTED]> > Subject: Re: Mysql on Production Servers > > > Keith Roberts thanks for the input ,i have few doubts on your reply. > > 1.when i am installaing rpm based files, should my operating > system contains the necessary files that are build on rpm > packagei.e compilers,gccversions etc. You might like to try this distro first: Linux x86 generic RPM (statically linked against glibc 2.2.5) downloads Server 5.0.21-013.9M Pick a mirror MD5: b22c4bb165716062647f3ffb478cf04e Max 5.0.21-02.9MPick a mirror MD5: bb4c3e5764ba6c5f8439dfbc849c5ca3 Benchmark/test suites 5.0.21-06.1MPick a mirror MD5: eed868b8cc18cd45f18f33db4e738955 Client programs 5.0.21-07.0MPick a mirror MD5: 0190ce11547014465747707ea4062abf Libraries and header files 5.0.21-03.8M Pick a mirror MD5: 8c3ea8cf16346f5292cdfe6296ebc956 Shared client libraries 5.0.21-02.0MPick a mirror MD5: 28d626cda409290e8527d1d79af91e0c Shared compatibility libraries (3.23, 4.x, 5.x libs in same package) 5.0.21-0 3.7MPick a mirror MD5: 2f8aac33b2c659ef1b9bdc634b6f419e > 2.if i want to create more than one instance on my server > with same mysql versions then how rpm package will help for > me. Please see: http://dev.mysql.com/doc/refman/5.0/en/ 5.4.3. mysqld_multi - Manage Multiple MySQL Servers 5.5. mysqlmanager - The MySQL Instance Manager 5.5.1. Starting the MySQL Server with MySQL Instance Manager 5.5.2. Connecting to the MySQL Instance Manager and Creating User Accounts 5.5.3. MySQL Instance Manager Command Options 5.5.4. MySQL Instance Manager Configuration Files 5.5.5. Commands Recognized by the MySQL Instance Manager > 3.in case of binary installation is it recommended for > production database compared to source files. The binaries are all built from the same source code for each version, so there should be no difference, apart from you not having to go to the trouble of compiling things yourself. Regards Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determine version of *.frm, *.MYD and *.MYI
This is right off the top of my head so I might be wrong! Does myisamchk not return the mysql version that the table was created with, if you use a very verbose option to check the *.MYI files? You could make a copy of your tables and experiment with myisamchk on the copies, see if that throws any light on things. Regards Keith Roberts In theory, theory and practice are the same; in practice they are not. On Fri, 5 May 2006, sheeri kritzer wrote: > To: Nils Lastein <[EMAIL PROTECTED]> > From: sheeri kritzer <[EMAIL PROTECTED]> > Subject: Re: Determine version of *.frm, *.MYD and *.MYI > > No backups? > > And you compiled it yourself with no records of how you compiled it or > what version you used? what if you want to compile a new version with > the same flags, how would you remember how to do that? > > If those don't help, trial and error is the only way. And you know > it's not 4.1.19, that was JUST released. And you just eliminated one > other one. Only 17 left to go. I wish I could be more helpful, but > when you don't backup or document, that's what happens. :( > > -Sheeri > > On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: > > > > > > I know it a 4.1... But as I compiled it my self it is not so easy to > > figure > > it out And it might take a while to trial-n-error all 4.1.x > > > > Nils > > > > > > -Oprindelig meddelelse- > > Fra: sheeri kritzer [mailto:[EMAIL PROTECTED] > > Sendt: fr 05-05-2006 20:28 > > Til: Nils Lastein > > Cc: mysql@lists.mysql.com > > Emne: Re: Determine version of *.frm, *.MYD and *.MYI > > > > > > You don't have ANY idea what branch it was created with? 3.2x, 4.0, > > 4.1, 5.0, 5.1 ? I'd recommend finding another similar server in > > your > > dept and see what it's running (assuming there's no standards doc, > > or > > sysadmin to ask, etc). > > > > Do you remember around when the time was that you last > > installed/upgraded mysql on the box? If so you might be able to > > figure out which release it was. > > > > You can try to use something in the same branch -- if it was > > 4.1.something, try the latest in the 4.1 branch. > > > > Where were your backups stored? If you used mysqldump it logged the > > server version at the top of the output file. > > > > -Sheeri > > > > On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: > > > After a disk crash I managed to save the *.frm, *.MYD and > > > *.MYI-files > > > from the disk. When putting these files into another mysql > > > server I get: > > > > > > mysql> select * from validate; > > > ERROR 1033 (HY000): Table './mydb/validate' was created with a > > > different > > > version of MySQL and cannot be read > > > > > > Unfortunately I'm unable to access the disk anymore, so I > > > cannot see > > > what version of the server generated these. > > > > > > How do I do that? > > > > > > Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: customer id - made the worst possible way
Well whatever you do to fix the problem, I would practice on a copy of the database first, just to make sure that any alterations to the tables are exactly what you want to happen. When you are 100% sure you know how to solve the problem, then you know it is safe to make the changes to the live database itself. Do you have the show create table xyz /G output for the tables you want to alter please? Regards Keith Roberts In theory, theory and practice are the same; in practice they are not. On Tue, 9 May 2006 [EMAIL PROTECTED] wrote: > To: mysql@lists.mysql.com > From: [EMAIL PROTECTED] > Subject: customer id - made the worst possible way > > hi to all, > I have to redo a web site of one company and the structure of the current > db is a little mess. > one of them is customer id number. right now, customer table use as > primary key cust_id column varchar(50) PRIMARY KEY (no auto increment). I > really have no idea why previous developer made cust_id with letter C on > the beggining of a number, and the number is made from date, (mdyHis) ?!?! > > What do you suggest to do: > 1. take off letter C and keep the numbers, change cust_id to integer NOT > NULL, add one customer with number 2000 and then apply auto_increment? > 2. replace current Cxx with INT numbers and replace the cust_id in > every other table where cust_id is foreign key? > 3. something else? > > Thanks for any help! > > -afan > > > -- > 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]
Random Table Lock
I am stumped by a table lock issue. First, some details. I am running MySQL 5.0.22 on an AMD 64 machine. mysqld -V outputs: /usr/local/libexec/mysqld Ver 5.0.22-debug-log for unknown-linux-gnu on x86_64 (Source distribution) uname -a: 2.6.8-11-amd64-generic #1 Wed Jun 1 00:42:47 CEST 2005 x86_64 GNU/Linux I compiled MySQL as follows ( from the docs ): CC=gcc CFLAGS="-O2" CXX=gcc CXXFLAGS="-O2 -felide-constructors \ -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql \ --with-debug --with-extra-charsets=complex The problem I am having is the following: Randomly, on any DB or table within, a lock will remain in place after an insert. The insert could be something very simple, but it will fail to unlock the table. Following this failure, any other query that needs to lock the table afterwards ( updates/inserts ) will fail and will queue up - visible when doing a show full processlist. Killing the PID of that particular INSERT doesn't do anything. In fact, the only way to kill it and unlock the table is to do a kill -9 on the mysqld process. This is what it looks like from within mysql: | 448 | root | localhost | shopDani | Query | 233 | update | insert into items set custID='X',item='y',qty='1' | I am not sure from where to start tackling this issue and my first step was to recompile MySQL with debug support. I then managed to catch it happening in the debug log but I'm afraid it's not offering more details: do_command: info: Command on socket (47) = 3 (Query) dispatch_command: query: insert into items set custID='XX',item='',qty='1' thr_lock: info: write_wait.data: 0x0 thr_lock: info: dellink: -1 r_locks: 0 w_locks: 1 mi_get_status: info: key_file: 311296 data_file: 217356 concurrent_insert: 1 unique_table: info: real table: store.items mysql_lock_have_duplicate: info: no duplicate found ha_myisam::start_bulk_insert: info: start_bulk_insert: rows 1 size 0 do_command: info: Command on socket (72) = 1 (Quit) There is more data but I thought only this was relevant to this particular insert. If anyone has any suggestions or clues, feel free to write back. Thank you, George -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changed?
Hi, I recently updated to version 4.1.2 from a slightly older one like 4.1.11 or something. But I am now having a problem. Before,this query would work fine: Select distinctrow * from mytable order by mydatecolumn. mydatecolumn is a column of type date. This used to return the records in order of date, as it should. After the update however, it now returns them in random order. If i use a int type column to order by, then it works fine, it seems ony the date columns will no longer work. If i take distinctrow out of the query then it should work, but i have way to much to change to do that, and i still need to be able to select distinct rows when making a join. Why does it no longer work? Thanks. No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.10.9/416 - Release Date: 8/10/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mass E-mail Options
-Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: 30 August 2006 11:55 PM To: MySQL List Subject: Mass E-mail Options -- SNIP http://www.mailenable.com If SMTP is all you really want and win32 is your base, then it is freeware and a rock solid option. Actually you will get much much more in the freeware version, e.g. unlimited POP boxes, unlimited domains and an unlimited listserver, Filebased / MySQL / MSSQL settings storage, etc etc. You can pretty much tune your SMTP exactly as to how many threads to use and how to time retries, etc Just be careful, it works so well, I quickly found myself upgraded to the Enterprise version. Regards, Jacques -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DB admin with limited access
Salutations! I am relatively new to the MySQL flavor of database server and now have run into a situation on my hosting that I don't seem to be able to resolve. I installed a new query using TEMPORARY tables via the PHP 4 interface on a production database and suddenly began receiving PHP timeouts. From this point on, access to the database has been severely restricted. I have run a few diagnostics and ascertained the following: - there are a growing number of processes - queries - displayed by mysql_list_processes as accessing my database - phpMyAdmin displays a process that can't be killed. It appears to be the process to execute the SQL using TEMPORARY tables. - only one table appears to be blocked. SELECT COUNT(*) on all other tables works fine. This is a central table, however it was not involved in the TEMPORARY table statement. When I attempt SELECT COUNT(*) on the problem table, the statement blocks for a period (PHP timeout?) and then dies with no apparent error status or message. - The support people at my hosting claim that I still have 1.4 GB space (my complete hosting allocation) for TEMPORARY tables. I have tried to get my hosting - hostm.com - to assist, but besides suggesting RTFM and the repair function, they have not been particularly helpful. I'm sure all it needs is for the server to be restarted, but I do understand that it is not always possible to do such in a shared environment. So 1) How does a user with no shell access kill this process? 2) Is a "repair" really the right thing to do in this situation? 3) With the constraint of PHP 4.3.11 and MySQL 4.0.25-standard, how do I use TEMPORARY tables and not get myself into this predicament? Thanks much for any tips. James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Postcode Search
It might be more productive to add a foreign key to the Offices and Properties tables that points to the Primary key ID of the respective PostCode in the Postcodes table. Combined with the other suggestions, this would give you a key from the Offices and Properties tables directly into the Postcodes table, and the associated coordinates you want to match on. HTH Keith In theory, theory and practice are the same; In practice they are not. On Mon, 23 Jan 2006, Shaun wrote: > To: mysql@lists.mysql.com > From: Shaun <[EMAIL PROTECTED]> > Subject: Postcode Search > > Hi, > > We have a dataset of uk postcodes and their relevant > X-Coordinates and Y-Coordinates, a table of properties > (houses), a table of users and a table of offices - users > work in an office - table structures below. > > Is it possible to run a search of all properties in the > properties table that come within a certain distance of > the users postcode, currently we do this by downloading > all properties into an array and stripping out the ones > that don't come within the radius with php. > > Any advice would be greatly appreciated. > > > # -- MySQL dump -- > # > # Table structure for table 'Offices' > # > CREATE TABLE Offices ( > Office_ID int(11) DEFAULT '' NOT NULL auto_increment, > Subscriber_ID int(11), > Type varchar(10), > Address_Line_1 varchar(50), > Address_Line_2 varchar(50), > City varchar(50), > County varchar(50), > Postcode varchar(10), > Telephone varchar(12), > Fax varchar(12), > Radius tinyint(4), > PRIMARY KEY (Office_ID) > ); > > # > # Table structure for table 'Postcodes' > # > CREATE TABLE Postcodes ( > PCDSECT varchar(6) DEFAULT '' NOT NULL , > SORTSECT varchar(6), > PCDDIST varchar(4), > SORTDIST varchar(4), > PCDAREA char(2), > X_COORD double(7,1) unsigned , > Y_COORD double(7,1) unsigned , > PRIMARY KEY (PCDSECT) > ); > > # > # Table structure for table 'Properties' > # > CREATE TABLE Properties ( > CHK varchar(20), > Property_ID int(11) DEFAULT '' NOT NULL auto_increment, > Insertion_Date date, > Status varchar(20), > Property_Name_Or_Number varchar(50), > Address_Line_1 varchar(50), > Address_Line_2 varchar(50), > City varchar(50), > County varchar(50), > Postcode varchar(12), > PRIMARY KEY (Property_ID) > ); > > # > # Table structure for table 'Users' > # > CREATE TABLE Users ( > User_ID int(11) DEFAULT '' NOT NULL auto_increment, > Office_ID int(11), > Type varchar(20), > Title varchar(4), > Firstname varchar(20), > Lastname varchar(20), > Password varchar(20) DEFAULT '' NOT NULL , > Email varchar(50), > PRIMARY KEY (User_ID) > ); > > # --- Dump ends --- > > > > -- > 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: count(*) send a wrong value
>From the MySQL 4.1 manual 12.10.1. GROUP BY (Aggregate) Functions COUNT(expr) Returns a count of the number of non-NULL values in the rows retrieved by a SELECT statement. COUNT() returns 0 if there were no matching rows. mysql> SELECT student.student_name,COUNT(*) ->FROM student,course ->WHERE student.student_id=course.student_id ->GROUP BY student_name; COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values. COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example: mysql> SELECT COUNT(*) FROM student; This optimization applies only to MyISAM and ISAM tables only, because an exact record count is stored for these table types and can be accessed very quickly. For transactional storage engines (InnoDB, BDB), storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count. COUNT(DISTINCT expr,[expr...]) Returns a count of the number of different non-NULL values. COUNT(DISTINCT) returns 0 if there were no matching rows. mysql> SELECT COUNT(DISTINCT results) FROM student; In MySQL, you can get the number of distinct expression combinations that do not contain NULL by giving a list of expressions. In standard SQL, you would have to do a concatenation of all expressions inside COUNT(DISTINCT ...). COUNT(DISTINCT ...) was added in MySQL 3.23.2. Keith In theory, theory and practice are the same; In practice they are not. On Mon, 23 Jan 2006, fabsk wrote: > To: mysql@lists.mysql.com > From: fabsk <[EMAIL PROTECTED]> > Subject: count(*) send a wrong value > > Hi, > > I'm facing a strange problem. I am using a database at my Internet > provider (Free, France). The type of table is MyISAM (no choice), MySQL > 4.1.15. I can do my tests with my PHP code or phpMyAdmin. > > The definition of my table is: > - uid, int > - cid, int > - response, text > - points, int (can be null) > > keys: > - uid, cid > - cid, response(4) > - cid > > When I do "select * from my_table where cid=123", I get my 10 records. > But when I do "select count(*) from my_table where cid=123" I get "2". I > also happens with many other values of "cid" and the bad result is > always "2". > > I can't understand what's happen. It seems to simple, but there should > be something. Do you have an idea? > > Thank you for your attention > Fabien > > > -- > 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: Support between MySQL and PHP
Hi Philip. I'm wondering if you have mysql server version 3.23.49 still running on your machine? If so, version 5 would not start up, as it would not be allowed access to port 3306. Try doing my_print_defaults mysqld and my_print_defaults client mysql from a root shell and see what values you get returned. HTH Keith In theory, theory and practice are the same; In practice they are not. On Sun, 29 Jan 2006, Philip R. Thompson wrote: > To: mysql@lists.mysql.com, php-general@lists.php.net > From: Philip R. Thompson <[EMAIL PROTECTED]> > Subject: Support between MySQL and PHP > > Hi all. > > I figured this question was suitable for both the MySQL list and the > PHP-General list. Here's what I'm running into. > > I just installed MySQL5 and currently have PHP 4.3.11 installed. I > am wanting to connect to the mysql database on localhost, but I > get the following results: > > -- > > "Client does not support authentication protocol requested by > server; consider upgrading MySQL client" > -- > > Well, I have the lastest stable version of MySQL, so I did some more > research on what the problem might be. When I checked my > information for PHP using phpinfo(), it gave me the "Client API > version" for MySQL was 3.23.49. So, I'm thinking my version of PHP > cannot connect to my version of MySQL. I then considered if I > installed the MySQLi extension for PHP (supports versions of MySQL > > 4.1), would that help me? Or, if I just upgraded PHP to version 5, > would that help me? > > Does anyone have any suggestions on the direction I should go? > > Thanks in advance, > ~Philip k -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: display a hierarchic tree
Hi Jochen. An alternative approach could be to pull all the values out of the database using select * from ..., and then build the tree-structure in your application logic. Insert the relevant values returned from mysql in the appropriate places of the tree-structure in the app code. Keith In theory, theory and practice are the same; In practice they are not. On Sun, 29 Jan 2006, Jochen Kaechelin wrote: > To: mysql@lists.mysql.com > From: Jochen Kaechelin <[EMAIL PROTECTED]> > Subject: display a hierarchic tree > > I have the following table: > > mysql> select * from link_categories; > ++---+-+---+---+-+ > | id | level | category_id | category | parent_id | deleted | > ++---+-+---+---+-+ > | 1 | 1 |1000 | Software | 0 | 0 | > | 2 | 1 |2000 | Harware | 0 | 0 | > | 3 | 2 |1001 | Virenscanner | 1000 | 0 | > | 4 | 2 |1003 | Packprogramme | 1000 | 0 | > | 5 | 3 |1004 | Linux | 1001 | 0 | > | 6 | 3 |1005 | Windows | 1001 | 0 | > | 7 | 4 |1006 | Windows XP| 1005 | 0 | > | 8 | 2 |1007 | Sniffer | 1000 | 0 | > | 9 | 4 |1008 | Debian Woody | 1004 | 0 | > | 10 | 1 | 10 | Vermischtes | 0 | 0 | > ++---+-+---+---+-+ > 10 rows in set (0.24 sec) > > and I want to display a tree like: > > Software > Virenscanner >Linux >Debian Woody > Windows >Windowsd XP > Packprogramm >Sniffer > Hardware > Vermischtes > > > Can someone give me hint how to build a query? > > I run MySQL 4.1.x and 5.0.x and I use PHP. > > Thanx. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to login to MYSQL as "root" without knowing the passward
Well under Linux I re-installed apache which overwrote the directory and deleted the pid of a currently running apache! My workaround was to do: $ netstat -l -t -p as root user from the command line. This gave me the running daemon processes that were listening to tcp ports, and their associated pid numbers. I was then able to stop the running apache with: $ kill 12345, where 12345 was the process number of the apache. There may be a similar command under windows that will enable you to get the PID of the mysql you want to kill. HTH Keith In theory, theory and practice are the same; In practice they are not. On Mon, 30 Jan 2006, Dan Trainor wrote: > To: sol beach <[EMAIL PROTECTED]>, mysql@lists.mysql.com > From: Dan Trainor <[EMAIL PROTECTED]> > Subject: Re: How to login to MYSQL as "root" without knowing the passward > > sol beach wrote: > > Sounds reasonable, but how do I shutdown down MYSQL so I can restart > > with > > --skip-grant-tables > > option? > > > > On 1/30/06, *Dan Trainor* <[EMAIL PROTECTED] > > <mailto:[EMAIL PROTECTED]>> wrote: > > > > sol beach wrote: > > > I've been asked to assist the folks who own the data in the > > database, but > > > folks who used to maintain it are no longer with the > > > company. > > > This installation resides on a Windows 2003 server to which > > > I > > have local > > > admin rights. > > > What is the most painless way to get "root" access to this > > > database? > > > I am more than willing to RTFM, if anyone will point me at > > > which > > FM to read. > > > > > > TIA > > > > > > > Hi - > > > > I've always used the startup option of '--skip-grant-tables'. > > This may > > or may not be the "correct" way to do so, but we'll see what kind > > of > > feedback we get. > > > > I do this, then update the Password field in the mysql.user > > table. > > > > HTH > > -dant > > > > > > Hi - > > Stop the service? Kill the process? That's up to you. > > hth > -dant > > -- > 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: Dictionary
Well I have just done a google.co.uk search for english dictionary downloadable and got the following results: Web Results 1 - 10 of about 1,290,000 for english dictionary downloadable. They may not be in the correct format to import directly. But I'm sure it is possible to write a script to parse the downloaded dictionary, and build the query to insert the contents into a database yourself, or to convert the dictionary into another suitable format that would be compatible for loading directly into a database table. HTH Keith In theory, theory and practice are the same; In practice they are not. On Wed, 1 Feb 2006 [EMAIL PROTECTED] wrote: > To: Peter of Pedsters Planet <[EMAIL PROTECTED]> > From: [EMAIL PROTECTED] > Subject: Re: Dictionary > > Peter of Pedsters Planet <[EMAIL PROTECTED]> wrote on 02/01/2006 > 01:27:45 PM: > > > I'd like to know too if posible :) > > > > On 01/02/06, Scott Hamm <[EMAIL PROTECTED]> wrote: > > > I've been trying to google to no avail for English dictionary (with > > > definitions) in any format that I can download and import into MySQL. > > > Do anyone know where I can find it? > > > > There is a lot of effort that goes into creating and > maintaining all of the definitions for hundreds of > thousands of words. I seriously doubt that any dictionary > publisher is just going to give it all away for free, > regardless of the language. > > 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]
Re: Kinda OT: Book database question
I think you will find a book ISBN is a reference to a particular publisher's version of a particular book. So in answer to your question, if several different publishers, or even the same publisher have published several different books all entitled 'Huckleberry Finn' then to avoid ambiguity in identifying one particular book from that group of books, each book should have a unique identifier, which AFAIK is what the ISBN is all about. The usual way to reference a book is by: Title: Author: Publisher: ISBN: Date Published: Edition: Price: plus any other attributes, such as hardback or paperback. HTH Keith In theory, theory and practice are the same; In practice they are not. On Wed, 1 Feb 2006, John Meyer wrote: > To: mysql@lists.mysql.com > From: John Meyer <[EMAIL PROTECTED]> > Subject: Kinda OT: Book database question > > Hi, I'm trying to develop my own book database, and I have a question > about ISBN: Is that number linked to a book or to a title? That is, can > one title (say, Huckleberry Finn) have several ISBNs associated with it > through several book releases? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: data entry GUI
I have just caught the end of this topic, so hope I'm not repeating something already mentioned. What I do is enter my data into a plain text file, like this; The questions are a bit dumb, just for testing purposes of course! /* file: general-quizdata.sql */ /* data to populate general knowledge quiz tables */ use web_app_tester; insert into question set question_text = 'What is the Capital of England?'; select @questionID := last_insert_id(); insert into answer set answer_text = 'London', status = 'right', questionID = @questionID; insert into answer set answer_text = 'Paris', questionID = @questionID; insert into answer set answer_text = 'Edinburgh', questionID = @questionID; insert into question set question_text = 'How many yards are there in a mile?'; select @questionID := last_insert_id(); insert into answer set answer_text = '5000', questionID = @questionID; insert into answer set answer_text = '1760', status = 'right', questionID = @questionID; insert into answer set answer_text = '2500', questionID = @questionID; insert into question set question_text = 'What are the 3 primary colors?'; select @questionID := last_insert_id(); insert into answer set answer_text = 'Red, Grey, Black', questionID = @questionID; insert into answer set answer_text = 'Yellow, White, Blue', questionID = @questionID; insert into answer set answer_text = 'Green, Blue, Red', status = 'right', questionID = @questionID; insert into question set question_text = 'RAM is an acronym for?'; select @questionID := last_insert_id(); insert into answer set answer_text = 'Random Access Memory', status = 'right', questionID = @questionID; insert into answer set answer_text = 'Read Access Memory', questionID = @questionID; insert into answer set answer_text = 'Read And Memorise', questionID = @questionID; /* data truncated here for brevity */ /* end of data */ and then load it into mysql from the mysql command prompt with: mysql> \. general-quizdata.sql This may seem like the long-winded version of LOAD DATA, but it does make the syntax easier to understand, plus you can put any other mysql commands in the file. Also you have the data and commands available in a file, in case you have to reload the table from scratch again. HTH Keith In theory, theory and practice are the same; In practice they are not. On Wed, 1 Feb 2006, Ryan Stille wrote: > To: mysql@lists.mysql.com > From: Ryan Stille <[EMAIL PROTECTED]> > Subject: RE: data entry GUI > > You can also install MyODBC and then hook an Excel spreadsheet into your > database. Editing the spreadsheet will update data in your database. > This isn't a good solution if you are going to be creating new tables > often. But for manipulating data in a known set of tables it's great. > > -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Connection Problem
do # my_print_defaults mysqld --port=3306 --socket=/var/lib/mysql/mysql.sock --skip-locking --key_buffer=16M --max_allowed_packet=1M --table_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --myisam_sort_buffer_size=8M --server-id=1 To give you the defaults for your mysqld server. It may be listening on the wrong socket for connections. If your mysqld is listening on the same port as DW MX is trying to connect to, then maybe mysql is not running after all. Keith In theory, theory and practice are the same; In practice they are not. On Thu, 2 Feb 2006, Rhodes, Casey wrote: > To: mysql@lists.mysql.com > From: "Rhodes, Casey" <[EMAIL PROTECTED]> > Subject: MySQL Connection Problem > > When testing my connection via Dreamweave MX, I get the > following error message: > > > > 2002 Can't connect to local MySQL server through socket > '/var/mysql/mysql.sock' (2) > > > > It was suggested to me that the server may not be running, > though when I go to my System Preferences Panel (MacOSX), > I have a green light and it says that it is currently > running. > > --Casey Rhodes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird MySQL Connection Issues
If you are running MySQL on Windows, then I'm wondering whether you are having a problem with running out of available ports, for clients to connect to MySQL on. This may be your problem: >From the manual, 2.3.16. MySQL on Windows Compared to MySQL on Unix *** MySQL for Windows has proven itself to be very stable. The Windows version of MySQL has the same features as the corresponding Unix version, with the following exceptions: Limited number of ports Windows systems have about 4,000 ports available for client connections, and after a connection on a port closes, it takes two to four minutes before the port can be reused. In situations where clients connect to and disconnect from the server at a high rate, it is possible for all available ports to be used up before closed ports become available again. If this happens, the MySQL server appears to be unresponsive even though it is running. Note that ports may be used by other applications running on the machine as well, in which case the number of ports available to MySQL is lower. For more information, see http://support.microsoft.com/default.aspx?scid=kb;en-us;196271. *** Keith In theory, theory and practice are the same; In practice they are not. On Sun, 12 Feb 2006, Aaron Axelsen wrote: > To: mysql@lists.mysql.com > From: Aaron Axelsen <[EMAIL PROTECTED]> > Subject: Weird MySQL Connection Issues > > I have been experiencing some weird MySQL connection issues lately. > Twice now in that last couple weeks, there have been times where some > mysql applications are working, and others are not working. Both times > the mysql connection limit was rather high. A simple mysql restart has > fixed the problem both times. > > There are a few forums using phpbb running on this server which get > heavy access, which is most likely the cause of the problem. > > The version of mysql running is: 4.1.14 > > Is there a known issue like this with alot of mysql connections? > > Does anyone have any related ideas or suggestions? Thanks! > > -- > Aaron Axelsen > [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installation Issue
If you have not installed MySQL before, you must create the MySQL grant tables: shell> scripts/mysql_install_db --user=mysql Keith In theory, theory and practice are the same; In practice they are not. On Tue, 14 Feb 2006, Ravi Kumar wrote: > To: Imran Chaudhry <[EMAIL PROTECTED]> > From: Ravi Kumar <[EMAIL PROTECTED]> > Subject: Re: Installation Issue > > Imran, > I noticed couple of permissions were not correct.I changed mysql.mysql. > Still Ihave been getting following errors. > 060214 15:53:05 mysqld started > 060214 15:53:05 InnoDB: Started; log sequence number 0 43655 > 060214 15:53:06 [ERROR] Fatal error: Can't open and lock privilege tables: > Table 'mysql.host' doesn't exist > 060214 15:53:06 mysqld ended > > Please advice. > thanks > > Imran Chaudhry <[EMAIL PROTECTED]> wrote: > On 2/14/06, Ravi Kumar wrote: > > Starting mysql with root.I tried withn mysql user account also but still > > same error. > > thanks > > Ravi, > Assuming you are starting MySQL with mysqld_safe, then it will invoke > the MySQL server as the mysql user. > > I suspect the cause is that /var/lib/mysql is not owned by mysql > > If so, as superuser: chown -R mysql:mysql /var/lib/mysql > > Regards, > Imran Chaudhry > -- > http://www.ImranChaudhry.info > MySQL Database Management & Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql oddity
You will not have access to the extra functionality in MySQL 5.0 if you are connecting to it via a php4 client API, as the php4 API does not know about the extra functionality in MSQL 5.0. You could try downloading and compiling Apache 2.0.55, or 2.2.0, MySQL standard linux statically linked binary .tar.gz, and php 5.1.2. You will need to compile apache first. Then install MySQL in /usr/local/mysql, or make a symlink from /usr/local/mysql, to the place you unpacked the MySQL binary. Then compile php with something like: #! /bin/sh # # Created by configure './configure' \ '--with-apxs2=/usr/local/apache-2.0.55/bin/apxs' \ '--prefix=/usr/local/php-5.1.2' \ '--with-mysql=shared,/downloads/mysql/bin/mysql-standard-5.0.18-linux-i686' \ '--with-mysql-sock=/var/lib/mysql/mysql.sock' \ '--with-zlib-dir=/usr/include' \ '--with-mysqli=shared,/downloads/mysql/bin/mysql-standard-5.0.18-linux-i686/bin/mysql_config' \ "$@" It is possible to compile php5 with both the old mysql extension, and the newer mysqli extension, provided they are both compiled to use the same MySQL client libraries. HTH Keith In theory, theory and practice are the same; In practice they are not. On Tue, 14 Feb 2006, Dan Stromberg wrote: > To: mysql@lists.mysql.com > From: Dan Stromberg <[EMAIL PROTECTED]> > Subject: mysql oddity > > > Hi folks. > > I've been compiling my own apps for over a decade, but it seems like > it's time to get -with- the times, and start using precompiled binaries > more. > > So I got a set of matching set of precomiled apache2/mysql5/php5 off of > ibiblio for a Solaris 9/Sparc box. > > However, even though I have apache2 running with php5, and mysql5 is > running (mysql4 is not), phpinfo is telling me that it was built against > mysql4... snip > So it kind of seems like maybe I have php5 using mysql5 via the mysql4 > client API. Does this sound about right? > > And if so, what would I lose by leaving it this way? That is, are there > useful things in later versions of the mysql client API? -Is- there a > version 5.* of the mysql client API? > > Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: APACHE 2.0 can't connect to MYSQL 5 when using PHP 5.1.2
First you need to make sure that you have a running mysql server for the client mysql program to connect to. If the server is not runing, you will obviously not be able to connect to it. I find the following utility very helpfull - been messsing around with it today. http://www.student.nada.kth.se/~f91-men/qps/ It give a graphical UI, into the running processes. I noticed using qps, that sometimes the mysqld_safe script loads into memory, but does not actually load a mysqld process. Also, version 4.0.21 only spawns one child process, where 5.0.18 starts several processes in memory. I had the RPM 5.0.18 version running, and two other seperate binary distro's of 5.0.18, all on different sockets and ports. I've given up with using mysqld_safe to run the mysqld daemon. It is to unpredictable. qps also shows all the command line arguments passed to mysqld, which shows at a glance which ports and sockets the daemons are listening on. It is included with SuSE 9.2 pro, as an extra package. I think it lives on the DVD's. You can download qps from the link above. Keith In theory, theory and practice are the same; In practice they are not. On Thu, 16 Feb 2006, persant mpote wrote: > To: mysql@lists.mysql.com > From: persant mpote <[EMAIL PROTECTED]> > Subject: APACHE 2.0 can't connect to MYSQL 5 when using PHP 5.1.2 > > hi, > since 3 days, i'm trying to connect to MYSQL 5.0.18 from > php scripts using Apache et Macromedia 2004 Dreamweaver. > Could someone help me doing this? Best regard. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error 1016 : cant open ibd file even though it exists
Sounds like you have any empty database, from the messages below. Try adding a new dummy database, and some test data. See if you can do some selects on that test data. Keith In theory, theory and practice are the same; In practice they are not. On Fri, 24 Feb 2006, Rithish Saralaya wrote: > To: MySQL general mailing list > From: Rithish Saralaya <[EMAIL PROTECTED]> > Subject: RE: error 1016 : cant open ibd file even though it exists > > Hello. > > The tables were working perfectly fine a week back. The database was created > from a sql file generated through the mysqldump utility. So there was > nothing wrong with the database. This irregularity happened this week > onwards. > > Our system admins tell us that the server was restarted last weekend. When I > dug up the mysql error logs, this was what I found saw. > > == > 060219 5:20:25 InnoDB: Starting shutdown... > 060219 5:20:30 InnoDB: Shutdown completed; log sequence number 19 > 1867461149 > 060219 5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete > > > Memory status: > Non-mmapped space allocated from system: 16589028 > Number of free chunks: 10 > Number of fastbin blocks: 0 > Number of mmapped regions: 19 > Space in mmapped regions: 1472028672 > Maximum total allocated space: 0 > Space available in freed fastbin blocks: 0 > Total allocated space: 16479548 > Total free space: 109480 > Top-most, releasable space:102224 > Estimated memory (with thread stack):1488744676 > > 060219 05:20:30 mysqld ended > > 060219 16:57:48 mysqld started > 060219 16:57:49 [Warning] Asked for 196608 thread stack, but got 126976 > InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not > exist: > InnoDB: a new database to be created! > 060219 16:57:49 InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10 > MB > InnoDB: Database physically writes the file full: wait... > 060219 16:57:50 InnoDB: Log file /var/lib/mysql/ib_logfile0 did not > exist: > new to be created > InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 256 MB > InnoDB: Database physically writes the file full: wait... > InnoDB: Progress in MB: 100 200 > 060219 16:58:02 InnoDB: Log file /var/lib/mysql/ib_logfile1 did not > exist: > new to be created > InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 256 MB > InnoDB: Database physically writes the file full: wait... > InnoDB: Progress in MB: 100 200 > 060219 16:58:14 InnoDB: Log file /var/lib/mysql/ib_logfile2 did not > exist: > new to be created > InnoDB: Setting log file /var/lib/mysql/ib_logfile2 size to 256 MB > InnoDB: Database physically writes the file full: wait... > InnoDB: Progress in MB: 100 200 > InnoDB: Doublewrite buffer not found: creating new > InnoDB: Doublewrite buffer created > InnoDB: Creating foreign key constraint system tables > InnoDB: Foreign key constraint system tables created > 060219 16:58:28 InnoDB: Started; log sequence number 0 0 > /usr/sbin/mysqld: ready for connections. > Version: '4.1.11-standard-log' socket: '/var/lib/mysql/mysql.sock' > port: > 3306 MySQL Community Edition - Standard (GPL) > = > > So... It shows that the ibdata1 file was recreated... But how can that be > possible? when it was a regular server shutdown and startup? > > Regards, > Rithish. > > > -Original Message- > From: Heikki Tuuri [mailto:[EMAIL PROTECTED] > Sent: Thursday, February 23, 2006 7:52 PM > To: mysql@lists.mysql.com > Subject: Re: error 1016 : cant open ibd file even though it exists > > > Rithish, > > the table definition does not exist in the ibdata file. You have the > .frm file and the .ibd file, but that does not help if the table > definition is not stored in the ibdata file. > > How did you end up in this situation? Did you move .frm and .ibd files > around? Did you recreate the ibdata1 file? > > Best regards, > > Heikki > Oracle Corp./Innobase Oy > InnoDB - transactions, row level locking, and foreign keys for MySQL > InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up > MyISAM tables > http://www.innodb.com/order.php > > . > List: mysql > Subject:error 1016 : cant open ibd file even though it exists > From: "Rithish Saralaya" > Date: 2006-02-22 11:27:44 > Message-ID: ANEAKJJGBMNHIAEBLIAIMEPCECAA.rithish.saralaya () > tallysolutions ! com > [Download message RAW] > > > Hello. > > I get the following error when I
RE: error 1016 : cant open ibd file even though it exists
I do not use Innodb tables at the moment, so all this is pure speculation. Is/was the server connected to a UPS when the power failure happened? If so, did the UPS function properly and do you have any UPS logs to match against the mysql error log? Things to check for would be the time the UPS took over from the mains supply? If there was no UPS in operation, were your InnoDB tables corrupted when the power failure occured. Is it possible for the InnoDB tables to have become so corrupted that mysql had no alternative but to re-construct the tables? What are your config settings for mysql at boot up time? Do you have any directives to force checking of InnoDB tables at boot-time, and if so what are they please? As soon as you mentioned 'power failure' the words 'table corruption' sprang to my mind. Also, are there any mysql transaction logs (*.log) you can check, to see what was happening when the power outage occured? Keith In theory, theory and practice are the same; In practice they are not. On Sat, 25 Feb 2006, Rithish Saralaya wrote: > To: [EMAIL PROTECTED] > From: Rithish Saralaya <[EMAIL PROTECTED]> > Subject: RE: error 1016 : cant open ibd file even though it exists > > Hello David. > > There was supposed to be a power outage in our office that day. So the > server was shut down. Finally when the power was back, the machine was > plugged on. That's all. No file system change. Nothing. > > Regards, > Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error 1016 : cant open ibd file even though it exists
Still a mystery about your missing InnoDB database files Rithish. I seem to remember there is an option that will allow you to store InnoDB files in their own seperate directory safely, and not in the /var/lib/mysql default directory. One of the many things I like about MyISAM tables is that each database is kept in its own subdirectory, whereas the default for InnoDB databases seems to be mixed in with the mysql log files. This gives me a uneasy feeling, especially as I delete the log files when backing up mysql databases. The moral of this post must certainly be something like: "As the data stored in a company's databases is vital to the operation of the company, when it comes to the integrity and safety of your company's databases, you cannot be too paranoid. Make regular checks and backups of your databases, implementing a staged backup policy, so that whatever happens, you as the DBA, are fully covered for all possible circumstances." Which is obviously, thank God, what you have done Rithish. Kind Regards Keith In theory, theory and practice are the same; In practice they are not. On Tue, 28 Feb 2006, Rithish Saralaya wrote: > To: mysql@lists.mysql.com > From: Rithish Saralaya <[EMAIL PROTECTED]> > Subject: RE: error 1016 : cant open ibd file even though it exists > > Hello Keith. > > The power outage was known before-hand, and the server was > shutdown before the outage happened. The server was > brought up once the power returned. So no UPs intervention > happened here. > > Regards, > Rithish. > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Saturday, February 25, 2006 8:13 PM > To: mysql@lists.mysql.com > Subject: RE: error 1016 : cant open ibd file even though it exists > > > > I do not use Innodb tables at the moment, so all this is > pure speculation. > > Is/was the server connected to a UPS when the power failure > happened? > > If so, did the UPS function properly and do you have any UPS > logs to match against the mysql error log? > > Things to check for would be the time the UPS took over from > the mains supply? > > If there was no UPS in operation, were your InnoDB tables > corrupted when the power failure occured. > > Is it possible for the InnoDB tables to have become so > corrupted that mysql had no alternative but to re-construct > the tables? > > What are your config settings for mysql at boot up time? > Do you have any directives to force checking of InnoDB > tables at boot-time, and if so what are they please? > > As soon as you mentioned 'power failure' the words 'table > corruption' sprang to my mind. > > Also, are there any mysql transaction logs (*.log) you can > check, to see what was happening when the power outage > occured? > > Keith > > In theory, theory and practice are the same; > In practice they are not. > > On Sat, 25 Feb 2006, Rithish Saralaya wrote: > > > To: [EMAIL PROTECTED] > > From: Rithish Saralaya <[EMAIL PROTECTED]> > > Subject: RE: error 1016 : cant open ibd file even though it exists > > > > Hello David. > > > > There was supposed to be a power outage in our office that day. So the > > server was shut down. Finally when the power was back, the machine was > > plugged on. That's all. No file system change. Nothing. > > > > Regards, > > Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I observe mysqld?
The web applications below will allow you to connect to and monitor and administer MySQL databases. http://www.phpmyadmin.net/home_page/index.php Check out the DEMO link in the top menu bar, RHS. Or there is the MySQL Administrator at: http://www.mysql.com/products/tools/administrator/index.html Check out the Tutorial Movie at the top of the RH column. Regards Keith Roberts In theory, theory and practice are the same; In practice they are not. On Tue, 7 Mar 2006, Karl Schock wrote: > To: mysql@lists.mysql.com > From: Karl Schock <[EMAIL PROTECTED]> > Subject: How can I observe mysqld? > > Hello, > > my problem: I run a phpbb-Forum with a mysql database on a linux > server. > Every 5 - 7 days the mysql database runs in a vicious circle: > > "mysqladmin --user=x --password=y ping" says that the "connect failed". > The socket /var/lib/mysql/mysql.sock is there. > "ps aux | grep mysqld" shows me some mysqld-processes. But one of them > is marked as "". > > To find the reason causing the problem I started mysqld with the > "--log"-option and the "--log-error"-option as recommended by others > to me at forums.mysql.com. > Now I have a log-file, but as far as I can judge it there > are no error-messages in the log-file but only innocent queries. > The mysqld.err-log-file is allways empty (0 Bytes). > > What else can I do to observe myslqd? > > If this mailing list is not the right place to ask such > questions please let me know. > > Bye > Karl > > > -- > 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: How can I observe mysqld?
Check this GUI out for monitoring running processes. http://www.student.nada.kth.se/~f91-men/qps/ It will show you the connection parameters passed to the mysqld daemon. I've had a terrible time trying to work out whether mysqld was actually running or not.The error messages are not too helpfull when trying to connect to the server. I also found it very usefull to know if mysqld was actually running. Sometimes, I would start mysql via mysqld_safe. But I noticed using qps that mysqld_safe was running, but it failed to start the mysqld! I don't bother using mysqld_safe anymore - apache doesn't need a wrapper script like mysqld_safe. If Apache stops running, then it doesn't matter if mysqld is still running or not - you won't connect to mysqld via a web application. Regards keith On Tue, 7 Mar 2006, Karl Schock wrote: > To: mysql@lists.mysql.com > From: Karl Schock <[EMAIL PROTECTED]> > Subject: Re: How can I observe mysqld? > > Hello Keith, > > > The web applications below will allow you to connect to and > > monitor and administer MySQL databases. > > > > http://www.phpmyadmin.net/home_page/index.php > > it helps a lot when MySQL is running. Thank you. > But when "mysqladmin --user=x --password=y ping" says that > the "connect failed" even phpmyadmin doesn't work anymore. > > Do you know programs like tcpdump to monitor a network interface > or top to watch the processes on a linux host? Is there a similar > program to observe mysqld? I would like to use it. Or can I do > that with phpmyadmin but I don't know it? > > Bye > Karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table with multiple primary keys - How
It is not possible to have more than one PRIMARY key per table. Maybe you need to use one PRIMARY key as the main index into the table, then use UNIQUE or KEY which is a synonym for INDEX on the other two columns. This book will help you ALOT with designing tables. It will also teach you how to normalise (refactor) your tables into a more efficient form. http://www.apress.com/book/bookDisplay.html?bID=338 It is also more efficient IMHO to index on integer values if you can, rather than character text. Regards Keith > create table members ( > logon_idMEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, > email_addr varchar(30), ??? > last_name varchar(30), ??? > member_type char(1), > email_verified char(1), > logon_pwvarchar(15), > date_added date, > last_login timestamp, > first_name varchar(30), > addr1 varchar(30), > addr2 varchar(30), > cityvarchar(20), > state varchar(20), > zip varchar(15), > phone_home varchar(15), > phone_officevarchar(15), > phone_cell varchar(15), > mothers_maiden_name varchar(30), > ip_of_useratsignup varchar(16), > primary key(login_id, email_addr, last_name) > ); primary key login_id (login_id), key email_addr (email_addr), key last_name (last_name) ); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a Basic Query.
13.1.5.1. Silent Column Specification Changes CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement] ^^^ Regards Keith In theory, theory and practice are the same; In practice they are not. On Wed, 8 Mar 2006, clint lenard wrote: > To: mysql@lists.mysql.com > From: clint lenard <[EMAIL PROTECTED]> > Subject: Need help with a Basic Query. > > Hey Guys, > > I was wondering if I could get some assistance with building a > Simple Import Script using PHP and MySQL. Basically I'm trying to pull > info out of one Table and Insert it into the other Table. > > Can anyone show me a simple example of this? I can figure out how to > do the rest if I had a simple example. > > Thanks! > > - Clint > > -- > 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: php and mysql
Mary, you need to know 100% that mysql is actually running and what port or socket it is listening on for connections, before you even try and connect to it. If you call mysqld directly with a script something like: #! /bin/sh # start-mysql-5.0.18 # # start the MySQL database server /usr/local/mysql-5.0.18/bin/mysqld \ --defaults-file=/usr/local/mysql-5.0.18/my.cnf \ --port=7000 \ --socket=/var/lib/mysql/mysql.sock \ --pid=/var/lib/mysql/laptop.pid \ --user=mysql \ --datadir=/var/lib/mysql & Then use a processes monitoring tool such as: http://www.student.nada.kth.se/~f91-men/qps/ This will then show you if mysqld is running OK. It will also show you the parameters in the above script that you passed to mysqld to get it started. You then need to try and connect to mysqld with the mysql monitor program as already mentioned. If you just set the client options in /etc/my.cnf config file, these will be applied globally to all mysql client programs. EG. # /etc/my.cnf # MySQL client program configuration file # last updated 2006-03-05 # mysqld server configuration file lives in # /usr/local/mysql-/my.cnf #--- # mysql client program configuration options #--- [mysql] auto-rehash # needed for security - to stops multiple deletes/updates # without a where clause safe-updates #--- # The following options will be passed to all MySQL clients [client] socket = /var/lib/mysql/mysql.sock port = 7000 #snip #--- # end of mysql client program configurations # /etc/my.cnf More options for your mysqld server can be set in the --defaults-file=/usr/local/mysql-5.0.18/my.cnf These options are additional to the options you pass in the above script. EG #--- # mysqld server configuration options #--- [mysqld] basedir=/usr/local/mysql-5.0.18 ## use for testing multiple instances of mysqld ## these parameters are normally passed to mysqld ## from the start-mysql-5.0.18 script ## ##basedir=/usr/local/mysql-5.0.18 ##port=7005 ##socket=/usr/local/mysql-5.0.18/data/mysql.sock ##pid-file=/usr/local/mysql-5.0.18/data/laptop.pid ##datadir=/usr/local/mysql-5.0.18/data ##user=mysql server-id=1 #skip-networking skip-name-resolve skip-locking set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = net_buffer_length=8K set-variable = myisam_sort_buffer_size=8M # logging options log=5-0-18.log log-bin=laptop-bin log-error=5-0-18.error-log log-queries-not-using-indexes log-slow-admin-statements log-slow-queries=5-0-18.slow-log log-warnings #--- # end of mysqld server configuration file # /usr/local/mysql-/my.cnf Next you will need to check php is using the same socket to connect to mysqld server. Use the phpinfo() function in a php script, to verify this. Obviously, you will need the mysql or mysqli extension loaded for this. HTH Keith Roberts In theory, theory and practice are the same; In practice they are not. On Fri, 10 Mar 2006, Logan, David (SST - Adelaide) wrote: > To: Mary Adel <[EMAIL PROTECTED]> > From: "Logan, David (SST - Adelaide)" <[EMAIL PROTECTED]> > Subject: RE: php and mysql > > > If you still have issues after that, then read > http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html > > Regards > > > --- > ** _/ ** David Logan > *** _/ *** ITO Delivery Specialist - Database > *_/* Hewlett-Packard Australia Ltd > _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] > _/ _/ _/ _/ Desk: +618 8408 4273 > _/ _/ _/_/_/ Mobile: 0417 268 665 > *_/ ** > ** _/ Postal: 148 Frome Street, > _/ ** Adelaide SA 5001 > Australia > invent > --- > > -Original Message- > From: JC [mailto:[EMAIL PROTECTED] > Sent: Friday, 10 March 2006 6:42 AM > To: Mary Adel > Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com > Subject: RE: php and mysql > > On Thu, 9 Mar 2006, Mary Adel wrote: > > > Thanks for al your help and i di that and now i have another error > >
Re: mysql workbench and download?
It's not on the mysql site yet Greg. >From [EMAIL PROTECTED] Sat Mar 4 23:00:04 2006 To: [EMAIL PROTECTED] From: Alfredo Kengi Kojima <[EMAIL PROTECTED]> Subject: ANNC: MySQL Workbench 1.0.5 beta released MySQL Workbench 1.0.5 beta has been released. MySQL Workbench is a database design tool for MySQL. MySQL objects such as tables, routines and views can be created and edited and their visual representation on the canvas enables one to quickly understand and work efficiently with complex and simple database schemas. Feature highlights: - Reverse engineering of existing MySQL databases - Import DBDesigner4 models - Synchronize edited model with MySQL database - Generate SQL create script file - Printing (Windows) - Powrefull scripting and plugin interface. Plugins can be written in several languages, such as Lua, PHP, Java and Python. - Fast, OpenGL based graphical canvas - Quickly accessible "Overview" mode, which zooms out the whole canvas to reveal a more general view. An OpenGL capable video card with at least 32MB of memory is needed to use MySQL Workbench. Windows binaries are available at: ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.5-beta-win32.msi ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.5-beta-win32.msi.md5 ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.5-beta-win32-noinstall.zip ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.5-beta-win32-noinstall.zip.md5 Linux/Gtk+ binaries are available at: mysql-workbench-1.0.5beta-1.suse93.i586.rpm mysql-workbench-1.0.5beta-1.suse93.i586.rpm.md5 mysql-workbench-1.0.5beta-1.suse93.src.rpm mysql-workbench-1.0.5beta-1.suse93.src.rpm.md5 mysql-workbench-1.0.5beta-linux-i386.tar.gz mysql-workbench-1.0.5beta-linux-i386.tar.gz.md5 The SuSE RPM or the tar.gz should work on most recent distributions. Binaries for more distributions and architectures will follow. Mac OS X binaries will be available soon briefly. Sources: mysql-workbench-1.0.5beta.tar.gz mysql-workbench-1.0.5beta.tar.gz.md5 Changes since the last alpha version: All platforms - Significant speed improvements in the Generic Canvas - Auto arrange elements - Markers - Switchable grid display - Complete database <-> synchronization - Reverse engineering is complete - New feature: plug-ins, which are scripts to be used for certain tasks in WB- DBDesigner4 import Windows - Significantly faster model load - Export model as image (png) - Printing with preview - Copy Region as Image - Full GRT integration (including tabbed shell), supported for scripting, are now: Lua, Python - Model properties page - Table options editing - BUG when deleting objects is still present Linux - Added several commands for layouting (send back/front etc) - Object properties tab Enjoy! -- Alfredo Kojima, GUI Developer MySQL AB, www.mysql.com Buenos Aires, Argentina Are you MySQL certified? www.mysql.com/certification On Fri, 10 Mar 2006, Gregory Machin wrote: > To: "mysql@lists.mysql.com" > From: Gregory Machin <[EMAIL PROTECTED]> > Subject: mysql workbench and download? > > Hi where can I download mysql workbench ? The links on the mysql forum dont work ... and i cant seem to find another download ... probably not see the wood for the treats .. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking for good update
looks a bit strange to me.
> $result = mysql_query($query) or die('Query couldn\'t
> executed:'.mysql_error());
please try something like this:
// build the query - (that's OK)
$query = "UPDATE members SET email_verified='X' WHERE
logon_id='" .$logonid. "'";
// send the query to the server - save the result resource
$res = mysql_query($query);
// test for the result of the above query
if(!$res)
{
// stop the script if the result is not valid
die('Query couldn\'t be executed:'.mysql_error());
}
// process a valid result
$row = mysql_fetch_array($res)
if (mysql_num_rows($res) == 1)
{
// the user id and password match,
print("User id on db");
}
else
{
//$errorMessage = 'Sorry, wrong user id / password';
print("Sorry, wrong user id / password");
}
I've not tested this - but it looks like you are mixing sending the
mysql query and testing for the result of the query at the same time,
which AFAIK is not possible.
Maybe you need to get a simple introductory book on php, such as O'reillys
php pocket reference, ISBN 0596-00402-8.
Regards
Keith
In theory, theory and practice are the same;
In practice they are not.
On Sun, 12 Mar 2006, fbsd_user wrote:
> To: Mysql
> From: fbsd_user <[EMAIL PROTECTED]>
> Subject: Checking for good update
>
> Using this code I get this error message.
>
> Warning: mysql_num_rows(): supplied argument is not a valid MySQL
> result resource in /usr/local/www/data/mls_verifyemail.php on line
Probably because you are not sending a valid query to the server,
you will not be getting a valid result resource back from the server.
> What code should I use to check if the update worked or not?
>
>
> $query = "UPDATE members SET email_verified='X' WHERE
> logon_id='".$logonid."'";
>
> $result = mysql_query($query) or die('Query couldn\'t
> executed:'.mysql_error());
>
> if (mysql_num_rows($result) == 1)
> {
>// the user id and password match,
>print("User id on db");
> }
> else
> {
>//$errorMessage = 'Sorry, wrong user id / password';
>print("Sorry, wrong user id / password");
> }
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking for good update
Maybe I need to read the copy of php pocket reference I have
to David - LOL.
Keith
On Sun, 12 Mar 2006 [EMAIL PROTECTED] wrote:
> To: mysql@lists.mysql.com
> From: [EMAIL PROTECTED]
> Subject: Re: Checking for good update
>
>
> looks a bit strange to me.
>
> > $result = mysql_query($query) or die('Query couldn\'t
> > executed:'.mysql_error());
>
> please try something like this:
>
> // build the query - (that's OK)
> $query = "UPDATE members SET email_verified='X' WHERE
> logon_id='" .$logonid. "'";
>
> // send the query to the server - save the result resource
> $res = mysql_query($query);
>
> // test for the result of the above query
> if(!$res)
> {
> // stop the script if the result is not valid
> die('Query couldn\'t be executed:'.mysql_error());
> }
>
> // process a valid result
> $row = mysql_fetch_array($res)
>
> if (mysql_num_rows($res) == 1)
> {
> // the user id and password match,
> print("User id on db");
> }
> else
> {
> //$errorMessage = 'Sorry, wrong user id / password';
> print("Sorry, wrong user id / password");
> }
>
> I've not tested this - but it looks like you are mixing sending the
> mysql query and testing for the result of the query at the same time,
> which AFAIK is not possible.
>
> Maybe you need to get a simple introductory book on php, such as O'reillys
> php pocket reference, ISBN 0596-00402-8.
>
> Regards
>
> Keith
>
> In theory, theory and practice are the same;
> In practice they are not.
>
> On Sun, 12 Mar 2006, fbsd_user wrote:
>
> > To: Mysql
> > From: fbsd_user <[EMAIL PROTECTED]>
> > Subject: Checking for good update
> >
> > Using this code I get this error message.
> >
> > Warning: mysql_num_rows(): supplied argument is not a valid MySQL
> > result resource in /usr/local/www/data/mls_verifyemail.php on line
>
> Probably because you are not sending a valid query to the server,
> you will not be getting a valid result resource back from the server.
>
> > What code should I use to check if the update worked or not?
> >
> >
> > $query = "UPDATE members SET email_verified='X' WHERE
> > logon_id='".$logonid."'";
> >
> > $result = mysql_query($query) or die('Query couldn\'t
> > executed:'.mysql_error());
> >
> > if (mysql_num_rows($result) == 1)
> > {
> > // the user id and password match,
> > print("User id on db");
> > }
> > else
> > {
> > //$errorMessage = 'Sorry, wrong user id / password';
> > print("Sorry, wrong user id / password");
> > }
>
> --
> 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 Debuggers
Is this a free hosting package David B? That's a bit naughty I think - just removing your database without warning you about it first. At least you would have had time to make a backup copy. Keith In theory, theory and practice are the same; In practice they are not. On Sun, 12 Mar 2006, David Blomstrom wrote: > To: mysql@lists.mysql.com > From: David Blomstrom <[EMAIL PROTECTED]> > Subject: MySQL Debuggers > > I recently discovered that all my websites were wrecked. I spent several > hours trying to troubleshoot it, until I finally discovered that my database > was missing. I asked my webhost about it, and they told me they deactivated > it because it was "overwhelming the server." > > Someone told me about a software program called Mytop that can be used to > debug MySQL. However, it looks way too complex for me. I just wondered if > anyone on this list is aware of other, more user-friendly MySQL debuggers. > > Thanks. > > > > - > Yahoo! Mail > Use Photomail to share photos without annoying attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking for good update
On Sun, 12 Mar 2006, Michael Stassen wrote:
> To: [EMAIL PROTECTED]
> From: Michael Stassen <[EMAIL PROTECTED]>
> Subject: Re: Checking for good update
>
> [EMAIL PROTECTED] wrote:
> > looks a bit strange to me.
> >
> > > $result = mysql_query($query) or die('Query couldn\'t
> > > executed:'.mysql_error());
> >
> > please try something like this:
>
> Why? There's nothing wrong with the above statement.
I've never seen logic like that before. It looks to me like
fbsd_user is trying to use the OR operator outside an if
statement.
Is the mentioned in the php manual somewhere Michael?
> > I've not tested this - but it looks like you are mixing sending the
> > mysql query and testing for the result of the query at the same time,
> > which AFAIK is not possible.
>
> You should try it. It works just fine, and isn't the problem. The
> problem is that you cannot treat the result of an UPDATE as if it were a
> SELECT.
Regards
Keith Roberts
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking for good update
Thankyou for that explanation Michael.
I shall look into using that construct in my own code now!
Apologies to fbsd_user for my previous comments on his
coding style.
Regards
Keith Roberts
In theory, theory and practice are the same;
In practice they are not.
On Mon, 13 Mar 2006, Michael Stassen wrote:
> To: [EMAIL PROTECTED]
> From: Michael Stassen <[EMAIL PROTECTED]>
> Subject: Re: Checking for good update
>
> [EMAIL PROTECTED] wrote:
> > On Sun, 12 Mar 2006, Michael Stassen wrote:
> > >
> > > [EMAIL PROTECTED] wrote:
> > >
> > > > looks a bit strange to me.
> > > >
> > > > > $result = mysql_query($query) or die('Query couldn\'t
> > > > > executed:'.mysql_error());
> > > >
> > > > please try something like this:
> > >
> > > Why? There's nothing wrong with the above statement.
> >
> > I've never seen logic like that before. It looks to me like fbsd_user
> > is trying to use the OR operator outside an if statement.
> >
> > Is the mentioned in the php manual somewhere Michael?
> >
> > > > I've not tested this - but it looks like you are mixing
> > > > sending the
> > > > mysql query and testing for the result of the query at the
> > > > same time,
> > > > which AFAIK is not possible.
> > >
> > > You should try it. It works just fine, and isn't the problem.
> > > The
> > > problem is that you cannot treat the result of an UPDATE as if it
> > > were a
> > > SELECT.
> >
> > Regards
> > Keith Roberts
>
> Yes, this is documented. It's also standard practice (in perl and C as
> well).
>
> OR is not part of an if statement, it is a logical operator.
> <http://www.php.net/manual/en/language.operators.logical.php> "A or B"
> has a value, true or false, depending on the values of A and of B. In
> fact, if A is true, then "A or B" is certainly true, so there's no need to
> look at B at all. This short-circuit evaluation, combined with the fact
> that every assignment returns the assigned value
> <http://www.php.net/manual/en/language.expressions.php>, makes a statement
> like this possible.
>
> $result = mysql_query($query) or die('Query error:'.mysql_error());
>
> First, the function mysql_query() is called. Its return value is assigned
> to $result, *and* returned as the return value of the assignment operator
> (=). Now we know A. If mysql_query succeeded, its return value (A)
> evaluates as true, so the or operation must be true, so no need to look at
> B. If, on the other hand, A is false (mysql_query failed), we must
> evaluate B to determine the value of the "or" expression. Of course, to
> determine the value of B, we have to call the referenced function, die().
>
> Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql5 options file location
Hi Alex. It seems that mysqld and all the client programs insist on reading /etc/my.cnf first. To overide this behaviour for a particular instance of mysqld you need to pass the --defaults-file option as the FIRST parameter to mysqld_safe or mysqld if you are not using mysqld_safe. --defaults-extra-file=path The name of an option file to be read in addition to the usual option files. If given, this option must be first. --defaults-file=path The name of an option file to be read instead of the usual option files. If given, this option must be first. --no-defaults Do not read any option files. If given, this option must be first. More options are found in /usr/local/mysql-5.0.18/man/man1/mysqld_safe.1 I found this behaviour very annoying at first. But I now use this to my advantage. I have split my my.cnf file like this: /etc/my.cnf only has parameters used by mysql client programs. the my.cnf that lives in the mysqld installation directory only contains directives pertinent to that particular version of mysqld, and nothing there for the client programs. That way you have total control over all the parameters passed to all client programs in one central place, /etc/my.cnf. You do not have to worry about mysqld reading the wrong parameters for its invocation, because you have a seperate my.cnf just for that version of mysqld. I have written the following script to start a particular version of mysqld. This lives in /usr/local/mysql-/bin/ #! /bin/sh # # start the MySQL database server /usr/local/mysql-5.0.18/bin/mysqld \ --defaults-file=/usr/local/mysql-5.0.18/my.cnf \ --port= \ --socket=/var/lib/mysql/mysql.sock \ --pid=/var/lib/mysql/laptop.pid \ --user=mysql \ --datadir=/var/lib/mysql & And this script gets called from /etc/init.d/boot.local when the machine boots up. /etc/init.d/halt.local calls the following script to shutdown the mysqld server gracefully. #! /bin/sh # # stop the MySQL database server /usr/local/mysql-5.0.18/bin/mysqladmin shutdown \ -usqlsuperuser -p \ --socket=/var/lib/mysql/mysql.sock The other advantage of passing parameters on the command-line to mysqld is that you can actually see, using a visual process manager like http://www.student.nada.kth.se/~f91-men/qps/ * if mysqld is running OK * what parameters you passed to mysqld, eg the port, socket, datadir etc, each mysqld is using * how many different versions of mysqld you have running I find this very helpfull when running two versions of mysqld at a time, eg testing a newer version against an already installed version, before removing the older version. HTH Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Alex Moore wrote: > To: mysql@lists.mysql.com > From: Alex Moore <[EMAIL PROTECTED]> > Subject: mysql5 options file location > > I am building mysql5 latest from source on Solaris. > > The location of the options file is very confusing and does not work > according to the online documentation. For example, I have --basedir > of /opt/csw/mysql5 and --datadir of /opt/csw/mysql5/var. If I put > my.cnf in datadir or in basedir, the file is not used. I am using > mysqld_safe to start mysqld. my.cnf options will only work if I put > the file in /etc/ > > './libexec/mysqld --verbose --help' returns: > Default options are read from the following files in the given order: > /etc/my.cnf ~/.my.cnf > > This is very different from my mysql4 builds from source, which include > the documented server-specific file listed after /etc/my.cnf > > How can I get mysql5 to use a server-specfic options file? Am I > missing a configure option or defines for mysql5? > > Thanks, > > Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: E/R Tool
http://www.thekompany.com/products/dataarchitect/ There are free evaluation copies to download, and it's not that dear to buy a copy, very good value actually. I'm not sure if there is a free Linux version that theKompany have released as well. Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Vinay wrote: > To: mysql@lists.mysql.com > From: Vinay <[EMAIL PROTECTED]> > Subject: E/R Tool > > > > > Hello , > Is there a mysql or any other tool that generates a E/R diagram using an > existing mysql database. > > > Thank You > Vinay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: E/R Tool
Excellent DB design tool Peter. Thanks for posting the information. http://www.fabforce.net/dbdesigner4/ I have bookmarked that, and will be checking that out soon! Regards Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Peter M. Groen wrote: > To: mysql@lists.mysql.com > From: Peter M. Groen <[EMAIL PROTECTED]> > Subject: Re: E/R Tool > > On Monday 13 March 2006 17:37, Vinay wrote: > > Hello , > > Is there a mysql or any other tool that generates a > > E/R diagram using an existing mysql database. > > > > > > Thank You > > Vinay > > Try fabforce for DbDesigner4MySQL. Very good tool. > > -- > Peter M. Groen > Open Systems Development > Klipperwerf 12 > 2317 DZ Leiden > T : +31-(0)71-5216317 > M : +31-(0)6-29563390 > E : [EMAIL PROTECTED] > Skype : peter_m_groen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: E/R Tool
OK TY Peter. I have downloaded both DBDesigner and MySQL Workbench. Looking forward to workbench reaching GA status. Regards Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Peter Brawley wrote: > To: [EMAIL PROTECTED] > From: Peter Brawley <[EMAIL PROTECTED]> > Subject: Re: E/R Tool > > [EMAIL PROTECTED] wrote: > > Excellent DB design tool Peter. Thanks for posting the information. > > > > http://www.fabforce.net/dbdesigner4/ > > > It has become MySQL Workbench > (http://forums.mysql.com/read.php?10,73820,73820#msg-73820), but it isn't > production-ready yet. > > PB > > > -- > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: 3/10/2006 > > > -- > 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: mysql5 options file location
In your global /etc/my.cnf fle, you should be able to split that into sections for each client program that you want to set specific options for. Eg. # /etc/my.cnf # MySQL client program configuration file # last updated 2006-03-05 # mysqld server configuration file lives in # /usr/local/mysql-/my.cnf #--- # mysql client program configuration options #--- [mysql] no-auto-rehash # needed for security - to stops multiple deletes/updates # without a where clause safe-updates # # The following options will be passed to all MySQL clients [client] socket = /var/lib/mysql/mysql.sock port = 7000 # [myisamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M # [mysqldump] quick set-variable = max_allowed_packet=16M # # available programs/scripts are: (from 4.0.21 version) #my_print_defaults mysqladmin #myisamchk mysqlbinlog #myisamlog mysqlbug - n/a #myisampack mysqlcheck #mysql mysqld #mysql_convert_table_format - .plmysqld_multi #mysql_find_rows mysqldump #mysql_fix_privilege_tables n/a mysqlhotcopy - .pl #mysql_install_dbmysqlimport #mysql_setpermission - .pl mysqlshow #mysql_zap mysqltest #mysqlaccess - .pl safe_mysqld # # end of mysql client program configurations # /etc/my.cnf Take a look at the man pages for the client programs, as well as mysqld_safe and mysqld, in /opt/csw/mysql5/man/man1/ Most of the options for client programs are listed there. Exactly what are the problems you are having with the server specific my.cnf file? AFAIK it should be in basedir, and not normally in datadir. The log files are OK to have in datadir - that's the default place SuSE Linux puts them. HTH Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Alex Moore wrote: > To: mysql@lists.mysql.com > From: Alex Moore <[EMAIL PROTECTED]> > Subject: Re: mysql5 options file location > > On Mon, 13 Mar 2006 16:52:03 + (GMT) > [EMAIL PROTECTED] wrote: > > > > > Hi Alex. It seems that mysqld and all the client > > programs insist on reading /etc/my.cnf first. > > > > To overide this behaviour for a particular instance of > > mysqld you need to pass the --defaults-file option as the > > FIRST parameter to mysqld_safe or mysqld if you are not using > > mysqld_safe. > > Yes, I understand everything that you said Keith and have used the > options file logic to my advantage as well. Thanks for the full > description. > > What is not working for me is that the server-specific file does not > appear to be built into all of the objects like it was in 4.1. > > For example, my.cnf is in basedir. my.cnf has a [mysqld] group that > defines many options like various logging selections and tuning. I do > not want those options in a global file since I am not supposed to > write to /etc/. I am using a directory structure similar > to /usr/local/mysql5, but it is actually /opt/csw/mysql5. > > The server-specific options are read on mysqld_safe at startup, as > evidenced by `mysqladmin variables`. > > Without a /etc/my.cnf file, 'my_print_defaults mysqld' prints nothing. > With a /etc/my.cnf file, 'my_print_defaults mysqld' prints only the > options in /etc/my.cnf. This is very different from 4.1 and causing me > a lot of headaches and is probably my biggest issue with 5.0. > > I could go on about the utilities that do not work without a global > options file. > > Another clue about this change from 4.1 is the `mysqld --verbose > --help` output not listing a server-specific options file on 5.0 > > I just wondered if I did something wrong or if I can just not use some > utilities in 5.0 > > The situation with 5.0 and the options file is really much bigger than > what I have described. I have big problems with the 5.0 location of > the server-specific options file moving from datadir to basedir. But > that is another discussion. > > Thanks, > > Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql5 options file location
I get the same results as you do using this: karsites:/usr/local/mysql-5.0.18/bin # ./my_print_defaults mysqld karsites:/usr/local/mysql-5.0.18/bin # ./my_print_defaults client --socket=/var/lib/mysql/mysql.sock --port= karsites:/usr/local/mysql-5.0.18/bin # It seems like the last parameter passed to my_print_defaults tells my_print_defaults to get that particular section from the/etc/my.cnf file and print it out. However, you can pass a parameter to my_print_defaults, to tell it which my.cnf file to check. So doing this: karsites:/usr/local/mysql-5.0.18/bin # ./my_print_defaults --defaults-file=/usr/local/mysql-5.0.18/my.cnf mysqld --basedir=/usr/local/mysql-5.0.18 --server-id=1 --skip-name-resolve --skip-locking --set-variable=key_buffer=16M --set-variable=max_allowed_packet=1M --set-variable=table_cache=64 --set-variable=sort_buffer=512K --set-variable=net_buffer_length=8K --set-variable=myisam_sort_buffer_size=8M --log=5-0-18.log --log-bin=laptop-bin --log-error=5-0-18.error-log --log-queries-not-using-indexes --log-slow-admin-statements --log-slow-queries=5-0-18.slow-log --log-warnings karsites:/usr/local/mysql-5.0.18/bin # Does actually return the correct my.cnf file details. You can also use the -c /usr/local... shorthand option to tell my_print_defaults which my.cnf to look at. 'my_print_defaults --help' will return all the available options you can use. This is actually a Linux ELF file, and not a shell script Alex. That's something I have also just learned, so I'm pleased you mentioned it, because I was having the same problem, until I checked the options available, using --help. Are there any other problems you have encountered using this type of my.cnf setup? Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Alex Moore wrote: > To: mysql@lists.mysql.com > From: Alex Moore <[EMAIL PROTECTED]> > Subject: Re: mysql5 options file location > > On Mon, 13 Mar 2006 23:05:30 + (GMT) > [EMAIL PROTECTED] wrote: > > > Exactly what are the problems you are having with the server > > specific my.cnf file? > > Sorry, I thought that I had described the problem. A quick example was > 'my_print_defaults mysqld' returning only the options defined in the > global file. None of the options from the server-specific my.cnf are > returned. This is not the way 4.1, and probably earlier, works. > > Thanks, > > Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: E/R Tool
So did I Jim, on SuSE 9.2 pro. So I downloaded the windows versions to my laptop instead. Keith In theory, theory and practice are the same; In practice they are not. On Tue, 14 Mar 2006, Jim Douglas wrote: > To: [EMAIL PROTECTED] > From: Jim Douglas <[EMAIL PROTECTED]> > Subject: Re: E/R Tool > > From: [EMAIL PROTECTED] > > To: mysql@lists.mysql.com > > Subject: Re: E/R Tool > > Date: Mon, 13 Mar 2006 22:40:34 + (GMT) > > > > > > OK TY Peter. > > > > I have downloaded both DBDesigner and MySQL Workbench. > > > > Looking forward to workbench reaching GA status. > > > > Regards > > > > Keith > > > > In theory, theory and practice are the same; > > In practice they are not. > > > > On Mon, 13 Mar 2006, Peter Brawley wrote: > > > > > To: [EMAIL PROTECTED] > > > From: Peter Brawley <[EMAIL PROTECTED]> > > > Subject: Re: E/R Tool > > > > > > [EMAIL PROTECTED] wrote: > > > > Excellent DB design tool Peter. Thanks for posting the > > > > information. > > > > > > > > http://www.fabforce.net/dbdesigner4/ > > > > > > > It has become MySQL Workbench > > > (http://forums.mysql.com/read.php?10,73820,73820#msg-73820), but > > > it > > isn't > > > production-ready yet. > > > > > > PB > > > > > > > > > -- > > > No virus found in this outgoing message. > > > Checked by AVG Free Edition. > > > Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: > > > 3/10/2006 > > > > > > > > > -- > > > 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] > > > > > Great looking tool I tried to install and got a lot of failed > dependencies on Fedora Core 4. > > Will a version for FC 4/5 be available anytime soon? > > Jim > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: getting table metadata
If you upgrade to mysql 5.0.x there are also the The INFORMATION_SCHEMA tables that return meta information about tables. AFAIK they may not be available in pre 5.0 versions. Keith In theory, theory and practice are the same; in practice they are not. To unsubscribe from this list, please see detailed instructions already posted at: http://marc.theaimsgroup.com/?l=php-install&m=114138567814319&w=2 On Fri, 17 Mar 2006, Yves Glodt wrote: > To: mysql@lists.mysql.com > From: Yves Glodt <[EMAIL PROTECTED]> > Subject: getting table metadata > > Hi, > > is it possible to get information about tables by doing queries on some > system > tables? I am using mysql version 4.1.11 on debian sarge. > > In my case I need to know which columns (names and types) a table has, and > how > the primary key is defined. > > How can I get this information out of mysql by only using sql ? > > Best regards, > Yves > > -- > 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: Discussion: the efficiency in using foreign keys
IMHO I think you will find that there is a balance between
the speed of opening and reading/writing several related
smaller tables connected by FK's, rather than one mega-sized
gigantic table.
How do you normalise a table without using FK's.
Your right, MySQL does not currently do any checking for
FK's, but this does not mean that you cannot still use them
in MyISAM tables.
Eg.
/* table to store quiz questions */
CREATE TABLE `quiz_question` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`question_text` text NOT NULL,
PRIMARY KEY `ID` (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
/* table to store quiz answers */
CREATE TABLE `quiz_answer` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`answer_text` text NOT NULL,
`status` enum('wrong', 'right') NOT NULL,
`questionID` mediumint UNSIGNED NOT NULL default '0',
PRIMARY KEY `ID` (`ID`),
KEY `questionID` (`questionID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
/* table to track quiz questions with user answers */
CREATE TABLE `quiz_result` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`user` char(32) NOT NULL default '',
`questionID` mediumint UNSIGNED NOT NULL default '0',
`answerID` mediumint UNSIGNED NOT NULL default '0',
PRIMARY KEY `ID` (`ID`),
KEY `questionID` (`questionID`),
KEY `answerID` (`answerID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
So in the quiz_result table above questionID is a
column holding the primary key of each question_text column
in the quiz_question table. It is a foreign key.
answerID is a foreign key that points to the primary
key of the answer submitted by the user doing the quiz.
When the user has finished doing the quiz, the quiz_result
table is scanned for the user session ID, 'user', and then
the question and the user's chosen answer are picked from
the quiz_question and quiz_answer tables, using the foreign
keys in the result table.
I find it helps me to think of foreign keys as unique
pointers to rows in other related tables.
HTH
Keith
In theory, theory and practice are the same;
in practice they are not.
On Mon, 20 Mar 2006, Foo Ji-Haw wrote:
> To: mysql@lists.mysql.com
> From: Foo Ji-Haw <[EMAIL PROTECTED]>
> Subject: Discussion: the efficiency in using foreign keys
>
> Hi all,
>
> This is a fundamental concept in RDBMS: the use of foreign keys in
> database design.
>
> I'd just like to poll the community here, on whether it is a best
> practice, or practically essential to 'link' related tables by use of
> foreign keys.
>
> For myself, I usually do all the validity checking when adding a new
> record that references a record id from another table. I understand that
> this may not be efficient because it becomes 2 database calls (and db
> calls are expensive in high-load environments).
>
> What are the advantages/ disadvantages in using foreign keys? In MySQL,
> this means one cannot use MyISAM. Do you place a lot of triggers as well?
>
> Thanks.
>
> --
> 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: Discussion: the efficiency in using foreign keys
I'd also like to add that if you have a choice between doing something in the application logic vs. MySQL's SQL statements, then it is probably more efficient to use SQL statements, constructs and related functions, to get the job done, rather than in the application logic if possible. Keith In theory, theory and practice are the same; in practice they are not. On Mon, 20 Mar 2006, Martijn Tonies wrote: > To: mysql@lists.mysql.com > From: Martijn Tonies <[EMAIL PROTECTED]> > Subject: Re: Discussion: the efficiency in using foreign keys > > Hi, > > > This is a fundamental concept in RDBMS: the use of foreign keys in > > database design. > > > > I'd just like to poll the community here, on whether it is a best > > practice, or practically essential to 'link' related tables by use of > > foreign keys. > > > > For myself, I usually do all the validity checking when adding a new > > record that references a record id from another table. I understand that > > this may not be efficient because it becomes 2 database calls (and db > > calls are expensive in high-load environments). > > > > What are the advantages/ disadvantages in using foreign keys? In MySQL, > > this means one cannot use MyISAM. Do you place a lot of triggers as well? > > When it comes to referential constraints, the answer is simple: > ALWAYS put them on the database. > > Anyway who answers differently either never had to recover > a database that was trashed by the lack of integrity constraints > or has no ide what he's talking about. > > Most probably, this statement will get me tons of e-mail again ;-) > > Martijn Tonies > Database Workbench - development tool for MySQL, and more! > Upscene Productions > http://www.upscene.com > My thoughts: > http://blog.upscene.com/martijn/ > Database development questions? Check the forum! > http://www.databasedevelopmentforum.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: Cannot select the database
Take a look at these links Charles. http://dev.mysql.com/doc/refman/4.1/en/privilege-system.html http://dev.mysql.com/doc/refman/4.1/en/user-account-management.html You don't need to fully understand the mysql privilege access system to set up users, but it would help in the long term. Regards Keith In theory, theory and practice are the same; in practice they are not. On Tue, 21 Mar 2006, Charles Gambrell wrote: > To: mysql@lists.mysql.com > From: Charles Gambrell <[EMAIL PROTECTED]> > Subject: Cannot select the database > > I know this must be a simple issue and maybe I am asking it in the > wrong place, so if the latter is the case, please direct me to the > correct place. > > I am getting my feet wet with MySQL. I have installed running on > WhiteBox linux and have created a datebase with one table and put some > date in it. All seems to work fine form the the command line. > > I am now trying to connect throw a browser on a different workstation > using PHP. I seem to be able to connect ok. I can select the "test" > database that ships with MySQL but when I try to select the database > I created the select fails. > > I am guessing this is some kind of premissions issue, that I am not > understanding yet. > > I have looked some at the db table i the mysql database and I see this - > > host | user | db > > %| | mynewdb > %| mysql | mynewdb > %| | test > %| | test\_% > > Where do I need to be looking to see the problem and better yet, > understanding the problem. > > Thanks for the help. > > Charles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error with mysqld_safe
On Tue, 21 Mar 2006, Áquila Chaves wrote: > [ERROR] > /usr/local/mysql/bin/mysqld: Can't create/write to file > '/var/run/mysqld/mysqld.pid' (Errcode: 13) 060321 12:12:22 > [ERROR] Can't start server: can't create PID file: > Permission denied 060321 12:12:22 mysqld ended Check the directory access permissions. Does mysql have permission to write the PID file to /var/run/mysqld/ ? Regards Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot select the database
>From the 5.0.x manual
How to create user accounts:
The next examples create three accounts and give them access
to specific databases. Each of them has a username of custom
and password of obscure.
To create the accounts with GRANT, use the following
statements:
shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON bankaccount.*
-> TO 'custom'@'localhost'
-> IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON expenses.*
-> TO 'custom'@'whitehouse.gov'
-> IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON customer.*
-> TO 'custom'@'server.domain'
-> IDENTIFIED BY 'obscure';
The three accounts can be used as follows:
The first account can access the bankaccount database, but
only from the local host.
The second account can access the expenses database, but
only from the host whitehouse.gov.
The third account can access the customer database, but only
from the host server.domain.
So you need 1 accout to access your database from localhost,
and another account (with the same password to avoid
confusion) to access your database from any other domain
apart from localhost.
*
You also need to read this Charles, then you will know how
mysql checks who is authorised to connect to the server.
http://dev.mysql.com/doc/refman/4.1/en/connection-access.html
*
HTH
Keith
In theory, theory and practice are the same;
in practice they are not.
On Tue, 21 Mar 2006, Charles Gambrell wrote:
> To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
> From: Charles Gambrell <[EMAIL PROTECTED]>
> Subject: Re: Cannot select the database
>
> Thanks for the links.
>
> The link on privilge had a link to a discussion of "access denied."
> http://dev.mysql.com/doc/refman/4.1/en/access-denied.html
>
> Where I read -
>
> "If you have access problems with a Perl, PHP, Python, or ODBC
> program, try to connect to the server with mysql -u user_name db_name
> or mysql -u user_name -pyour_pass db_name. If you are able to
> connect using the mysql client, the problem lies with your program,
> not with the access privileges. (There is no space between -p and the
> password; you can also use the --password=your_pass syntax to specify
> the password. If you use the -p --passwordoption with no password
> value, MySQL prompts you for the password.)"
>
> Well, that describes my situation and when I attempt at the command
> line of the host running MySQL to connect by "mysql -u mysql mydb" I
> get the error message "error 1044 (42000) Access denied for user "@"
> localhost to database "mydb."
>
> So I guess I do have an access problem the user "mysql" weather it is
> on the localhost or throught PHP.
>
> Now to figure out how to give the right permission(s) to the user. I
> know that the mysql user can connect to the "test" database from the
> command line or through a browser and PHP. At least it looks like I
> am narrowing the problem down.
>
> host | user | db
>
> %| | mynewdb
> %| mysql | mynewdb
> %| | test
> %| | test\_%
>
>
> This looks like it means that from any host the mysql user can access
> the mynewdb database. But it must not mean that. What am I missing?
> And I don't understand "test\_%" but I know I can connect to and open
> the test database.
>
> Charles
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about autoincrement ID
On Thu, 23 Mar 2006 [EMAIL PROTECTED] wrote: > > To: saf <[EMAIL PROTECTED]> > From: [EMAIL PROTECTED] > Subject: Re: Question about autoincrement ID > > One important thing to remember: You should not let UI > design requirements dictate your DB design. Most > developers who design the database just to support the > front end up regretting the decision. Those designs are > either impossible to extend or impossible to manage or > both. You should always design for an efficient database > and adjust your retrieval methods to present the data in > the manner requested, not the other way around. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine IMHO I think the database is the central core of a DB driven website. Therefore it should be the first thing designed in a DB driven website. Everything else in a DB driven site should then be built around the expected functionality of the database. So, if one starts out by designing a database (and it's server(s)) with optimum performance and upgradability as design goals, you won't go to far wrong. Just my 2c. Keith Roberts In theory, theory and practice are the same; in practice they are not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock'
On Tue, 28 Mar 2006, Áquila Chaves wrote:
> To: mysql@lists.mysql.com
> From: Áquila Chaves <[EMAIL PROTECTED]>
> Subject: error: 'Can't connect to local MySQL server through socket
> '/tmp/mysql.sock'
>
> - When I execute the command "mysqld_safe":
> [EMAIL PROTECTED] mysql]# bin/mysqld_safe --user=mysql &
>[1] 7298 [EMAIL PROTECTED] mysql]# Starting mysqld daemon
>with databases from /var/lib/mysql /* {processing...
>the cursor is blinking but I don't have any answer...
>So, I press } */ [EMAIL PROTECTED] mysql]#
>
> - The log message is:
> 060323 16:51:11 mysqld started
> 060323 16:51:11 InnoDB: Started; log sequence number 0 43655
> 060323 16:51:11 [Note] /usr/local/mysql/bin/mysqld: ready for
> connections.
> Version: '5.0.19-standard' socket: '/var/lib/mysql/mysql.sock' port:
> 3306 MySQL Community Edition - Standard (GPL)
So you have got mysqld running OK, and waiting for you to
connect to it on the socket /var/lib/mysql/mysql.sock
> - Aparently it's OK. But when I execute the command below
>occurs the following error: [EMAIL PROTECTED] mysql]#
>bin/mysqladmin version bin/mysqladmin: connect to
>server at 'localhost' failed error: 'Can't connect to
>local MySQL server through socket '/tmp/mysql.sock'
>(2)' Check that mysqld is running and that the socket:
>'/tmp/mysql.sock' exists!
You are trying to connect to the mysql server on a different
socket than the one mysqld is listening on for connections.
You need to tell mysqladmin to connect to the socket that
mysqld is listening to. In this case it is
/var/lib/mysql/mysql.sock.
You could do this with:
/bin/mysqladmin --socket=/var/lib/mysql/mysql.sock
That should work. If you have set a password you will need
to use that as well.
You could also set the --socket value in /etc/my.cnf by
adding a few lines to it like this:
Note that directives in the my.cnf file are the same as on
the command-line, but without the preceeding -- double-dash.
# /etc/my.cnf
#
# The following options will be passed to all MySQL clients
[client]
socket = /var/lib/mysql/mysql.sock
port = 3306
HTH
Keith
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Customer Recommendation Query
On Tue, 28 Mar 2006, Brian Erickson wrote: > To: mysql@lists.mysql.com > From: Brian Erickson <[EMAIL PROTECTED]> > Subject: Customer Recommendation Query snip > We are using MySQL version 3.23. There are approximately > 500 unique rows in the 'actions' table and 2,000,000 rows > in the member_actions table, with 3,000+ actions being > recorded at any given time. snip > So, our question is whether or not this is feasible with a > one/few query approach, or if this is something that > should be accomplished with something similar to the > approach above? Can anyone provide a good start for us? I think a good start would be to consider the possiblity of upgrading from 3.23 to 5.0.18/19. I'm sure there is alot more functionality available for you to utilise then - not just in SELECT statements either. Regards Keith In theory, theory and practice are the same; in practice they are not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
On Tue, 28 Mar 2006, Jorrit Kronjee wrote: > To: mysql@lists.mysql.com > From: Jorrit Kronjee <[EMAIL PROTECTED]> > Subject: Re: Random 'select permission denied' since upgrade to 5.0.18 snip > Could this message appear when, for instance, a maximum amount of threads > has been spawned or MySQL has reached its connection limit? Possible - what are your settings for the relevant mysql server variables? You could try something like: show variables like "max%" \G I'm not really sure what all the server variables do, but they may be relevant to your problem. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
On Tue, 28 Mar 2006, Jorrit Kronjee wrote: > To: mysql@lists.mysql.com > From: Jorrit Kronjee <[EMAIL PROTECTED]> > Subject: Re: Random 'select permission denied' since upgrade to 5.0.18 > > [EMAIL PROTECTED] wrote: > > On Tue, 28 Mar 2006, Jorrit Kronjee wrote: > > > > > To: mysql@lists.mysql.com > > > From: Jorrit Kronjee <[EMAIL PROTECTED]> > > > Subject: Re: Random 'select permission denied' since upgrade to > > > 5.0.18 > > snip > > > Could this message appear when, for instance, a maximum amount of > > > threads > > > has been spawned or MySQL has reached its connection limit? > > > > Possible - what are your settings for the relevant mysql server > > variables? > > > > You could try something like: > > > > show variables like "max%" \G > > > > I'm not really sure what all the server variables do, but they may be > > relevant to your problem. > > > > Keith > > > > Keith, > > Here's the output: > > mysql> show variables like "max%" \G > *** 1. row *** > Variable_name: max_allowed_packet > Value: 1047552 > *** 2. row *** > Variable_name: max_binlog_cache_size > Value: 4294967295 > *** 3. row *** > Variable_name: max_binlog_size > Value: 1073741824 > *** 4. row *** > Variable_name: max_connect_errors > Value: 10 > *** 5. row *** > Variable_name: max_connections > Value: 300 > *** 6. row *** > Variable_name: max_delayed_threads > Value: 20 is this relevant ? > *** 7. row *** > Variable_name: max_error_count > Value: 64 > *** 8. row *** > Variable_name: max_heap_table_size > Value: 16777216 > *** 9. row *** > Variable_name: max_insert_delayed_threads > Value: 20 ditto > *** 10. row *** > Variable_name: max_join_size > Value: 4294967295 > *** 11. row *** > Variable_name: max_length_for_sort_data > Value: 1024 > *** 12. row *** > Variable_name: max_relay_log_size > Value: 0 > *** 13. row *** > Variable_name: max_seeks_for_key > Value: 4294967295 > *** 14. row *** > Variable_name: max_sort_length > Value: 1024 > *** 15. row *** > Variable_name: max_sp_recursion_depth > Value: 0 > *** 16. row *** > Variable_name: max_tmp_tables > Value: 32 > *** 17. row *** > Variable_name: max_user_connections > Value: 0 > *** 18. row *** > Variable_name: max_write_lock_count > Value: 4294967295 > 18 rows in set (0.00 sec) > > > -- > System Developer > > Infopact Network Solutions > Hoogvlietsekerkweg 170 > 3194 AM Rotterdam Hoogvliet > tel. +31 (0)88 - 4636700 > fax. +31 (0)88 - 4636799 > mob. +31 (0)6 - 14105968 > [EMAIL PROTECTED] > http://www.infopact.nl/ > > -- > 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 5.0.19-2 and repair problems...
Have you tried the following myisamchk option: --extend-check, -e Check the table very thoroughly. This is quite slow if the table has many indexes. This option should only be used in extreme cases. Normally, myisamchk or myisamchk --medium-check should be able to determine whether there are any errors in the table. Keith In theory, theory and practice are the same; in practice they are not. On Wed, 29 Mar 2006, Sander Smeenk wrote: > To: mysql@lists.mysql.com > From: Sander Smeenk <[EMAIL PROTECTED]> > Subject: Re: MySQL 5.0.19-2 and repair problems... > > Quoting Kishore Jalleda ([EMAIL PROTECTED]): > > > That usually means the table is corrupt beyond repair and nothing is really > > fixing it or there is something one cannot easily comprehend ( this usually > > happens with inconsistency among deleted records and some kind of mismatch > > that occurs) -anyway what I would really advice in this case is to > > rebuild the table from a working/clean backup and start-over, if this is a > > slave then thats very easy to do, if not it depends on your latest clean > > backup available .. > > Amazing. Amazing that even the tools can't tell me it's unfixable. > I mean, i tried everything. :) > > I'll restore the databases from the master (this isn't a slave, but it's > a machine i want to switch to when it finally becomes stable...) and see > where we get from that... > > The machine didn't crash. Nor did mysql. Any clue what might cause this > to happen? Disk looks fine too, no read or write errors whatsoever... > > Thanks, > Sander. > > -- > | Just remember -- if the world didn't suck, we would all fall off. > | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D > > -- > 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: auto_increment and the value 0
Are you saying just change the row with the 0 value as the PK, and change the FK's in the related tables to point to the new value instaed of 0? If so, would this move the row logically to the end of the table, if the 0 PK was replaced with the next auto_increment value? I suppose that would be alot easier than trying to bump the PK and related FK values of the whole table by 1, just to give the first row in the table the auto_increment value of 1? What about before migrating the database, just adding a new row to the end of the table, that would duplicate the data in the first row, then deleting the first row from the table? Would that work? Keith In theory, theory and practice are the same; in practice they are not. On Thu, 30 Mar 2006, Daniel Kasak wrote: > To: "Stanton, Brian" <[EMAIL PROTECTED]>, > "'mysql@lists.mysql.com'" > From: Daniel Kasak <[EMAIL PROTECTED]> > Subject: Re: auto_increment and the value 0 > > Stanton, Brian wrote: > > I'm migrating a database from 4.0.12 on Solaris to 4.0.18-0 on Red > > Hat > > Linux. A few of the tables have a 0 (zero) in the auto_increment > > primary > > key column. However, when importing, the 0 in the insert is > > translated to > > the next available auto_increment value thus causing a duplicate key > > situation on the next value in the import. I've tried removing the 0 > > row > > from the export and adding it in manually afterwards, but that also > > translates the 0 to the next available auto_increment value. I've > > also > > tried creating the table with the table option AUTO_INCREMENT=0 and > > inserting the 0 row first. That also translated it to a value of 1 > > and > > caused duplicate keys. > > > You can either: > - create the table without the auto_increment field, load the data, and > add the auto_increment field, or > - change all your zero values *now* ( and related fields in other tables > ), back things up, and then move the data > > I would take the 2nd option. > > -- > Daniel Kasak > IT Developer > NUS Consulting Group > Level 5, 77 Pacific Highway > North Sydney, NSW, Australia 2060 > T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 > email: [EMAIL PROTECTED] > website: http://www.nusconsulting.com.au k -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AlterTable Structure Across Multiple DBs
ALTER TABLE requires a table name for the current database.
You can specify:
ALTER TABLE db_name.tbl_name MODIFY col_name ...
or
mysql> alter table test1.t1 modify test1.t1.set1 varchar(30);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
but you cannot modify a table in a different database to the
one you first specify after TABLE db_name:
mysql> alter table test1.t1 modify test2.t1.set1 varchar(30);
ERROR 1102 (42000): Incorrect database name 'test2'
.
.
.
mysql> show create table t1 \G
*** 1. row
Table: t1
Create Table: CREATE TABLE `t1` (
`ID` int(11) NOT NULL auto_increment,
`set1` set('this','is','today') default NULL,
`col2` char(20) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
mysql> alter table t1 modify set1 varchar(30);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t1 \G
*** 1. row
Table: t1
Create Table: CREATE TABLE `t1` (
`ID` int(11) NOT NULL auto_increment,
`set1` varchar(30) default NULL,
`col2` char(20) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
It should not take you too long to use the command history
to recall the alter table ... statement, edit it, and work your
way through the databases like that.
Another way is to write a script that will parse the
database names in the data directory, and then generate the
SQL code to perfom the multiple ALTER TABLE statements.
The script could then replace the db_name to be changed on
each iteration.
To be safe you could make copies of your databases, and
perform the ALTER TABLE statements away from your live data
directory.
When you are happy with the modifications, then copy the
altered databases back to your live data directory.
Personally I'd feel alot safer altering one database table
at a time - just in case errors start appearing.
You need to be carefull that you do not loose any multiple
values in your set, as varchar will only hold one value at
a time.
HTH
Keith
In theory, theory and practice are the same;
in practice they are not.
On Thu, 30 Mar 2006, Jason Dimberg wrote:
> To: mysql@lists.mysql.com
> From: Jason Dimberg <[EMAIL PROTECTED]>
> Subject: AlterTable Structure Across Multiple DBs
>
> I have about 25 databases with the same structure and
> occasionally need to update the table structure. For
> example, I recently found a mistake in a field that was of
> type SET and needed to be VARCHAR. I will now need to
> edit each table. Is there an easy method to alter table
> structure across multiple dbs as opposed to editing each
> one individually?
>
> In retrospect I should have combined them into one db and
> may consider doing that.
>
> All dbs start with 'pm_' and have identically named tables
>
> MySQL 5.0.18
> Windows 2003
>
> Thank you,
> --
>
> *Jason Dimberg*
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Type For PK/FK
>From the 5.0.18 manual: The FOREIGN KEY and REFERENCES clauses are supported by the InnoDB storage engine, which implements ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...). See Section 14.2.6.4, FOREIGN KEY Constraints. For other storage engines, the clauses are parsed but ignored. The CHECK clause is parsed but ignored by all storage engines. See Section 13.1.5, CREATE TABLE Syntax. The reason for accepting but ignoring syntax clauses is for compatibility, to make it easier to port code from other SQL servers, and to run applications that create tables with references. See Section 1.9.5, MySQL Differences from Standard SQL. You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE statement. You must use separate statements. InnoDB supports the use of ALTER TABLE to drop foreign keys: ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE statement. You must use separate statements. For more information, see Section 14.2.6.4, FOREIGN KEY Constraints. Regards Keith ;-) In theory, theory and practice are the same; in practice they are not. On Fri, 31 Mar 2006, sheeri kritzer wrote: > To: Martijn Tonies <[EMAIL PROTECTED]> > From: sheeri kritzer <[EMAIL PROTECTED]> > Subject: Re: Table Type For PK/FK > > I didn't write the codebase for MySQL, so it's pointless to tell me > that "it's useless to be able to create a foreign key on a MyISAM > table". I agree that it's useless, however, it's possible, which is > why I put it in there -- as a caveat. > > The use is that apparently in future versions MyISAM will support > foreign key constraints. It's a comment because it still shows up in > SHOW CREATE TABLE and such. > > Like I said, I didn't design MySQL -- I just use it and was warning > that it's possible to create a table. I've seen the dreaded Error > number 150 way too many times, and sometimes it's because I forgot the > "engine=innodb" part of the CREATE TABLE statement. > > -Sheeri > > On 3/31/06, Martijn Tonies <[EMAIL PROTECTED]> wrote: > > Hello Sheeri, > > > > > Indeed, only the BDB and InnoDB storage engines support referential > > > integrity. If you accidentally create the table as MyISAM, there is > > > no error, though -- the constraints serve as a comment. > > > > No error? A comment? What use is that? > > > > If you want FKs, having the FKs as "a comment" is useless. > > > > Instead, re-create the table as of the InnoDB type. > > > > Martijn Tonies > > Database Workbench - development tool for MySQL, and more! > > Upscene Productions > > http://www.upscene.com > > My thoughts: > > http://blog.upscene.com/martijn/ > > Database development questions? Check the forum! > > http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stunningly slow query
If you use ALTER TABLE on a MyISAM table, all non-unique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes. This feature can be activated explicitly. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier. Can you post your show create table tbl_name statement for these tables that involve slow queries? Do you have alot of indexes on these slow queries? If so, would using the above help? (may have been mentioned already) ALTER TABLE tbl_name DISABLE KEYS; your slow insert or update query here; ALTER TABLE tbl_name ENABLE KEYS; HTH Keith more -> On Fri, 31 Mar 2006, Chris Kantarjiev wrote: > To: [EMAIL PROTECTED], mysql@lists.mysql.com > From: Chris Kantarjiev <[EMAIL PROTECTED]> > Subject: Re: stunningly slow query > > > Are your logs and data on the same partition? That's a bad idea for > > recovering from a blown part of the disk, but we also saw that one of > > our databases would crash when there were lots of > > inserts/updates/replaces -- other databases, which had the same > > version of MySQL and operating system, had the logs and data on a > > separate partition, and they did not crash. > > It's a MyISAM table. Are there separate logs files? If so, where? > I thought it was just .frm, .MYI and .MYD. We have MYI and MYD on > separate drives. Log files usually default to the mysql data directory, eg. /var/lib/mysql/ Putting the database files on seperate drives may slow things down alot too - unless others know better. .frm is the database definition file. .MYI is the index file, and .MYD is the data file. There is one each of these files for each myisam table in the database. I may be wrong, but I would have thought it better if these are all together on the same disk and partition for each table in the database? > We're investigating a possible MERGE organization. I'll report > back if we learn anything new. > > Thanks, > chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: searching for words with special chars
There at least two ways to accomplish this. The easy way is to use the LIKE operator: See http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html Regards Keith In theory, theory and practice are the same; in practice they are not. On Sat, 1 Apr 2006, Octavian Rasnita wrote: > To: mysql > From: Octavian Rasnita <[EMAIL PROTECTED]> > Subject: searching for words with special chars > > Hi, > > Is it possible to create a query that searches for records which contains > words with special chars and with their english correspondents? > > For example, if a user searches for "mata", I want to return all the records > that contain the words: > > mata > m?ta > mâ?a > mâ?? > > (just like Google does). > > Is it possible with MySQL, or I need to create all the possible combinations > in the client program, then search for all those words? > > Thank you. > > Teddy > > > -- > 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: Tuning a Server with >10,000 databases
I'm just wondering if it would be possible to use several apache servers on different ports and using virtual servers, that would each talk to a different instance of mysql, each running on different unix sockets? The main apache server listening on port 80 could then redirect requests to the other apache virtual server instances. Everything after that should be plain sailing I think. Best place to ask would probably be the apache user mailing list on this one. Regards Keith In theory, theory and practice are the same; in practice they are not. On Sat, 1 Apr 2006, Gary Huntress wrote: > To: [EMAIL PROTECTED] > From: Gary Huntress <[EMAIL PROTECTED]> > Subject: Re: Tuning a Server with >10,000 databases > > > > David Logan wrote: > > mos wrote: > > > > > At 09:27 PM 3/31/2006, you wrote: > > > > > > > I have been offering free database hosting for over 4 years > > > > and I've been doing it on a shoestring.My last MySQL > > > > server was a generic 1GHz system with 256MB RAM running > > > > Redhat 9. The performance was surprisingly good because the > > > > query loads were not typically high. One persistent problem > > > > was the initial connection times. On that old system if I > > > > had less than approx 10,000 separate databases then the > > > > connection times were "fast", and on the order of 1 second or > > > > so. If I had more than 10,000 databases this dramatically > > > > changed the connection times to well over 15 seconds or more. > > > > > > > > I always attributed this connection lag to a problem with the > > > > filesystem and the large number of directories. The old > > > > server had RH9 and ext3 with no htree support which I was > > > > told could help with this problem. > > > > > > > > I recently bought a new 2.4 GHz system with 1GB of RAM and > > > > installed Fedora 4 with ext3 and htree support. All new > > > > hardware, faster drives, more RAM and updated software. I > > > > thought I was golden!Well, I have 14,000 databases on > > > > this new system and it is as slow as the old 1GHz system. > > > > The tuning articles I've read, and the sample my-*.cnf files > > > > that ship with the tarball appear to apply to the more > > > > typical installation of a single huge database rather than > > > > thousands of individual dbs. Can anyone offer any > > > > suggestions? > > > > > > > > Thanks, > > > > > > > > Gary Huntress > > > > > > > > > > > > Gary, > > >Just a guess, but could the problem be the 14,000 > > > directories you have to store the 14,000 databases? The problem > > > could be the OS directory structure. Putting the data into fewer > > > databases will likely solve the problem or perhaps move half of > > > the directories to another drive. > > > > > > Mike > > > > > > > > > > > > > -- > > > > MySQL General Mailing List > > > > For list archives: http://lists.mysql.com/mysql > > > > To unsubscribe: > > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > Hi Gary, > > > > I think that Mike may have hit the nail on the head. I've a few unix > > directories with multiple thousand files and they do become a bit of > > a problem to manage speedwise. Perhaps, as Mike has suggested, place > > half of them on another drive. > > > > The other option could be to run multiple instances of MySQL, each > > having a different port number (this could be based on username or > > something similar) eg. A-D port 3306, E-H 3307, etc. and reducing the > > number of dbs per instance (server) that way. > > > > Regards > > > I agree with the diagnosis. I'm unsure how to move 1/2 the databases to a > new drive though. That would be the simplest solution. As I understand > it, MySQL will only use 1 data directory, so the best case would be > symlinks. I'm not sure about this but 15,000 symlinks to multiple drives > may be just as slow as 15,000 directory entries. Were either of you > thinking of another way to split up the directories?Unfortunately, > since I assign one database per user, I can't limit the number created. > > Multiiple servers may be my best option. > > Thanks, > > Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting number days between 2 dates
Use SELECT DATEDIFF('new_date', 'old_date');
mysql> SELECT DATEDIFF('2006-04-01','2006-04-01');
+-+
| DATEDIFF('2006-04-01','2006-04-01') |
+-+
| 0 |
+-----+
1 row in set (0.00 sec)
mysql> SELECT DATEDIFF('2006-04-01','2007-04-01');
+-+
| DATEDIFF('2006-04-01','2007-04-01') |
+-+
| -365 |
+-+
1 row in set (0.00 sec)
mysql> SELECT DATEDIFF('2006-04-01','2005-04-01');
+-+
| DATEDIFF('2006-04-01','2005-04-01') |
+-+
| 365 |
+-+
1 row in set (0.00 sec)
DATEDIFF(expr,expr2)
DATEDIFF() returns the number of days between the start date
expr and the end date expr2. expr and expr2 are date or
date-and-time expressions. Only the date parts of the values
are used in the calculation.
mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
-> 1
mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
-> -31
Regards
Keith
In theory, theory and practice are the same;
in practice they are not.
On Sat, 1 Apr 2006, Rhino wrote:
> To: Mike Blezien <[EMAIL PROTECTED]>,
> Jorrit Kronjee <[EMAIL PROTECTED]>, mysql@lists.mysql.com
> From: Rhino <[EMAIL PROTECTED]>
> Subject: Re: Getting number days between 2 dates
>
>
> - Original Message - From: "Mike Blezien"
> <[EMAIL PROTECTED]>
> To: "Jorrit Kronjee" <[EMAIL PROTECTED]>;
> Sent: Saturday, April 01, 2006 9:00 AM
> Subject: Re: Getting number days between 2 dates
>
>
> > Jorrit,
> >
> > - Original Message - From: "Jorrit Kronjee"
> > <[EMAIL PROTECTED]>
> > To:
> > Sent: Saturday, April 01, 2006 7:46 AM
> > Subject: Re: Getting number days between 2 dates
> >
> >
> > > Mike Blezien wrote:
> > > > Hello,
> > > >
> > > > I'm sure this is a simple query but haven't come up with a
> > > > good approach. Need to get the number of days between two
> > > > dates. IE: today's date: (2006-04-01 - 2006-03-05)
> > > > need to calculate the number of days between these dates..
> > > > what is the best query statement to accomplish this?
> > > >
> > > > TIA,
> >
> > > Mike,
> > > You probably want to use something like this:
> > >
> > > SELECT TO_DAYS('2006-03-15') - TO_DAYS('2006-03-01');
> >
> > Thanks, that works, also using the DAYOFYEAR produces the same
> > results as I just found :)
> >
> > appreciate the help
> >
> I'd be careful with DAYOFYEAR() if I were you.
>
> DAYOFYEAR() only tells you which day it is within a given year. If you try
> to use DAYOFYEAR to tell the difference in days between dates that are in
> different years, you are certainly going to get the wrong answer. For
> instance, DAYOFYEAR(2006-04-01) - DAYOFYEAR(2005-04-01) gives an answer of
> 0 days when the correct answer is 365.
>
> A better choice for getting the difference between two dates in days is
> probably DATEDIFF() or TO_DAYS().
>
> --
> Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
I'm not saying it is an apache issue Gary. I was just suggesting a way you might be able to implement several different mysql server instances, and still have one apache instance for your users to connect to via port 80. Each mysql server instance could be bound to an apache server instance via it's own php module. So each apache instance can talk to a different mysql server, via php, on a different unix socket. It may be possible for one apache listening on port 80 to forward requests to virtual servers, running on different apache servers on your machine, listening on different ports. That way your users will not have to define a different port to connect to. I've not tested this out yet, but it may be an option that would work for you. Hope this poor diagram make sense! - | apache main server port 80 | - | | | | | | | | | avs2 avs3 avs3 | | | --- | apache virtual server 1 port 81 | --- | v | php module 1 | | v -- | mysqld server 1 | -- ditto --- | apache virtual server x port xx | --- | v | php module x | | v -- | mysqld server x | -- Regards Keith On Sat, 1 Apr 2006, Gary Huntress wrote: > To: [EMAIL PROTECTED] > From: Gary Huntress <[EMAIL PROTECTED]> > Subject: Re: Tuning a Server with >10,000 databases > > I really don't think this is an apache issue. I get the same poor > connection speeds using the mysql client. > > Gary > > [EMAIL PROTECTED] wrote: > > I'm just wondering if it would be possible to use several apache > > servers on different ports and using virtual servers, that would each > > talk to a different instance of mysql, each running on different unix > > sockets? > > > > The main apache server listening on port 80 could then redirect > > requests to the other apache virtual server instances. > > > > Everything after that should be plain sailing I think. > > > > Best place to ask would probably be the apache user mailing list on > > this one. > > > > Regards > > > > Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
Gary - is there any way to check how many concurrent connections you have per second out of those 14,000 users? IIRC, each table requires at least one file handle to open the table's data file (.MYD), plus 1 more for the index file (.MYI) if the index file is updated. So, I'm wondering if you are running out of file handles, which would make mysql wait untill there are enough file handles free, for mysql to do it's job. open_files_limit The number of files that the operating system allows mysqld to open. This is the real value allowed by the system and might be different from the value you gave mysqld as a startup option. The value is 0 on systems where MySQL can't change the number of open files. mysql> show variables like "open%"; +--+---+ | Variable_name| Value | +--+---+ | open_files_limit | 1024 | +--+---+ 1 row in set (0.00 sec) mysql> show status like "open%"; +---+---+ | Variable_name | Value | +---+---+ | Open_files| 44| | Open_streams | 0 | | Open_tables | 20| | Opened_tables | 0 | +---+---+ 4 rows in set (0.00 sec) What does this return on your system? If this is the case, then I can't see how running multiple mysql servers will help. Keith On Sun, 2 Apr 2006, Alexey Polyakov wrote: > To: [EMAIL PROTECTED] > From: Alexey Polyakov <[EMAIL PROTECTED]> > Subject: Re: Tuning a Server with >10,000 databases > > On 4/1/06, Greg Whalin <[EMAIL PROTECTED]> wrote: > > > Not necessarily sure this is the problem. But if it is, it could be > > solved by switching to a different filesystem. Or, if you are using > > ext3fs, you could try enabling dir_index on the filesystem (tune2fs -O > > dir_index ... man tune2fs), which could give you a boost in performance > > in a large dir (this could take a long time to complete). You may also > > want to up your table cache so that mysql can keep more of your commonly > > used tables open? > > FWIW, I've experimented heavily with FS options, and found out that > dir_index on ext3 doesn't help at all, it actually harms performance. > 'noatime' and 'nodiratime' options do help a little. > Also, 14000 subdirectories is not something that will cause 15 seconds > delay - those 14000 subdirectories will always live in OS dentry cache > anyway. > > > -- > Alexey Polyakov > > -- > 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 4.0.18 on Mac OS X 10.2.8 won't start
Do you have some sort of visual process manager for Mac OS X that can tell you at a glance if mysqld_safe and mysql server are actually running in memory? Under linux I use a program called qps. http://www.student.nada.kth.se/~f91-men/qps/ You may already have a similar utility to view running processes under Mac OS X. Or there may be something similar you can download for free off the net. You really need some way of verifying that mysqld is actually running in memory, before attempting to connect to it. This is handy for showing running multiple servers, ie when upgrading to a newer version. You can see the port and socket each mysqld is listening to, plus other server directives such as the data directory and PID. I start mysqld directly with a bash shell script: #! /bin/sh # # start the MySQL database server /usr/local/mysql-5.0.18/bin/mysqld \ --defaults-file=/usr/local/mysql-5.0.18/my.cnf \ --port=7000 \ --socket=/var/lib/mysql/mysql.sock \ --pid=/var/lib/mysql/laptop.pid \ --user=mysql \ --datadir=/var/lib/mysql & and stop it with: #! /bin/sh # # stop the MySQL database server /usr/local/mysql-5.0.18/bin/mysqladmin shutdown \ -uXX -pXX --socket=/var/lib/mysql/mysql.sock If I don't use the script to pass parameters to mysqld but add them to my.cnf, they will not appear in qps process manager. I have noticed that sometimes mysqld_safe script would start, and be in memory, but the mysqld server was not being loaded into memory for some reason, which obviuosly meant I could not connect to the mysql server. For that reason I no longer use mysqld_safe to start mysqld. HTH Keith In theory, theory and practice are the same; in practice they are not. On Sun, 2 Apr 2006, Sachin Petkar wrote: > To: mysql@lists.mysql.com > From: Sachin Petkar <[EMAIL PROTECTED]> > Subject: MySQL 4.0.18 on Mac OS X 10.2.8 won't start > > For some reason, MySQL 4.0.18 has suddenly stopped running and will not > start anymore. > > It has been running for several weeks until about 5 days > ago. When I tried to reach it, I discovered that it is no > longer running. However, attempting to start it via the > mysqld_safe script simply returns with: > > Starting mysqld daemon with databases from /usr/local/mysql/data > 060402 18:49:55 mysqld ended > > [1]Done ./mysqld_safe --user mysql > > > To confirm, the /tmp/mysql.sock file does not exist at this point. > > Any ideas on how to get this running again? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.18 on Mac OS X 10.2.8 won't start
Here are some screen snapshots of qps showing mysql server running on my machine. I tries to post these to the list, but they went over the file size limit for the mailing list. Showing mysql running in memory without using mysqld_safe script: http://www.karsites.net/KAR/websites/pub/computing/mysql-running/mysqld1.jpg First part of command-line parameters passed to mysqld: http://www.karsites.net/KAR/websites/pub/computing/mysql-running/mysqld2.jpg Second part of command-line parameters passed to mysqld: http://www.karsites.net/KAR/websites/pub/computing/mysql-running/mysqld3.jpg Regards Keith In theory, theory and practice are the same; in practice they are not. On Sun, 2 Apr 2006, Sachin Petkar wrote: > To: mysql@lists.mysql.com > From: Sachin Petkar <[EMAIL PROTECTED]> > Subject: MySQL 4.0.18 on Mac OS X 10.2.8 won't start > > For some reason, MySQL 4.0.18 has suddenly stopped running and will not > start anymore. > > It has been running for several weeks until about 5 days > ago. When I tried to reach it, I discovered that it is no > longer running. However, attempting to start it via the > mysqld_safe script simply returns with: > > Starting mysqld daemon with databases from /usr/local/mysql/data > 060402 18:49:55 mysqld ended > > [1] Done ./mysqld_safe --user mysql > > > To confirm, the /tmp/mysql.sock file does not exist at this point. > > Any ideas on how to get this running again? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stunningly slow query
On Sun, 2 Apr 2006, Chris Kantarjiev wrote: > To: mysql@lists.mysql.com > From: Chris Kantarjiev <[EMAIL PROTECTED]> > Subject: Re: stunningly slow query > > > The problem with Load Data is the larger the table, the > > slower it gets because it has to keep updating the index > > during the loading process. > > Um, thanks. I'm not sure how Load Data got involved here, because > that's not what's going on. > > > > > > It's a MyISAM table. Are there separate logs files? If so, where? > > > I thought it was just .frm, .MYI and .MYD. We have MYI and MYD on > > > separate drives. > > > > Log files usually default to the mysql data directory, eg. > > /var/lib/mysql/ > > As I said, I don't think there are any log files for a MyISAM table. > InnoDB has separate logs. > > > > > Putting the database files on seperate drives may slow > > things down alot too - unless others know better. > > > > .frm is the database definition file. .MYI is the index > > file, and .MYD is the data file. There is one each of these > > files for each myisam table in the database. > > > > I may be wrong, but I would have thought it better if these > > are all together on the same disk and partition for each > > table in the database? > > This is counter-intuitive. Separating .MYI and .MYD means that > I can overlap the i/o. This is a standard strategy for other > databases (Oracle, in particular). I would be really surprised > if this was causing my problem. OK - something new I've just learnt Chris. > > This feature can be activated explicitly. ALTER TABLE ... > > DISABLE KEYS tells MySQL to stop updating non-unique indexes > > for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should > > be used to re-create missing indexes. > > > Can you post your show create table tbl_name statement for > > these tables that involve slow queries? > > | old_crumb |CREATE TABLE `old_crumb` ( > `link_ID` bigint(20) default NULL, > `dir_Travel` char(1) default NULL, > `customer_ID` int(11) NOT NULL default '0', > `source_ID` int(11) NOT NULL default '0', > `vehicle_ID` int(11) NOT NULL default '0', > `actual_Time` datetime NOT NULL default '-00-00 00:00:00', > `actual_TZ` varchar(30) default NULL, > `reported_Time` datetime default NULL, > `reported_TZ` varchar(30) default NULL, > `speed_Format` int(11) default NULL, > `speed` float default NULL, > `direction` char(2) default NULL, > `compass` int(11) default NULL, > `speed_NS` float default NULL, > `speed_EW` float default NULL, > `distance` decimal(10,0) default NULL, > `duration` decimal(10,0) default NULL, > `latitude` decimal(10,5) default NULL, > `longitude` decimal(10,5) default NULL, > `report_Landmark` varchar(255) default NULL, > `report_Address` varchar(255) default NULL, > `report_Cross` varchar(255) default NULL, > `report_City` varchar(255) default NULL, > `report_State` char(2) default NULL, > `report_Zip` varchar(10) default NULL, > `report_County` varchar(255) default NULL, > `category` int(11) default NULL, > `speed_Limit` int(11) default NULL, > `street` varchar(255) default NULL, > `city` varchar(255) default NULL, > `state` char(2) default NULL, > `zip` varchar(10) default NULL, > `county` varchar(255) default NULL, > `match_Name` tinyint(1) default NULL, > `name_Matched` tinyint(1) default NULL, > `last_Modified` datetime default NULL, > PRIMARY KEY (`customer_ID`,`source_ID`,`vehicle_ID`,`actual_Time`), > KEY `old_crumb_ix_reported_Time` (`reported_Time`), > KEY `old_crumb_ix_link_ID` (`link_ID`,`dir_Travel`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=10 COMMENT='List of > breadcrumbs already rolled up.' INDEX DIRECTORY='/var/mysql_idx/trimble/' | I'm no DB expert Chris but do you really need to create a primary key index over 4 columns? What about something simple and possibly faster like adding a seperate ID primary key column to the table like: | old_crumb |CREATE TABLE `old_crumb` ( `ID` int unsigned not null auto_increment `link_ID` bigint(20) default NULL, `dir_Travel` char(1) default NULL, `customer_ID` int(11) NOT NULL default '0', `source_ID` int(11) NOT NULL default '0', `vehicle_ID` int(11) NOT NULL default '0', snip PRIMARY KEY (`ID`), snip An unsigned int will take an extra 4 bytes of storage space per row, and will give you an index range of 0 - 4294967295. If that is not enough range, an unsigned bigint will take an extra 8 bytes
Re: stunningly slow query
On Mon, 3 Apr 2006 [EMAIL PROTECTED] wrote: > To: [EMAIL PROTECTED] > From: [EMAIL PROTECTED] > Subject: Re: stunningly slow query > > [EMAIL PROTECTED] wrote on 04/02/2006 05:35:59 PM: > > > > > > Can you post your show create table tbl_name statement for > > > > these tables that involve slow queries? > > > > > > | old_crumb |CREATE TABLE `old_crumb` ( > > > `link_ID` bigint(20) default NULL, > > > `dir_Travel` char(1) default NULL, > > > `customer_ID` int(11) NOT NULL default '0', > > > `source_ID` int(11) NOT NULL default '0', > > > `vehicle_ID` int(11) NOT NULL default '0', > > > `actual_Time` datetime NOT NULL default '-00-00 00:00:00', > > > > `last_Modified` datetime default NULL, > > > PRIMARY KEY (`customer_ID`,`source_ID`,`vehicle_ID`,`actual_Time`), > > > KEY `old_crumb_ix_reported_Time` (`reported_Time`), > > > KEY `old_crumb_ix_link_ID` (`link_ID`,`dir_Travel`) > > > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=10 > > COMMENT='List of breadcrumbs already rolled up.' INDEX > > DIRECTORY='/var/mysql_idx/trimble/' | > > > > I'm no DB expert Chris but do you really need to create a > > primary key index over 4 columns? > > > > What about something simple and possibly faster like adding > > a seperate ID primary key column to the table like: > > > > | old_crumb |CREATE TABLE `old_crumb` ( > >`ID` int unsigned not null auto_increment > >`link_ID` bigint(20) default NULL, > >`dir_Travel` char(1) default NULL, > >`customer_ID` int(11) NOT NULL default '0', > >`source_ID` int(11) NOT NULL default '0', > >`vehicle_ID` int(11) NOT NULL default '0', > > snip > >PRIMARY KEY (`ID`), > > snip > > > > An unsigned int will take an extra 4 bytes of storage space > > per row, and will give you an index range of 0 - 4294967295. > > > > If that is not enough range, an unsigned bigint will take an > > extra 8 bytes of storage space, and will give you an index > > range of 0 - 18446744073709551615. > > > > Although this will increase the amount of storage space > > required in the .MYD file, it may also decrease the amount > > of space required in the .MYI index file, as you would not > > be needing to store multi-column indexes. > > > > Keith, > Your method won't guarantee that there are no rows where the combination > of the values in those four columns fails to repeat in any other row. To > do that would require an EXTRA four-column unique index of type UNIQUE. > Your proposal would actually make the situation worse as now there would > be two indexes to maintain to achieve the same effect as the previous > single PK. Thankyou for your expert reply Shawn. Is it not possible to mark each of those those column values as UNIQUE without them becoming a part of the index as well? Or is this a contradiction in terms? Regards Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undelete rows with .MYD-File?
If those three files were backed up some where before you dropped the table all you need to do is to copy them back into the data dir, and things should be ok again. Keith In theory, theory and practice are the same; in practice they are not. On Mon, 3 Apr 2006, Nico Schefer wrote: > To: mysql@lists.mysql.com > From: Nico Schefer <[EMAIL PROTECTED]> > Subject: Undelete rows with .MYD-File? > > Hi! > > Today i've dropped a MySQL-table and realized seconds later that i've > dropped the wrong one.. I've saved the .MYD-File and tried to recover it > with a tool (MySQLRecovery 1.5), but it just recovered the structure and > not the data (about 620 rows). > I've searched now all the day and not found anything. Maybe someone knows > how to bring the data back or has got any hint for me? > > Binary log is not enabled unfortunately... > > http: //www.swissmade.com/mysql/shop_item.MYD > http: //www.swissmade.com/mysql/shop_item.MYI > http: //www.swissmade.com/mysql/shop_item.frm > > Thanks a lot! > > Nico > > -- > Nico Schefer > [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undelete rows with .MYD-File?
I have downloaded the table files and this is what I get: mysql> use swissmade; Database changed mysql> show tables; +-+ | Tables_in_swissmade | +-+ | shop_item | +-+ 1 row in set (0.00 sec) mysql> select * from shop_item \G Empty set (0.00 sec) Do you have any copies of the table files that you have not run the recovery program on? Keith In theory, theory and practice are the same; in practice they are not. On Mon, 3 Apr 2006, Nico Schefer wrote: > To: mysql@lists.mysql.com > From: Nico Schefer <[EMAIL PROTECTED]> > Subject: Undelete rows with .MYD-File? > > Hi! > > Today i've dropped a MySQL-table and realized seconds later that i've > dropped the wrong one.. I've saved the .MYD-File and tried to recover it > with a tool (MySQLRecovery 1.5), but it just recovered the structure and > not the data (about 620 rows). > I've searched now all the day and not found anything. Maybe someone knows > how to bring the data back or has got any hint for me? > > Binary log is not enabled unfortunately... > > http: //www.swissmade.com/mysql/shop_item.MYD > http: //www.swissmade.com/mysql/shop_item.MYI > http: //www.swissmade.com/mysql/shop_item.frm > > Thanks a lot! > > Nico > > -- > Nico Schefer > [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: Undelete rows with .MYD-File?
IIRC there may be a hidden field in each table row that mysql uses to mark that row as deleted. I'm not sure if you can use some mysql utility program such as myisamchk to undelete the rows. This may be possible. Regards Keith In theory, theory and practice are the same; in practice they are not. On Mon, 3 Apr 2006 [EMAIL PROTECTED] wrote: > To: mysql@lists.mysql.com > From: [EMAIL PROTECTED] > Subject: Re: Undelete rows with .MYD-File? > > > I have downloaded the table files and this is what I get: > > mysql> use swissmade; > Database changed > mysql> show tables; > +-+ > | Tables_in_swissmade | > +-+ > | shop_item | > +-----+ > 1 row in set (0.00 sec) > > mysql> select * from shop_item \G > Empty set (0.00 sec) > > Do you have any copies of the table files that you have not > run the recovery program on? > > Keith > > In theory, theory and practice are the same; > in practice they are not. > > > On Mon, 3 Apr 2006, Nico Schefer wrote: > > > To: mysql@lists.mysql.com > > From: Nico Schefer <[EMAIL PROTECTED]> > > Subject: Undelete rows with .MYD-File? > > > > Hi! > > > > Today i've dropped a MySQL-table and realized seconds later that i've > > dropped the wrong one.. I've saved the .MYD-File and tried to recover it > > with a tool (MySQLRecovery 1.5), but it just recovered the structure and > > not the data (about 620 rows). > > I've searched now all the day and not found anything. Maybe someone knows > > how to bring the data back or has got any hint for me? > > > > Binary log is not enabled unfortunately... > > > > http: //www.swissmade.com/mysql/shop_item.MYD > > http: //www.swissmade.com/mysql/shop_item.MYI > > http: //www.swissmade.com/mysql/shop_item.frm > > > > Thanks a lot! > > > > Nico > > > > -- > > Nico Schefer > > [EMAIL PROTECTED] > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undelete rows with .MYD-File?
karsites:/var/swissmade # ./myisamchk -dvv shop_item.MYI MyISAM file: shop_item.MYI Record format: Packed Character set: latin1_swedish_ci (8) File-version:1 Creation time: 2006-03-31 13:59:48 Status: open,changed Auto increment key: 1 Last value: 673 Data records: 0 Deleted blocks: 675 Datafile parts: 675 Deleted data: 33760 Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4 Datafile length: 33760 Keyfile length: 8192 Max datafile length: 4294967294 Max keyfile length: 4398046510079 Recordlength: 295 It looks like someone has packed this table with myisampack, which means it's read only. Did you use myisampack on the table before it got dropped Nico? Also, did mysql die when the table was in use? I have tries to run myisamchk -r shop_item.MYI but this sets the *.MYD file to zero. I don't know if it is possible to recover data from a packed table. AFAIK the packing process is one way, and you may need the original non-compressed table to get your data. Regards Keith On Mon, 3 Apr 2006, Nico Schefer wrote: > To: [EMAIL PROTECTED] > From: Nico Schefer <[EMAIL PROTECTED]> > Subject: Re: Undelete rows with .MYD-File? > > Hi > > Keith, thanks for your proposal with myisamchk. > > If i'm using the myisamchk, it finds the deleted rows, but i have not > found a way to restore them and i can't find nothing in the manual.. > > Checking MyISAM file: shop_item.MYI > Data records: 0 Deleted blocks: 675 > myisamchk.exe: warning: 1 client is using or hasn't closed the table > properly > - check file-size > - check record delete-chain > - check key delete-chain > - check index reference > - check data record references index: 1 > - check record links > MyISAM-table 'shop_item.MYI' is usable but should be fixed > > Does anybody know how to restore the data this way? > > Thanks a lot, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undelete rows with .MYD-File?
As my server does not get alot of traffic I tend to shutdown mysql, and do an OS copy of the complete /var/lib/mysql directory to another partition on another drive. Then restart mysql again. This may not be a feasable option on a busy server. Obviously each person has their way of doing backups. I do need to study the manual myself on all available backup options. Regards Keith On Mon, 3 Apr 2006, Nico Schefer wrote: > To: [EMAIL PROTECTED] > From: Nico Schefer <[EMAIL PROTECTED]> > Subject: Re: Undelete rows with .MYD-File? > > Hi Keith > > > It looks like someone has packed this table with myisampack, > > which means it's read only. Did you use myisampack on the > > table before it got dropped Nico? > > Thanks a lot for looking at the problem. As far as i know the table has > not been compressed, and i've not used myisampack. I'v tried to run > myisamchk -e -r as well, but is has trunctated my MYD-File as well. > I think i have to live with it, i begun to recunstruct the data by hand > wich took me the whole day now and i'm not finished by far.. but well, > i'll certainly do some backups now ;-) > > Thanks and greetings, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undelete rows with .MYD-File?
Thankyou for your reply Dilipkumar. Much appreciated. Regards Keith On Tue, 4 Apr 2006, Dilipkumar wrote: > To: [EMAIL PROTECTED] > From: Dilipkumar <[EMAIL PROTECTED]> > Subject: Re: Undelete rows with .MYD-File? > > Hi, > > If it is a busry server you cannot copy MYD & FRM file * MYI files to > another data ditrectory. > Try to take a dump or try using snapshot your datadirectory. > If it is a less MB database you can user as: > In mysql prompt. > flush tables with read locks > dont exit you mysql terminal > Open an another terminal tar -cvzf you mysql old datadirectory and then > after doing so > give in 1st prompt flush tables. > Then Untar your tar files to your new data-direcotry. > > This might help you out. > > [EMAIL PROTECTED] wrote: > > > As my server does not get alot of traffic I tend to shutdown mysql, > > and do an OS copy of the complete /var/lib/mysql directory to another > > partition on another drive. Then restart mysql again. > > > > This may not be a feasable option on a busy server. > > > > Obviously each person has their way of doing backups. I do need to > > study the manual myself on all available backup options. > > > > Regards > > > > Keith > > > > > > On Mon, 3 Apr 2006, Nico Schefer wrote: > > > > > > > > > To: [EMAIL PROTECTED] > > > From: Nico Schefer <[EMAIL PROTECTED]> > > > Subject: Re: Undelete rows with .MYD-File? > > > > > > Hi Keith > > > > > > > > > > > > > It looks like someone has packed this table with myisampack, > > > > which means it's read only. Did you use myisampack on the > > > > table before it got dropped Nico? > > > > > > > > > > > Thanks a lot for looking at the problem. As far as i know the > > > table has > > > not been compressed, and i've not used myisampack. I'v tried to > > > run > > > myisamchk -e -r as well, but is has trunctated my MYD-File as > > > well. > > > I think i have to live with it, i begun to recunstruct the data by > > > hand > > > wich took me the whole day now and i'm not finished by far.. but > > > well, > > > i'll certainly do some backups now ;-) > > > > > > Thanks and greetings, Nico > > > > > > > > > > > > > > > -- > Thanks & Regards, > Dilipkumar > DBA Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stunningly slow query
I have just noticed this from section 13.4.5. LOCK TABLES and UNLOCK TABLES Syntax of the 5.0.18 ref manual, and wondered if it will help improve the speed of your query: Normally, you do not need to lock tables, because all single UPDATE statements are atomic; no other thread can interfere with any other currently executing SQL statement. However, there are a few cases when locking tables may provide an advantage: If you are going to run many operations on a set of MyISAM tables, it is much faster to lock the tables you are going to use. Locking MyISAM tables speeds up inserting, updating, or deleting on them. The downside is that no thread can update a READ-locked table (including the one holding the lock) and no thread can access a WRITE-locked table other than the one holding the lock. The reason some MyISAM operations are faster under LOCK TABLES is that MySQL does not flush the key cache for the locked tables until UNLOCK TABLES is called. Normally, the key cache is flushed after each SQL statement. Regards Keith Roberts In theory, theory and practice are the same; in practice they are not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax Error
Hi Mark. On Fri, 7 Apr 2006, Mark Sargent wrote: > To: mysql@lists.mysql.com > From: Mark Sargent <[EMAIL PROTECTED]> > Subject: Syntax Error > > Hi All, > > am trying to get up to speed on cli syntax again, > > mysql> show open tables from osc > -> > > what is wrong with the command above and the one below, > > mysql> show tables from osc > -> Although it is not mentioned in the syntax diagram in the manual, you need to terminate a mysql command with ';', like this: mysql> show tables from osc; The reason for this is that mysql allows you to spread a command over many lines, which can be helpfull, eg: mysql> show create table bible_quiz_question \G *** 1. row *** Table: bible_quiz_question Create Table: CREATE TABLE `bible_quiz_question` ( `ID` mediumint(8) unsigned NOT NULL auto_increment, `question_text` text NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> select ID, question_text -> from bible_quiz_question -> where ID = 1 -> ; ++---+ | ID | question_text | ++---+ | 1 | How old was the first man Adam, when he died? | ++---+ 1 row in set (0.00 sec) So mysql will not execute the select query above, untill it sees the ';' that terminates the command. This is why you were getting: > mysql> show tables from osc > -> because mysql was waiting for you to type something else in, or terminate the command with ';'. If you have problems displaying output because it is to large to fit into the table output format, you can terminate the mysql command with: mysql> show tables from osc \G instead of: mysql> show tables from osc; HTH Regards Keith > Why do I not get any output? I was following here, > > http://dev.mysql.com/doc/refman/5.1/en/show-open-tables.html > > I'm a Linux user, and wish to do everything via cli as opposed to > phpmyadmin. That's a good way to learn how to use mysql properly. phpmyadmin is a usefull tool for people that allready know how to use mysql via the mysql monitor program (CLI program). > Cheers. > > Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need to select correct package
Hi balaraju. You could try: Linux x86 generic RPM (statically linked against glibc 2.2.5) downloads Server 5.0.19-013.5M Pick a mirror MD5: 118abbb9c8ee5ff212fd2797fcde35e8 Max 5.0.19-02.8MPick a mirror MD5: a49c484f561753678319678c1cfcc6c3 Benchmark/test suites 5.0.19-05.6MPick a mirror MD5: ff11b603da7544c4bb7b8e2c7b93997c Client programs 5.0.19-06.1MPick a mirror MD5: 5b3e0d88e11ddc7f4a97aecefb12b36e Libraries and header files 5.0.19-03.5M Pick a mirror MD5: 6a940c8a4123c4c733e55c77218e230f Shared client libraries 5.0.19-01.7MPick a mirror MD5: d7a4016797424da3dc957643b45e3076 Shared compatibility libraries (3.23, 4.x, 5.x libs in same package) 5.0.19-0 3.3MPick a mirror MD5: b55dd27aafeb22b22c3cd8a26120dfa4 Or if that does not work, go for: Linux (non RPM package) downloads (platform notes) Linux (x86, glibc-2.2, "standard" is static, gcc) Standard5.0.19 28.4M Pick a mirror MD5: c979236136e416497d951a84e28c676e | Signature Max 5.0.19 36.8M Pick a mirror MD5: 524f6e26065aaf0ed5e55d77aef81305 | Signature Debug 5.0.19 53.4M Pick a mirror MD5: 5cd9e1694b8b20443613627755260f3b | Signature Make sure you download the statically linked versions, as they do not rely on external libraries to work. Both the above packages are pre-compiled and either of them should work ok. The RPM version is the easiest to install, but IMO using the non-RPM version will give you the most flexibility if you want to upgrade to a later version of mysql. I guess you could try and install the RPM version first, and then try the non-RPM version later on if you want to upgrade. Regards Keith In theory, theory and practice are the same; in practice they are not. On Fri, 7 Apr 2006, balaraju mandala wrote: > To: "mysql@lists.mysql.com" > From: balaraju mandala <[EMAIL PROTECTED]> > Subject: Re: need to select correct package > > I am planning to upgrade mysql ver 4 to mysql ver 5 but i am getting this > errors > > [EMAIL PROTECTED] trinity]$rpm -Uvh > MySQL-server-standard-5.0.19-0.rhel4.i386.rpm > warning: MySQL-server-standard-5.0.19-0.rhel4.i386.rpm: V3 DSA signature: > NOKEY, key ID 5072e1f5 > error: Failed dependencies: > libmysqlclient.so.14 is needed by (installed) mod_auth_mysql- > 2.6.1-2.2.i386 > libmysqlclient.so.14 is needed by (installed) > cyrus-sasl-sql-2.1.19-5.EL4.i386 > libmysqlclient.so.14 is needed by (installed) > dovecot-0.99.11-2.EL4.1.i386 > libmysqlclient.so.14 is needed by (installed) > perl-DBD-MySQL-2.9004-3.1.i386 > libmysqlclient.so.14 is needed by (installed) > php-mysql-4.3.9-3.8.i386 > > Please can anybody help me -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: any suodo columns are there Like Rowid, rownum in mysql
from the mysql 5.0.18 manual section 13.1.5. CREATE TABLE Syntax If a PRIMARY KEY or UNIQUE index consists of only one column that has an integer type, you can also refer to the column as _rowid in SELECT statements. Regards Keith In theory, theory and practice are the same; in practice they are not. On Mon, 10 Apr 2006, Veerabhadra rao Narra wrote: > To: mysql@lists.mysql.com > From: Veerabhadra rao Narra <[EMAIL PROTECTED]> > Subject: any suodo columns are there Like Rowid, rownum in mysql > > > any suodo columns are there Like Rowid, rownum in mysql > -- > Thanks & Regards, > veerabhadra rao narra, > +91-988-556-5556 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Any limits on Database Size?
Hi Jim - see this for your answers. 1.4.4. How Large MySQL Tables Can Be http://dev.mysql.com/doc/refman/5.0/en/table-size.html Regards Keith In theory, theory and practice are the same; in practice they are not. On Tue, 11 Apr 2006, Jim wrote: > To: mysql@lists.mysql.com > From: Jim <[EMAIL PROTECTED]> > Subject: Any limits on Database Size? > > > > Hi All, > > We used to use Interbase which required a new file to be > assigned for every 4 gig of data stored in a DB. Is there > any issues like this in mySQL? > > Thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
