Start mysqld 5.7.7 with new disk
Hello I had to replace the datadisk where mysql had its innodb files. But after I had added a new disk I'm not able to initialize mysql files any more: # mysqld --initialize --user=mysql 2015-12-23T13:07:08.216472Z 0 [ERROR] Plugin 'InnoDB' init function returned error. 2015-12-23T13:07:08.216556Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2015-12-23T13:07:08.216577Z 0 [ERROR] Failed to initialize plugins. 2015-12-23T13:07:08.216595Z 0 [ERROR] Aborting I have created the directories stored in my /etc/my.cnf and changed group and ownership to the directories to mysql:mysql Any suggestions how to fix this? -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Start mysqld 5.7.7 with new disk
On Wednesday, December 23, 2015, Jørn Dahl-Stamnes wrote: > Hello > > I had to replace the datadisk where mysql had its innodb files. > > But after I had added a new disk I'm not able to initialize mysql files any > more: > > # mysqld --initialize --user=mysql > 2015-12-23T13:07:08.216472Z 0 [ERROR] Plugin 'InnoDB' init function > returned error. 2015-12-23T13:07:08.216556Z 0 [ERROR] Plugin 'InnoDB' > registration as a STORAGE ENGINE failed. 2015-12-23T13:07:08.216577Z 0 > [ERROR] Failed to initialize plugins. 2015-12-23T13:07:08.216595Z 0 > [ERROR] Aborting Is'nt it typical... after I posted my message I found the error in my /etc/my.cnf file. I had changed this line: innodb_data_file_path = ibdata1:60G;ibdata2:60G;ibdata3:60G;ibdata4:60G:;ibdata5:60G:autoextend Notice the extra column in "ibdata4:60G:". After I removed it, it worked. -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Sending a fax
On Monday, June 15, 2015, Trianon 33 wrote: All, Maybe a somewhat weird request: I need to verufy my fax can receive foreign faxes. If someone (one is really enough) send me a (preferrably 2 pager) fax on +31848708584 (destination is in The Netherlands) I hope noone is that stupid. I'm sure it cost the sender a lot to send fax/call that number! -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
Found the cause. sync_binlog was set to 1. I suspect that the default value is 5.5 was 0 and that is has changed to 1 sometime after that. Setting it to 0 boosted the performance back to normal (4x speed) and the HD LED indicated much lower stress on the hard disk. Found this after I found out what caused it: https://www.percona.com/blog/2009/01/21/beware-ext3-and-sync-binlog-do-not-play-well-together/ I suspect that this also apply to ext4, or? -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL 5.7 Innodb performans issue
Hello (again I must say). Over a year ago I experienced a severe drop in the MySQL Innodb performance after ugrading to MySQL 5.6. I did not found any solution to that so I downgraded back to 5.5.33 and lived with in until recently. After a system disk crash I replaced the system disk with an identical disk, upgraded the test server to Fedora Core 21 and installed MySQL 5.7.7 by downloading RPM files. Now I'm facing the same problem with poor Innodb performance. How have I messured the performance? I got a data collector system that get new data from mails (stored as files). The system read the files, store data in the database and then prerform a lot of calulcations on the data. The performance is messured by how many jobs the system can process when putting months of mails into a queue at once. While I was using 5.5.53 it processed about 2000 jobs per minute with a peak up to 2500. Each time I test the system I use an initial database and the same mail files, so the input is always the same. With 5.7.7 the performance has dropped by a factor 4 - about 500-600 jobs per minute. I have played with the settings in my.cnf file but nothing seem to have any influence on the performance. So I'm using more or less the same settings for both version. Another thing I have noticed is that when running the test with MySQL 5.5.33, the hard disk LED was mostly dark. But now, it is flashing like h**l. So it seems like the problem has to do with the disk activity. I would be very glad to get some feedback on this that would bring my performance back to normal :) About the server: System disk (where the binlogs are stored) is a regular magnetic disk. All Innodb files are stored on a SSD disk mounted with defaults, nouser_xattr,noatime,data=writeback,barrier=0 The system got 32 Gb memory and have an AMD 8-core CPU (AMD FX-8120). This is my.cnf I'm currently using: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock explicit_defaults_for_timestamp = TRUE # Logging slow_query_log_file = /var/log/mysql-slow.log slow_query_log = 1 long_query_time = 10 log_queries_not_using_indexes = OFF skip-external-locking key_buffer_size = 384M max_allowed_packet = 32M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 max_connections = 50 join_buffer_size = 64M # Replication Master Server (default) # binary logging is required for replication log-bin=/var/mysql/mysql-bin server-id = 1 binlog_format=mixed # Innodb settings. innodb_open_files = 2048 open_files_limit= 8096 innodb_data_home_dir= /data/mysql/data innodb_data_file_path = ibdata1:20G;ibdata2:20G;ibdata3:20G;ibdata4:20G:autoextend innodb_file_per_table = 0 innodb_autoextend_increment = 256 innodb_log_group_home_dir = /data/mysql/data innodb_buffer_pool_size = 25G innodb_log_file_size= 300M innodb_log_files_in_group = 2 innodb_log_buffer_size = 128M innodb_flush_log_at_trx_commit = 1 innodb_support_xa = 0 innodb_flush_method = O_DIRECT innodb_lock_wait_timeout= 50 innodb_thread_concurrency = 16 innodb_fast_shutdown= 0 [mysql] no-auto-rehash -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
14.80 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 995 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 3.80/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 12269, unzip_LRU len: 0 I/O sum[0]:cur[1], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 204775 Free buffers 192081 Database pages 12645 Old database pages 4687 Modified db pages 93 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 12644, created 1, written 155 16.50 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 990 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 3.90/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 12645, unzip_LRU len: 0 I/O sum[0]:cur[1], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 204775 Free buffers 192108 Database pages 12617 Old database pages 4677 Modified db pages 366 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 1, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 12614, created 3, written 194 22.20 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 993 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 11.20/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 12617, unzip_LRU len: 0 I/O sum[0]:cur[1], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 204775 Free buffers 191980 Database pages 12744 Old database pages 4724 Modified db pages 214 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 12736, created 8, written 172 20.10 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 990 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 7.50/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 12744, unzip_LRU len: 0 I/O sum[0]:cur[1], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 204775 Free buffers 192376 Database pages 12349 Old database pages 4578 Modified db pages 198 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 3, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 12328, created 21, written 158 26.70 reads/s, 0.10 creates/s, 0.00 writes/s Buffer pool hit rate 989 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 11.60/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 12349, unzip_LRU len: 0 I/O sum[0]:cur[1], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 204775 Free buffers 191857 Database pages 12865 Old database pages 4768 Modified db pages 236 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 8, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 12855, created 10, written 165 25.30 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 991 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 4.90/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 12865, unzip_LRU len: 0 I/O sum[0]:cur[1], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 204800 Free buffers 191992 Database pages 12758 Old database pages 4729 Modified db pages 134 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 12723, created 35, written 194 20.50 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 986 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 12758, unzip_LRU len: 0 I/O sum[0]:cur[1], unzip sum[0]:cur[0] -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=23924, Main thread ID=140300711941888, state: sleeping Number of rows inserted 16718, updated 6422, deleted 12070, read 18051815 34.80 inserts/s, 21.00 updates/s, 31.40 deletes/s, 113447.76 reads/s END OF INNODB MONITOR OUTPUT 1 row in set (0.01 sec) -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
On Wednesday, May 20, 2015, Morgan Tocker wrote: Hi Jørn, Wagner’s point about SHOW ENGINE INNODB STATUS is a good one. A couple of other questions about your workload: - The data collector system processing jobs, is it multi threaded? Sorry, forgot about that. No, it is not multi threaded. It is a PHP bases system using several script running sequently in an infinite loop. Each script taking care of part of the job of processing the data. - Do you have a sample schema + set of queries we could look at? (We pay close attention to regressions.) This will be BIG, since it it so many different queries and tables. Not sure what you mean by We pay close attention to regressions. In terms of your configuration: I would usually recommend assuming the default values for some of the settings you’ve specified (table_open_cache, sort_buffer_size, thread_cache_size, innodb_log_buffer_size, innodb_thread_concurrency..). A 25G buffer pool on a 32G server with some of your other buffers being quite large is something you may need to look into too. I know, but sofare no swapping is taking place. The test server is used for testing this system only. And in 99% of the time, only one client is using the SQL server. -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.7 Innodb performans issue
On Wednesday, May 20, 2015, wagnerbianchi.com wrote: I'd like to add to the Morgan's note that if you want to restrict the number of transactions inside InnoDB kernel to 16, you need at least configure the tickets... = http://www.pythian.com/blog/once-again-about-innodb-concurrency-tickets/ BTW, leave it as its default, IMHO, innodb_thread_concurrency=0, is better... Changed it to 0 and restarted MySQL. Now the test will be running all night long... (a full test took 3+ weeks with 5.5 and I expect 12-14 weeks with the speed of 5.7). But as I wrote before, it seems like 5.7 causing a much higher disk load than 5.5 based on how the HD LED is flashing. It could be off for a long time (a second or even more) with 5.5 while now it is hardly off at all. On Wednesday, May 20, 2015, Morgan Tocker morgan.toc...@oracle.com wrote: It looks from show engine innodb status that your server is just starting up, and caches are empty, so versus a 5.5 server that has been running for a while it will likely be slower. It this is the case I have never experienced this behavior. The PHP scripts write output about everything that is happening. And the speed of the output tells me how fast things goes. With 5.5 the speed was much higher right from the start (after a reboot and starting up the test). -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Starting mysql 5.7.7
is not usable without certificate and private key May 15 09:42:57 anakin mysqld: 2015-05-15T07:42:57.975663Z 0 [Note] Server hostname (bind-address): '*'; port: 3306 May 15 09:42:57 anakin mysqld: 2015-05-15T07:42:57.975705Z 0 [Note] IPv6 is available. May 15 09:42:57 anakin mysqld: 2015-05-15T07:42:57.975725Z 0 [Note] - '::' resolves to '::'; May 15 09:42:57 anakin mysqld: 2015-05-15T07:42:57.975733Z 0 [Note] Server socket created on IP: '::'. May 15 09:42:57 anakin mysqld: 2015-05-15T07:42:57.984931Z 0 [Warning] InnoDB: Cannot open table mysql/server_cost from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. May 15 09:42:57 anakin mysqld: 2015-05-15T07:42:57.984972Z 0 [Warning] Failed to open optimizer cost constant tables May 15 09:42:57 anakin mysqld: 2015-05-15T07:42:57.985645Z 0 [Warning] InnoDB: Cannot open table mysql/time_zone_leap_second from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. May 15 09:42:57 anakin mysqld: 2015-05-15T07:42:57.985661Z 0 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them May 15 09:42:57 anakin mysqld: 2015-05-15T07:42:57.986086Z 0 [Warning] InnoDB: Cannot open table mysql/servers from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. May 15 09:42:57 anakin mysqld: 2015-05-15T07:42:57.986100Z 0 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist May 15 09:42:57 anakin mysqld: 2015-05-15T07:42:57.997527Z 0 [Note] Event Scheduler: Loaded 0 events May 15 09:42:57 anakin mysqld: 2015-05-15T07:42:57.997956Z 0 [Note] /usr/sbin/mysqld: ready for connections. May 15 09:42:57 anakin mysqld: Version: '5.7.7-rc' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Cannot install mysql 5.6.24 from source
Hello, I'm trying to install mysl 5.6.24 from source on a Fedora Core 21 server, but it fails during 'make install': CMake Error at libmysqld/examples/cmake_install.cmake:74 (file): file INSTALL cannot copy file /var/local/src/mysql-5.6.24/mysql_client_test_embedded. Call Stack (most recent call first): cmake_install.cmake:111 (include) Makefile:66 recipe for target 'install' failed make: *** [install] Error 1 Any suggestions? BTW, I first tried 5.5.36 which I had installed before I had to replace the system disk on this server. I was able to start it but not to use 'mysql -u root -p' after I started it. Got a Segmentation fault - core dumped. :( -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Cannot install mysql 5.6.24 from source
On Tuesday, May 12, 2015, Jørn Dahl-Stamnes wrote: Hello, I'm trying to install mysl 5.6.24 from source on a Fedora Core 21 server, but it fails during 'make install': CMake Error at libmysqld/examples/cmake_install.cmake:74 (file): file INSTALL cannot copy file /var/local/src/mysql-5.6.24/mysql_client_test_embedded. Call Stack (most recent call first): cmake_install.cmake:111 (include) Makefile:66 recipe for target 'install' failed make: *** [install] Error 1 Any suggestions? Never mind - I found out about this. For some reason the disk partition was out of free space. BTW, I first tried 5.5.36 which I had installed before I had to replace the system disk on this server. I was able to start it but not to use 'mysql -u root -p' after I started it. Got a Segmentation fault - core dumped. :( -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Crash after shutdown/restart
On Wednesday 22 January 2014 22:56, you wrote: Hi Jørn, But I must say I'm not very impressed by the speed. I'm running a test on an application that do a lot of reads and writes queries and the general performance has dropped to 50% of the what I had in 5.5.20. I would say that this sort of performance drop is not typical. Some users have reported a smaller performance loss in single threaded workloads in 5.6. But dropping from an average of 1800 jobs per minute down to 300? I don't think that should be expected. A few weeks ago I stopped the test and restored the initial database starting the test over again. Now the performance was back to 1700 jobs per minute, but it slowly went down as the test ran. Yesterday it was down to 300 per minutes and still (but very slowly) dropped. Yesterday I did the following: * stopped the test * dumped all databases * stopped the mysql server 5.6 * Downloaded 5.5.33-log source and installed it * Removed all inodb* and ib_log* files * Removed all databases * Started and initialized mysql * Restored all databases * Started the test where I left it. After a few hours I could see that the performance was back to normal - 1800 - 2000 jobs per minute. There is no sign of drop in performace so far. Please explain. -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Crash after shutdown/restart
On Tuesday 14 January 2014 21:51, Jesper Wisborg Krogh wrote: Hi Jørn, On 15/01/2014 04:36, Jørn Dahl-Stamnes wrote: 140114 18:20:08 InnoDB: Error: data file /data/mysql/data/ibdata3 uses page size 1024, 140114 18:20:08 InnoDB: but the only supported page size in this release is=16384 140114 18:20:08 InnoDB: Could not open or create data files. That error is typical for bug http://bugs.mysql.com/bug.php?id=64160 which was present in 5.5.20 and 5.5.21 (see also http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-22.html). So try to upgrade to 5.5.22 or later (I'll recommend 5.5.35) and see if that fixes the issue. Thanks a lot :) I installed 5.6.15 from source and things seems to work OK after a restore. But I must say I'm not very impressed by the speed. I'm running a test on an application that do a lot of reads and writes queries and the general performance has dropped to 50% of the what I had in 5.5.20. I have tried misc combination of innodb_xxx settings but without much luck. 5.6.15 is just slow compared 5.5.20. A short description of the application being tested: The application read a lot of data from files with misc formats. The files are read, parsed (based on the format in each file) and then data is written to the database (raw data). Based on the content of each file, computation jobs are created in a queue (implemented as a table in the database). And then a different process will start doing calculation on the raw and create new data which is written to other tables. After eacn calculation job is done, a record is added in the queue log table. All tables involved are innodb. It's the queue log table that I use to find out how many jobs the system is able to process each minute. A full test takes 2 weeks creating over 15 million jobs. Before each test a initial database is restored and then a set of files are feed to the application. With 5.5.20 the application was able to process an average of 1800 jobs per minute (with peeks up to 2000/min). With 5.6.15 it's around 700-800 jobs per minute and never over 1000/min. Except for the database version everything are the same - the same initial database, the same datafiles and the same order of processing (eventually the result after a full test will be the same). The setup show below gave me 677 jobs per minute in average. I later changed innodb_flush_log_at_trx_commit to 2. Thag gave me 753 jobs per minute. Setting it to 1 gave me 695 jobs per minute. Still long way to go to reach the 1800 jobs per minute. So my question is: What's wrong? Is 5.6.15 slower or? The test machine: - Fedora Core 16 (no X-windows) 8 core AMD (FX-8120) at 3100 Mhz. 32 Gb memory 120 Gb SSD disk for the database (mounted with ext4 and defaults) (*) 1 Tb disk for datafiles and bin log files. *: I'm going to change this later to noatime,data=writeback,barrier=0,nobh and test again. Initial my.cnf: y.cnf: [mysqld] port= 3306 socket = /tmp/mysql.sock explicit_defaults_for_timestamp = TRUE skip-external-locking key_buffer_size = 384M max_allowed_packet = 32M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M thread_concurrency = 14 max_connections = 50 log-bin=/var/mysql/mysql-bin server-id = 1 binlog_format=mixed # Open files. innodb_open_files = 2048 open_files_limit= 8096 innodb_data_home_dir= /data/mysql/data innodb_data_file_path = ibdata1:20G;ibdata2:20G;ibdata3:20G;ibdata4:20G:autoextend innodb_file_per_table = 0 innodb_log_group_home_dir = /data/mysql/data innodb_buffer_pool_size = 25G innodb_log_file_size= 300M innodb_log_files_in_group = 2 innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 0 innodb_support_xa = 0 innodb_flush_method = O_DIRECT innodb_lock_wait_timeout= 50 innodb_thread_concurrency = 14 innodb_fast_shutdown= 0 -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Crash after shutdown/restart
Hello, Got a test server with version 5.5.20. I wanted to unmount/mount the disk where the innodb files was located, so I did a shutdown followed by unmount, then a mount before I tried to start the MySQL server. But it did not work as shown in the log below. I wanted to unmount the disk since I wanted to change the 'defaults' in /etc/fstab with 'defaults,noatime,data=writeback,barrier=0,nobh,errors=remount-ro'. What could cause this? Guess I have to recreate the files and start all over again? # Older message from the error file showing the version. Version: '5.5.20-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution # Shutdown messages 140114 18:14:53 [Note] Event Scheduler: Purging the queue. 0 events 140114 18:14:53 InnoDB: Starting shutdown... 140114 18:17:58 InnoDB: Shutdown completed; log sequence number 2230197670580 140114 18:17:58 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete # Restart after umount/mount. 140114 18:17:59 mysqld_safe mysqld from pid file /usr/local/mysql/data/hostname.pid ended 140114 18:20:05 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 140114 18:20:05 InnoDB: The InnoDB memory heap is disabled 140114 18:20:05 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140114 18:20:05 InnoDB: Compressed tables use zlib 1.2.5 140114 18:20:05 InnoDB: Initializing buffer pool, size = 25.0G 140114 18:20:08 InnoDB: Completed initialization of buffer pool 140114 18:20:08 InnoDB: Error: data file /data/mysql/data/ibdata3 uses page size 1024, 140114 18:20:08 InnoDB: but the only supported page size in this release is=16384 140114 18:20:08 InnoDB: Could not open or create data files. 140114 18:20:08 InnoDB: If you tried to add new data files, and it failed here, 140114 18:20:08 InnoDB: you should now edit innodb_data_file_path in my.cnf back 140114 18:20:08 InnoDB: to what it was, and remove the new ibdata files InnoDB created 140114 18:20:08 InnoDB: in this failed attempt. InnoDB only wrote those files full of 140114 18:20:08 InnoDB: zeros, but did not yet use them in any way. But be careful: do not 140114 18:20:08 InnoDB: remove old data files which contain your precious data! 140114 18:20:08 [ERROR] Plugin 'InnoDB' init function returned error. 140114 18:20:08 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140114 18:20:08 [ERROR] Unknown/unsupported storage engine: InnoDB 140114 18:20:08 [ERROR] Aborting -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Lost connection to MySQL server - need help.
completition [isamchk] key_buffer = 16M # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Lost connection to MySQL server - need help.
11:53:33 cebycny mysqld: Connection ID (thread ID): 92 Oct 12 11:53:33 cebycny mysqld: Status: NOT_KILLED Oct 12 11:53:33 cebycny mysqld: Oct 12 11:53:33 cebycny mysqld: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains Oct 12 11:53:33 cebycny mysqld: information that should help you find out what is causing the crash. Oct 12 11:53:33 cebycny mysqld_safe: Number of processes running now: 0 Oct 12 11:53:33 cebycny mysqld_safe: mysqld restarted -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Lost connection to MySQL server - need help.
On Saturday 12 October 2013 13:07, Andrew Moore wrote: Could be a crash related to innodb data dictionary being out of sync. Could be a bug. Seems like a bug yes. However, we had a strange situation yesterday when we had several processes in the state copying to tmp table (if i remember the exact phrase). After witing 2 seconds, I restarted the server. It seemed to work OK until the backup started. Perhaps we should restore the database that I suspect cause this, in order to rebuild the complete database. -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Lost connection to MySQL server - need help.
On Saturday 12 October 2013 17:36, Reindl Harald wrote: so someone did optimize table on a large table you do yourself not a favour restarting the server in such a moment 7 hours before the server was shut down, we did a alter table to add a primary key to a table that is read-only from the web application. -- Jørn Dahl-Stamnes homepage: http://photo.dahl-stamnes.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Help with Date in Where Clause
On Monday 31 January 2011 21:12, Phillip Baker wrote: Greetings All, I am looking for a little help in setting a where clause. I have a dateAdded field that is a DATETIME field. I am looking to pull records from Midnight to midnight the previous day. I thought just passing the date (without time) would get it but I keep getting an empty record set. So looking for something that works a bit better. select * from your_table where convert(dateAdded, date)='2011-01-31'; -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Group by question
On Monday 17 January 2011 09:53, Steve Meyers wrote: On 1/16/11 5:22 AM, Jørn Dahl-Stamnes wrote: mysql select album_id, updated_at, created_at from album_stats group by album_id order by updated_at desc limit 8; I believe that your problem is that the group by happens before the order by. Since you're grouping, the updated_at column is not deterministic. If there are multiple rows per album_id, any one of those rows could provide the updated_at column that you're then using to order by. What you probably want is to select (and order by) the max(updated_at). moving the group by to after order by will result in an error: mysql select album_id, updated_at, created_at from album_stats order by updated_at group by album_id desc limit 8; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by album_id desc limit 8' at line 1 Beside, since I'm using Propel and Creole for ORM and database abstraction, I would never be able to change the order of them. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Group by question
Hello, I got a table that store information about which photo-albums that a client is viewing. I want to get the N last visited albums and use the query: mysql select album_id, updated_at, created_at from album_stats order by updated_at desc limit 8; +--+-+-+ | album_id | updated_at | created_at | +--+-+-+ | 51 | 2011-01-16 13:03:04 | 2011-01-16 13:03:04 | | 10 | 2011-01-16 12:20:39 | 2011-01-16 12:20:39 | |2 | 2011-01-16 12:20:35 | 2011-01-16 12:20:35 | | 81 | 2011-01-16 12:20:34 | 2011-01-16 12:20:34 | | 97 | 2011-01-16 11:25:03 | 2011-01-16 11:19:05 | | 81 | 2011-01-16 11:19:04 | 2011-01-16 11:19:04 | |2 | 2011-01-16 11:19:02 | 2011-01-16 11:19:02 | | 10 | 2011-01-16 11:18:58 | 2011-01-16 11:13:04 | +--+-+-+ 8 rows in set (0.09 sec) The problem is that album_id 81 is occuring two times in the list. So I thought I should add a group by in the query: mysql select album_id, updated_at, created_at from album_stats group by album_id order by updated_at desc limit 8; +--+-+-+ | album_id | updated_at | created_at | +--+-+-+ | 278 | 2011-01-13 14:02:50 | 2011-01-13 14:02:00 | | 281 | 2011-01-11 16:41:16 | 2011-01-11 16:35:41 | | 276 | 2010-12-15 14:42:57 | 2010-12-15 14:42:57 | | 275 | 2010-12-15 14:42:48 | 2010-12-15 14:42:48 | | 269 | 2010-09-11 14:09:10 | 2010-09-11 14:09:10 | | 271 | 2010-09-11 14:02:27 | 2010-09-11 14:02:27 | | 273 | 2010-09-11 13:59:06 | 2010-09-11 13:59:06 | | 270 | 2010-09-11 13:57:25 | 2010-09-11 13:57:25 | +--+-+-+ 8 rows in set (0.23 sec) But the result is not what I expected. What have I missed? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Incorrect key file for table
Hello, While doing a select query I got the following error in the error-log file: Incorrect key feil for table '/tmp/#sql_5f8_0.MYI'; try to repair it It seem rather meaningless to try to repair a temporary table... So is this a bug in MySQL, or? The database I'm using has only InnoDb tables and I'm using version 5.1.42-log. The query is build using Propel (from a web-application made by Symfony framework). -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Incorrect key file for table
On Friday 14 January 2011 14:03, Johan De Meersman wrote: Check your free diskspace on your temp location. About 900+ Mb free. But I don't think that a full filesystem was the problem (I don't think the mysqld server was able to fill the disk with 900 Mb in 1-2 seconds). After some debugging I found that it was an error in the SQL statment: mysql describe SELECT images.* FROM images, albums, accesses WHERE images.IMAGE_CATEGORY_ID=22 AND albums.ACCESS_ID=accesses.ID; ++-+--+---+---+-+-+---+--++ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+---+---+-+-+---+--++ | 1 | SIMPLE | accesses | index | PRIMARY | PRIMARY | 4 | NULL |3 | Using index| | 1 | SIMPLE | albums | ref | albums_FI_4 | albums_FI_4 | 4 | photo_dev.accesses.id | 68 | Using index| | 1 | SIMPLE | images | ALL | images_FI_2 | NULL| NULL | NULL | 9712 | Using where; Using join buffer | ++-+--+---+---+-+-+---+--++ A join was missing. Strange that this passed the syntax check because the select statment does not make sense (in the application). It should containt a AND images.ALBUM_ID=albums.ID'. When I fixed this, it worked :) Thanx anyway. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Incorrect key file for table
On Saturday 15 January 2011 00:28, Johnny Withers wrote: The result of your query without the join probably exceeded your tmp_table_size variable. When this occurs, MySQL quit writing the temp table to disk thus producing an incorrect table file. (I think). Yes, part of this was my fault (the missing join) but I still wonder why the server processed the query. Would it not be more appropriate with an error message saying that this query contain an error? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What if the user closes the browser while INSERT INTO MySQL? (PHP/MySQL)
On Friday 16 April 2010 11:39, Antonio PHP wrote:
This maybe a newbie question.
Consider the following concept,
~/index.php
#1. Fetch data from an external webpage using PHP Curl;
#2. Preg_match/Prepare Data to INSERT from local MySQL; - this may take a
few secs
#3. While Loop { INSERT data (from #2) into local MySQL } - this may take
only mili secs.
Suppose this code is run by a random user (say, my website visitor), and
he/she closes the browser while the code was running. The real problem is
when the browser is closed while #3 is executing. Because only portion of
data is inserted, ~/index.php, and it doesn't know if it needs to visit the
site again (i.e. repeat from #1 - over visiting the same webpage /
possibility of inaccurate data in local MySQL).
The server does not know if the browser is closed or not (or if the network
connection is losted). It will continue to execute the code until finnished.
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
... or 50 ways to leave your Oracle... ... or 50 ways to save your money... Choose mysql! :) -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record old passwords ?
On Wednesday 20 January 2010 01:10, Daevid Vincent wrote:
-Original Message-
From: John Meyer [mailto:john.l.me...@gmail.com]
Sent: Monday, January 18, 2010 5:04 PM
To: co...@obviouslymalicious.com; mysql@lists.mysql.com
Subject: Re: Record old passwords ?
Although, on an OT, forcing people to not use a password that they
have recently used is a bad idea. What they eventually do is go with
something like hometown01 hometown02, etc. Or worse, they start
writing down their passwords which is a whole other security problem.
Amen to that. At my work, they require a password change every month, but
they store the last 6 passwords you used, so I do exactly what you say -- I
have a logbook and store the same 6 passwords in it and just cycle them.
Other tricks I do, is use a pattern on the keyboard and just shift it.
None of this is secure, and I totally know it (although I'm not picking
secret or something as my PW, it's random letters/numbers/symbols). But I
hate the policy and I'm kind of a rebel like that. ;-p
Several years ago I worked at a place where users had to change their windows
password every N month and they kept a long history log of used password.
My solution to this was to write a program that asked me for my current
password and how many previous used password the system remembered. The
program worked like this:
for (n = 0; no_of_stored_password n; n++) {
set_password(random_generated_password);
do_a_short_sleep();
}
set_password(original_password);
... and the problem was solved :)
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with mysql
On Monday 11 January 2010 08:53, Mihamina Rakotomandimby wrote: Jørn Dahl-Stamnes sq...@dahl-stamnes.net : I have checked the local DNS and 'r2-d2' and 'r2-d2.dahl-stamnes.net' resovl to the same IP-addresse. What's wrong? Reverse resolution? I have checked that, and reverse DNS is OK - both resolve to 192.18.2.22 -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with mysql
On Monday 11 January 2010 09:55, Ananda Kumar wrote: use mysql; select * from user; see if you able to see 'r2-d2' entry in this table. also you can try this grant all on . to 'root'@'%' idenfified by 'secret-password'; I just found the cause of the problem... /etc/nsswitch.conf. It said that files should be used before dns. After I changed the order, it worked (so did the strange sendmail problem I had). But still I wonder why it worked on the other host, since it also had the order files dns in it's nsswitch.conf file... Thanks for your support. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with mysql
On Monday 11 January 2010 12:33, Mihamina Rakotomandimby wrote: (Correction, seel below) Jørn Dahl-Stamnes sq...@dahl-stamnes.net : I have checked the local DNS and 'r2-d2' and 'r2-d2.dahl-stamnes.net' resovl to the same IP-addresse. What's wrong? Reverse resolution? I have checked that, and reverse DNS is OK - both resolve to 192.18.2.22 I read you solved your problem, but by reverse I meant what r2-d2.dahl-stamnes.net resolves to. Sorry (...) what 192.18.2.22 resolves to. it resolv to r2-d2.dahl-stamnes.net -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Binary log problems
I tried to look at the binary logs by using phpMyAdmin, but got an error: SQL error: SHOW BINLOG EVENTS LIMIT 0, 30; MySQL said: #1220 - Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O error. From the error-file: 100111 16:15:40 [ERROR] Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 100, event_type: 2 100111 16:16:51 [ERROR] Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 100, event_type: 2 100111 16:17:08 [ERROR] Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 100, event_type: 2 What is causing this? How can I fix this? I'm using version mySQL 5.1.42. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Problem with mysql
Hello I have installed mysql, apache and phpmyadmin on a new server running Fedora Core 12. For some unknown reason I cannot connect to the local host using a full hostname. This is what I have done: r2-d2# mysql -u root -p grant all on . to 'root'@'r2-d2' idenfified by 'secret-password'; grant all on . to 'root'@'r2-d2.dahl-stamnes.net' idenfified by 'secret-password'; flush privileges; quit r2-d2# mysql -h r2-d2 -u root -p (works) r2-d2# mysql -h r2-d2.dahl-stamnes.net -u root -p ERROR 1130 (HY000): Host 'r2-d2.dahl-stamnes.net.' is not allowed to connect to this MySQL server Then I did: r2-d2# mysql -u root -p grant all on . to 'root'@'jedi.dahl-stamnes.net' idenfified by 'secret-password'; jedi# mysql -h r2-d2.dahl-stamnes.net -u root -p (works) I have checked the local DNS and 'r2-d2' and 'r2-d2.dahl-stamnes.net' resovl to the same IP-addresse. What's wrong? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with mysql
On Sunday 10 January 2010 12:34, sureshkumar...@gmail.com wrote: Hi, The grant stmt has to be as below. Grant all on databasename.tablename to usern...@iporhostname identified by 'password'; Use as above Grant stmt doesn't require a flush stmt. Woops... Seems like the * in my first mail has dissapeared. The correct grant statment I used was: r2-d2# mysql -u root -p grant all on *.* to 'root'@'r2-d2' idenfified by 'secret-password'; grant all on *.* to 'root'@'r2-d2.dahl-stamnes.net' idenfified by 'secret-password'; grant all on *.* to 'root'@'jedi.dahl-stamnes.net' idenfified by 'secret-password'; --Original Message-- From: Jørn Dahl-Stamnes To: mysql@lists.mysql.com ReplyTo: sq...@dahl-stamnes.net Subject: Problem with mysql Sent: Jan 10, 2010 3:20 PM Hello I have installed mysql, apache and phpmyadmin on a new server running Fedora Core 12. For some unknown reason I cannot connect to the local host using a full hostname. This is what I have done: r2-d2# mysql -u root -p grant all on . to 'root'@'r2-d2' idenfified by 'secret-password'; grant all on . to 'root'@'r2-d2.dahl-stamnes.net' idenfified by 'secret-password'; flush privileges; quit r2-d2# mysql -h r2-d2 -u root -p (works) r2-d2# mysql -h r2-d2.dahl-stamnes.net -u root -p ERROR 1130 (HY000): Host 'r2-d2.dahl-stamnes.net.' is not allowed to connect to this MySQL server Then I did: r2-d2# mysql -u root -p grant all on . to 'root'@'jedi.dahl-stamnes.net' idenfified by 'secret-password'; jedi# mysql -h r2-d2.dahl-stamnes.net -u root -p (works) I have checked the local DNS and 'r2-d2' and 'r2-d2.dahl-stamnes.net' resovl to the same IP-addresse. What's wrong? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update Doesn't Update!
On Friday 11 December 2009 10:38, Victor Subervi wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Include SQL files
Hello, After googling for an hour I gave up to find a solution. Problem: A framework is creating a lot of SQL files that will create the database. But I'm tired of doing 10-20-30 'mysql -u... filen.sql' each time I rebuild the model. What I want to do is to create a sql file like: use foobar; include some/dir/file1.sql include some/dir/file2.sql ... and so on. Is it possible? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Include SQL files
On Wednesday 01 April 2009 16:49, Eugene Kosov wrote: What about source? use foobar; source some/dir/file1.sql; source some/dir/file2.sql; ... Gee... why did I not find that on google? :D Thanks... this give me control of the order of the files, which can be important. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
DBDesigner 4 and MySQL
Hello, This my be a bit off-topic... I installed DBDesigner 4 and tried to connect to two of my MySQL servers, one running 4.1 and one running 5.1. But no mather what I try, it failes to connect. The servers are behind a firewall and I can see that when trying to connect to the 4.1 server, my client do send a tcp-package to the server. But when client tries to connect to the 5.1 server, there is no tcp packages comming from the client. On both servers I have issues a GRANT ALL ON *.* TO 'username'@'client.hostname' identified by 'some password'; flush privileges; DBDesigner claim that the user/password is invalid. Any idea what can be wrong? I whish to do a reverse engineering on a big database in order to save time making the visual design in DBDesigner. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sql 5 new installation and root password?
I have just installed MySQL 5. The server is running, but I'm not able to connect to the server as root. I have not set any root password, since I have not found any way to do this in version 5 (it was rather good documented in version 4). I have tried this solution to set the root password, but it does not work: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html Can someone please explain who to set the root password? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sql 5 new installation and root password?
On Saturday 01 March 2008 13:45, Jørn Dahl-Stamnes wrote:
I have just installed MySQL 5. The server is running, but I'm not able to
connect to the server as root.
I have not set any root password, since I have not found any way to do this
in version 5 (it was rather good documented in version 4).
I have tried this solution to set the root password, but it does not work:
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
Can someone please explain who to set the root password?
Never mind... I found a solution AFTER posting the mail, typical :-)
I did a:
* Stop the server
* start it with 'bin/mysqld_safe --skip-grant-tables -u root '
* run 'mysql -u root'
mysql use mysql;
mysql update user set Password=PASSWORD('Some password') where User='root';
mysql quit
* Restart the server
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and Perl
test without successful make Running make install make had returned bad status, install seems impossible Running install for module DBD::mysql Running make for R/RU/RUDY/DBD-mysql-2.9008.tar.gz Is already unwrapped into directory /root/.cpan/build/DBD-mysql-2.9008 Has already been processed within this session Running make test Can't test without successful make Running make install make had returned bad status, install seems impossible Bundle summary: The following items in bundle Bundle::DBD::mysql had installation problems: Mysql DBD::mysql [EMAIL PROTECTED] ~]# uname -a Linux jedi.dahl-stamnes.net 2.6.11-1.1369_FC4 #1 Thu Jun 2 22:56:33 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup InnoDB db to another server
On Friday 30 November 2007 17:12, Jeff Mckeon wrote: Ok, so what would be the command to get a mysqldump of DB1 from 10.10.0.1 into file DB1backup.sql on 10.10.0.2? What about running mysqldump on 10.10.0.2? or mysqldump DB1 -uroot -ppassword dump.sql scp dump.sql [EMAIL PROTECTED]:. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Debugging batch sql statements
On Friday 05 October 2007 16:50, Arun wrote: Baron, I am sourcing it. I do not know if that is the best way though.. I am running the insert statement from a mysql prompt in a linux/windows machine. My script has a single build script which sources other scripts. build.sql == source ./ddl/useraccount/useraccountddl.sql source ./mobchannel_db.sql source ./ddl/usercontent/usercontentddl.sql source ./ddl/userinfo/profilesddl.sql source ./ddl/system/systemddl.sql source ./ddl/shopping/retailitem.sql source ./ddl/shopping/shoppingddl.sql source ./ddl/rewards/userrewardsddl.sql source ./ddl/location/locationddl.sql source ./ddl/listing/listingddl.sql === I will cd into the directory which contains the sql (so that '.' gets resolved) and do a mysql -u root -p and enter password and do a mysqluse dbname; mysqlsource build.sql; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 8. foreach source do source file fix any error until no errors -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting rows by DATE ranges
On Monday 25 June 2007 10:32, Mogens Melander wrote: Looks like you have datetime fields makeing 2007-01-01 00:00:01 2007-01-01. Or use SELECT ... WHERE CONVERT(date,DATE)2007-01-01... if 'date' is a DATETIME field. Also using BETWEEN on date-ranges might help. On Sun, June 24, 2007 02:13, Miguel Cardenas wrote: Hello list I found a little problem with an application am developing, in particular creating reports by DATE ranges. Examples: select ... where date2007-01-01; returns all records where date is greater (and equal inclusive) to 2007-01-01 select ... where date=2007-01-01; returns all records where date is greater/equal to 2007-01-01 the and = have the same effect select ... where date2007-01-01 and date2007-01-20; returns all records where date is greater/equal to 2007-01-01 and less than 2007-01-20 *** DOES NOT RETURN RECORDS FROM DAY *20* select ... where date2007-01-01 and date=2007-01-20; returns all records where date is greater/equal to 2007-01-01 and less than 2007-01-20 although I'm using = *** DOES NOT RETURN RECORDS FROM DAY *20* My doubts are: 1. how can I retrieve rows with a date... NOT INCLUDING the day of the specified date, I mean apply a strict GREATHER THAN 2. how can I retrieve rows with date=... INCLUDING the day of the specified date. currently I have to do a date=date1 and date=date2+1day I need to retrieve rows in this way dateX date=X dateX date=X dateX and dateY date=X and dateY date=X and date=Y dateX and date=Y and so... didn't find a function to specify ranges of dates and the LESS THAN/EQUAL operator does not include the last day, so my reports with = are done by adding one day but don't like to use it this way since it could be confusing and generate errors on reports. Thanks for any comment, Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update question
On Wednesday 25 April 2007 23:14, you wrote: try this: update table1, table2 set table1.value = table2.value where table1.id = table2.id Thanks for the replies... It was late evening when I tried to figure out how to do this. Today I found the answer myself, which is exactly as described above. Lesson learned: Get a good night sleep and then try to figure out how to do things... ;-) -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update question
Please, I nedd help!! I have two tabels: table1: id value table2: id value Both tables has a lot of records with identical IDs. I need to update the table1.value with the table2.value where the id are identical. But I cannot find any UPDATE query that can do this in a single operation. Anyone that can give me a suggestion? I'm using MySQL 4.1.8 -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL and PHP: Incorrect errorcode
Hello folks, I was playing around with my PHP code that shall report any SQL error. To test this code I made an error in a SQL query by changing the table name in the field list to a table that did not exist. This triggered the following error: Error message: 'Unknown table 'q' in field list' Error code: 1109 The error message is correct, but the problem is that the error code (returned by mysql_errno) is not correct. A 'perror 1109' say Illegal error code: 1109. It seems like the error number returned by mysql_errno is not to be trusted, or? Any comments? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need a help in Query
On Saturday 10 February 2007 12:45, balaraju mandala wrote: Hi Fello MySQL users, I am Bala Raju, i have a problem to extract data by writing query, i need u r help. Please help me. I have two tables, i am giving the tables as attachement, firs table is playersloginfo and second one is playerhanddetails. playersloginfo : This table stores all the loginsession details of the players, who visited the game. playerhanddetails: This table stores all the games the player played during a session. I need to extract the data from both these tables, i need to display the player's logintime, logouttime and the totaltime per a session(these are available in 1 table) and number of games the player played(from 2 table). Please give some ideas to write this queries. (I tried in this way to extract data, i simple selected all the columns and used time difference function, but to find number of games by a player, i need to use second table(playerhanddetails). I am searching second table by conditions of user_name, and date between (logindate, logoutdate) and time between (logintime, logouttime)) You should redesign your table playersloginfo. logindate and logintime should be merge into one field: login DATETIME The same goes for logoutdate and logouttime: logout DATETIME. Then you query will be much easier to handle. You should also change ip from varchar(15) to INT UNSIGNED and use INET_ATON to convert a ip of the form a.b.c.d to unsinged int, and INET_NTOA to go back to a.b.c.d -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need a help in Query
On Saturday 10 February 2007 19:21, balaraju mandala wrote: Hi Jorn, Thank you for reply, of course i can merge the columns and change the datatype. But buddy that is not problem here, the problem is extract the data. After reading your message once more, I realise that you should do even more changes. The username should not be a part of the playersloginfo table. Consider this: Table: userinfo id mediumint unsigned not null auto_increment, user_name varchar(20) Table: playersloginfo user_id mediumint unsigned not null comment '--userinfo.id', ip logint unsinged, action varchar(15), login datetime, logout datetime, status varchar(15) Table: playershanddetails playername mediumint unsigned not null comment '--userinfo.id', handnumber bigint(20), date_time datetime Your query might look something like. select u.user_name,l.login,l.logout,timediff(l.login,l.logout) as totaltime, count(h.*) as no_of_games from userinfo as u inner join playersloginfo as l on (l.user_id=u.id) inner join playershanddetails as h on (h.playername=u.id) group by u.id; Please note: I have not tried this... just a quick suggestion right out of my brain... :-) -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table update
On Saturday 20 January 2007 05:52, ViSolve DB Team wrote:
Hi
Update will never support group by clause, only supports group functions;
Try as:
Update teams t
inner join rider_team as rt on (rt.team_id=t.id)
inner join participants as p on (p.rider_id=rt.rider_id)
inner join races as r on (r.id=p.race_id)
set t.created = ( select min(starttime) from races);
Thanks, it worked, but all get the same date. So the result is not correct.
I want something like this (pseudo code):
for each $id in teams
{
set teams.created = select min(r.starttime)
from races as r on (r.id=p.race_id)
inner join participants as p on (p.rider_id=rt.rider_id)
inner join rider_team as rt where rt.team_id=$id
}
I guess that I have to do this in a perl script? But I would like to be able
to do this with a SQL line.
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table update
I want to update new column in a table with data from other tables. The following query give me the data: select t.id,min(r.starttime) from teams as t inner join rider_team as rt on (rt.team_id=t.id) inner join participants as p on (p.rider_id=rt.rider_id) inner join races as r on (r.id=p.race_id) group by t.id; Is it possible to write a query that do a: update teams set created=min(r.starttime) ... which updates all records in the teams table based on first query? Thanks BTW: mysql select version(); ++ | version() | ++ | 4.1.8-standard | ++ -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql, PHP and Persistent mode
Hello, I have been using mysql_connect in a script that display a lot of thumbnails for an album. Each thumbnail is displayed using the code: IMG SRC=thm.php?id=some_id ALT=some title thm.php use a mysql_connect to the database to access the info about the picture based on the id. This worked fine. However, the SQL server is located on a different network than the web-server with a firewall between. When I looked into the firewalls log I saw that there was large amount of new connections when someone accessed the page where all the thumbnails was displayed. I then changed mysql_connect to mysql_pconnect on the scripts and viola... the amount of new connections to the SQL server dropped to only two. Good, I thought. But later I discovered that the SQL server had a large amount of childs running. I had 50-60 mysqld running on the system. This number was constant to below 10 before I changed to persistent mode. Any settings in the config file for the sql-server I need to be aware of? I'm using MyISAM tables . mysql select version(); ++ | version() | ++ | 4.1.8-standard | ++ -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql, PHP and Persistent mode
On Thursday 21 December 2006 11:03, Scott Haneda wrote: Hello, I have been using mysql_connect in a script that display a lot of thumbnails for an album. Each thumbnail is displayed using the code: IMG SRC=thm.php?id=some_id ALT=some title thm.php use a mysql_connect to the database to access the info about the picture based on the id. This worked fine. However, the SQL server is located on a different network than the web-server with a firewall between. When I looked into the firewalls log I saw that there was large amount of new connections when someone accessed the page where all the thumbnails was displayed. I then changed mysql_connect to mysql_pconnect on the scripts and viola... the amount of new connections to the SQL server dropped to only two. Good, I thought. But later I discovered that the SQL server had a large amount of childs running. I had 50-60 mysqld running on the system. This number was constant to below 10 before I changed to persistent mode. Any settings in the config file for the sql-server I need to be aware of? I'm using MyISAM tables . I would have to say, this is suboptimal, and perhaps a little OT, but here goes. If you have 50 images, you are going to make 50 selects to your database, depending on how you connect, you may make 50 connections, or not. First, I know that this may be a bit OT. What you should do, is grab the 50 images ahead of time, in one select, and use some php to iterate them and display them. Second, your suggestion is not an option in my case, since the image and the thumbnails are not stored in the database. The database contain image id, image attributes (exposure++) and name of the image and thumbnail file. I have looked into the docs about configuration, and there is parameter called wait_timeout, which is set to 28800 (8 hours). I have not tested it yet, but perhaps this should be set to something much lower? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql, PHP and Persistent mode
On Thursday 21 December 2006 11:28, Scott Haneda wrote: What you should do, is grab the 50 images ahead of time, in one select, and use some php to iterate them and display them. Second, your suggestion is not an option in my case, since the image and the thumbnails are not stored in the database. The database contain image id, image attributes (exposure++) and name of the image and thumbnail file. That's all the data you need, I am not saying to pull the image out of the database, I am saying to pull the file path out of the database and put it in your html img src tag. Its the difference of making one call to the database, versus x, where x is as many thumbnails as you have, not to mention, x also represents that many http calls to your server, and also, that many times php must process that file. If the image files are within the document path for the Apache web-server. But they are not. In order to display an image, a PHP script must be used (a php script can access files outside the document path). But now we are moving away from the config question I had about configurating the mysqld server. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and Perl
On Monday 25 September 2006 20:05, you wrote: there are two pieces -- dbi, and dbd::mysql. you installed the former but not the latter. go to: http://cpan.perl.org and search for mysql ... Found it, but it won't install: # perl Makefile.PL I will use the following settings for compiling and testing: cflags (mysql_config) = -I/usr/include/mysql -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -m32 -march=i386 -mtune=pentium4 -fasynchronous-unwind-tables -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing embedded (mysql_config) = libs (mysql_config) = -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib -lssl -lcrypto mysql_config (guessed ) = mysql_config nocatchstderr (default ) = 0 nofoundrows (default ) = 0 ssl (guessed ) = 1 Use of uninitialized value in printf at Makefile.PL line 172, PIPE line 103. test_user ( ) = testdb (default ) = test testhost (default ) = testpassword (default ) = testsocket (default ) = testuser (default ) = To change these settings, see 'perl Makefile.PL --help' and 'perldoc INSTALL'. Checking if your kit is complete... Looks good Multiple copies of Driver.xst found in: /usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi/auto/DBI/ /usr/lib64/perl5/vendor_perl/5.8.6/x86_64-linux-thread-multi/auto/DBI/ at Makefile.PL line 724 Using DBI 1.52 (for perl 5.008006 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi/auto/DBI/ Writing Makefile for DBD::mysql # make cp lib/DBD/mysql.pm blib/lib/DBD/mysql.pm cp lib/DBD/mysql/GetInfo.pm blib/lib/DBD/mysql/GetInfo.pm cp lib/Mysql.pm blib/lib/Mysql.pm cp lib/DBD/mysql/INSTALL.pod blib/lib/DBD/mysql/INSTALL.pod cp lib/Mysql/Statement.pm blib/lib/Mysql/Statement.pm cp lib/Bundle/DBD/mysql.pm blib/lib/Bundle/DBD/mysql.pm gcc -c -I/usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi/auto/DBI -I/usr/include/mysql -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -m32 -march=i386 -mtune=pentium4 -fasynchronous-unwind-tables -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing -DDBD_MYSQL_WITH_SSL -DDBD_MYSQL_INSERT_ID_IS_GOOD -g -D_REENTRANT -D_GNU_SOURCE -DDEBUGGING -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -m64 -mtune=nocona -DVERSION=\3.0007\ -DXS_VERSION=\3.0007\ -fPIC -I/usr/lib64/perl5/5.8.6/x86_64-linux-thread-multi/CORE dbdimp.c dbdimp.c:1: error: CPU you selected does not support x86-64 instruction set make: *** [dbdimp.o] Error 1 # uname -a Linux hostname 2.6.11-1.1369_FC4 #1 Thu Jun 2 22:56:33 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux It seems like it does not accept my 64-bit processor as a 64-bit prosessor... I also got some hints about using cpan (perl -MCPAN -eshell) but I felt like a prisoner @ Guantamo who had to answer questions to CIA... At one point I had no answer so I could not continue. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql and Perl
I try to write a perl script that will update a mysql database, but when running the script I get: install_driver(mysql) failed: Can't locate DBD/mysql.pm I have installed DBI 1.52, but that did not help. Now I have spend 1 hour searching internet for this well hidden file mysql.pm, but without luck. I have found a lot of references etc, but not a single link to the file... Can some kind soul please guide me to this file? Or has it been lost for man kind? ;-) -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
COUNT question
I have a query like: SELECT a.a,aa,COUNT(b.id),COUNT(c.id) FROM a LEFT JOIN b ON (b.a_ref=a.id) LEFT JOIN c ON (c.a_ref=a.id); But it seems like SQL is mixing up the two count's. I get the count from table c instead of table b for the first occurence of COUNT in the query. Can I use two COUNT's in one query or do I have to split the query into two queries? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COUNT question
On Monday 18 September 2006 14:55, Brent Baisley wrote: You might try changing it to distinct if you are looking for unique count of ids from each. SELECT a.a,aa,COUNT(DISTINCT b.id),COUNT(DISTINCT c.id) FROM... This return 0 or 1 for b.id (1 if there is 1 or more records) and the correct value for the c.id. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about COUNT()
Assume the following tables: CREATE TABLE x_type ( idmediumint unsigned not null auto_increment, name char(20), primary key (id) ) INSERT INTO x_type (id,name) VALUES (1,'aa'),(2,'bb'),(3,'cc'); CREATE TABLE x_ref ( idmediumint unsigned not null auto_increment, type mediumint unsigned not null, name char(20), primary key (id) ) insert into x_ref (type,name) values (1,'a-test'),(2,'b-test'); SELECT a.name,COUNT(b.id) FROM x_type AS a LEFT JOIN x_ref AS b ON (b.type=a.id) GROUP BY a.id; This works fine. aa, bb and cc from x_types are shown: +--+-+ | name | COUNT(b.id) | +--+-+ | aa | 1 | | bb | 1 | | cc | 0 | +--+-+ Then add a column to x_ref: alter table x_ref add column verified boolean default 0 after name; update x_ref set verified=1; Then I modify the query to: SELECT a.name,COUNT(b.id) FROM x_type AS a LEFT JOIN x_ref AS b ON (b.type=a.id) WHERE b.verified=1; If I do the query now, only 'aa' and 'bb' from the x_type table is listed. Not the 'cc' with count 0: +--+-+ | name | COUNT(b.id) | +--+-+ | aa | 1 | | bb | 1 | +--+-+ How can I get all records in x_type listet in one query? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL order by BUG?
I get the following query: select rt.team_id,count(p.race_id) as cnt,sum(p.points) as sum_score,sum(p.points) / count(p.race_id) as avg_score from some tables, joins and where-criterias group by rt.team_id order by avg_score desc; which gives: +-+--+---+---+ | team_id | cnt | sum_score | avg_score | +-+--+---+---+ | 163 |3 | 4.5 | 1.500 | | 312 |6 | 9.0 | 1.500 | | 223 |2 | 3.0 | 1.500 | | 167 |1 | 1.5 | 1.500 | But if I change to order by from 'avg_score desc' to 'avg_score,sum_score desc' I get: +-+--+---+---+ | team_id | cnt | sum_score | avg_score | +-+--+---+---+ | 262 |9 | 9.0 | 1.000 | | 161 |7 | 7.0 | 1.000 | | 317 |2 | 2.0 | 1.000 | | 97 |1 | 1.0 | 1.000 | | 143 |1 | 1.0 | 1.000 | The order has changed from desc to asc! Looks like a bug for me or am I blind? ;-) select version(); ++ | version() | ++ | 4.1.8-standard | ++ -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL order by BUG?
On Wednesday 06 September 2006 15:20, you wrote: the direction (desc/asc) order qualifier is per-sortfield and defaults to asc, so to get what you're after you'd want: order by avg_score desc, sum_score desc or order by avg_score desc, sum_score if you're after 'asc' on sum_score. - Rick Hmmm.. Seems like I have missed something important here. Thanks. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP!
On Tuesday 22 August 2006 10:29, Peter Lauri wrote: Hi, I did something terrible similar to UPDATE table SET testdate=NOW() And I kind of forgot the WHERE lalalala, so now all my records are screwed. Is there any way of actually undoing this? :) Backup? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP!
On Tuesday 22 August 2006 10:37, Peter Lauri wrote: Don't have any recent, or actually I do not know, because I am not in charge of the hosting part of this, only access to upload scripts and control MySQL via phpMyAdmin. :( A good backup is always a good thing. I dump my databases every night so if I screw up, I can always go back without loosing more than 24 hours of updates. But then, my databases are not that big. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with WHERE clause
I got the following query: select s.id,s.name,sum(p.fee) as fee from serie as s inner join race_serie as rs on (rs.serie_id=s.id) inner join races as r on (r.id=rs.race_id) inner join participants as p on (p.race_id=r.id) where s.receipt=1 and p.rider_id=236 and fee 0 group by s.id order by s.f_date; which gives me the error: ERROR 1052 (23000): Column 'fee' in where clause is ambiguous Without the 'and fee 0' the query works fine. mysql select version(); ++ | version() | ++ | 4.1.8-standard | ++ Is this due to an old version of MySQL? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with WHERE clause
On Sunday 30 July 2006 12:37, Gabriel PREDA wrote: You must specify explicitly what 'fee' to use... so if you wand every p.fee to be greater than zero then you must do: SELECT s.id, s.name, SUM(p.fee) AS fee FROM serie AS s INNER JOIN race_serie AS rs ON rs.serie_id = s.id INNER JOIN races AS r ON r.id = rs.race_id INNER JOIN participants AS p ON p.race_id = r.id WHERE s.receipt = 1 AND p.rider_id = 236 AND p.fee 0 GROUP BY s.id ORDER BY s.f_date; This did the trick. What I want is to find out which series a given rider has participated where (s)he has paid fee for participating (in some cases a rider my participate without haveing paied). If the rider has paied fee once in a serie, then the sum will be more than 0. But will the query above give me the id and name for a serie where a rider has participated but not paid? One way to find out is to test it. If you want the sum to be larger that zero then you would have to do: SELECT s.id, s.name, SUM(p.fee) AS fee FROM serie AS s INNER JOIN race_serie AS rs ON rs.serie_id = s.id INNER JOIN races AS r ON r.id = rs.race_id INNER JOIN participants AS p ON p.race_id = r.id WHERE s.receipt = 1 AND p.rider_id = 236 AND SUM(p.fee) 0 GROUP BY s.id ORDER BY s.f_date; This gave the following error: ERROR (HY000): Invalid use of group function -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: for queries and enquiries
On Tuesday 27 June 2006 13:43, Veerabhadra rao Narra wrote: Hi all if u have doubts pls feel free to send mails to [EMAIL PROTECTED] www.venadsolutions.com its not spam -Rao I consider this as spam. I would suggest that this person is removed from the list. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT and NULL
This my be a dumb question, but I have search the docs without finding the answer. What I want is something like: select * from table where some_field not null; But this gives me an error. I can do a 'where field is null', so I have tried different combination with 'not' etc, but without luck. All I get is an SQL error. The default value for some_field is null. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dont click this link
On Friday 23 June 2006 09:09, Veerabhadra rao Narra wrote: pls click this link www.venadsolutions.com and find new things This is what I consider as SPAM... So do not click the link... -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PHP mysql_connect
I got a strange problem. I run a test-webserver and a MySQL server on the same machine. The code to connect to the database is: $db_link = mysql_connect (sql2.dahl-stamnes.net,stmbk,); This gives me the error: Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client in /home/dahls/Sykkel/Karusellen/Homepage/functions.inc on line 46 But if I, on the same machine, use the mysql command, it works: mysql -h sql2.dahl-stamnes.net -u webuser -p mysql What makes this even more strange, is that I have other pages on the same server, which connect to a different sql-server (a different machine running the same version of MySQL) without problems. What can cause this problems? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP mysql_connect
On Friday 23 June 2006 15:30, Brent Baisley wrote: I assume you are using php. It has to do with how the password in mysql is encrypted. On some accounts, the ones that work, it's encrypted in the old way that php can use. The default new, php 4 can't use. Here's the part of the manual that explains it and how to fix it: http://dev.mysql.com/doc/refman/4.1/en/old-client.html Yes, I forgot to say that I was using PHP. However, using mysql does not work eigher. Thanks for the tip. I guess I just drop the password... -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: About mysqldump
On Thursday 22 June 2006 04:00, Daniel da Veiga wrote: On 6/21/06, Jørn Dahl-Stamnes [EMAIL PROTECTED] wrote: On Wednesday 21 June 2006 17:17, Barry wrote: Jørn Dahl-Stamnes schrieb: Is it possible to get mysqldump to include rights that has been GRANTED to a database or to tables in the database that is being dumped? Dump the Grant tables? I have though of it. Currently I am dumping the mysql database, but there is a lot of tables that I probably don't need. Based on a dump of the mysql database, I guess that the following tables are the one I need to dump: mysql.db mysql.table_privs mysql.user Comments? Isn't the hosts table needed too?! Maybe, but it is empty on my sql-server. BTW, I'm using an older version of MySQL: -- MySQL dump 10.9 -- -- Host: localhostDatabase: mysql -- -- -- Server version 4.1.8-standard -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if else statement
On Wednesday 21 June 2006 11:16, Song Ken Vern-E11804 wrote:
Hi,
I'm trying to build a query in using SQL instead of doing it in Perl.
I am trying to do something like this :
If ((select col1 from table1 where id = 1) == 3)
Then
Select col2 from table2 where table2.id = 1;
Else
Select col2 from table3 where table3.id = 1;
In Perl I would probably do have to access the DB twice.
Select col2 from table1 where if = 1;
If (col2 == 3) {
Select col2 from table2 where table2.id = 1;
} else {
Select col2 from table3 where table3.id = 1;
}
I've read the manual on subqueries but the example don't indicate how I
can
do a conditional test using a subquery?
Am I on the right track or is there another way to do this?
Maybe:
(SELECT col2 FROM table2 left join table1 on table1.id=1 where table1.col1=3
and table2.id=1)
UNION
(SELECT col2 FROM table3 left join table1 on table1.id=1 where table1.col13
and table3.id=1);
I have not tested it...
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Split a Delimited String in SQL ( PROCEDURE split_string )
On Wednesday 21 June 2006 13:37, listsql listsql wrote: Just for the happy comment: Argentina Will win the match tonight against Holland :) MARTIN ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Argentina Will win the match tonight against Holland' at line 1 -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
About mysqldump
Is it possible to get mysqldump to include rights that has been GRANTED to a database or to tables in the database that is being dumped? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: About mysqldump
On Wednesday 21 June 2006 17:17, Barry wrote: Jørn Dahl-Stamnes schrieb: Is it possible to get mysqldump to include rights that has been GRANTED to a database or to tables in the database that is being dumped? Dump the Grant tables? I have though of it. Currently I am dumping the mysql database, but there is a lot of tables that I probably don't need. Based on a dump of the mysql database, I guess that the following tables are the one I need to dump: mysql.db mysql.table_privs mysql.user Comments? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copy database to a file
On Tuesday 20 June 2006 21:37, Thomas Amundsen wrote: I'm running MySQL server 5.0...??? mysql mysqldump --help; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql dump --help' at line 1 Exit mysql and run mysqldump from the shell. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL
On Wednesday 07 June 2006 17:04, Kaushal Shriyan wrote: Hi ALL [EMAIL PROTECTED] mysql]# mysql -u root -h saabworld.com -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 to server version: 3.23.54 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use mysql; Can't read dir of './mysql/' (Errcode: 13) Database changed mysql show tables; ERROR 12: Can't read dir of './mysql/' (Errcode: 13) mysql A quick check with the perror command: # perror 13 Error code 13: Permission denied -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SPAMs
Seems like someone has got their hand of my e-mail alias which I use for this list only. It started last friday and I get about 10-15 messages every day. Does someone else has this problem? What's the procedure to change my subscribtion e-mail addresse? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SPAMs
On Wednesday 24 May 2006 13:36, Logan, David (SST - Adelaide) wrote: Hi Jørn, I myself haven't had too many spams, I get a few anyway from various places my email address is placed. I would suggest try unsubscribing using this alias and just re-subscribe with your new (known only to you) alias. They must have found my e-mail addresse from this list or from some sort of archive. I don't know which e-mail system (majordomo, listserver, or) this list use. But some of the systems do have commands to change the e-mail address without doing a unsubscribe and a new subscribe. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SPAMs
On Wednesday 24 May 2006 14:48, George Law wrote: more than likely someone is harvesting emails off groups.google.com http://groups.google.com/group/mailing.database.myodbc Your posting shows top of the list :) Yet another reason for not using google. It seems like they help the spammers by doing this. It's OK that they show the item in the list, but the e-mail addresse should be masked... -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update query
Assume that you have two tables (in two different databases):
table A in database dbA:
id CHAR(6)
foo int
bar int
table B in database dbB:
id INT(6)
foo int
bar int
Both tables has a several records with identical ID values, but the format is
different ('001234' vs 1234).
Is it possible to create a update query that copies the 'foo' and 'bar' from
table dbA.A to dbB.B for each record in dbB.B?
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update query
On Tuesday 24 January 2006 12:03, Gleb Paharenko wrote: Hello. If dbA.id has the format you have specified MySQL should be able to silently convert the type from char to int, and you can work with dbA.id as it is integer column. mysql create table ch(id char(6)); Query OK, 0 rows affected (0.04 sec) mysql insert into ch set id='001234'; Query OK, 1 row affected (0.00 sec) mysql select id+0 from ch; +--+ | id+0 | +--+ | 1234 | +--+ Use something similar to: update dbB, dbA set dbB.foo=dbA.foo, dbB.bar=dbA.bar where dbB.id=dbA.id ; See: http://dev.mysql.com/doc/refman/5.0/en/update.html Thanks a lot. That did the trick. I ended up with a command like this: update newdb.table as T,olddb.table as S set T.foo=S.foo,T.bar=S.bar,...(more fields that should be copied) where T.id=S.id; -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
On Friday 20 January 2006 11:19, Giandomenico Sica wrote: Hi, I've a little problem with mysqldump. I'm using wordpress in localhost. Now I'd like to put online my wordpress website, including the related mysql database. In order to make this operation, I've used the command: mysqldump database name The work seems correct, but the problem is that I'm not able to find the exported sql file in my computer (also by using the find file option). I use Linux Ubuntu. Can you help me please? Furthermore, how should I do in order to load online the exported database? Many thanks in advance for the answer. this is how I dump my database: /usr/local/mysql/bin/mysqldump -u root -psomepassword imagedb /tmp/image_db.tmp -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query question.
Assume the following table:
CREATE TABLE test (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
parent MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
name VARCHAR(60) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO test (name) VALUES ('root');
SELECT @root_id:=id FROM test WHERE name = 'root';
INSERT INTO test (name,parent) VALUES ('sub 1',@root_id),('sub 2',@root_id);
SELECT @id:=id FROM test WHERE name = 'sub 1';
INSERT INTO test (name,parent) VALUES ('sub 1.1',@id);
Is it possible to create a query that create a result which looks something
like:
idname parent
--
id root
id sub 1 root
id sub 1.1 sub 1
id sub 2 root
where id is the actual value of the id field.
I'm also looking for a query where I select one record based on id and get a
output like root sub 1 sub 1.1, if this is possible. Or do I have to do
this in a loop in my PHP code?
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myPhpAdmin
This is perhaps a bit off-topic, but... I tried to install and configurate phpAdmin on an internal web-server. I copied a default config file and created my own config.inc.php file. In this I set $cfg['Servers'][$i]['host'] = 'sql.dahl-stamnes.net'; a 'host sql.dahl-stamnes.net' shows that it is an alias pointing to another machine where the mySQL server is running. However, when trying to access myPhpAdmin, I get the following error: Error MySQL said: Documentation #1045 - Access denied for user 'quest'@'r2d2.dahl-stamnes.net' (using password: NO) It seems like the host name given in the config file is ignored and that it try to connect to the host where the web-server is running. It should not be like this, should it? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL server has gone away ??
I have been using mysql client for some time when I got this error: mysql select * from mytable; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... ERROR 1045 (28000): Access denied for user 'username'@'my.ip.addr' (using password: YES) ERROR: Can't connect to the server mysql quit Bye $ mysql -h sql -u sqluser -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6669 to server version: 4.1.8-standard MySQL is running on a server and I have to go through a firewall to reach it. Any reason why this happens? Should I be worried? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on table udate
I have two tables; CREATE TABLE category ( category_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, nameCHAR(15) NOT NULL, PRIMARY KEY (category_id) ); CREATE TABLE albums ( album_idMEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, category_id MEDIUMINT UNSIGNED NOT NULL, PRIMARY KEY (album_id) ); (the other fields are removed for clerity). What I'm trying to do is to update all records in the albums table where the category_id = 0 with a new id-value found by a SELECT statement in the category table, somethink like: SELECT category_id FROM category WHERE name = 'some name'; UPDATE albums SET category_id='value found from above' WHERE category_id='some value'; But I have not found any way to do this from a script. Any hints from some kind souls out there? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on table udate
On Monday 19 December 2005 14:09, [EMAIL PROTECTED] wrote: CREATE TABLE category ( category_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, nameCHAR(15) NOT NULL, PRIMARY KEY (category_id) ); CREATE TABLE albums ( album_idMEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, category_id MEDIUMINT UNSIGNED NOT NULL, PRIMARY KEY (album_id) ); (the other fields are removed for clerity). What I'm trying to do is to update all records in the albums table where the category_id = 0 with a new id-value found by a SELECT statement in the category table, somethink like: SELECT category_id FROM category WHERE name = 'some name'; UPDATE albums SET category_id='value found from above' WHERE category_id='some value'; If I recall correctly (IIRC) from another thread, you are on a fairly recent version of MySQL. That means you should be able to perform a multitable update. These look just like regular JOIN queries except they are flipped around from the SELECT format into the UPDATE format. As a regular SELECT statement: SELECT a.album_id, c.category_id, c.name FROM albums a INNER JOIN categories c ON a.category_id = c.category_id AND a.category_id = 0; If that query returns what you would like your data to look like, you can turn it into an UPDATE statement through a little bit of rearranging: UPDATE albums a INNER JOIN categories c ON a.category_id = c.category_id AND a.category_id = 0 SET a.category_id=c.category_id; Thanks. I'll try this. I found another way that seem to work: SELECT @x:=category_id FROM category WHERE name='undefined'; UPDATE albums SET [EMAIL PROTECTED] WHERE category_id=0; But your suggestions is a bit more elegant... :-) -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: proper way to export with the --tab function
On Monday 19 December 2005 08:15, Bruce Carey wrote: Hi List, I have recently had a crash coursh in the fineer points of db recovery due to a mis-managed server. Could someone help me out with advice on the proper syntax for doing mysqldump? I have a rather large db (4.2GB), and regular dump files get easily corrupted, so I found out, plus LONG restore times. My other problem is that many fields will have \r and \n etc in them, for instance because we store web content. So, I want to do it in this format: mysqldump --opt --fields-terminated-by='\t' --fields-optionally-enclosed-by='#*#*#' --fields-escaped-by='\' --lines-terminated-by='\n\r' --tab=/Users/theuser/testexport mydb mytable -u root -p I wanted to try this, but got an error when trying it on my test database. I use the following commands in a script: mysqldump --opt --fields-terminated-by='\t' --fields-optionally-enclosed-by='#*#*#' --fields-escaped-by='\' --tab=/home/dahls/Dump imagedb -u root -proots-password A file called albums.sql was created in the Dump directory, but then I got the following error: mysqldump: Got error: 1: Can't create/write to file '/home/dahls/Dump/albums.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Network login
I'm trying to access a database over my local network. On the server, I had applied the following commands: shell mysql -u root -p mysql GRANT ALL on mydb.* To 'snotface'@'otherhost.mydomain' IDENTIFIED BY 'novice'; From the otherhost.mydomain, I try a: shell mysql -h [EMAIL PROTECTED] -u snotface -p Password: novice ERROR 1045 (28000): Access denied for user 'snotface'@'otherhost.mydomain' (using password: YES) I'm running MySQL version 4.1.8 on the server. and Ver 14.7 Distrib 4.1.11, for redhat-linux-gnu (i386) on the client. Why? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Network login
On Saturday 17 December 2005 21:43, [EMAIL PROTECTED] wrote: I think the most likely culprit will be an unexpected reverse DNS result. Here are the other likely reasons to get an access denied error: http://dev.mysql.com/doc/refman/4.1/en/access-denied.html To the server, does the machine you are trying to login from reverse DNS to 'otherhost.mydomain' or some other address? Was the server started with --skip-name-resolve option? If it was, during your login attempt the server will be trying to validate your IP address (what it sees) against your DNS name (what's in the permissions tables). I forgot to do a 'FLUSH PRIVILEGES;' after the GRANT command. Thanks to Sol Beach for this solution. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequential id
On Wednesday 14 December 2005 16:31, Joseph Alotta wrote:
Greetings,
I have a bunch of names and addresses that I am adding to MySql
database. I would like to automatically assign a unique sequence
number to each person. Is there a way to do this easily?
idname
1 bob jones
2 larry smith
3 henry rogers
Create your table like this:
CREATE TABLE people (
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(40),
PRIMARY KEY (id)
);
and then insert the records like this:
INSERT INTO people (name) VALUES
('bob jones'),('larry smith'),('henry rogers');
BTW, I started to learn SQL two days ago...
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
