Re: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
The socket hets created when you start the server It might be in the mysql home dir, it might be in /var/run. See if its declared in my.cnf Sent via BlackBerry from T-Mobile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: CONNECTION (SOCKET AND TCP/IP)
Socket connections do not use tcp (meaning localhost) Remote hosts use tcpip Sent via BlackBerry from T-Mobile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
need help with query...
I hate when somebody put in Subject line something like I just did but after 15
minutes to try to be specific just with one short sentence - I gave up. So, you
can hate me - I understand (though, help with my problem too) :-)
I have let say 3 tables people, organization, addresses. and they are linked to
each other with column person_id.
to select person by specified person_id it's not big deal, really simple.
though, I wonder how can I select records from these three tables but somehow
marked which table it belongs?
e.g.
select p.first_name, p.last_name, p.status, p.date_registered, o.org_id,
o.org_name, o.org_department, a.addres1, a.address2, a.city, a.state, a.zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id
I need somehow, together with result data, info which table data belogs?
e.g.
not exactly this way but something like this:
$data = array(
'people' = array('lamp', 'lists', 1, '2008-12-12'),
'organization' = array(56, 'Lamp List', 'web'),
'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345')
}
thanks for any help.
-afan
Re: need help with query...
From: Andy Shellam andy-li...@networkmail.eu
To: Lamp Lists lamp.li...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, December 17, 2008 2:29:08 PM
Subject: Re: need help with query...
Hi Afan
Why not prefix your field names with the table name?
select
p.first_name AS person_first_name,
p.last_name AS person_last_name,
p.status AS person_status,
p.date_registered AS person_date_registered,
o.org_id AS organization_org_id,
o.org_name AS organization_org_name,
o.org_department AS organization_org_department,
a.addres1 AS addresses_address1,
a.address2 AS addresses_address2,
a.city AS addresses_city,
a.state AS addresses_state,
a.zip AS addresses_zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id
Then in PHP (which I guess you're using from your example) do something like:
// Get every record from the database ($result is your MySQL result from
mysql_query)
while ($row = mysql_fetch_assoc($result))
{
$result = Array();
// Run through each field in the row
foreach ($row as $field = $value)
{
// Split the field into 2 segments split by _
$fieldSplit = explode('_', $field, 1);
// $fieldSplit will be, for example, Array(0 = 'person', 1 =
'first_name')
$result[$fieldSplit[0]][$fieldSplit[1]] = $value;
// Now you should be able to access the person's first name using
$result['person']['first_name']
}
}
This code may not be perfect as I've just typed it out from memory so it may
take a bit of tweaking.
Thanks,
Andy
Hi Andy,
the reason I can't use this because fields (columns) in select statement
(p.first_name, p.last_name,...) are actually dynamically created. In my project
different client will select different fields to be shown. 99% will select
first_name, and last_name, but some don't care about date_registered, some will
need more org data...
actually, it will be more this way:
SELECT {$selected_fields} FROM people p, organization o. addresses a
WHERE ...
where
$selected_fields = p.first_name, p.last_name, o.org_name
or
$selected_fields = p.first_name, p.last_name, o.org_name, a.address, a.city,
a.state, a.zip
or
$selected_fields = o.org_name, a.address, a.city, a.state, a.zip
I hope I'm more clear now?
Though, I can do something as you suggested while creating $selected_fields
:-)
Thanks
Jason Pruim wrote:
On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote:
I hate when somebody put in Subject line something like I just did but after
15 minutes to try to be specific just with one short sentence - I gave up.
So, you can hate me - I understand (though, help with my problem too) :-)
I have let say 3 tables people, organization, addresses. and they are linked
to each other with column person_id.
to select person by specified person_id it's not big deal, really simple.
though, I wonder how can I select records from these three tables but
somehow marked which table it belongs?
e.g.
select p.first_name, p.last_name, p.status, p.date_registered, o.org_id,
o.org_name, o.org_department, a.addres1, a.address2, a.city, a.state, a.zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id
I need somehow, together with result data, info which table data belogs?
e.g.
not exactly this way but something like this:
$data = array(
'people' = array('lamp', 'lists', 1, '2008-12-12'),
'organization' = array(56, 'Lamp List', 'web'),
'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345')
}
thanks for any help.
-afan
It may not be the best option, but when I've done that type of stuff in the
past, I add another field to the database and call it like List and put
People in the people database. and then you could just query the field List
and display it how ever you needed.
-- Jason Pruim
japr...@raoset.com
616.399.2355
Re: need help with query...
From: Andy Shellam andy-li...@networkmail.eu
To: Lamp Lists lamp.li...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, December 17, 2008 2:48:31 PM
Subject: Re: need help with query...
Hi,
Hi Andy,
the reason I can't use this because fields (columns) in select statement
(p.first_name, p.last_name,...) are actually dynamically created. In my
project different client will select different fields to be shown. 99% will
select first_name, and last_name, but some don't care about date_registered,
some will need more org data...
actually, it will be more this way:
SELECT {$selected_fields} FROM people p, organization o. addresses a
WHERE ...
where
$selected_fields = p.first_name, p.last_name, o.org_name
or
$selected_fields = p.first_name, p.last_name, o.org_name, a.address, a.city,
a.state, a.zip
or
$selected_fields = o.org_name, a.address, a.city, a.state, a.zip
So just tag AS table_field_name to each field when you're building your list
of $selected_fields - e.g.
$selected_fields = p.first_name AS person_first_name, p.last_name AS
person_last_name, o.org_name AS organization_org_name
You don't have to use the full table name either - for example in the following
statement, you would then access the data using $result['p']['first_name'];
$selected_fields = p.first_name AS p_first_name, p.last_name AS p_last_name,
o.org_name AS o_org_name
This approach is actually easier if you're creating the query dynamically,
because you don't have to manually type a load of AS xxx statements after
every field.
I've recently done something similar in one of my applications to wrap
date/time fields in either FROM_UNIXTIME() or UNIX_TIMESTAMP() functions.
Andy
Yup! That'll do it!
Thanks Andy
;-)
which query solution is better?
hi, I would like to get your opinions regarding which query you think is better solution and, of course - why. I have (very simplified example) 3 tables: orders, members and addresses I need to show order info for specific order_id, solution 1: select ordered_by, order_date, payment_method, order_status from orders where order_id=123 select m.name, a.address, a.city, a.state, a.zip from members m, addresses a where m.member_id=$ordered_by and a.address_id=m.address_id //$ordered_by is value from first query solution 2: select ordered_by, order_date, payment_method, order_status, (select m.name, a.address, a.city, a.state, a.zip from members m, addresses a where m.member_id=ordered_by and a.address_id=m.address_id) from orders where order_id=123 (queries are written without testing and maybe it doesn't work exactly, but it's more to gave you the idea what I'm talking about :D) also,what if I have to list 20,50 or 100 orders instead one order? would be subquery still be an option? thanks for any opinion. -ll
Re: which query solution is better?
- Original Message From: John Hicks [EMAIL PROTECTED] To: Lamp Lists [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, July 8, 2008 11:20:16 AM Subject: Re: which query solution is better? Lamp Lists wrote: hi, I would like to get your opinions regarding which query you think is better solution and, of course - why. I have (very simplified example) 3 tables: orders, members and addresses I need to show order info for specific order_id, solution 1: select ordered_by, order_date, payment_method, order_status from orders where order_id=123 select m.name, a.address, a.city, a.state, a.zip from members m, addresses a where m.member_id=$ordered_by and a.address_id=m.address_id //$ordered_by is value from first query solution 2: select ordered_by, order_date, payment_method, order_status, (select m.name, a.address, a.city, a.state, a.zip from members m, addresses a where m.member_id=ordered_by and a.address_id=m.address_id) from orders where order_id=123 (queries are written without testing and maybe it doesn't work exactly, but it's more to gave you the idea what I'm talking about :D) also,what if I have to list 20,50 or 100 orders instead one order? would be subquery still be an option? thanks for any opinion. -ll I don't understand what syntax you're using for your second solution. Your first solution uses two separate queries which will accomplish the task. They could be combined into a single query like this: select * from orders left join members on member_id = ordered_by left join addresses on addresses.address_id = members.address_id where order_id = 123 -- john sorry john. my bad. I should test the query before I post it because it doesn't work that way :D though, let me modify the question: solution 1: select o.ordered_by, o.order_date, o.payment_method, o.order_status, concat(m.first_name, ' ', m.last_name) name left join members m on m.member_id=o.ordered_by from orders o where o.order_id=123 vs. select o.ordered_by, o.order_date, o.payment_method, o.order_status, (select concat(first_name, ' ', last_name) name from members where member_id=o.registered_by) name from orders o where o.order_id=123 in first solution there is join and in second subquery. what's better and why? sorry for this mess :D -ll
can't find ft-min_word_len in /etc/my.conf ?
hi, I need to change ft_min_word_len fro 4 to 3. the proces is very well explained on mysql.com though, when open /etc/my.conf can't find the ft_min_word_len line? when check is there: mysql show variables like 'ft_min_word_len' ft_min_word_len4 am I looking at the wrong file or something? thanks. -ll Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can't find ft-min_word_len in /etc/my.conf ?
Thanks. Though, I added, repair table, restarted mysql - and the same :( -ll --- Warren Young [EMAIL PROTECTED] wrote: Lamp Lists wrote: I need to change ft_min_word_len fro 4 to 3. the proces is very well explained on mysql.com though, when open /etc/my.conf can't find the ft_min_word_len line? If a value for a configurable isn't given in my.cnf, it takes the default value. So, add the line, restart the server, and it will override the default. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
different results between FULLTEXT search and LIKE search
hi,
I created table tasks
create table tasks(
task_id, int(4) not null primary key,
task text not null,
resolution text not null,
fulltext (task, resolution)
)engine=myisam
when I run
seect * from tasks match(task,resolution)
against('certain service' in boolean mode)
I would get one record and the phrase is in resolution
column.
though, when I serach using LIKE
select * from tasks where task like '%certain
service%' or resolution like '%certain service%'
I would get 2 records. one record is the same as the
one above and the 2nd has the phrase in task column.
means there are 2 records, but fulltext shows me only
one.
what I'm doing wrong?
thanks.
-ll
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: different results between FULLTEXT search and LIKE search
--- Lamp Lists [EMAIL PROTECTED] wrote:
hi,
I created table tasks
create table tasks(
task_id, int(4) not null primary key,
task text not null,
resolution text not null,
fulltext (task, resolution)
)engine=myisam
when I run
seect * from tasks match(task,resolution)
against('certain service' in boolean mode)
I would get one record and the phrase is in
resolution
column.
though, when I serach using LIKE
select * from tasks where task like '%certain
service%' or resolution like '%certain service%'
I would get 2 records. one record is the same as the
one above and the 2nd has the phrase in task column.
means there are 2 records, but fulltext shows me
only
one.
what I'm doing wrong?
thanks.
-ll
just made 2nd test and got different reault too:
select * from tasks match(task,resolution)
against('+certain +service' in boolean mode)
result: 232 records
select * from tasks where (task like '%certain%' and
task like '%service%') or (resolution like '%certain%'
and resolution like '%service%')
result: 7 records
?!?!!??
-ll
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: different results between FULLTEXT search and LIKE search
--- Lamp Lists [EMAIL PROTECTED] wrote:
--- Lamp Lists [EMAIL PROTECTED] wrote:
hi,
I created table tasks
create table tasks(
task_id, int(4) not null primary key,
task text not null,
resolution text not null,
fulltext (task, resolution)
)engine=myisam
when I run
seect * from tasks match(task,resolution)
against('certain service' in boolean mode)
I would get one record and the phrase is in
resolution
column.
though, when I serach using LIKE
select * from tasks where task like '%certain
service%' or resolution like '%certain service%'
I would get 2 records. one record is the same as
the
one above and the 2nd has the phrase in task
column.
means there are 2 records, but fulltext shows me
only
one.
what I'm doing wrong?
thanks.
-ll
just made 2nd test and got different reault too:
select * from tasks match(task,resolution)
against('+certain +service' in boolean mode)
result: 232 records
select * from tasks where (task like '%certain%' and
task like '%service%') or (resolution like
'%certain%'
and resolution like '%service%')
result: 7 records
?!?!!??
-ll
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
even worse:
select count(*) from tasks match(task,resolution)
against('certain')
result: 0
select count(*) from tasks where task like '%certain%'
or resolution like '%certain%';
result: 173
?
-ll
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to re-index a table?
hi, I have table, something like: create table example ( ex_id integer(8) not null auto_increment primary key, ex_col1 int(8) null, ex_col2 int(4) not null, index(ex_col1), index(ex_col2) )engine=myisam; ex_col1 and ex_col2 are indexed separately. Now I need to make one index with these to columns. Using MySQL Browser I did it no problem. I think I have to re-index them now, right? How? thanks for any help. -a Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping
Simple Regex Question
It's been a few years since I did any regex queries, but I swear I used to be able to do something like: SELECT fieldname FROM tablename WHERE field RLIKE '(^|\|)2(\||$)'; And that would find '2', with an optional '|' at the beginning, or at the beginning of the line, and an optional '|' or end of the line. So, it would match the following: 2|3 1|2|3 1|2 But not 1|20|3 Can someone give me a little guidance on this? I'm pulling my hair out on what should be a simple thing The database is currently in 4.1 but will soon be going to 5.0, so, ideally the solution will work in both... Thanks.
Sorting Question
Does anyone have a clever way that I can sort on two fields? Let me explain...I need the sort to be by the uid field (see below), but also have the parent field taken into consideration: Here's a sample of what I get now: +---+---++ | uid | duplicate | parent | +---+---++ | 12880 | 1 | 12878 | | 12879 | 0 | NULL | | 12878 | 0 | NULL | | 12877 | 0 | NULL | | 12840 | 0 | NULL | | 11616 | 0 | NULL | | 10871 | 0 | NULL | | 7696 | 0 | NULL | | 5770 | 0 | NULL | +---+---++ Here's what I want: +---+---++ | uid | duplicate | parent | +---+---++ | 12879 | 0 | NULL | | 12878 | 0 | NULL | | 12880 | 1 | 12878 | | 12877 | 0 | NULL | | 12840 | 0 | NULL | | 11616 | 0 | NULL | | 10871 | 0 | NULL | | 7696 | 0 | NULL | | 5770 | 0 | NULL | +---+---++ Note that uid 12880 is now AFTER 12878, which is its parent. I know in PHP I can easily do this. Just wondering if I can get it out of MySQL directly, rather than writing PHP code to do it. This is MySQL 5.0, by the way. Thanks, Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't find file: 'general_log'
Hi Joseph, Joseph Koenig wrote: Hi, I'm running MySQL 5.0.27-standard-log on RHEL 4. After install, I moved the data directory of MySQL. I updated everything that SELinux complained about, etc., and have a functional install of MySQL. It's actually been up and running with no issues for months. However, every time I run a mysqldump, I get: mysqldump: Got error: 1017: Can't find file: 'general_log' (errno: 2) when using LOCK TABLES mysqldump: Couldn't execute 'show create table `general_log`': Can't find file: 'general_log' (errno: 2) (1017) mysqldump: Couldn't execute 'show create table `slow_log`': Can't find file: 'slow_log' (errno: 2) (1017) My dump proceeds and just spits these errors out to me. From what I can tell, no harm is done, as the dump is full and is perfectly usable for restoring databases from. However, it's driving me nuts. Is there any way to create the necessary tables now so that MySQL does actually start logging everything they way it should be able to and also will make these errors go away? Thanks in advance, It's a hard to tell from this description what is the matter. Can you connect via mysql and run SHOW CREATE TABLE slow_log without errors? If so, what storage engine do they use? It sounds to me like they might be using the CSV storage engine and the file isn't there. Error 2 is 'OS error code 2: No such file or directory' according to perror. You probably don't want to mysqldump a big CSV file of your general log, at least not if you're using this for backups (but maybe you do, I don't know). In short, you might want to DROP the tables instead of creating them. Baron Thanks for the reply. I went and checked a few things and in my data dir, there is a mysql.log file that is actively logging any mysql activity. In data dir/mysql there is a general_log.CSM, general_log.CSV, general_log.frm, and the same goes for slow_log. However, the .CSV files are empty. Based on this, any additional thoughts for me? Again, thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE and INSERT in one
Hi, I want to update a table if it meets some conditions (already exist) and INSERT otherwise? Right now I am doing it this way: 1. SELECT the record 2. If it exist, I UPDATE it 3. If it does not exit, I INSERT a new record Could this be done in one query? I mean like conditional update/insert? /Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql question.
hi i need help regarding a sql query in my php app. the query is : $SQL = SELECT DISTINCT(EMail) FROM mena_guests WHERE Voted = 'yes' LIMIT $startingID,$items_numbers_list; i want to sort this query by the number of the repeated EMail counts. can anyone help me with that please ?
Re: Is there a professional quality mySQL GUI for Linux?
On Mar 15, 2007, at 4:25 AM, Daevid Vincent wrote: Can anyone recommend a real, quality, professional level mySQL GUI for Linux? KDE, Gnome, whatever. Doesn't matter. Beggars can't be choosers right. Something along the lines of SQLYog (Enterprise ideally). I'm kind of disappointed that I can't seem to find anything. They're all either some Admin tool designed to setup users and all that stuff. Yawn. Or they're so limited, I might as well just use an XP VMWare and a windows GUI client instead. Sadly SQLYog has no intentions of porting to Linux :-\ mySQL Query Browser is for the most part useless. It's v1.1.18 and gives almost no benefit to using the CLI mode. You can't sort by clicking headings. They UI is awkward to use. You can't even set the font sizes, so it's HUGE (at least in my KDE it is). mySQL Workbench is Alpha, and I couldn't even get it to connect to the localhost server (despite the other tools in that package work)!!? phpMyAdmin is wonderful -- for a Web UI tool. But not very practical for serious development. Anything else in my search is either equally amateur or simply just an inactive or dead project. How is it that mySQL is effectively a Linux native tool for all intents and purposes, yet there isn't nearly the level of GUIs for it that there are for Windows?! (Sorry Daevid, I neglected to send this to the entire list) After searching around, I settled on AquaFold's Aqua Data Studio 6.0 (www.aquafold.com). I've been using it since version 4. It is a Java app, but it is very professional and works real well. I use it on my MacBookPro as well as my Linux boxes. Not cheap though. $399 per license. However, it is the best thing around. The license allows multiple installs, but you are only allowed to run one at a time. This is a nice convenience. It also supports many databases besides MySQL. Again, expensive but worth it in terms of productivity. -Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with upgrade
I upgraded a server from 4.0 to 4.1 and then to 5.0 but I've been
running into a problem. When I was running 4.0 the passwords with the
password('password') command where being created like this:
2a287c002f9773dc now after I upgraded to 4.1 when I add a new user the
passwords end up being like this: *A645B7228E54A58C02488027D2FBD96853E7BF8B
When I connect via the command line everything is peachy but when I
connect via PHP I kept getting this error:
*Warning*: mysql_connect() [function.mysql-connect
http://karma.detroitonline.com/function.mysql-connect]: Client does
not support authentication protocol requested by server; consider
upgrading MySQL client in */usr/local/vps/karma/apache/htdocs/test.php*
on line *3*
Client does not support authentication protocol requested by server;
consider upgrading MySQL client
So I upgraded to 5.0 and I'm still seeing long passwords and I'm still
getting this error with PHP...can anyone tell me what's going on here? I
upgraded another server to 5.0 and I didn't see this problem at all. I'm
a little screwed right now because this server is replacing one that had
to come out of service due to a bad harddrive and now clients can't make
connections anymore with their scripts. The old server was running
4.0it's looking like I might have to downgrade until this is fixed...
any help would be greatly appreciated
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Difference between Pro and commuity edition
I am trying to find out the difference between the Pro and community editions of MySQL. If I want to support GPL applications I can install the community ed. If later want to support a non GPL application do I need to install anything different. I have found lots of web pages but they don't quite answer this question (apologies if I have mussed something obvious). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installing 32 and 64 bit versions on Solaris
I have some applications that need a 32 bit library for Mysql. I would prefer to install the 64 bit version of MySQL but that doesnt seem to come with 32 bit libraries. Can I install both libraries. Any pointers would be appreciated Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Results Order Question
I'm running into a problem with a ratings script I'm writing. The overview is that a user can rate an item from 1-10. What I want it to do is display the highest rated item, with 10 being the highest rating. In case there are multiple items sharing the same score, the item that has been rated the most should be displayed. Here's a bit of the code: $albums=mysql_query(SELECT albumRating,totalRatings WHERE albumType='Album' ORDER BY albumRating,totalRatings DESC); The problem I'm running into is that it always displays the item with a 9 rating and not the 10. I'm figuring it's because mysql is seeing the order as 9,8,7,6,5,4,3,2,10,1 Am I going to have to store the numbers as 01,02,03,etc... in order to get this to work right or is there a better way to structure the query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Autoindexing
Well I believe I'll need to update mysql since I just realized this server is using 3.23. Gotta love taking something over from someone and finding out they weren't very good at the job to begin with. Karl Larsen wrote: Hi Remo, your method works fine on version 4.1 and the one shown for version 5 does not work here. Nice to know there is a SQL word AUTO_INCREMENT to do the job. Karl Remo Tex wrote: If you are using autoincrement filed you could try this: ALTER TABLE `my_database`.`my_table` AUTO_INCREMENT = 201; ...or else if it is some stored proc you should find and edit table where it sotres index/counter data.. Tom Ray [Lists] wrote: Hey, I have a really simple question (I hope)..I have a database that has a field in it that autoindexes the number. I screwed up and imported a bunch of wrong data. Up to row 200 it's right, beyond that it was wrong so I had delete it all. The problem now is the autoindexing is set to 1105, I want to change it so the next insert gets a number of 201 not 1105. I've tried to change it via phpMyAdmin but it keeps going back to 1105. Is there anyway I can do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Autoindexing
Hey, I have a really simple question (I hope)..I have a database that has a field in it that autoindexes the number. I screwed up and imported a bunch of wrong data. Up to row 200 it's right, beyond that it was wrong so I had delete it all. The problem now is the autoindexing is set to 1105, I want to change it so the next insert gets a number of 201 not 1105. I've tried to change it via phpMyAdmin but it keeps going back to 1105. Is there anyway I can do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf / mysqld logging
Hey there, I inherited a couple of servers that are in production but I noticed that there's not active logging for mysql on any of them. I looked and they don't have an active my.cnf file any where on the machines. One machine is running mysql 4.1.5 and the other is running mysql 5.0.18 When I ran a ps -ef on the machines I saw this for both /usr/local/mysql/bin/mysqld defaults-extra-file=/usr/local/mysql/data/my.cnf so I went and I put a my.cnf right where it was looking for it. Stopped/Started the mysql server and I'm still not seeing any logging happening for the mysql servers. Perhaps I'm messing this up? I want them to log everything to /var/log/mysqld.log and in the my.cnf I have the line: log = /var/log/mysqld.log Can anyone tell me where I'm messing this up or what is wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf / mysqld logging
How would I do that? I'm still a novice when it comes to many aspects of mysql. [EMAIL PROTECTED] wrote: Hi, Try to enable query log. Thanks Regards Dilipkumar [EMAIL PROTECTED]: Hey there, I inherited a couple of servers that are in production but I noticed that there\'s not active logging for mysql on any of them. I looked and they don\'t have an active my.cnf file any where on the machines. One machine is running mysql 4.1.5 and the other is running mysql 5.0.18 When I ran a ps -ef on the machines I saw this for both /usr/local/mysql/bin/mysqld defaults-extra-file=/usr/local/mysql/data/my.cnf so I went and I put a my.cnf right where it was looking for it. Stopped/Started the mysql server and I\'m still not seeing any logging happening for the mysql servers. Perhaps I\'m messing this up? I want them to log everything to /var/log/mysqld.log and in the my.cnf I have the line: log = /var/log/mysqld.log Can anyone tell me where I\'m messing this up or what is wrong? -- 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]
Can't reload from dump file
We have had some problems with corrupt data due to running out of space recently. I wanted to repair the tables so I backed up our database by dumping to a file with mysqldump. I then tried to repair the database and had some problems with that. I stopped MySQL and moved the database to database.old and restarted the database. I wanted to import the dump file. As it is 700 MB it takes a while to load. I got this error [EMAIL PROTECTED] backups]# mysql -u root -prt3.sql Enter password: ERROR 1005 (HY000) at line 694: Can't create table './rt3/Attributes.frm' (errno: 121) and in the error file: 060129 19:27:32 InnoDB: Error: table `rt3/Attributes` already exists in InnoDB internal InnoDB: data dictionary. Have you deleted the .frm file InnoDB: and not used DROP TABLE? Have you used DROP DATABASE InnoDB: for InnoDB tables in MySQL version = 3.23.43? InnoDB: See the Restrictions section of the InnoDB manual. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html What can I do? -- Chris Mason NetConcepts (264) 497-5670 Fax: (264) 497-8463 Int: (305) 704-7249 Fax: (815)301-9759 UK 44.207.183.0271 Cell: 264-235-5670 Yahoo IM: [EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY for ints
I'm getting a strange ordering when using ORDER BY on a int column. The rows are being returned sorted as follows: 1 10 11 12 13 14 15 2 3 4 5 6 7 8 9 I'm sure this is a simple one, but I haven't found an answer in the archives. Thanks, Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY for ints
On Sep 27, 2005, at 2:58 AM, Jigal van Hemert wrote: You are most likely to get meaningful suggestions to solve the mystery if you include the table definition (output of SHOW CREATE TABLE tablename) and the query. mysql SHOW CREATE TABLE Player| + +--- --+ | Table | Create Table | + +--- --+ | Player | CREATE TABLE `Player` ( `id` int(16) NOT NULL auto_increment, `first_name` varchar(32) NOT NULL default '', `last_name` varchar(32) NOT NULL default '', `year` varchar(16) NOT NULL default '', `height` varchar(8) NOT NULL default '', `season` int(4) NOT NULL default '0', PRIMARY KEY (`id`), KEY `season` (`season`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | + +--- --+ 1 row in set (0.00 sec) It now looks like mysql is returning the correct thing (at least on the command line), but for some reason inside php it's all screwedup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY for ints
On Sep 27, 2005, at 3:29 AM, Jasper Bryant-Greene wrote: mysql SHOW CREATE TABLE Player| | Player | CREATE TABLE `Player` ( `id` int(16) NOT NULL auto_increment, `first_name` varchar(32) NOT NULL default '', `last_name` varchar(32) NOT NULL default '', `year` varchar(16) NOT NULL default '', `height` varchar(8) NOT NULL default '', `season` int(4) NOT NULL default '0', PRIMARY KEY (`id`), KEY `season` (`season`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | What column are you ordering on? The command in PHP is: $query=SELECT id,first_name,last_name FROM Player ORDER BY id; $players=mysql_query($query); When issued from the mysql prompt, order is fine, but when called from php I'm getting that strange order: 1, 10, 11, 12, etc... Steve Cochran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY for ints
On Sep 27, 2005, at 9:56 AM, Edward Vermillion wrote:
Michael Stassen wrote:
Stephen A. Cochran Lists wrote:
On Sep 27, 2005, at 3:29 AM, Jasper Bryant-Greene wrote:
mysql SHOW CREATE TABLE Player|
| Player | CREATE TABLE `Player` (
`id` int(16) NOT NULL auto_increment,
`first_name` varchar(32) NOT NULL default '',
`last_name` varchar(32) NOT NULL default '',
`year` varchar(16) NOT NULL default '',
`height` varchar(8) NOT NULL default '',
`season` int(4) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `season` (`season`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
What column are you ordering on?
The command in PHP is:
$query=SELECT id,first_name,last_name FROM Player ORDER BY id;
$players=mysql_query($query);
When issued from the mysql prompt, order is fine, but when
called from php I'm getting that strange order:
1, 10, 11, 12, etc...
Steve Cochran
Then the problem is in your php code. Mysql will certainly return
the rows ordered the same way to both the mysql client and to
php. If php is showing a different order, then it must be
something your php code is doing. If you post the code which
displays the results, I'm sure someone could point out the
problem, though that really belongs on a php list.
Michael
I had this same problem a while back, and while I'm probably making
the same mistakes you are but have no idea what they are, I solved
it by using ZEROFILL on the field I was sorting. So that PHP was
seeing 0001, 0002, 0003...
Worked for me, although from some of the replies I'm wondering if
that wasn't the best way to do it. :/
Well, since I wasn't the only person to have this problem, I'll post
this here in case someone has the answer. My php code is:
$query=SELECT id,first_name,last_name FROM Player ORDER BY id;
$players=mysql_query($query);
$numPlayers=mysql_numrows($players);
for ($i=0, $i $numPlayers; $i++)
{
$label = mysql_result($players,$i,'id');
echo $labelbr
}
And that generates an order like it was doing a string comparison.
I'm just iterating over the rows in the result in order, so not sure
what would be applying another sort.
Thanks in advance.
Steve Cochran
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY for ints
On Sep 27, 2005, at 10:28 AM, Pooly wrote:
The command in PHP is:
$query=SELECT id,first_name,last_name FROM Player ORDER BY id;
$players=mysql_query($query);
When issued from the mysql prompt, order is fine, but when
called from php I'm getting that strange order:
1, 10, 11, 12, etc...
Steve Cochran
Then the problem is in your php code. Mysql will certainly return
the rows ordered the same way to both the mysql client and to
php. If php is showing a different order, then it must be
something your php code is doing. If you post the code which
displays the results, I'm sure someone could point out the
problem, though that really belongs on a php list.
Michael
I had this same problem a while back, and while I'm probably making
the same mistakes you are but have no idea what they are, I solved
it by using ZEROFILL on the field I was sorting. So that PHP was
seeing 0001, 0002, 0003...
Worked for me, although from some of the replies I'm wondering if
that wasn't the best way to do it. :/
Well, since I wasn't the only person to have this problem, I'll post
this here in case someone has the answer. My php code is:
$query=SELECT id,first_name,last_name FROM Player ORDER BY id;
$players=mysql_query($query);
$numPlayers=mysql_numrows($players);
for ($i=0, $i $numPlayers; $i++)
{
$label = mysql_result($players,$i,'id');
echo $labelbr
}
Try with mysql_fetch_array
And that generates an order like it was doing a string comparison.
I'm just iterating over the rows in the result in order, so not sure
what would be applying another sort.
or it's likely that mysql_result retrieve an array of rows
(well-ordered), but fetch it by using a string for the index.
This seems unlikely since the mysql_result takes a row number (int)
to select which fetched row to get a cell from (zero based).
Using mysql_fetch_array woulnd't work since I need to select a
certain row based on the order by, but not necessarily accessed in
sequence as shown in the code above.
Steve Cochran
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 4.1.14 memory leak.
I'm using mysql 4.1.14 rpm's on Fedora Core 4. I've setup max_heap_table_size to allow for 500M heap tables. Currently I use roughly 435M in heap by about 16 different tables. Some use btree, other's use default hash. The problem is, over time mysql looks like this in top: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2898 mysql 15 0 1127m 626m 2076 S 18.3 62.5 2974:17 mysqld-max mysql is forcing the machine to swap. I've tried dropping all heap tables that are in use and this does not free the ram. I have to restart mysql and and using init-file reload the heap tables and everything is fine for a couple of days. My problem is strikingly similar to this: http://groups.google.com/group/mailing.database.myodbc/browse_thread/thread/485647dae02b59a1/32f0009e9cb135b7?lnk=stq=virtual+heap+mysqlrnum=1utoken=rT79JjoAAABobq0US6-f3p1tupn-bp7-GyqMAsXdt4_lvPhOluyGzfrEz8xuJ8FzZhQCB5gw1_s38laLLlcPg_ShAKo-q_vP I'm using the mysql rpm's from mysql.com http://mysql.com. Other than restarting mysql nightly, is there anything I could do to further debug this? Thanks, Mysql Rocks.
Re: Loading Decimal Values with load data infile
Hallo Thorsten, Op 14 Sep 05 schreef Thorsten Moeller aan MySQL Mailinglist: TM i am trying to load a csv file with LOAD DATA INFILE. This File TM contains columns with decimal values with the german comma instead of TM the dot as separator (e.g. 3.20 is 3,20). Is there an option to handle TM this during or immediately before LOAD with MySQL Commands/Tools. Now we TM only see values where the values on the right of the comma have been cut TM off. I asked this same question on June 22, and got the following answer: What about reading the data into the table and storing the 'amount' in varchar for now. Then you can run an update query in which you replace the decimal comma with a decimal point and store that in the float field (emptying the varchar in the process). UPDATE `table` SET `amount`= REPLACE( `v_amount`, ',' , '.'), `v_amount` = NULL WHERE `v_amount` IS NOT NULL; Groetjes, Hans. jdh punt beekhuizen bij duinheks punt xs4all punt nl --- GoldED+/LNX 1.1.5/050823 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mark a single row?
Hallo, I would like a single row in a table, and not more than one, to be used as a preferred value in another application. Is this possible, and, if yes, how? Groetjes, Hans. --- GoldED+/LNX 1.1.5/050823 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mark a single row?
Hallo Jasper, Op 29 Aug 05 schreef Jasper Bryant-Greene aan mysql: I would like a single row in a table, and not more than one, to be used as a preferred value in another application. JBG add a column 'preferred', tinyint(1) NOT NULL. Thank you! Groetjes, Hans. --- GoldED+/LNX 1.1.5/050823 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mark a single row?
Hallo Michael, Op 29 Aug 05 schreef Michael Stassen aan [EMAIL PROTECTED]: I would like a single row in a table, and not more than one, to be used as a preferred value in another application. JBG add a column 'preferred', tinyint(1) NOT NULL. MS It's hard to say without knowing just what you mean by used as a MS preferred value in another application, but I suspect that adding a MS whole column for this may not the best way to go. You'd be storing a MS lot of 0s just to keep one 1. The simplest solution may be to code the MS preferred row's id in your app. Your instinct to keep this value in the MS db is probably a better idea, however, especially if the preferred id MS could ever change. It won't change very often, but there certainly is a chance. That's why I decided to keep it in the database somehow, rather than hard-coding it. Also, this way the users can change it without having to mess with the code. The amount of 0s would not be more than about 40, so not a big disaster. MS An alternative to adding a column would be to add a table. MS Something like: MSCREATE TABLE pref_value (pref_id INT); MSINSERT INTO pref_value (pref_id) VALUES (id_of_preferred_row); But this method is much more elegant. I wonder why I couldn't think of it myself... Thank you very much! Groetjes, Hans. --- GoldED+/LNX 1.1.5/050823 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile
Hallo, Op 30 Jun 05 schreef blackwater dev aan mysql@lists.mysql.com: bd 1,23,345,45; bd 34,4,444,1er; bd Load data local infile '/httpd/htdocs/sql/loader.sql' into table bd vehicles fields terminated by ',' enclosed by ' lines terminated by bd ';' I think it should be: enclosed by '' Groetjes, Hans. jdh punt beekhuizen bij duinheks punt xs4all punt nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Preventing duplicates with load data
Hello Mike, On 27 Jun 05, mos wrote to mySQL list: How can I prevent duplicate entries when I fill the data base with load data? I tried ignore, but that has no effect. m Ignore/Replace will only work on Unique keys and I bet your key is m not unique. If you make it unique, then Ignore will keep the m existing value, or Replace will replace the existing row with the m new row. You're good at betting :) The next question would of course be: how do I create a unique key, but somebody else already asked that and got a clear answer. Thank you very much. Regards, Hans. jdh dot beekhuizen at duinheks dot xs4all dot nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Decimal comma in input
Hallo Anoop, Op 22 Jun 05 schreef Anoop kumar V aan Jigal van Hemert [EMAIL PROTECTED],: AkV Alternatively, you can parse the text files using application logic AkV (java, c++, etc) and then after extracting (and cleaning) your data AkV insert them into mysql. I had been thinking about that, but that would be a cowardly way out :) As I'm very new to [My]SQL, I prefer to use its own methods as much as possible. Groetjes, Hans. jdh punt beekhuizen bij duinheks punt xs4all punt nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Decimal comma in input
Hallo Jigal, Op 22 Jun 05 schreef Jigal van Hemert aan [EMAIL PROTECTED]: JvH What about reading the data into the table and storing the JvH 'amount' in varchar for now. Then you can run an update query in JvH UPDATE `table` SET `amount`= REPLACE( `v_amount`, ',' , '.'), JvH `v_amount` = NULL WHERE `v_amount` IS NOT NULL; Perfect! Thank you very much. Groetjes, Hans. jdh punt beekhuizen bij duinheks punt xs4all punt nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Preventing duplicates with load data
Hello, How can I prevent duplicate entries when I fill the data base with load data? I tried ignore, but that has no effect. Probably I'm something very elementary, but I'm still learning... Regards, Hans. jdh dot beekhuizen at duinheks dot xs4all dot nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Decimal comma in input
Hallo, I get data with a format that I can't choose. The fields are enclosed in double quotes (), separated by commas (,). The main problem is that some fields contain amounts with a decimal comma. I have not found a way yet to load these data properly. Could somebody help me please? Groetjes, Hans. --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Decimal comma in input
Hello Anoop, On 22 Jun 05, Anoop kumar V wrote to All: AkV Are u saying that u have data in a text file and you need to parse AkV this and insert them into mysql? That's what I Ntried to say, yes :) The main problem is the decimal comma in the amounts. Regards, Hans. jdh dot beekhuizen at duinheks dot xs4all dot nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Filling database with load data
Hallo Peter, Op 30 May 05 schreef Peter aan [EMAIL PROTECTED]: P http://dev.mysql.com/doc/mysql/en/load-data-local.html P # P If LOAD DATA LOCAL INFILE is disabled, either in the server or the P client, a client that attempts to issue such a statement receives the P following error message: P ERROR 1148: The used command is not allowed with this MySQL version P I hope this help. I sure does, thank you very much. And that information was even in the tutorial part of the manual. I'm ashamed of myself :( Groetjes, Hans. --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Filling database with load data
Hallo, I'm using MySQL 4.0.23a as found in the SlackWare 10.1 distribution. As I'm totally new to MySQL I tried tom follow the tutorial from the manual. When I tried to fill the data base using a text file with the command load data local infile '~/temp' into table huisdier; I got ERROR 1148: The used command is not allowed with this MySQL version What's going wrong? The insert command works without problem. Groetjes, Hans. --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow as Christmas join
Kevin Cagle wrote: Hello! I have a couple of tables I am doing a join on. Both have 6 fields. Most fields have fewer than 16 characters in them, but at most 75. Neither table is huge: one with 14004 rows and the other with 23677. I created a temp table to insert the data into. When I perform the join, it takes about 17 minutes to complete. The result is only 23674 rows. Question is, why is this query taking so long? I have searched the manual and only found an example where they talk about millions of rows being slow and mine has far fewer than that. It is a one to many relationship between the two tables, but should that really slow things down that much? Is there a way to speed things up a little...or a lot? Just for reference, here is the syntax I am using: insert into tmp1 select table2.field1, table1.field2, table1.field3, table2.field2, table2.field3, table2.field4, table2.field5, table2.field6, table1.field4, table1.field5, table1.field6 from table2,table1 where table2.field1=table1.field1; Hi! You may want to revisit the query statement above and see if you can do an outer join. That should help you as far as the query statement goes. Next, on to your indexes... (I have tried the same query with the tables reversed, i.e, table1.field1=table2.field1 but it still takes a long time.) MySQL Version: 4.0.15 Mac OS X 10.3.7 Explain: ++--+---+--+-+--+--- +---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+--- +---+ | table1 | ALL | NULL | NULL |NULL | NULL | 14004 | | | table2 | ALL | NULL | NULL |NULL | NULL | 23677 || ++--+---+--+-+--+--- +---+ Judging from the output of your EXPLAIN statement, MySQL is forced to do a full table scan (14004 rows in one table and 23677 rows) in another using your current query statement. At the table-level, you should consider using an index, especially in regards to the columns that you list in your WHERE clause since you say that this is a one-to-many relationship. Going back to your query statement, field1 in each table could stand to be indexed. If there is anything else that will help you solve this problem, let me know and I will be happy to provide it! Try the two steps I mentioned and see if that doesn't help you out. Thanks In Advance! --kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of memory
Philippe Poelvoorde wrote: Hi, mysqldump returns with this error on one of my nightly batch : mysqldump: Out of memory (Needed 8164 bytes) mysqldump: Got error: 2008: MySQL client run out of memory when retrieving data from server Fri Dec 24 00:00:22 CET 2004 Is there any way to avoid this particular error ? (bear I mind that I don't have any access to my.cnf on this environnement!) The last table in the dump (and not dump, it stops just after the CREATE TABLE) is not that big (less than 8Mo on disk). See what happens if you try it with the --quick option. This will prevent mysqldump from writing to memory before writing to the dump file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA LOCAL INFILE
Richard Whitney wrote: Hi! Can someone point me in the right direction? I have this that works in v.4x: $sql = LOAD DATA LOCAL INFILE '$file' REPLACE INTO TABLE `members` FIELDS TERMINATED BY '\\t' OPTIONALLY ENCLOSED BY '\' ESCAPED BY '' LINES TERMINATED BY '\\r\\n'; When I try it using 3.23.55 I get the following error: #1148 - The used command is not allowed with this MySQL version Is there any way I can make this work using 3.23.55? TIA! R. Whitney Transcend Development Producing the next phase of your internet presence http://xend.net Premium Quality Web Hosting http://hosting.xend.net [EMAIL PROTECTED] 310-943-6498 602-288-5340 The day this country abandons God is the day God will abandon this country Linux is like a wigwam, no windows, no gates, apache inside. - borrowed from Sharon Kimble off of the Fedora mailing list Someone else had asked about the LOAD DATA syntax earlier this morning and getting the same error message that you're getting and one of the Ensita guys responded with this link: http://dev.mysql.com/doc/mysql/en/LOAD_DATA_LOCAL.html It looks like you might fall into the MySQL version discussed in the link. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
runaway process eating all cpu-time?
Hi there, after a system-crash I got the following problem. Some seconds after starting mysql (v4.0.20) a process will stay there taking all cpu-time it can get (99.9% ;)) ... and it can't be terminated, just killed completely. mysqlcheck ran through without any problems, the logs tell nothing. I just did a strace on the last process doing that: --- time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 sched_yield() = 0 time(NULL) = 1093348120 rt_sigprocmask(SIG_SETMASK, NULL, [HUP INT QUIT PIPE TERM TSTP 32], 8) = 0 rt_sigsuspend([HUP INT QUIT PIPE TERM TSTP] unfinished ... --- SIGRT_0 (Real-time signal 0) --- --- Any idea where to look at that problem further? The system is a linux - redhat-7.2 with dual athlon-mp, 2 GB ram, running kernel-2.4.26. Using the official mysql.com-binaries (rpm-install). TIA, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: runaway process eating all cpu-time?
Hi Victor, thanks for your reply. Victor Pendleton wrote: Do you have anything in the show processlist? Are you using innodb table types and a rollback is occurring? mysql show processlist; +---+-+---+---++--++--+ | Id| User| Host | db| Command| Time | State | Info | +---+-+---+---++--++--+ | 810 | DELAYED | | phpadsnew | Delayed_insert | 3| Waiting for INSERT | | | 10125 | root| localhost | NULL | Sleep | 70 || NULL | | 12020 | root| localhost | NULL | Query | 0| NULL | show processlist | +---+-+---+---++--++--+ Could it be the delayed_insert thread? I checked its tables extended already. Any way to see a connection-id - process-id relation? I'm using innodb just for one table at the moment. but its idle too. Any ideas? Thanks, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using CASE for store values
Hi allit's possible to use the CASE clause in MySQLlike this:SELECT statusID, Case(statusID = 1, TotalStatus1=TotalStatus1 + 1) Case(statusID = 2, TotalStatus2 = TotalStatus2 +1) It's possible? How to do that? Thanx for your time.Crie seu Yahoo! Mail, agora com 100MB de espaço, anti-spam e antivírus grátis!---BeginMessage--- Hi! I'm looking for a better way to insert large numbers of rows from a client application that is sampling physical data in real-time. In our case, we are using a C double hipvalues[100] cyclical array to buffer our sampled values. We're currently creating large query strings similar to: INSERT DELAYED INTO hipjoint VALUES (hipvalues[0]),(hipvalues[1]),(hipvalues[2]),(hipvalues[3]),etc... We would like to continue to insert our values directly from our client app without first having to dump the data to a temp file and LOAD DATA INFILEing it periodically. Any ideas? Config values of interest: key_buffer_size = 4G bulk_insert_buffer_size = 1024M We are using MySQL 4.1.2. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] ---End Message--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Returning where COUNT 5
Hi all how to do this in MySQL? Returning only records with COUNT 5? SELECT `groups`.`groupsDescr`, `roles`.`roles_Agroup`, `roles`.`rolesDescr`, COUNT(`roles`.`rolesDescr`) AS TOTAL FROM `roles` INNER JOIN `groups_roles` ON (`roles`.`rolesID` = `groups_roles`.`fkrolesID`) INNER JOIN `groups` ON (`groups_roles`.`fkgroupsID` = `groups`.`groupsID`) GROUP BY `roles`.`rolesDescr` WHERE COUNT(`roles`.`rolesDescr`) GT 5 MySQL 4.0.12 give an error... maybe because i'm using WHERE COUNT? Thanx for your time. - Crie seu Yahoo! Mail, agora com 100MB de espaço, anti-spam e antivírus grátis!
Using IF clause
Hi my table have an field when users can enter any chars If users dont send any content for that field(phone), mysql store ( ) - without quotes in that What im looking for: Display IF phone field is ( ) - NOT ENTER Otherwise display the phone of user. It's possible? Using IF clause Mysql give me an error - maybe IF(`users`.`phone` IS '( ) - ','NOT ENTER',`users`.`phone`) AS phonefield; What's happened? Thanx for your time Diga-me e eu esquecerei Mostre-me e lembrarei Ensina-me e aprenderei - Yahoo! Mail - Participe da pesquisa global sobre o Yahoo! Mail. Clique aqui!
Backing up InnoDB MySQL DB
Hi all we're using MySQL 4.0.12 with InnoDB... please do you have any scripts, comments, suggestions, ideas, tutorials... about how to backup from that DB? It's possible to? Thanx for your time. Diga-me e eu esquecerei Mostre-me e lembrarei Ensina-me e aprenderei - Yahoo! Mail - Participe da pesquisa global sobre o Yahoo! Mail. Clique aqui!
MySQL and Xbasic
Howdy -- What I want to do is use Xbasic as a frontend for MySQL. The problem is I'm mainly a PHP(and a little Perl) person. It looks like we should be able to make calls to the MySQL libs,dills headers from Xbasic. My interest for the mement is with doing it on Windows. Here's the Xbasic site: http://xbasic.org and a link to a DLL Primer tutorial: http://xbnotes.freehosting.net/dllprimer.html Anyone up to the challenge, David - Forwarded message from Steven V Gunhouse [EMAIL PROTECTED] - Date: Fri, 30 Apr 2004 22:20:00 -0400 From: Steven V Gunhouse [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Subject: Re: [xbasic] Calls to C libs (database) To: [EMAIL PROTECTED] On Sat, 01 May 2004 00:20:45 - davidjayjakson [EMAIL PROTECTED] writes: Howdy -- I read the reply previous post about SQL Server/MySQL support mentioned ODBC? What's the status of that, and can I get a lib to test? My other question(s) are, Can I make a call to a C lib? Or maybe directly to MySQL lib or MySQL API? Links to doc/examples explaining how to make calls to C libs in general as well as links, example dealing with MySQL would be appreciated. In general, the *.dec file for a C library will say EXTERNAL CFUNCTION FunctionName (argument1, argement 2, etc.) That's about it. There are two standard calling conventions. The standard calling convention used in C is different from that used in the Windows API. In C language, Windows API functions must be called using the keyword pascal, as Pascal (and also XBasic) use the same calling convention as the Windows API. Though it is possible to create a C library which can be called using the Pascal calling convention... If you have an appropriate header file (libname.h) for your library, you can tell which calling convention is used by whether or not the pascal keyword is used. If the header file declares a function as pascal, then XBasic would refer to it using the FUNCTION keyword. If the header file does not declare the function with the pascal keyword, then XBasic will refer to it using the CFUNCTION keyword. There are a couple of other requirements to actually compile a program which uses a library, but they are not specific to C. If you have a DLL file, then you will also need a LIB file to link against. And as mentioned, you will need to have or create a *.dec file for the library. If you have a C header file, creating your own *.dec file is pretty straightforward. The best thing to hit the Internet in years - Juno SpeedBand! Surf the Web up to FIVE TIMES FASTER! Only $14.95/ month - visit www.juno.com to sign up today! Yahoo! Groups Links To visit your group on the web, go to: http://groups.yahoo.com/group/xbasic/ To unsubscribe from this group, send an email to: [EMAIL PROTECTED] Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. - End forwarded message - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: [xbasic] Re: Calls to C libs (database)
Howdy -- I've been tinkering with Xbasic(http://xbasic.org). While there isn't any native DB support (ODBC is in the works). This got me to thinking about making calls to MySQL libs or dll(s) (in the case of Windows). Being mainly a PHP person (and no C), I'm hoping than someone will explain the forwarded email to me (example would be great), also here is alink to a how-to for using 3rd party dll and libs: http://xbnotes.freehosting.net/dllprimer.html TIA, David - Forwarded message from Ken Minogue [EMAIL PROTECTED] - Date: Sat, 01 May 2004 01:04:02 - From: Ken Minogue [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Subject: [xbasic] Re: Calls to C libs (database) To: [EMAIL PROTECTED] --- In [EMAIL PROTECTED], davidjayjakson [EMAIL PROTECTED] wrote: Howdy -- I read the reply previous post about SQL Server/MySQL support mentioned ODBC? What's the status of that, and can I get a lib to test? My other question(s) are, Can I make a call to a C lib? Or maybe directly to MySQL lib or MySQL API? Links to doc/examples explaining how to make calls to C libs in general as well as links, example dealing with MySQL would be appreciated. TIA, David Jackson Can't help you on MySQL, but you can get general information about calling other-language DLLs at http://xbnotes.freehosting.net/dllprimer.html Ken Yahoo! Groups Links To visit your group on the web, go to: http://groups.yahoo.com/group/xbasic/ To unsubscribe from this group, send an email to: [EMAIL PROTECTED] Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. - End forwarded message - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SLOW 22million rows, 5 hour query?
I have a table with 22,371,273 rows, current type is MyISAM. I have one
query tha took nearly 5 hours to complete. I do not know if this like
it should be or not, but I thought I'd ask the list. My gut feeling is
the `gropu by` that's slowing it down but nonetheless 5 hours seems
excessive. I'm trying to find the source of the limitation and work
from there.
Mysql:
4.0.18-standard, precompiled 32-bit sparc 2.8
Server:
Sun 420
Solaris 2.8
4x450MHZ Ultrasparc-II
4GB Ram
Two 50gb mounts, fiber channel scsi to EMC.
Brand new systems, totally idle.
I think everything relevant is here. The skinny: full table scan on
22 million rows, group and insert into new table. MyISAM and InnoDB
appear to give similar results when used as the destination
('new_table').
insert into new_table
select month_day, floor(bucket/3) as bucket, date, src, avg(value) as
value
from source_table
group by month_day, bucket, src;
Relevant `explain` details:
Full table scan: 22,371,273 rows, Using temporary; Using filesort
Query OK, 11495208 rows affected (4 hours 47 min 21.01 sec)
Records: 11495208 Duplicates: 0 Warnings: 0
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
10 minutes seems excessive...
Hello all... Source table has approximately 23,000,000 rows. The explain below shows we'll be working with 2,707,366 of those rows just inserting them into a table. Why does this take 10 minues to run? System specifications below all that. * Precompiled binary from mysql.com * Completely idle machine * Completely idle disk * Not I/O bound during query. * Mysqld maxing one cpu out reading data, but not writing to any temp tables on disk for quit some time. * Started with mysql-huge reference config. 'explain select * from below': == table: source_table type: range possible_keys: PRIMARY,MONTH_DAY key: PRIMARY key_len: 1 ref: NULL rows: 2707366 Extra: Using where CREATE TABLE tmp.seperate_disk_partition SELECT * FROM source_table WHERE month_day IN (14, 7, 31, 24) Query OK, 2862629 rows affected (9 min 37.36 sec) Records: 2862629 Duplicates: 0 Warnings: * Sun 420, 4-400mhz, 4gb ram. 100gb EMC attached via fiber channel. Server version4.0.18-standard Protocol version 10 ConnectionLocalhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 7 days 9 hours 6 min 50 sec Threads: 1 Questions: 3103 Slow queries: 89 Opens: 352 Flush tables: 1 Open C compiler:gcc (GCC) 3.3 C++ compiler: gcc (GCC) 3.3 Environment: machine, os, target, libraries (multiple lines) System: SunOS f10212-06.adc1.level3.com 5.8 Generic_108528-22 sun4u sparc SUNW,U Architecture: sun4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New user B setup - documentation?
Hallo, I'm totally new to MySQL or any other data bases... I installed MySQL 4.0.15a as supplied with the SlackWare 9.1 Linux distribution. The only documentation supplied are the Reference Manual en the man pages. But the Reference Manual is a little bit too touch for me. I managed to get mysqld started, but now I'm stuck. Is there any other documentation available somewhere that will lead me through the setup in a nice and gentle way? Groetjes, Hans. --- GoldED+/LNX 1.1.5cvs031202 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import from excel to MYSQL
Is there an easy way to get an excel spread sheet imported into a MYSQL database? Any links to a tutorial? Thanks in advance! Dustin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with mysql-crashes
Hey folks, before posting to the bugs-list I would like to try it here to get some help :). Every now and then MySQL-4.0.13 crashes on my Dual-Athlon-MP-machine. Its not reproducible nor I know a query which does it. (The log shows that at the end, so no query :-( : Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x5e13a478 is invalid pointer thd-thread_id=2223573 ) The server is the standalone db-server for one accessing webserver, its running RedHat-Linux 7.2 with all their latest patches. Kernel 2.4.19-XFS with MySQL running on an XFS-filesystem. No replication but query-cache enabled. Ok but now the worse thing started for 2 times I already got this problem in the logs: Number of processes running now: 16 mysqld-max process hanging, pid 17193 - killed mysqld-max process hanging, pid 17191 - killed mysqld-max process hanging, pid 17190 - killed mysqld-max process hanging, pid 17145 - killed mysqld-max process hanging, pid 17144 - killed mysqld-max process hanging, pid 17098 - killed mysqld-max process hanging, pid 17025 - killed mysqld-max process hanging, pid 16987 - killed mysqld-max process hanging, pid 28393 - killed mysqld-max process hanging, pid 28392 - killed mysqld-max process hanging, pid 28391 - killed mysqld-max process hanging, pid 28389 - killed mysqld-max process hanging, pid 28388 - killed mysqld-max process hanging, pid 28387 - killed mysqld-max process hanging, pid 28386 - killed mysqld-max process hanging, pid 28385 - killed 030722 20:46:26 mysqld restarted Afterwards there is a init-process taking 99% from one CPU. The first time I tried to reboot the machine, which didn't work as the shutdown process was hanging then. Any ideas whats wrong and how to fix it? Didn't see a mention of a fix for that problem in the 4.0.14-changelog, will it still fix it? Thanks in advance, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
index question
Ok, don't shoot me for not entirely understanding indexes. Can you build an index across two different tables w/in the same database? I need an index on fields in table a and in table b and I want that index to exist in table a. Is it possible? I'm running 4.0.12. TIA, Charlie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2-way replication..how many slaves?
I'm researching a specific network design and have some questions that may involve 2-way replication. I have one master mysql server doing 1 way replication to a large number of remotely located slaves. Each slave serves a number of proprietary software clients running inside a private network. These proprietary software clients do not have Internet access but of course the local slave does. These proprietary software clients enter data into 2 tables on the local slave. We now have the need to get these remote slaves to send the data in these 2 tables back to the master and merge it in 2 central tables. The amount of data that needs to be sent back is not terribly large and only needs to happen maybe once or twice a day. What is the most logical and efficient way to accomplish this? Is 2-way replication a candidate here with the master server also acting as a slave to each of the remote slaves? I was under the initial impression that any slave can only have one master and if so it would seem that 2 way replication would only work with 2 servers. What method would work if I had 1 master with 100 slaves and each of these slaves needed to merge 2 tables back into the single master? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble optimizing a query
Hello everyone,
I'm working with MySQL 3.23.41-log developing a chat application. I need to run a
query, order the results in
descending order, then get the last 0-15 entries that are less than 20 minutes old.
The query and tables in question
are explained at the bottom of this posting. In the query shown below, the number
1054889629 indicates at 20 minute
old UNIX Timestamp.
The query below returns the correct results, but as of yet, I have been unable to get
the query to take advantage of
the indexes in place. When sorting ASC, I can get a reasonable efficient query,
however that gives me the oldest 0-15
when I need the newest. If I sort by DESC, which would give me the incorrect answer,
the query uses Filesort, which
is unacceptable for my application.
The order the fields are selected in is unimportant and can be rearranged if need be.
The WHERE and ORDER BY sections
can be change freely so long as the resultant data is the same and indexes can be
added or removed as needed. This is
the only major query being run against the database.
Ideally, I'd like the query to use index, but I'd be satisfied if it just didn't use
filesort. Can anyone help me
tighten this up?
Thanks!
Jacob
-- Table/Query/Index Info --
Some field explanations:
Message_ID - Aribrary message ID
Username - Standard UNIX username
Date_Time - Date and time message was stored
Unix_Timestamp - UNIX_TIMESTAMP(Date_Time)
Sample table data is avaliable if needed.
CREATE TABLE Chat_2 (
Message_ID mediumint(8) unsigned NOT NULL auto_increment,
Username varchar(8) NOT NULL default '',
Date_Time datetime NOT NULL default '-00-00 00:00:00',
Message tinytext NOT NULL,
Visible enum('Y','N') NOT NULL default 'Y',
Unix_Timestamp int(11) NOT NULL default '0',
PRIMARY KEY (Message_ID),
KEY A_1 (Unix_Timestamp,Visible),
KEY A_3 (Unix_Timestamp,Visible,Username),
KEY A_2 (Unix_Timestamp,Visible,Date_Time,Username,Message_ID),
KEY A_4 (Unix_Timestamp,Visible,Unix_Timestamp)
) TYPE=MyISAM;
CREATE TABLE Users (
Username varchar(8) NOT NULL default '',
Access_Level tinyint(3) unsigned NOT NULL default '0',
Is_Active enum('Y','N') NOT NULL default 'Y',
Display_Name varchar(20) default NULL,
Picture_URL varchar(100) default NULL,
Added datetime default NULL,
Show_Icons enum('Y','N') NOT NULL default 'Y',
Clock_Format enum('24-hour','12-hour') NOT NULL default '24-hour',
Last_Modified timestamp(14) NOT NULL,
PRIMARY KEY (Username)
) TYPE=MyISAM;
mysql EXPLAIN SELECT Date_Time, Display_Name, Chat_2.Username,
Message, Users.Picture_URL, Users.Access_Level, Chat_2.Message_ID
FROM Chat_2, Users WHERE Unix_Timestamp 1054889629
Chat_2.Visible = 'Y' Chat_2.Username = Users.Username ORDER BY
Unix_Timestamp DESC LIMIT 0,15;
+--+--++---+---+-++--+
|table |type |possible|key|key_len|ref |rows|Extra |
| | | _keys | | | || |
+--+--++---+---+-++--+
|Chat_2|range |A_1,A_3,|A_1| 4|NULL | 8|where used; |
| | |A_2,A_4 | | | ||Using filesort|
| | || | | || |
|Users |eq_ref|PRIMARY |PRIMARY| 8|Chat_2. | 1| |
| | || | | Username|| |
+--+--++---+---+-++--+
2 rows in set (0.00 sec)
mysql EXPLAIN SELECT Date_Time, Display_Name, Chat_2.Username, Message,
Users.Picture_URL, Users.Access_Level, Chat_2.Message_ID FROM Chat_2,
Users WHERE Unix_Timestamp 1054889629 Chat_2.Visible = 'Y'
Chat_2.Username = Users.Username ORDER BY Unix_Timestamp LIMIT 0,15;
+--+--++---+---+++--+
|table |type |possible|key|key_len|ref |rows|Extra |
| | | _keys | | ||| |
+--+--++---+---+++--+
|Chat_2|range |A_1,A_3,|A_1| 4|NULL| 8|where used|
| | |A_2,A_4 | | ||| |
| | || | ||| |
|Users |eq_ref|PRIMARY |PRIMARY| 8|Chat_2. | 1| |
| | || | |Username|| |
+--+--++---+---+++--+
2 rows in set (0.00 sec)
--- Msg sent via [EMAIL PROTECTED] - http://mail.statisticalanomaly.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble optimizing a query
On Sat, 7 Jun 2003 15:33 , Paul DuBois [EMAIL PROTECTED] sent: This is a problem that is fixed in MySQL 4. If you can upgrade, that should help you a lot. http://www.mysql.com/doc/en/News-4.0.0.html That explains that. I was relying on the online docs and it didn't even occur to me that there might be a version discrepancy. Unfortunately, I'm operating on a shared university database server, and chances are they wouln't upgrade until 4.0 has been in use a while. How much of a penalty am I going to pay because of that filesort? If I understand the docs correctly, the filesort is only performed on entries that survive the WHERE clause. If that's the case, I'm assuming the speed hit would be minimized. One thing that still puzzles me: should the index have kicked in on the ASC query? or are my indexes designed badly? Thanks for the input! Jacob --- Msg sent via [EMAIL PROTECTED] - http://mail.statisticalanomaly.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RPM Start/Stop Script not releasing terminal
Description: after starting mysql via init script, one can not log off a SSH session How-To-OBRepeat: log into box via SSH as root /sbin/service mysql start attempt to log off.. and the session hangs until you use another session to stop mysql. Fix: redirect the in/out/err to /dev/null in start script Submitter-Id: submitter ID Originator:Ian Holsman Organization: Holsman.net MySQL support: none Synopsis: not releasing stdin/out/err Severity: non-critical Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.11-gamma (Official MySQL RPM) Server: /usr/bin/mysqladmin Ver 8.39 Distrib 4.0.9-gamma, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.11-gamma-Max Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 23 hours 39 min 33 sec Threads: 1 Questions: 620 Slow queries: 0 Opens: 79 Flush tables: 1 Open tables: 19 Queries per second avg: 0.007 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux c10-pma-monitor.cnet.com 2.4.18-24.7.xbigmem #1 SMP Fri Jan 31 05:34:01 EST 2003 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='g++' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Sep 22 07:05 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 5 16:12 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 5 15:59 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 5 15:50 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Redhat 8.0 MySQL 3.23.52-3 Replication Problems
I have been having a lot of trouble configuring mysql to do just the simplest replication. Its a standard RedHat 8.0 install, I have followed the directions on http://www.mysql.com/doc/en/Replication_HOWTO.html to the letter half a dozen times now and still, the slave server crashes repeatedly as soon as I enter SLAVE START command. Number of processes running now: 1 mysqld process hanging, pid 26754 - killed 030113 17:54:33 mysqld restarted /usr/libexec/mysqld: ready for connections Number of processes running now: 1 mysqld process hanging, pid 26778 - killed 030113 17:54:34 mysqld restarted /usr/libexec/mysqld: ready for connections Above is what is logged. I eventually have to kill the process and try again. I have tried entering master login/bin-log information in my.cnf and using CHANGE MASTER TO... while logged into slave mysql server. Neither method works. Was wondering if anyone had some insight into this problem. I would be happy to provide more information. Thanks all. -Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Source RPM Compile Error
i downloaded mysql-4.0.8-0.src.rpm off of the mysql web-site and have tried to compile it on several different redhat 8.0 boxes. the same error happens whether compiling latest mysql 3.23 or 4.0 source rpms. the build moves along for about fifteen minutes (long after i begin to believe it will succeed without error). i don't know if it is redhat specific or not. anyone have any suggestions or ideas about how to resolve the error below? thanks all. automake: strings/Makefile.am: Assembler source seen but `CCAS' not defined in `configure.in' automake: strings/Makefile.am: Assembler source seen but `CCASFLAGS' not defined in `configure.in' error: Bad exit status from /var/tmp/rpm-tmp.37688 (%build) RPM build errors: Bad exit status from /var/tmp/rpm-tmp.37688 (%build) -chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql
automake: strings/Makefile.am: Assembler source seen but `CCAS' not defined in `configure.in' automake: strings/Makefile.am: Assembler source seen but `CCASFLAGS' not defined in `configure.in' error: Bad exit status from /var/tmp/rpm-tmp.37688 (%build) RPM build errors: Bad exit status from /var/tmp/rpm-tmp.37688 (%build) i downloaded mysql-4.0.8-0.src.rpm off of the mysql web-site and have been trying to compile it. does anyone have any suggestions or ideas about how to resolve the above error. thanks all. -chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RES: SQL Select Idea [ORDER BY]
Hi Michael, Using two select's we already solved the problem. The challenge is to make the same thing using just one select. I need to know if is there an way to get the last date or the 10th index to make the WHERE part. Like the LAST_INDEX() function, that gets the last AUTO_INCREMENT...but I just saw apllacation using the update command. Well, any new ideas? Best Regards, Felipe -Mensagem original- De: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Enviada em: quinta-feira, 5 de dezembro de 2002 21:03 Para: [EMAIL PROTECTED] Assunto: Re: SQL Select Idea [ORDER BY] On Thu, Dec 05, 2002 at 08:54:29PM -0200, Felipe Moreno - MAILING LISTS wrote: Well, could you explain the behave of ORDER BY DATE,COD? Just try it (it will order by date, then cod). What I think you want is (as I originally said, but briefly): create temporary table top10 select * from ... limit 10; select * from top10 order by cod; ... SQL -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RES: RES: RES: SQL Select Idea
Hi Michael, The ten last dates appear as a default result of a Archives page. When I enter for the first time in the page, it give me the last ten Files that was uploaded. In the same page, I can ORDER BY the ten last dates results by COD, DATE, NAME or FILE. So, when I click in some os then, I need to ORDER the TEN DATES, not the entire table and give ten results, did you get it? Any idea? Regards, Felipe -Mensagem original- De: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Enviada em: quarta-feira, 4 de dezembro de 2002 17:33 Para: [EMAIL PROTECTED] Assunto: Re: RES: RES: SQL Select Idea On Wed, Dec 04, 2002 at 08:55:51AM -0200, Felipe Moreno - MAILING LISTS wrote: Well, the SQL Query you requested is exatcly the one I'm asking! :-) No; I want to know what you intended for those 10 dates to do. The LIMIT query worked, as posted by someone else, which you quoted. What do you then intend to do with that data? Since I have a link in the header of the tabel that make the ORDER BY work, when I select de COD after the result above, I should get: [ you didn't give the EXACT SQL QUERY that you're typing in; please give it ] Again, you didn't quote the when I select the COD after ... -- what is that query? And how do you expect it to behave. I'm assuming that you're forgetting a step, or misunderstanding a step involved. Are you doing anything with that data you're selecting, or just selecting it and leaving it? You might be wanting to select it into a new table or something; look up INSERT INTO ... SELECT FROM This still applies. -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RES: SQL Select Idea [ORDER BY]
Well, could you explain the behave of ORDER BY DATE,COD? I will show you bellow what I want, graphically: 1) What I have: Table: processo_arquivos _ |Cod| Date | - |1 | 12/10 | - |2 | 13/10 | - |3 | 14/10 | - |4 | 15/10 | - |5 | 16/10 | - 2) What I get if I use the: SELECT * FROM processo_arquivos ORDER BY DATE DESC LIMIT 0,3 (considering that I only want 3) _ |Cod| Date | - |5 | 16/10 | - |4 | 15/10 | - |3 | 14/10 | - 3) The problem getting the result like this: Since I have a link in the header of the tabel that make the ORDER BY work, when I select de COD after the result above, I should get: PS: When I select the COD (order by COD) it sends the ORDER BY value to the same select above. _ |Cod (link) | Date | - |3 | 14/10 | - |4 | 15/10 | - |5 | 16/10 | - but instead of the above I get: _ |Cod| Date | - |1 | 12/10 | - |2 | 13/10 | - |3 | 14/10 | - So, the problem is when I select a new ORDER BY I make the query in the entire Table. What I want is to make the query only in the matched results. I have a php page that starts with a default search (the ten dates). So, what I really need ia an way to make a select without any data from the DB. I tried to use de LAST_INDEX() function to do this but I didn't go anywhere. Any ideas??? -Mensagem original- De: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Enviada em: quinta-feira, 5 de dezembro de 2002 16:02 Para: [EMAIL PROTECTED] Assunto: Re: SQL Select Idea [ORDER BY] On Thu, Dec 05, 2002 at 09:41:24AM -0200, Felipe Moreno - MAILING LISTS wrote: The ten last dates appear as a default result of a Archives page. When I enter for the first time in the page, it give me the last ten Files that was uploaded. In the same page, I can ORDER BY the ten last dates results by COD, DATE, NAME or FILE. So, when I click in some os then, I need to ORDER the TEN DATES, not the entire table and give ten results, did you get it? So you want your query to have 'ORDER BY DATE,COD' ?? -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RES: RES: SQL Select Idea
Hi Michael,
Well, the SQL Query you requested is exatcly the one I'm asking! :-)
Like I said in other message, I solved the problem using TWO queries. In
the first one I get the 10th date. In the second one I use this date to
LIMIT the result to what I want. But is not a question of solving the
problem, but a question for a better designed query. I just want to use 1
(one) select to do the same thing that the two selects above do, if it is
possible of course. Below, I will show you the actual scenary, and after,
the question, how can I optimize it to use just one select?. If I can get
the value of the LAST index, I can subtract 10 indexes from this value and
get only this range of values, doing this I will get the last ten dates
without the LIMIT 0,10 that don't give me what I want.
Actual scenary:
// This is the main select that give me the last ten dates.
$sql = select pa.codigo,arquivo,label,tipo,DATE_FORMAT(data,'%d/%m/%Y -
%H:%i') as ndata,RS,ref_wicie from processo_arquivos as pa,cliente as
c,processo as p where pa.codigocliente=c.codigo and
pa.codigoprocesso=p.codigo ;
// This is the FIRST select that catch the LIMIT date
$sql2 = select data from processo_arquivos as pa,cliente as c,processo as
p where pa.codigocliente=c.codigo and pa.codigoprocesso=p.codigo order by
data desc limit 0,11;
$conexao-Query($sql2,$this-banco,$this-valor);
$i = 0;
if ($conexao-Select($this-banco)){
while (($this-valor =
mysql_fetch_array($this-banco)) ($i10)){
$i++;
}
$dataLimite = $this-valor['data'];
$sql .= and data'.$dataLimite.' ; --- Here I
select the last
ten dates.
}
What I wanted:
--- ???
Just do what I'm doing above using only ONE select.
Any tips?
Best Regards,
Felipe
-Mensagem original-
De: Michael T. Babcock [mailto:[EMAIL PROTECTED]]
Enviada em: quarta-feira, 4 de dezembro de 2002 02:06
Para: [EMAIL PROTECTED]
Assunto: Re: RES: SQL Select Idea
On Tue, Dec 03, 2002 at 07:02:02PM -0200, Felipe Moreno - MAILING LISTS
wrote:
2) What I get is I use the SELECT * FROM processo_arquivos ORDER BY DATE
DESC LIMIT 0,3 (considering that I only want 3)
[ that worked as described ]
3) The problem getting the result like this:
Since I have a link in the header of the tabel that make the ORDER BY
work,
when I select de COD after the result above, I should get:
[ you didn't give the EXACT SQL QUERY that you're typing in; please give
it ]
I'm assuming that you're forgetting a step, or misunderstanding a step
involved. Are you doing anything with that data you're selecting, or just
selecting it and leaving it? You might be wanting to select it into a new
table or something; look up INSERT INTO ... SELECT FROM
--
Michael T. Babcock
CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc)
http://www.fibrespeed.net/~mbabcock/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL Select Idea
Hi List Users, I want to know if anyone has any idea on how can I do the SQL command below to archive a result. I have one table called processo_arquivos that have a filed called DATE and another FIELD called COD (primary key). I want to select the last TEN (10) dates from the Database, but only the last TEN. How Can I do this? Any ideia? I tried the sql bellow o archive this, but I was unable to do it. I just want to do this with ONLY one select, not with two. Thanks for any idea. Regards, Felipe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL: Select HELP!
Hi List Users, I want to know if anyone has any idea on how can I do the SQL command below to archive a result. I have one table called processo_arquivos that have a filed called DATE and another FIELD called COD (primary key). I want to select the last TEN (10) dates from the Database, but only the last TEN. How Can I do this? Any ideia? I tried the sql bellow o archive this, but I was unable to do it. I just want to do this with ONLY one select, not with two. Thanks for any idea. Regards, Felipe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RES: SQL Select Idea
Hi Jim and others that tried to help me! Thanks for any kind os answer. Well, the SQL command that you suggested don't work for me. Below, I will show What I want and what I get if I use the select command you suggested: 1) What I have: Table: processo_arquivos _ |Cod| Date | - |1 | 12/10 | - |2 | 13/10 | - |3 | 14/10 | - |4 | 15/10 | - |5 | 16/10 | - 2) What I get is I use the SELECT * FROM processo_arquivos ORDER BY DATE DESC LIMIT 0,3 (considering that I only want 3) _ |Cod| Date | - |5 | 16/10 | - |4 | 15/10 | - |3 | 14/10 | - 3) The problem getting the result like this: Since I have a link in the header of the tabel that make the ORDER BY work, when I select de COD after the result above, I should get: _ |Cod| Date | - |3 | 14/10 | - |4 | 15/10 | - |5 | 16/10 | - but instead of the above I get: _ |Cod| Date | - |1 | 12/10 | - |2 | 13/10 | - |3 | 14/10 | - So, the problem is when I select a new ORDER BY I make the query in the entire Table. What I want is to make the query only in the matched results. I have a php page that starts with a default search (the ten dates). So, what I really need ia an way to make a select without any data from the DB. I tried to use de LAST_INDEX() function to do this but I didn't go anywhere. Any ideas??? Best Regards, Felipe -Mensagem original- De: Jim Esten [mailto:[EMAIL PROTECTED]] Enviada em: terça-feira, 3 de dezembro de 2002 17:40 Para: 'Felipe Moreno - MAILING LISTS'; [EMAIL PROTECTED] Assunto: RE: SQL Select Idea Something on the order of... SELECT * FROM processo_arquivos ORDER BY DATE DESC LIMIT 0,10 Seems like that ought to do it.. Jim Jim Esten Chief Techbot WebDynamic http://www.wdynamic.com -Original Message- From: Felipe Moreno - MAILING LISTS [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 10:39 AM To: [EMAIL PROTECTED] Subject: SQL Select Idea Importance: High Hi List Users, I want to know if anyone has any idea on how can I do the SQL command below to archive a result. I have one table called processo_arquivos that have a filed called DATE and another FIELD called COD (primary key). I want to select the last TEN (10) dates from the Database, but only the last TEN. How Can I do this? Any ideia? I tried the sql bellow o archive this, but I was unable to do it. I just want to do this with ONLY one select, not with two. Thanks for any idea. Regards, Felipe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: newbie: creating database error
try www.mysql.com, i heard they have docs there you can read...:) -Original Message- From: Admin-Stress [mailto:meerkapot;yahoo.com] Sent: Monday, November 04, 2002 1:41 PM To: [EMAIL PROTECTED] Subject: newbie: creating database error After playing around with 'test' database, then I would like to create my own database. I logged in into mysql using root account and created a database. But, when I logged in using regular user, I got his error : ERROR 1044: Access denied for user: '@localhost' to database 'card' What necessary to do to create database? I tried to create a database using regular user, still got same error. I did change create_priv='Y' in user table from mysql database for this regular user. Anyone know how the basic steps to create database? or any documentation/website resource would be appreciated. Thanks. kapot __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: secure replication
i understand there are other ways of encrypting the traffic, i am just curious if there are any that employed internally in mysql. thanks. -chris -Original Message- From: Ronald Petty [mailto:ron.petty;unigeek.com] Sent: Thursday, October 24, 2002 6:51 PM To: Lists @ Apted Technologies Inc. Subject: Re: secure replication have you thought about ssh? just a thought. Ron - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
secure replication
i am going to be setting up four mysql servers, three of which will replicate data off of the primary. i know that in more recent versions of mysql client/server communciations can be encrypted internally. but is there any way to encrypt replication communcations between these mysql servers internally so i don't need to set up a vpn or ssl tunnel? thanks all. -chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Test for table lock
Hi, Using MySQL, perl and DBI, is there a way to test to see if a table is locked (as in LOCK table...). We batch process our updates every 3 hours or so (it's not a set schedule though, it depends when a certain level of new updates is reached), and our main tables are locked during this time for around 10 minutes or so. I'd like to be able to modify our search and display scripts to inform the users to come back later while the tables are locked. Thanks, Martyn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Security problem
*This message was transferred with a trial version of CommuniGate(tm) Pro* I installed Mac OS X 10.2 this last weekend and since then I've been having some problems with the security on the MySQL files. I thought that I had everything fixed, but now when my web users try and update or insert a record in one of my files, it doesn't actually update anything. I've checked the mysql.log and mysql.err and there isn't any kind of error listed. Selects work just fine. Any ideas would be appreciated. Thanks, Daniel Wilson Austin Shakespeare Festival - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication PROBLEM
Does any know how to fix this database replication problem. Last Lines from Err File 010815 21:38:55 Slave thread exiting, replication stopped in log 'salem-bin.013' at position 6867 010815 21:38:55 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'salem-bin.013' position 6867 010815 21:38:55 Slave: error running query 'UPDATE themes SET textcolor='#0a' WHERE theme_id = 2 AND theme_name = 'Ocean' AND bgcolor = '#FF' AND textcolor = '#00' AND color1 = '#CC' AND color2 = '#9BB6DA' AND table_bgcolor = '#00' AND header_image = 'images/toxicmold1.gif' AND newtopic_image = 'images/new topic.gif' AND reply_image = 'images/reply.gif' AND linkcolor = '#011001' AND vlinkcolor = '#2100cc' AND theme_default = 0 AND fontface = 'sans-serif' AND fontsize1 = '1' AND fontsize2 = '2' AND fontsize3 = '-2' AND fontsize4 = '+1' AND tablewidth = '95%' AND replylocked_image = 'images/reply_locked-dark.jpg'' ERROR: 1146 Table 'mold_db.themes' doesn't exist .013' at position 6867 I have done mysqladmin start-slave and it does not work, The master is a Linux and the slave is Windows 2000 pro.
Re: Linux to NT
Thank you - Original Message - From: Grigory Bakunov [EMAIL PROTECTED] To: Lists Servers Email [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 14, 2001 11:16 PM Subject: Re: Linux to NT Date |Tue, 14 Aug 2001 22:55:47 -0700 From |Lists Servers Email [EMAIL PROTECTED] Hello! LSE Is it possible to Replication from Linux database to NT? Yes it's possible. Read about it here http://www.mysql.com/doc/R/e/Replication.html ___ For technical support contracts, visit https://order.mysql.com/ This email is sponsored by SWSoft, http://www.asplinux.ru/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Grigory Bakunov [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB / SWSoft /_/ /_/\_, /___/\___\_\___/ ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Linux to NT
Is it possible to Replication from Linux database to NT? Thanks Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Transaction Support in MySQL
What sort of support for transactions and table/record locking is there in MySQL right now? I had heard that only table locking was supported, and limited support for transactions. Aaron - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL query - not too complex?
I need some help with a query to sum data for each id and write it into a new table. I have a source data table for each calendar month containing i.d.'s (not unique) and a value and I need to sum all the values for each i.d. for all months into a new table (where each i.d. will now be unique). The only way I could see of doing it was in two or more stages? Duncan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query prob.
Hi, I have a table like this: mysql select id, ticketid, rdvwhen from rdv order by ticketid asc, id desc; ++--++ | id | ticketid | rdvwhen| ++--++ | 1 | 18 | 2001050309 | | 2 | 20 | 20010430153000 | | 7 | 30 | 2001051709 | | 6 | 31 | 2001051409 | | 15 | 32 | 2001052114 | | 12 | 33 | 20010523133000 | | 5 | 33 | 2001051513 | | 4 | 35 | 00 | | 14 | 36 | 2001051814 | | 3 | 36 | 2001050909 | | 8 | 37 | 2001052109 | | 13 | 38 | 2001052214 | | 9 | 41 | 20010522093000 | | 10 | 42 | 2001051714 | | 16 | 44 | 2001052814 | ++--++ 15 rows in set (0.00 sec) I'd like to filter this so that the ticketid is unique. If there are multiple identical ticketids I'd like to get the one where the id field is the biggest. This is in fact only one step to accomplish the following: $table1=tickets; $table2=rdv; SELECT $table1.id, $table1.firstmsgid, $table1.customerid, $table1.creator, $table1.assigneduserid, $table1.statusid, $table1.priorityid, $table1.travail, $table1.bpdfile, $table1.bpduser, $table1.typeoftask, UNIX_TIMESTAMP($table1.created) AS created, UNIX_TIMESTAMP($table1.bpdwhen) AS bpdwhen FROM $table1 LEFT JOIN $table2 ON ($table1.id = $table2.ticketid) --* WHERE (statusid = '$sid') GROUP BY $table1.customerid ORDER BY $table2.rdvwhen ASC * I'd like to join to the above criteria only (field with largest id only if there are multiple ticketid)... regards, --- J.M. Roth - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql Full-text Search indexes
Can anyone share the experiences with mysql Full-text Search indexes? I have been reading here and there about how slow it can be to create/update a text index. Also, are there any benchmarks on query results on searching such indexes? Christopher Lambrou, CGL Computer Services, Inc. Empire State Building, PMB 16J Suite 3304 New York, NY 10118 Tel: (212) 971-9723 Fax: (212) 564-1135 URL: http://www.cglcomputer.com Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
NULL value in VARCHAR fields
Stefan, When you insert a row in a table and you do not specify a value for a field, mySQL (and any other DBMNS?) will do the following: 1. If field accepts null: it will set the value of that field to null 2. If field DOES NOT accept null - no default value set: it will set the field's value as per primitive data type of field. If CHAR() it will set it to '', if date/datetime to -00-00, if numeric to 0. And so on. And this is why you get the '' in your field when you do your insert. 2. If field DOES NOT accept null - default value set: it set the field's value to the deafult. Christopher Lambrou, CGL Computer Services, Inc. Empire State Building, PMB 16J Suite 3304 New York, NY 10118 Tel: (212) 971-9723 Fax: (212) 564-1135 URL: http://www.cglcomputer.com Email: [EMAIL PROTECTED] On 5/03/2001 20:59:48, you said: hi list, sorry for posting again, i will learn it. i have a problem with DEFAULT NULL value in a varchar field. mysql is version 3.23.33. i want to have an user field in a row, to see who has created the row. create table minidb.tel ( id int UNSIGNED NOT NULL AUTO_INCREMENT, tel char(16) NOT NULL, service char(1) BINARY, idate timestamp(14) NOT NULL, iuser varchar(255) NOT NULL, primary key (id, tel, idate) ); using DEFAULT USER(), would be nice, but only constants are allowed. to prevent inserting rows without iuser, i like to have this field with NOT NULL restriction and default value NULL. when inserting a row with insert into tel set tel='+49 174 92932000', service=1; there will be a record with iuser = ''. i thought the meaning of NOT NULL is to reject inserting rows with iuser as NULL. the line iuser varchar(255) NOT NULL DEFAULT NULL, will rejected with an errormsg. thx in advance for your help stefan -- st3V13h nach diktat verreist - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Auto_increment or manual??
Personnaly, i have found autoincrement fields to be rock solid. I use'em all over the place on a 1 Gb database, web based. Don't do it manually . You'll end up doing the same thing that mysql gives you for free. Christopher Lambrou, CGL Computer Services, Inc. Empire State Building, PMB 16J Suite 3304 New York, NY 10118 Tel: (212) 971-9723 Fax: (212) 564-1135 URL: http://www.cglcomputer.com Email: [EMAIL PROTECTED] On 5/3/2001 15:57:38, you said: Hello! I have a database with about 10 tables in it. In every table I have a RECORD_ID field so that I can at least uniquely identify a row if I need to, also its used in relationships. The question is should I use the AUTO_INCREMENT for this, or should I manually generate this value, getting the next highest number, then putting it in there. Is there any known replication problems if I use AUTO_INCREMENT??? Would I be safer in just doing this manually myself in my code?? This is going to be a web-based app, so many users will be using the db at the same time. Thanks! Patrick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Max connections on Freebsd
Hi, Is there someone who incremented the max_connection value to something above 100 (200) (300) on Freebsd 4.2? I had problems with mysql on freebsd 4.0, hooked up after some days without any good reason :). Some people told me thread libraries on freebsd 4.0 suck a bit. So I wonder if 4.2's thread libraries are stable enough to handle 200 concurrent threads?!. Thanks in advance Regards, Patric de Waha - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Strange connect (socket) problem
HI,
Try this
?
$host = localhost:/var/mysql/mysql.sock; // focus here
$user = webrings;
$password = XXX;
$link = MYSQL_CONNECT($host,$user,$password);
if ($link == 0) {
echo Can't connect to MySQL.;
exit;
} else {
echo Connection established.;
exit;
}
?
regs, pdw
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL CRASHED after some days with this message in errlog:
Hi, Mysql ran for 6 days.. It is a website with heavy traffic. /usr/libexec/ld-elf.so.1: /usr/local/libexec/mysqld: Undefined symbol strtoull 010423 23:18:08 mysqld restarted /usr/local/libexec/mysqld: ready for connections What is this function about? What feature shouldn't I use to avoid this problem? Thanks in advance Regards, Patric de Waha - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
reusing unique id's when deleting/inserting?
Hi all, Here are two tables I'm working with. I apologize if you are not using a monospaced font and they are messed up. This is used by a gradebook program. When a professor adds an assignment to a class he teaches, it inserts the information about the assignment into the assignments table, and inserts the grade each student receives in the student_assignment table. This happens at the same time so the unique ID is generated the same. When he deletes an assignment from the class, it deletes it from both tables. However, after this, if he adds an assignment again, the unique ID generated by insertion into the assignment table is one greater than it should be - i.e., it auto increments the id based on the id of the row that just got deleted, not on the last row that is actually in the table. When the assignment is inserted into the student_assignment table, the unique ID is auto incremented based on the last ID that is actually in the table, not on the row that just got deleted. The workaround I have found is to use last_insert_id() to find the ID that got inserted in to the assignment table, and use this to manually specify the ID for the student_assignment table. This has the effect of putting the ID's in sync, which is what I want, but it seems like there should be a better solution. Any suggestions? Thanks, Peter (tables below) mysql describe assignments; +++--+-+-++ | Field | Type | Null | Key | Default | Extra | +++--+-+-++ | id | int(30)| | PRI | NULL| auto_increment | | title | char(50) | YES | | NULL|| | class_num | char(6)| | | || | weight | float(4,2) | YES | | NULL|| | max_points | int(3) | | | 0 || | type | char(20) | YES | | NULL|| +++--+-+-++ 6 rows in set (0.00 sec) mysql describe student_assignment; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | stu_id| int(10) | | PRI | 0 || | assign_id | int(30) | | PRI | NULL| auto_increment | | grade | int(3) | YES | | NULL|| +---+-+--+-+-++ 3 rows in set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 2 Problems, Shutdown and BDB (data loss)
Enabled the binary log seems to have helped fix the data loss somewhat...I definetly
don't loss it all, but I still get those restarts when I shutdown and sometimes, but
now less often, I find the last few rows gone.
/etc/my.cnf
[mysqld]
user=sql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
port=3306
myisam-recover=BACKUP,FORCE
log-slow-queries=/usr/local/mysql/data/slow.log
log=/usr/local/mysql/data/mysqld.log
log-bin=/usr/local/mysql/data/binary.log
[mysql.server]
user=sql
basedir=/usr/local/mysql
On Tue, 13 Mar 2001, goEbusiness.com Mail Lists wrote:
OS: Solaris 7, Sparc
MySQL Version: 3.23.29a
Problem:
Sometimes when I do a mysqladmin shutdown on the command line mysql restarts
itself instead of shutting down.
From the CLI:
elvis~ mysqladmin -uadmin -pdevsqladmin shutdown
Terminated
010313 18:17:40 mysqld restarted
hangs until I press control C
From the .err log:
010313 18:17:40 mysqld restarted
/usr/local/mysql-3.23.29a/libexec/mysqld: ready for connections
From the query log:
010313 18:17:40 3 Connectadmin@localhost on
3 Query SHOW VARIABLES LIKE 'pid_file'
3 Shutdown
/usr/local/mysql-3.23.29a/libexec/mysqld, Version: 3.23.29a-gamma-log, started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id CommandArgument
Sometimes it shutdowns properly. Any ideas?
2nd Problem, using BDB when I insert data, for example
mysql CREATE TABLE testBDB (
- name varchar(30) not null
- ) TYPE=BDB ;
Query OK, 0 rows affected (0.10 sec)
mysql INSERT INTO testBDB (name) VALUES ('test no begin/commit') ;
Query OK, 1 row affected (0.01 sec)
mysql SELECT * FROM testBDB ;
+--+
| name |
+--+
| test no begin/commit |
+--+
1 row in set (0.00 sec)
mysql BEGIN WORK ;
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO testBDB (name) VALUES ('test with begin and commit') ;
Query OK, 1 row affected (0.00 sec)
mysql COMMIT ;
Query OK, 0 rows affected (0.00 sec)
mysql SELECT * FROM testBDB ;
++
| name |
++
| test no begin/commit |
| test with begin and commit |
++
2 rows in set (0.00 sec)
then I do a restart and I get the behavior in problem #1
mysql SELECT * FROM testBDB ;
Empty set (0.00 sec)
all the data is gone!
Any ideas on what is happening? I can't find anything in the logs about this, and
the log.X are all binary data so I cannot decopher that.
I'd upgrade to 34a but I am getting hte configure error that other people here are
getting about no value for Char.
Startup options for MySQL:
/usr/local/mysql/bin/safe_mysqld --user=sql
--log=/disk1/mysql-data-3.23.29/mysqld.log
Bill
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
2 Problems, Shutdown and BDB (data loss)
OS: Solaris 7, Sparc
MySQL Version: 3.23.29a
Problem:
Sometimes when I do a mysqladmin shutdown on the command line mysql restarts
itself instead of shutting down.
From the CLI:
elvis~ mysqladmin -uadmin -pdevsqladmin shutdown
Terminated
010313 18:17:40 mysqld restarted
hangs until I press control C
From the .err log:
010313 18:17:40 mysqld restarted
/usr/local/mysql-3.23.29a/libexec/mysqld: ready for connections
From the query log:
010313 18:17:40 3 Connectadmin@localhost on
3 Query SHOW VARIABLES LIKE 'pid_file'
3 Shutdown
/usr/local/mysql-3.23.29a/libexec/mysqld, Version: 3.23.29a-gamma-log, started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id CommandArgument
Sometimes it shutdowns properly. Any ideas?
2nd Problem, using BDB when I insert data, for example
mysql CREATE TABLE testBDB (
- name varchar(30) not null
- ) TYPE=BDB ;
Query OK, 0 rows affected (0.10 sec)
mysql INSERT INTO testBDB (name) VALUES ('test no begin/commit') ;
Query OK, 1 row affected (0.01 sec)
mysql SELECT * FROM testBDB ;
+--+
| name |
+--+
| test no begin/commit |
+--+
1 row in set (0.00 sec)
mysql BEGIN WORK ;
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO testBDB (name) VALUES ('test with begin and commit') ;
Query OK, 1 row affected (0.00 sec)
mysql COMMIT ;
Query OK, 0 rows affected (0.00 sec)
mysql SELECT * FROM testBDB ;
++
| name |
++
| test no begin/commit |
| test with begin and commit |
++
2 rows in set (0.00 sec)
then I do a restart and I get the behavior in problem #1
mysql SELECT * FROM testBDB ;
Empty set (0.00 sec)
all the data is gone!
Any ideas on what is happening? I can't find anything in the logs about this, and the
log.X are all binary data so I cannot decopher that.
I'd upgrade to 34a but I am getting hte configure error that other people here are
getting about no value for Char.
Startup options for MySQL:
/usr/local/mysql/bin/safe_mysqld --user=sql --log=/disk1/mysql-data-3.23.29/mysqld.log
Bill
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RDBMS question on coding expanding series-like fields
Hi Warren,
What I personally would do is simply include some sort of 'ID' field. In
other words, each question would have a unique ID. Question 1's ID would be
1, Question 2's ID would be 2, etc. Or however you wanted to number them.
You could even set this up as an auto_increment and have the next number in
sequence automatically generated when a new question is inserted into the
database.
Then, if you want to call something "Question 1", in your programming
language, simply prepend Question to the ID field. If I were doing this in
Perl, for example, I would do:
print "Question " . $db_query_hash-{'id'};
#where $db_query_hash is the hash of field names and values returned from the
#query, and 'id' is the field containing the id of the question.
The above example would output "Question 1" if the ID selected from the
database were 1.
The main point here is that putting a value like "Quest1" in a database is
(in my opinion) redundant. Simply make the name of the field "Question" and
make the type of the field "num".
Of course I have not been using MySQL that long either, so perhaps some
wizard will have a much smarter answer. :-)
Peter
On Tuesday 27 February 2001 11:24 am, you transmuted into bits these words:
Hi!
I have a Test Questions database. Right now I have a hard-coded limit of
200 questions in it; I actually made a table with field names like Quest1,
Quest2. . . Quest200. However, I know that I am not using the power of
MySql in setting it up this way. This is more an example of my own
ignorance than of the power of MySql!
Can someone explain how to set up a table with a fieldname that 'expands'
(maybe something like Quest[i] where "i" can be incremented as required).
Is there more than one way of doing this? Is there a place where I might
see some sample code?
I did look up 'enum' and 'set' in the manual but I don't feel confident
with my grasp of the limited explanations given of these. My feeling is
that perhaps 'enum' would be a candidate for what I need, as 'set' has a
limit on how big the set can get.I would like to have the possibility
of data expansion as needed.
Any tips whatever would be appreciated!
Thanks very much!
Cheers!
-Warren
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED] Trouble
unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mod_auth_mysql
Hi all, Does anyone know if mod_auth_mysql is still under active development, and if so, where can I find current information on it? I have searched the list archives here and indeed turned up 254 instances of people having problems with mod_auth_mysql. From the sounds of it, it doesn't look like an easily solved problem. Is there a better alternative to get MySQL authentication with Apache? I have looked at several HOWTOs on compiling apache with mod_auth_mysql, but none have yielded good results. I used the file http://www.mysql.com/Downloads/Contrib/mod_auth_mysql-2.20.tar.gz in the MySQL contribs, but this did not compile. I'm trying to compile it with Apache 1.3.17 and MySQL 3.23.33. Any suggestions as to a better/more current method, or a working method to build this module would be appreciated. Thanks! Peter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
