Funny connection problem
Hello, I'm running MySQL 5.0.22 x86_64 on CentOS 4.3 on one server and web apps on another server. Occasionally these web apps (PHP) will return error 1130, Host 'x' is not allowed to connect to this MySQL server. From my understanding of the documentation, this is an "access denied" message that normally would happen if no user has this host in the mysql.user table. Obviously since it connects properly most of the time, the permissions are properly set up. What should I look for to stop this error from occuring? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
create view and insert into problems
Hello NG, I have two tables in my mysql database created with phpmyadmin: t_authors: 1 authorid (primary key, auto_increment) 2 lastname 3 firstname And a table named t_books, fields inside: t_books: 1 bookid (primary key, auto_increment) 2 authorid (Typ:index, reference to authorid from t_authors done with phpmyadmin) 3 title 4 subtitle Now I want to create a view from t_authors, so that the fields of lastname and firstname are one field with the value inside: "lastname, firstname": create view v_authornames as select authorid, lastname || ', ' || firstname from t_authors; But this view created only an empty field named lastname ||', ' firstname. How can I join this two fields so that I get one and this value? There is another problem of me inserting values sequently in the two tables: insert into t_authors (lastname, firstname) values ('Meyers', 'Scott'); insert into t_books (authorid, title, subtitle) values ('1'), 'Effektiv C++ Programmieren', '50 Wege zur Verbesserung Ihrer Programme und Entwuerfe'); insert into t_books (authorid, title, subtitle) values ('1'), 'Mehr Effektiv C++ Programmieren', '35 neue Wege zur Verbesserung Ihrer Entwuerfe und Programme'); insert into t_authors (lastname, firstname) values ('Schlossnagle', 'George'); insert into t_books (authorid, title, subtitle) values ('1'), 'Advanced PHP Programming', 'A practical guide'); The problem is the authorid of t_books: which value should I take for authorid of table t_books. If I took '1', the referenced value of the authorid from t_authors was not taken from the authorid from t_books, but always the value '1'? If I took '0' or others I get errors. In phpmyamin I set the reference to t_authors.authorid in the t_books.authorid field. Best regards and many thanks Andreas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to deploy MYSQL db to client
Hi! I wrote small windows application which use MySql 5 databse. I want to make one installation pakage with which I will install application and database. What is procedure to deploy database to client? Regards Tomaz
Re: A lot of HD Writing
In the last episode (Jun 24), Santiago del Castillo said: > Hi, is usual to have a lot of HD writing on a MySQL server where > (according to mytop) there are between 800 and 1200 queries per > second? my MRTG is showing a lot of HD Writing and i wanted to know > if it's usual. The database is growing about 11 MB every 3 minutes. If you are issuing INSERT or UPDATE queries, you should expect disk writes :) > An extra question: > Which scale of MaxBytes should i use for my HD Writing MRTG config? i'm > using "MaxBytes[hd-write]: 2400" right now. The Disk is a 80 GB SCSI disc > with RAID 1 What units is hd-write in? If you're graphing the net-snmp diskIOTable values (diskIONRead and diskIONWritten or the preferred 64-bit counterparts diskIONReadX and diskIONWrittenX), they are in bytes. 2400 sounds either way too low if your units are KB/sec (==2.4 MB/sec), or way too high if it's in MB/sec (==2.4 GB/sec). -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A lot of HD Writing
Hi, thanks for answering! I am using MyISAM tables. So, based in your answer i assume that is usual to have a lot of HD Writing, isn't it? Thanks! Santiago mos wrote: At 06:55 PM 6/24/2006, Santiago del Castillo wrote: Hi, is usual to have a lot of HD writing on a MySQL server where (according to mytop) there are between 800 and 1200 queries per second? my MRTG is showing a lot of HD Writing and i wanted to know if it's usual. The database is growing about 11 MB every 3 minutes. FYI, I don't have any log-type (binlog, queries log or slow queries log) activated. Well, if this keeps up, you better run out and buy more hard drives. Are you using InnoDb or MyISAM tables? You can run "Show ProcessList" to see what task is currently executing on the MySQL server. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query slow
Hello friends Id like to thanks all friends that helped with this question Regards Luiz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 3 Table Join question
Solved it with Union :) SELECT images.id,images.name, playlist.title FROM images,playlist WHERE playlist.image_id = images.id UNION SELECT images.id,images.name, media.title FROM images,media WHERE media.image_id = images.id ORDER BY id ASC On Jun 23, 2006, at 6:44 PM, Graham Anderson wrote: I am trying to build a query to 1) Get all the results from one table, 'images' 2) For each entry in the 'images' table, find the correct title from the 'playlist' OR 'media' table where images.id = which_table.images_id images table id, filename playlist table title images_id media table title, images_id So the result would something like id filenametitle 1 file1 playlist-title1 // id matches entry in the Playlist table 2 file2 playlist-title2 3 file3 media-title1//id matches entry in the Media table 4 file4 media-title2 any help is appreciated as my queries have been pretty simple up to this point many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if else statement
fre 2006-06-23 klockan 01:52 -0400 skrev Michael Stassen: > Thomas Lundström wrote: > > Not sure what you're aming for here and how your data is structured but > > why not use a join and alias and fetch all info in one select and then > > solve what you need in your code? > > > > Something in the line of: > > > > select t2.col2 from_t2, t3.col2 from_t3 > > from table1 t1, table2 t2, table3 t3 > > where t1.id = t2.id > > and t1.id = t3.id > > and t1.id = 3 > > > > Maybe you can do something like that? > > That may be a start, but you have the wrong condition on t1.id, and you've > left > out any mention of t1.col1. Also, explicit JOINs are better than implicit > (using commas) JOINs. > Well of course you are correct, only probably you've missed the intention. The error is the late-in-the-evening substitution of "t1.id = 3" where it should read "t1.col1 = 3" of course. Yes, explicit JOINS are always better. These ones are though easier to fiddle with! ;-) Regards, Thomas Lundström > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > � -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re-importing a mysqldump file
Hi, No unfortunately not... Cheers Ian > -Original Message- > From: John Meyer [mailto:[EMAIL PROTECTED] > Sent: 25 June 2006 05:41 PM > To: mysql@lists.mysql.com > Subject: Re: Re-importing a mysqldump file > > Ian Barnes wrote: > > Is this possible? Or would the best way be to import the dumped file > into a > > temp table and then select out of the temp table into my correct table ? > > > > > Anyway to use a trigger? > > -- > Online library -- http://pueblonative.110mb.com > 126 books and counting. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re-importing a mysqldump file
Ian Barnes wrote: Is this possible? Or would the best way be to import the dumped file into a temp table and then select out of the temp table into my correct table ? Anyway to use a trigger? -- Online library -- http://pueblonative.110mb.com 126 books and counting. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re-importing a mysqldump file
Hi, I need to auto re-import a mysqldump file, but when importing it I need to make a certain field a value for all information imported. For example my db looks like this: Id Name Value Serverid Now, on the remote server, name and value get exported, and when I re-import it here, I need id to auto-increment and serverid to be set by something that I specify depending on what file im importing. Is this possible? Or would the best way be to import the dumped file into a temp table and then select out of the temp table into my correct table ? Thanks for any help! Ian
Re: mysqld refuses to run on boot
Karl Larsen wrote: Duncan Hill wrote: On Saturday 24 June 2006 12:49, Karl Larsen wrote: Hi Joerg, I'm using Ferdora Core 4 of Red Hat and I have in /etc/rc.d/init/ a file mysqld which when I use ./mysqld start does start the system. But it has to be done every time I turn on the computer. I do not understand your patch. I can't find what I think is where you want to put one line of code. The other lines do not exist. Or I don't know what I.m doing :-) chkconfig mysqld on I am really sorry. I had to read man chkconfig and got really confused but did it because it appeared to do something to the mysqld file. Maybe it will work. I will see soon. Karl This morning when I turned on my computer init loaded mysqld in the proper slot and I had a system running in X-Windows. Karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld refuses to run on boot
Hi Karl, Fredrik, all! Karl Larsen wrote: Joerg Bruehe wrote: Hi Fredrik, all! Fredrik Andersson wrote: Hi all I have problems getting MySQL autoboot on my RedHat installation. [[...]] In addition to permissions (see the other posts), there is another possible problem: [[...]] Try this patch to "/etc/init.d/mysql": --- /etc/init.d/mysql-OLD +++ /etc/init.d/mysql @@ -17,6 +17,7 @@ ### BEGIN INIT INFO # Provides: mysql # Required-Start: $local_fs $network $remote_fs +# Should-Start: ypbind nscd ldap ntpd xntpd # Required-Stop: $local_fs $network $remote_fs # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 Hi Joerg, I'm using Ferdora Core 4 of Red Hat and I have in /etc/rc.d/init/ a file mysqld which when I use ./mysqld start does start the system. But it has to be done every time I turn on the computer. I do not understand your patch. I can't find what I think is where you want to put one line of code. The other lines do not exist. Or I don't know what I.m doing :-) Ok, verbose form: Depending on how a machine and a MySQL server is configured, it _may_ be necessary to start some more other services before MySQL is started. The start script, which is installed as "/etc/init.d/mysql", contains some lines that describe such dependencies. Up to 5.0.22, the lines are: ... ### BEGIN INIT INFO # Provides: mysql # Required-Start: $local_fs $network $remote_fs # Required-Stop: $local_fs $network $remote_fs # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 ... The system tools ensure (on installing) that start scripts are called in bottom-up sequence, first the prerequisites and then the dependents (first the lower-level services, then the ones using them). Now _if_ the installation uses NIS (former "yp", "yellow pages"), a name server cache daemon, or LDAP, these services may not be up and running when the MySQL server starts - causing problems. To prevent this, more dependency information has been added. So the resulting lines are these: ... ### BEGIN INIT INFO # Provides: mysql # Required-Start: $local_fs $network $remote_fs # Should-Start: ypbind nscd ldap ntpd xntpd # Required-Stop: $local_fs $network $remote_fs # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 ... If you install / upgrade using a RPM package, "rpm" should take care of installing the server start script. If you use a "tar.gz" package, "chkconfig" may be the tool to use - sorry, I am no RedHat user myself, so I will leave the search for details to you. Keywords: "chkconfig", "ntsysv", "redhat-config-services", "run level". In any case, the result should be some symbolic links from the run level start directories "/etc/init.d/rc2.d", "/etc/init.d/rc3.d", ... to that script, similar to this: :~> ls -l /etc/init.d /etc/init.d/rc3.d/ /etc/init.d: insgesamt 381 -rw-r--r-- 1 root root 482 2004-08-25 16:20 Makefile -rw-r--r-- 1 root root 6851 2005-03-23 22:31 README . . -rwxr-xr-x 1 root root 9359 2006-06-23 16:41 mysql . . /etc/init.d/rc3.d/: insgesamt 0 lrwxrwxrwx 1 root root 6 2006-03-27 09:31 K06hal -> ../hal . . lrwxrwxrwx 1 root root 8 2006-06-19 15:44 K10mysql -> ../mysql . . lrwxrwxrwx 1 root root 8 2006-06-19 15:44 S12mysql -> ../mysql . . Which means: In my example, in run level 3 ("rc3.d") the MySQL server is at position 10 for stopping (K10) and at position 12 for starting (S12). You need such symbolic links for MySQL to be started automatically at all on system bootup (for each run level in which you want it started), and you need them in the proper position (number) so that starting (stopping) services is done in the correct order of dependencies. Hope that helps, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT and NULL
SELECT * FROM table WHERE some_field IS NOT NULL; -Original Message- From: Jørn Dahl-Stamnes [mailto:[EMAIL PROTECTED] Sent: Sunday, June 25, 2006 2:24 PM To: mysql@lists.mysql.com Subject: 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB database Lost
Hello, I am having a problem with one of my MySQL databases, the server was crashed, and then all InnoDB tables seem to be empty, when I click any innoDB table name in PhpMyAdmin i get the following error: #1016 - Can't open file: 'forums.ibd' (errno: 1) when I click the database name, I get a list of the tables, where PhpMyAdmin writes "in use" under the following columns: Records,Type,Collation, and size Do you have any idea why such a thing happens, and what can be done to restore the database? thanks Khaled
Upgrading from 3.23.58 > 5.0.22?
Good morning, all - I've read for quite a while tonight, but still haven't been able to figure out - can I upgrade directly from 3.23.58 to 5.0.22? I've read that I'd have to do something like 3.23.58 > 4.0 > 4.1 > 5.0.22, but then also the 'mysql_upgrade' application which, as documented, sounds like it can do magic things. To be quite honest I'd like nothing more than to take a 'mysqldump' of the database, and just re-import that, and run an app such as 'mysql_upgrade' against it and call it good. This will all be done on a pretty recent Linux distribution, CentOS 4.3. Nothing out of the ordinary, but an upgrade such as this one is definitely a bit out of my realm. I was able to do such an upgrade a few weeks ago, but added stuff like permissions by hand - there were only a few. However, for this particular instance, there's 250+ MySQL users. Anyone have any suggestions? Thanks! -dant -- 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]