Re: Optimizing InnoDB tables
Have a look at this: https://rtcamp.com/tutorials/mysql/enable-innodb-file-per-table/ -- Andre Matos andrema...@mineirinho.org On Jun 25, 2014, at 2:22 AM, Antonio Fernández Pérez antoniofernan...@fabergames.com wrote: Hi again, I have enabled innodb_file_per_table (Its value is on). I don't have clear what I should to do ... Thanks in advance. Regards, Antonio. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Collate issue
Hello, I have a file that contains some SQL statements including CREATE TABLE, INSERT, UPDATE and specially CREATE VIEW. This file is used to apply changes into the database. All databases tables use the 'DEFAULT CHARSET=latin1 COLLATE=latin1_General_CS', so when creating views, the fields that exists in the table I am querying will have the same collation. However, new fields created by the SELECT do not use the collation I want: 'latin1_General_CS'. The first line of this file is: SET NAMES latin1 COLLATE latin1_general_cs; So, when running - mysql -u myuser -p testdb settings.sql The views are created using the 'latin1_General_CS'. However, when I try to do the same in the MySQL installed on our servers (5.0.51a-3ubuntu5.5 and 5.0.32-Debian_7etch1), the views do not get created as they were on my local computer. Some fields are created using 'latin1_swedish_ci', some as 'latin1_bin', and others without any. Is there any known issue with previous versions of the MySQL 5.0.x regarding this? Is there a way I can do this so all applicable fields on my view uses the collation I want? Thanks for any help! Andre -- Andre Matos andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Using RAND to get a unique ID that has not been used yet
Hi All, I have a table that uses auto_increment to generate the Id automatically working fine. However, I need to create a new table where the Id must be a number generated randomly, so I cannot use the auto_increment. MySQL has a function RAND. So I could use something like this: SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable But, let's suppose that the RandId is a number that was already used in the table. Then I need to run the SELECT again and again until I find a number that hasn't been used. Is there a way to have this SELECT to loop until it finds a number that hasn't been used? The RandId must be only numbers and length of 6 (from 1 to 99). No other character is allowed. Thanks for any help! Andre -- Andre Matos andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using RAND to get a unique ID that has not been used yet
It seems to be a good approach, although I was trying to get this by querying the table without creating another table to keep the Ids. Thanks, Andre -- Andre Matos andrema...@mineirinho.org On 2010-05-28, at 12:15 PM, Steven Staples wrote: If you wanted to use/go that route, then why not select a random limit 1 from that table, and then delete that row? SELECT `column` FROM `table` ORDER BY RAND() LIMIT 1; On a side note, I would use the auto-inc field still, and store this number in another field. Steven Staples -Original Message- From: Jim Lyons [mailto:jlyons4...@gmail.com] Sent: May 28, 2010 11:49 AM To: Andre Matos Cc: mysql@lists.mysql.com Subject: Re: Using RAND to get a unique ID that has not been used yet If your specs are that specific (IDs must be between 1 and 99) then you could create a 99-row table with one integer column and prefill it with the numbers 1 to 99 in random order. Then you could write a function that would select and return the first number in the table, then delete that record so you would not reuse it. Once you've done the work of sorting 99 numbers in random order (which can be done anywhich way) it's easy and you don't have to loop an indeterminant number of times. You would be looping an increasing number of times as you begin to fill up the table. Jim On Fri, May 28, 2010 at 10:38 AM, Andre Matos andrema...@mineirinho.org wrote: Hi All, I have a table that uses auto_increment to generate the Id automatically working fine. However, I need to create a new table where the Id must be a number generated randomly, so I cannot use the auto_increment. MySQL has a function RAND. So I could use something like this: SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable But, let's suppose that the RandId is a number that was already used in the table. Then I need to run the SELECT again and again until I find a number that hasn't been used. Is there a way to have this SELECT to loop until it finds a number that hasn't been used? The RandId must be only numbers and length of 6 (from 1 to 99). No other character is allowed. Thanks for any help! Andre -- Andre Matos andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.819 / Virus Database: 271.1.1/2895 - Release Date: 05/28/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using RAND to get a unique ID that has not been used yet
When I mentioned having everything in the Query, I was thinking about this. I don't want to have a loop repeating the query until I get a unique Id. This is ridicules and imagine how many queries I might end up running. No way! Thanks for the warning and feedback! Andre -- Andre Matos andrema...@mineirinho.org On 2010-05-28, at 1:51 PM, Jerry Schwartz wrote: -Original Message- From: Andre Matos [mailto:andrema...@mineirinho.org] Sent: Friday, May 28, 2010 1:44 PM To: Steven Staples Cc: mysql@lists.mysql.com Subject: Re: Using RAND to get a unique ID that has not been used yet It seems to be a good approach, although I was trying to get this by querying the table without creating another table to keep the Ids. [JS] That would be a VERY bad idea. My predecessor designed our system that way: it would generate a random key, check to see if that key were in use, and either use it or try again. As you would expect, the whole process get slower and slower as we ran out of unique keys. Eventually the whole application became unusable. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Thanks, Andre -- Andre Matos andrema...@mineirinho.org On 2010-05-28, at 12:15 PM, Steven Staples wrote: If you wanted to use/go that route, then why not select a random limit 1 from that table, and then delete that row? SELECT `column` FROM `table` ORDER BY RAND() LIMIT 1; On a side note, I would use the auto-inc field still, and store this number in another field. Steven Staples -Original Message- From: Jim Lyons [mailto:jlyons4...@gmail.com] Sent: May 28, 2010 11:49 AM To: Andre Matos Cc: mysql@lists.mysql.com Subject: Re: Using RAND to get a unique ID that has not been used yet If your specs are that specific (IDs must be between 1 and 99) then you could create a 99-row table with one integer column and prefill it with the numbers 1 to 99 in random order. Then you could write a function that would select and return the first number in the table, then delete that record so you would not reuse it. Once you've done the work of sorting 99 numbers in random order (which can be done anywhich way) it's easy and you don't have to loop an indeterminant number of times. You would be looping an increasing number of times as you begin to fill up the table. Jim On Fri, May 28, 2010 at 10:38 AM, Andre Matos andrema...@mineirinho.org wrote: Hi All, I have a table that uses auto_increment to generate the Id automatically working fine. However, I need to create a new table where the Id must be a number generated randomly, so I cannot use the auto_increment. MySQL has a function RAND. So I could use something like this: SELECT FLOOR(RAND() * COUNT(*)) AS RandId FROM mytable But, let's suppose that the RandId is a number that was already used in the table. Then I need to run the SELECT again and again until I find a number that hasn't been used. Is there a way to have this SELECT to loop until it finds a number that hasn't been used? The RandId must be only numbers and length of 6 (from 1 to 99). No other character is allowed. Thanks for any help! Andre -- Andre Matos andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.819 / Virus Database: 271.1.1/2895 - Release Date: 05/28/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrema...@mineirinho.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SELECT through many databases
Hello,
Let's suppose I have 5 database: db1, db2, db3, db4, and db5. They all
have the same structure but different data.
I would like perform this select
SELECT TaskDoneOn, TaskDoneBy
FROM {database}
WHERE TaskDoneOn IS NOT NULL
and collect the data from all 5 database. However, I would like to
avoid doing something like this:
SELECT TaskDoneOn, TaskDoneBy
FROM db1
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db2
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db3
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db4
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db5
WHERE TaskDoneOn IS NOT NULL
Today I have 5, but tomorrow I can have 50 and I don't want to forget
any database.
Thanks for any help.
Andre
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT through many databases
I was trying to avoid both since the SELECT statement is not fixed.
Time to time, users want different information.
Thanks,
Andre
On 21-Nov-08, at 12:59 PM, Peter Brawley wrote:
Andre Matos wrote:
Today I have 5, but tomorrow I can have 50 and I don't want to
forget any database.
Do it in an app language or as a PREPARED statement in an sproc.
PB
---
Andre Matos wrote:
Hello,
Let's suppose I have 5 database: db1, db2, db3, db4, and db5. They
all have the same structure but different data.
I would like perform this select
SELECT TaskDoneOn, TaskDoneBy
FROM {database}
WHERE TaskDoneOn IS NOT NULL
and collect the data from all 5 database. However, I would like to
avoid doing something like this:
SELECT TaskDoneOn, TaskDoneBy
FROM db1
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db2
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db3
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db4
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db5
WHERE TaskDoneOn IS NOT NULL
Today I have 5, but tomorrow I can have 50 and I don't want to
forget any database.
Thanks for any help.
Andre
Internal Virus Database is out of date.
Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus
Database: 270.9.0/1777 - Release Date: 11/9/2008 9:53 AM
--
Dr. André Matos
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT through many databases
Sounds interesting, but does the MERGER support complex SELECT
statements and LEFT JOIN?
Andre
On 21-Nov-08, at 1:45 PM, Brent Baisley wrote:
On Fri, Nov 21, 2008 at 12:44 PM, Andre Matos [EMAIL PROTECTED]
wrote:
Hello,
Let's suppose I have 5 database: db1, db2, db3, db4, and db5. They
all have
the same structure but different data.
I would like perform this select
SELECT TaskDoneOn, TaskDoneBy
FROM {database}
WHERE TaskDoneOn IS NOT NULL
and collect the data from all 5 database. However, I would like to
avoid
doing something like this:
SELECT TaskDoneOn, TaskDoneBy
FROM db1
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db2
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db3
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db4
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db5
WHERE TaskDoneOn IS NOT NULL
Today I have 5, but tomorrow I can have 50 and I don't want to
forget any
database.
Thanks for any help.
Andre
Create a MERGE table that is all those tables combined. Then you just
need to do 1 select as if it was one table. Just be sure to update the
MERGE table description when ever you add a table.
Brent Baisley
--
Dr. André Matos
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT through many databases
Thanks everyone for the help.
I was trying to use any API (e.g. PHP, Java, etc). I just gave up
because unfortunately the time fly and the user needs this asap. I am
doing via script.
Thanks!
Andre
On 21-Nov-08, at 2:34 PM, Jerry Schwartz wrote:
Many MySQL APIs (such as PHP) allow you to treat the result
returned from a
SHOW statement as you would a result set from a SELECT; see Chapter
22, APIs
and Libraries, or your API documentation for more information. In
addition,
you can work in SQL with results from queries on tables in the
INFORMATION_SCHEMA database, which you cannot easily do with results
from
SHOW statements. See Chapter 20, The INFORMATION_SCHEMA Database.
You should be able to refresh your MERGE table using a programming
language. Get a list of the databases, then construct a CREATE TABLE
or what
have you.
-Original Message-
From: Andre Matos [mailto:[EMAIL PROTECTED]
Sent: Friday, November 21, 2008 2:11 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: SELECT through many databases
I was trying to avoid both since the SELECT statement is not fixed.
Time to time, users want different information.
Thanks,
Andre
On 21-Nov-08, at 12:59 PM, Peter Brawley wrote:
Andre Matos wrote:
Today I have 5, but tomorrow I can have 50 and I don't want to
forget any database.
Do it in an app language or as a PREPARED statement in an sproc.
PB
---
Andre Matos wrote:
Hello,
Let's suppose I have 5 database: db1, db2, db3, db4, and db5. They
all have the same structure but different data.
I would like perform this select
SELECT TaskDoneOn, TaskDoneBy
FROM {database}
WHERE TaskDoneOn IS NOT NULL
and collect the data from all 5 database. However, I would like to
avoid doing something like this:
SELECT TaskDoneOn, TaskDoneBy
FROM db1
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db2
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db3
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db4
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db5
WHERE TaskDoneOn IS NOT NULL
Today I have 5, but tomorrow I can have 50 and I don't want to
forget any database.
Thanks for any help.
Andre
-
---
Internal Virus Database is out of date.
Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus
Database: 270.9.0/1777 - Release Date: 11/9/2008 9:53 AM
--
Dr. André Matos
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Dr. André Matos
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT through many databases
The reason for having many databases with the same structure but
with different data is because for regulatory and compliance
requirements. Each database belongs to a separate company: company
specific database. The applications (some written in PHP4 and others
in J2EE) are unique and they can access one database each time.
Connect to one database and then disconnect to connect to a different
one.
The issue is when I need to collect some data for statistics purpose.
Unfortunately, I cannot have an official application to go through all
database or even use the MERGE table as suggested here in the list. I
am not allowed to. That's why I was thinking to use only a SELECT
statement to do the job as I have been doing.
Thanks,
Andre
On 21-Nov-08, at 3:16 PM, Olexandr Melnyk wrote:
Hello Andre,
I would recommend you to rethink your criteria (if there's any) for
splitting data into multiple tables.
Because now. the more tables you add, the more of a performance
problem it
may cause.
--
Sincerely yours,
Olexandr Melnyk
http://omelnyk.net/
On Fri, Nov 21, 2008 at 9:58 PM, Andre Matos [EMAIL PROTECTED]
wrote:
Thanks everyone for the help.
I was trying to use any API (e.g. PHP, Java, etc). I just gave up
because
unfortunately the time fly and the user needs this asap. I am doing
via
script.
Thanks!
Andre
On 21-Nov-08, at 2:34 PM, Jerry Schwartz wrote:
Many MySQL APIs (such as PHP) allow you to treat the result
returned from
a
SHOW statement as you would a result set from a SELECT; see
Chapter 22,
APIs
and Libraries, or your API documentation for more information. In
addition,
you can work in SQL with results from queries on tables in the
INFORMATION_SCHEMA database, which you cannot easily do with
results from
SHOW statements. See Chapter 20, The INFORMATION_SCHEMA Database.
You should be able to refresh your MERGE table using a programming
language. Get a list of the databases, then construct a CREATE
TABLE or
what
have you.
-Original Message-
From: Andre Matos [mailto:[EMAIL PROTECTED]
Sent: Friday, November 21, 2008 2:11 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: SELECT through many databases
I was trying to avoid both since the SELECT statement is not fixed.
Time to time, users want different information.
Thanks,
Andre
On 21-Nov-08, at 12:59 PM, Peter Brawley wrote:
Andre Matos wrote:
Today I have 5, but tomorrow I can have 50 and I don't want to
forget any database.
Do it in an app language or as a PREPARED statement in an sproc.
PB
---
Andre Matos wrote:
Hello,
Let's suppose I have 5 database: db1, db2, db3, db4, and db5.
They
all have the same structure but different data.
I would like perform this select
SELECT TaskDoneOn, TaskDoneBy
FROM {database}
WHERE TaskDoneOn IS NOT NULL
and collect the data from all 5 database. However, I would like
to
avoid doing something like this:
SELECT TaskDoneOn, TaskDoneBy
FROM db1
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db2
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db3
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db4
WHERE TaskDoneOn IS NOT NULL
UNION
SELECT TaskDoneOn, TaskDoneBy
FROM db5
WHERE TaskDoneOn IS NOT NULL
Today I have 5, but tomorrow I can have 50 and I don't want to
forget any database.
Thanks for any help.
Andre
-
---
Internal Virus Database is out of date.
Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus
Database: 270.9.0/1777 - Release Date: 11/9/2008 9:53 AM
--
Dr. André Matos
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Dr. André Matos
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Dr. André Matos
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deployment of Database in a DEB package.
Just be aware about the security. I use shell or perl scripts to make automatic dumps (backups) or updates/changes in the database. Unfortunately, because they are automatic scripts, I need to add the username and password inside the scripts and if someone else have access to these scripts (e.g., read access), he/she will see this information. Andre On 2-Oct-08, at 11:57 AM, Heston James - Cold Beans wrote: Dave, This looks like a perfect solution. We can indeed use a shell script for the task in hand and knowing that we an action the .sql script so simply from the command line like that makes life very simple indeed :-) I can't see this will cause too many challenges. Thanks Dave, I appreciate it a great deal. Heston -Original Message- From: Ellison, David [mailto:[EMAIL PROTECTED] Sent: 02 October 2008 07:24 To: mysql@lists.mysql.com Subject: RE: Deployment of Database in a DEB package. I know nothing of DEB packages. However, when we need to change / make a database etc on automatic scripts. We would write the script and save it to a .sql file on console. We would then create a shell script that had something like: mysql -u username -p etc yourinsertsql.sql Essentially the username would have the ability to create tables/ db's if the DB does not exist already, so make sure the user exists first. If you wanted to remove the DB afterwards, although if a DEB can do it, ask first if you want to remove the DB. Have another .sql script with essentially dropping the database. Then same again a shell that: mysql -u username -p etc yourremovesql.sql This is of course assuming you can refer to a shell script in a DEB package. But from what I have seen when using them, I would guess they are used. Hope this helped. Dave -Original Message- From: Heston James - Cold Beans [mailto:[EMAIL PROTECTED] Sent: 01 October 2008 15:22 To: mysql@lists.mysql.com Subject: Deployment of Database in a DEB package. Afternoon All, I'm hoping you'll have some recommendations for me on this little challenge. I have an application which we're preparing to deploy in a DEB package using APT on Debian based systems, although I would imagine this very same rule/process applies to other packaging methods. On the back end of my application we have a MySQL Database which needs to be created as part of the deployment process, which is automated in a script, usually SHELL or Perl, something like that. How would you deploy the database using an automated script? Would you have a SQL script which you action through the console to create the database? What's the simplest way to action a SQL script to run on the local server? Do we need to keep in mind what permissions/users are assigned to the database? In addition to this create script, we'll also need to have an automated, scripted process for removing the database if the package is removed, I'd be interested to get your thoughts on that too. Thanks in advance for any suggestions guys, Heston This message has been scanned for viruses by MailControl - (see http://bluepages.wsatkins.co.uk/?6875772) This email and any attached files are confidential and copyright protected. If you are not the addressee, any dissemination of this communication is strictly prohibited. Unless otherwise expressly agreed in writing, nothing stated in this communication shall be legally binding. The ultimate parent company of the Atkins Group is WS Atkins plc. Registered in England No. 1885586. Registered Office Woodcote Grove, Ashley Road, Epsom, Surrey KT18 5BW. A list of wholly owned Atkins Group companies registered in the United Kingdom can be found at http://www.atkinsglobal.com/terms_and_conditions/index.aspx Consider the environment. Please don't print this e-mail unless you really need to. -- 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] -- Dr. André Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complex SELECT
Hello, I need help with a SQL Select statement: I have three tables: report, category, and optional a) report table has 3 fields: ReportId, CategoryId, ReportDesc b) category table has 2 fields: CategoryId, CategoryDesc c) optional table has three fields: OptionalId, CategoryId, OptionalDesc I would like to have something like this: --- | ReportId | CategoryDesc | OptionalDesc | ReportDesc| --- | 0001 | Example 1| late, query, test | report desc abc | | 0002 | Example 2| query | test 123| | 0003 | Example 3| | test xzy | | 0004 | Example 1| late, test | report desc abc | --- However, when performing my SELECT using LEFT JOIN, I am getting something like this: --- | ReportId | CategoryDesc | OptionalDesc | ReportDesc| --- | 0001 | Example 1| late | report desc abc | | 0001 | Example 1| query | report desc abc | | 0001 | Example 1| test | report desc abc | | 0002 | Example 2| query | test 123| | 0003 | Example 3| | test xzy | | 0004 | Example 1| late | report desc abc | | 0004 | Example 1| test | report desc abc | --- Is it possible to have the column OptionalDesc as showed in the first table above? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complex SELECT
It worked like a charm. Thanks for the help! Andre On 22-Sep-08, at 2:58 PM, Jim Lyons wrote: look at using the group_concat function on OptionalDesc and group by the other fields. On Mon, Sep 22, 2008 at 1:40 PM, Andre Matos [EMAIL PROTECTED] wrote: Hello, I need help with a SQL Select statement: I have three tables: report, category, and optional a) report table has 3 fields: ReportId, CategoryId, ReportDesc b) category table has 2 fields: CategoryId, CategoryDesc c) optional table has three fields: OptionalId, CategoryId, OptionalDesc I would like to have something like this: --- | ReportId | CategoryDesc | OptionalDesc | ReportDesc| --- | 0001 | Example 1| late, query, test | report desc abc | | 0002 | Example 2| query | test 123| | 0003 | Example 3| | test xzy | | 0004 | Example 1| late, test | report desc abc | --- However, when performing my SELECT using LEFT JOIN, I am getting something like this: --- | ReportId | CategoryDesc | OptionalDesc | ReportDesc| --- | 0001 | Example 1| late | report desc abc | | 0001 | Example 1| query | report desc abc | | 0001 | Example 1| test | report desc abc | | 0002 | Example 2| query | test 123| | 0003 | Example 3| | test xzy | | 0004 | Example 1| late | report desc abc | | 0004 | Example 1| test | report desc abc | --- Is it possible to have the column OptionalDesc as showed in the first table above? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Dr. André Matos [EMAIL PROTECTED]
InnoDB Transaction and LAST_INSERT_ID()
Hi List,
Let's suppose I have these two tables:
CREATE TABLE `changes` (
`ID` int(12) unsigned NOT NULL auto_increment,
`Key` varchar(25) collate latin1_general_cs NOT NULL default '',
`Table` varchar(25) collate latin1_general_cs NOT NULL default '',
`Value` text collate latin1_general_cs NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs
AUTO_INCREMENT=1;
CREATE TABLE `staff` (
`ID` int(3) unsigned NOT NULL auto_increment,
`Name` varchar(35) collate latin1_general_cs NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs
AUTO_INCREMENT=1;
The idea is to have a audit trail to record the changes made. So, I want to
insert a new record in the staff table and right after this, insert a
record in the changes table.
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Value`) VALUES (LAST_INSERT_ID(),
'staff', 'ABC');
COMMIT;
SET AUTOCOMMIT=1;
This works fine in my test environment, however what about many users doing
at the same time. Does the LAST_INSERT_ID() get the correct ID for each
user?
Is there a better way to do this or this is fine? I will be using this with
PHP4.
Thanks for any help.
Andre
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Transaction and LAST_INSERT_ID()
Thanks Mike.
I understand the possible gaps that I might have if I use the ROLLBACK.
This is acceptable in my case.
What I really want to avoid is what I am doing now: open one transaction to
insert, or update, or delete certain information and close with the commit.
Then, I get the LAST_INSERT_ID() and open another transaction to write my
audit trail. However, if the first one went through ok but if I got a
problem at the second transaction, I need to delete the inserted or updated
or move back the deleted information. This doesn't work well.
Let's expand my staff and change tables to have this structure to
simulate my problem:
++--++
| ID | Name | Gender |
++--++
++-+---+---+---+
| ID | Key | Table | Field | Value |
++-+---+---+---+
And do this:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`, `M`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'ID', LAST_INSERT_ID());
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Name', 'ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Gender', 'M');
COMMIT;
SET AUTOCOMMIT=1;
I will get something like this:
mysql select * from staff;
++--++
| ID | Name | Gender |
++--++
| 1 | ABC | M |
++--++
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+---+
| ID | Key | Table | Field | Value |
++-+---+---+---+
| 1 | 1 | staff | ID| 1 |
++-+---+---+---+
| 2 | 1 | staff | Name | ABC |
++-+---+---+---+
| 3 | 2 | staff | Gender| M |
++-+---+---+---+
3 row in set (0.00 sec)
See that I have a problem in the third line at the Key column where I
should have 1 but I got 2 instead. This happened because of
LAST_INSERT_ID() used the ID from the changes table instead of the desired
staff table.
Is there any way to avoid this? What about the mysql_insert_id()?
Andre
On 11/28/06 7:50 PM, Mike Kruckenberg [EMAIL PROTECTED] wrote:
Andre Matos wrote:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Value`) VALUES (LAST_INSERT_ID(),
'staff', 'ABC');
COMMIT;
SET AUTOCOMMIT=1;
This works fine in my test environment, however what about many users doing
at the same time. Does the LAST_INSERT_ID() get the correct ID for each
user?
Is there a better way to do this or this is fine? I will be using this with
PHP4.
To further clarify (my initial reply didn't give much detail), when an
auto increment value is created for inserting it is in the scope of the
current connection, and is not changed by the outcome of the transaction.
If you follow the string of SQL statements against your tables you'll
see that the ID assigned to the record is not released on a rollback,
the second insert gets a new auto increment value.
mysql SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO staff (`Name`) VALUES ('ABC');
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(),
- 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql select * from staff;
++--+
| ID | Name |
++--+
| 1 | ABC |
++--+
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+
| ID | Key | Table | Value |
++-+---+---+
| 1 | 1 | staff | ABC |
++-+---+---+
1 row in set (0.00 sec)
mysql select last_insert_id();
+--+
| last_insert_id() |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql rollback;
Query OK, 0 rows affected (0.26 sec)
mysql select * from staff;
Empty set (0.00 sec)
mysql select * from changes;
Empty set (0.00 sec)
mysql INSERT INTO staff (`Name`) VALUES ('ABC');
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(),
- 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql select * from staff;
++--+
| ID | Name |
++--+
| 2 | ABC |
++--+
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+
| ID | Key | Table | Value |
++-+---+---+
| 2 | 2 | staff | ABC |
++-+---+---+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Transaction and LAST_INSERT_ID()
Thanks for all your help Mike.
Problem solved. I divided to process in two parts: one write the
insert/update/delete and then write the changes in the audit trail. All this
inside one transaction. If the first part fails, ROLLBACK. If the second
part fails, ROLLBACK, otherwise, if both were done ok, then COMMIT.
I just wanted to have all this in only one part, but that's fine. It's
working fine.
Final question: Can I create an audit trail using TRIGGER in MySQL 5? This
would be the best because any changes in the database (insert/update/delete)
will start the trigger which will be responsible for writing the audit
trail.
Thanks again!!!
Andre
On 11/28/06 9:22 PM, Mike Kruckenberg [EMAIL PROTECTED] wrote:
Andre Matos wrote:
Thanks Mike.
I understand the possible gaps that I might have if I use the ROLLBACK.
This is acceptable in my case.
What I really want to avoid is what I am doing now: open one transaction to
insert, or update, or delete certain information and close with the commit.
Then, I get the LAST_INSERT_ID() and open another transaction to write my
audit trail. However, if the first one went through ok but if I got a
problem at the second transaction, I need to delete the inserted or updated
or move back the deleted information. This doesn't work well.
Let's expand my staff and change tables to have this structure to
simulate my problem:
++--++
| ID | Name | Gender |
++--++
++-+---+---+---+
| ID | Key | Table | Field | Value |
++-+---+---+---+
And do this:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`, `M`) VALUES ('ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'ID', LAST_INSERT_ID());
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Name', 'ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(LAST_INSERT_ID(), 'Staff', 'Gender', 'M');
COMMIT;
SET AUTOCOMMIT=1;
I will get something like this:
mysql select * from staff;
++--++
| ID | Name | Gender |
++--++
| 1 | ABC | M |
++--++
1 row in set (0.00 sec)
mysql select * from changes;
++-+---+---+---+
| ID | Key | Table | Field | Value |
++-+---+---+---+
| 1 | 1 | staff | ID| 1 |
++-+---+---+---+
| 2 | 1 | staff | Name | ABC |
++-+---+---+---+
| 3 | 2 | staff | Gender| M |
++-+---+---+---+
3 row in set (0.00 sec)
See that I have a problem in the third line at the Key column where I
should have 1 but I got 2 instead. This happened because of
LAST_INSERT_ID() used the ID from the changes table instead of the desired
staff table.
Is there any way to avoid this? What about the mysql_insert_id()?
I see. In this case you could make it two operations and use the
mysql_insert_id() to capture the id from the first insert, setting a
variable to that in PHP and using that variable to ensure the same number.
What I would consider is setting a database variable inside the
transaction to store the id - the @ signifies it's a session variable
that is specific to this connection:
SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO staff (`Name`, `Gender`) VALUES ('ABC','M');
SET @staff_id = LAST_INSERT_ID();
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'ID', @staff_id);
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'Name', 'ABC');
INSERT INTO changes (`Key`, `Table`, `Field`, `Value`) VALUES
(@staff_id, 'Staff', 'Gender', 'M');
COMMIT;
SET AUTOCOMMIT=1;
So you store the value after the first insert and then reuse. You can
see it in the following string of SQL commands to demonstrate:
mysql INSERT INTO staff (`Name`, `Gender`) VALUES ('ABC','M');
Query OK, 1 row affected (0.01 sec)
mysql SET @staff_id = LAST_INSERT_ID();
Query OK, 0 rows affected (0.01 sec)
mysql SELECT @staff_id;
+---+
| @staff_id |
+---+
| 3 |
+---+
1 row in set (0.00 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(), 'staff', 'ABC');
Query OK, 1 row affected (0.01 sec)
mysql INSERT INTO changes (`Key`, `Table`, `Value`) VALUES
(LAST_INSERT_ID(), 'staff', 'ABC');
Query OK, 1 row affected (0.00 sec)
mysql select LAST_INSERT_ID();
+--+
| LAST_INSERT_ID() |
+--+
|5 |
+--+
1 row in set (0.00 sec)
mysql SELECT @staff_id;
+---+
| @staff_id |
+---+
| 3 |
+---+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert and Update together
Hi List, I would like to know if it is possible to combine Insert and Update in one SQL instruction. This is what I want to do: I have two tables: one where I will perform and Update replacing m0 by scr. If MySQL find a m0, it will need to perform an insert into a log table including the information updated. I am trying to avoid writing a php4 program to do this. I am using MySQL 4.1 Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert and Update together
No George. I took a look there before sent this email to the mysql list. My case it is not a duplicate record. What I want is that if the update in one table happen, it will be proceed by an insert in another table like a log of changes. Andre On 2/11/06 12:48 PM, George Law [EMAIL PROTECTED] wrote: Andre, I tried this a couple weeks ago... I think you want the on duplicate option for the INSERT query. depends what version you have... I think this was introduced in mysql 4.1 insert into values () on duplicate key update set x=2,y=5; unfortunately, the server I was testing this on was running 4.0.x so I couldn't use it. http://dev.mysql.com/doc/refman/5.0/en/insert.html - Original Message - From: Andre Matos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, February 11, 2006 10:54 AM Subject: Insert and Update together Hi List, I would like to know if it is possible to combine Insert and Update in one SQL instruction. This is what I want to do: I have two tables: one where I will perform and Update replacing m0 by scr. If MySQL find a m0, it will need to perform an insert into a log table including the information updated. I am trying to avoid writing a php4 program to do this. I am using MySQL 4.1 Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.15 or 5.0.15
Hi List, I am current using MySQL 4.1.9 and I want to upgrade it, but which one, 4.1.15 or 5.0.15? Any suggestions? Andre -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Tracking changes in the database
It did not work Daniel. I added this into my.cnf file: [mysqld] log-bin=/var/log/mysql/binary.log and saved the file in two places: /etc/my.cnf /usr/local/mysql/my.cnf Then, I restart the server: sudo /Lybrary/StartupItems/MySQLCOM restart I also did two UPDATE, one INSERT, and one DELETE, but the binary.log file was not created. Note: I am using Mac OS X. Andre -- Andre Matos [EMAIL PROTECTED] -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Thursday, October 13, 2005 2:33 AM To: Andre Matos; mysql@lists.mysql.com Subject: Re: Tracking changes in the database Andre Matos wrote: I am using InnoDB and replication, is there any issues? Thanks. Andre It depends what you're doing. If you use the binary log, there are no issues - in replication uses the binary log to achieve it's goals. Since the plain-text log is depreciated, you probably should learn to use the binary log. As I noted before, the plain-text log has some issues. I'm not sure what all of them are, but I've encountered issues when using plain-text logs and temporary tables ( eg 2 users create temporary tables - in the plain text log, it looks like the 1 user ). -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- 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]
Tracking changes in the database
Hi List, I need to track the changes made in the database using the insert, update and delete statements in MySQL 4.1.9. Can I use the Binary Log to get this information or is there another better solution? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Tracking changes in the database
I am using InnoDB and replication, is there any issues? Thanks. Andre -- Andre Matos [EMAIL PROTECTED] -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Thursday, October 13, 2005 1:01 AM To: Andre Matos; 'mysql@lists.mysql.com ' Subject: Re: Tracking changes in the database Andre Matos wrote: Hi List, I need to track the changes made in the database using the insert, update and delete statements in MySQL 4.1.9. Can I use the Binary Log to get this information or is there another better solution? Thanks for any help. Andre The binary log is the recommended way. There's also a plain-text log, but you'll run into issues if you're using temporary tables, amongst other things. Also, the plain text log has been removed from 5.0.x ( I believe ). You can enable either with startup switches or with entries in the my.cnf file. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PHP MySQL connection problem
Hi list, I am getting slow connections between php4 and MySQL 4.1.9. There are some connections that are very fast, but others can take from 5 to 20 second or more (usually it takes less than 0.1 second to create a new connection). I tried to identify if there is a pattern but it seems that it does not exist, in other words, it can create 5 connections and then the 6th takes too much time. In another time, it process 13 connections fast and the 14th is slow again. Does anyone has any idea what is going on? Can this be related to the MySQL connections limit? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Collation latin1_general_cs
Hi List, I was checking the list of collations for a character set latin that I have in my MySQL version 4.1.10-standard running on Mac OS X 10.4. Below is the that I have and does not include latin1_general_cs. What will happen if I force to use it when I create a database, tables and fields? Will it work or not? mysql SHOW COLLATION LIKE 'latin1%'; +---+-++-+--+-+ | Collation | Charset | Id | Default | Compiled | Sortlen | +---+-++-+--+-+ | latin1_german1_ci | latin1 | 5 | | | 0 | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 | | latin1_danish_ci | latin1 | 15 | | | 0 | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | | latin1_bin| latin1 | 47 | | Yes | 1 | | latin1_general_ci | latin1 | 48 | | | 0 | | latin1_general_cs | latin1 | 49 | | | 0 | | latin1_spanish_ci | latin1 | 94 | | | 0 | +---+-++-+--+-+ Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Collation latin1_general_cs
Hi Paul, It does appear in the list, but there is no Yes in the column Compiled and that's why my concern. I am using the Collation latin1_general_cs since I moved from version 3.x to 4.1.10. Is it really working without compiled? Andre On 5/2/05 4:04 PM, Paul DuBois [EMAIL PROTECTED] wrote: At 15:28 -0400 5/2/05, Andre Matos wrote: Hi List, I was checking the list of collations for a character set latin that I have in my MySQL version 4.1.10-standard running on Mac OS X 10.4. Below is the that I have and does not include latin1_general_cs. What will happen if I force to use it when I create a database, tables and fields? Will it work or not? I don't understand what you mean by stating that the list doesn't include latin1_general_cs. It does appear in the list. mysql SHOW COLLATION LIKE 'latin1%'; +---+-++-+--+-+ | Collation | Charset | Id | Default | Compiled | Sortlen | +---+-++-+--+-+ | latin1_german1_ci | latin1 | 5 | | | 0 | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 | | latin1_danish_ci | latin1 | 15 | | | 0 | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | | latin1_bin| latin1 | 47 | | Yes | 1 | | latin1_general_ci | latin1 | 48 | | | 0 | | latin1_general_cs | latin1 | 49 | | | 0 | | latin1_spanish_ci | latin1 | 94 | | | 0 | +---+-++-+--+-+ -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Collation latin1_general_cs
Ok Paul. Do you have any idea why phpMyAdmin removed the latin1_general_cs from the collation pop-up list? I have the same problem like this one (except that I am using Mac OS X): http://forums.mysql.com/read.php?11,21462,21462 I looked at the release notes for the version 2.6.2 (it was working up to version 2.6.1-pl3), and I found this: Do not offer unavailable collations. Does this means that if the MySQL is not compiled with the latin1_general_cs it will not work even if MySQL loaded it at runtime? Andre On 5/2/05 4:44 PM, Paul DuBois [EMAIL PROTECTED] wrote: At 16:15 -0400 5/2/05, Andre Matos wrote: Hi Paul, It does appear in the list, but there is no Yes in the column Compiled and that's why my concern. I am using the Collation latin1_general_cs since I moved from version 3.x to 4.1.10. Is it really working without compiled? Yes. Some character set information is loaded at runtime. Andre On 5/2/05 4:04 PM, Paul DuBois [EMAIL PROTECTED] wrote: At 15:28 -0400 5/2/05, Andre Matos wrote: Hi List, I was checking the list of collations for a character set latin that I have in my MySQL version 4.1.10-standard running on Mac OS X 10.4. Below is the that I have and does not include latin1_general_cs. What will happen if I force to use it when I create a database, tables and fields? Will it work or not? I don't understand what you mean by stating that the list doesn't include latin1_general_cs. It does appear in the list. mysql SHOW COLLATION LIKE 'latin1%'; +---+-++-+--+-+ | Collation | Charset | Id | Default | Compiled | Sortlen | +---+-++-+--+-+ | latin1_german1_ci | latin1 | 5 | | | 0 | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 | | latin1_danish_ci | latin1 | 15 | | | 0 | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | | latin1_bin| latin1 | 47 | | Yes | 1 | | latin1_general_ci | latin1 | 48 | | | 0 | | latin1_general_cs | latin1 | 49 | | | 0 | | latin1_spanish_ci | latin1 | 94 | | | 0 | +---+-++-+--+-+ -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Collation latin1_general_cs
Hi List, I was checking the list of collations for a character set latin that I have in my MySQL version 4.1.10-standard running on Mac OS X 10.4. The list that I have, bellow, does not include latin1_general_cs. What will happen if I force to use it when I create a database, tables and fields? Will it work or not? mysql SHOW COLLATION LIKE 'latin1%'; +---+-++-+--+-+ | Collation | Charset | Id | Default | Compiled | Sortlen | +---+-++-+--+-+ | latin1_german1_ci | latin1 | 5 | | | 0 | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 | | latin1_danish_ci | latin1 | 15 | | | 0 | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | | latin1_bin| latin1 | 47 | | Yes | 1 | | latin1_general_ci | latin1 | 48 | | | 0 | | latin1_general_cs | latin1 | 49 | | | 0 | | latin1_spanish_ci | latin1 | 94 | | | 0 | +---+-++-+--+-+ Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow access Apache + PHP + MySQL
Hi List, I have 4 web based systems developed using PHP4 and MySQL accessed for 10 users. The Web Server and Database Server were running ok on a Mac OS X 10.3 G4 dual. However, since we move to a new server, the access becomes very slow. This was not expected since we move to a 64 bits high performance machine. Now, we are using MySQL version 4.1.9 with Apache 2.0.52 and PHP 4.3.10, all compiled and running on a Linux Fedora X86_64. My first thought was the systems, but since I have not changed 3 of the 4 systems, I start to look to the database. I monitored the MySQL using MySQL Administrator, but I couldn't identify any problem. It looks ok, but not completely sure if really is. The system administrator told me that could be the PHP session, but again, he also was not complete sure about this. It is a big problem since I need to check in 3 places: MySQL, Apache, or PHP. Does anyone had this kind of problem or has any suggestion or direction to help me to identify and solve this issue? Any help will be appreciated!!! Thanks. Andre -- Andre Matos [EMAIL PROTECTED] -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB and Transactions
Hi List, Let's suppose that I have this sequence of events: - create a connection 1 -- start a transaction --- create a new connection 2 insert a new record named B --- close the connection 2 --- insert a new record named A -- rollback - close the connection 1 This sequence will be written in PHP4. My question is: Will the record named B be written in the database or it will be rollback with the record named A? Thanks for any help!!! Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Symchronization problem
Hi List, A have two MySQL 4.1.9 installed into two Linux servers and synchronized them, so I have now a master and slave. My problem is that since I synchronized them, I am receiving comments from my users that the speed is not good as before the synchronization. Is this possible? If yes, how and where can I check this? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: utf8 x latin
Thanks for your help Gabriel. I will use the latin1 with case sensitive. Andre On 1/31/05 7:32 AM, Gabriel PREDA [EMAIL PROTECTED] wrote: I think you should use: latin1_general_ci and on columns with spanish you should use latin1_spanish_ci. English, French, Portuguese not having a separate collation I believe that they are included into latin1_general_ci ? Please observe the last part from a collation name: case sensitive (_cs) ... case insensitive (_ci) ... and binary (_bin) ! UTF8 consumes more space on disk ! If you issue a SHOW CHARACTER SET command mysql SHOW CHARACTER SET; You will notice the last column named Maxlen that defines how many bytes takes to store a letter. On utf8_general_ci you will notice 3... as on latin1__xx you will only see 1... Although these values represent a MAX value... what you need will use at most 2 bytes per letter in UTF-8... but in latin1_xxx_xx will, for sure, will use one byte... on long texts this will count ! In my opinion you should stick to latin1_xxx_xx ! Afther some thought and aditional computation I declared myself satisfied with: latin2 - ISO 8859-2 Central European - latin2_general_ci - 1byte but I'm having Romanian + English + French columns. Try to read http://dev.mysql.com/doc/mysql/en/charset.html with no distraction arround you ! Gabriel - Original Message - From: Andre Matos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, January 30, 2005 9:29 PM Subject: utf8 x latin Hi list, I was wondering which one is better to use, utf8_general_ci or latin1_general_cs, with English, French, Portuguese, and Spanish? Thanks. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
utf8 x latin
Hi list, I was wondering which one is better to use, utf8_general_ci or latin1_general_cs, with English, French, Portuguese, and Spanish? Thanks. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server crached problem
Hi List, Today the hard drive from my server where MySQL was running crashed. I have a dump from yesterday night but I would like to save the work for today before the crash. I saved the full directory of the MySQL (/usr/local/mysql). Is my data inside the frm file or in another file? How can I access and retrieve my data? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rollback and INSERT_ID() or LAST_INSERT_ID()
Hi List, I have a field in one of my tables that uses auto-increment from MySQL 4.1.8-nt (Windows XP). My problem is to get the last insert ID when the insert fails and I use rollback. The MySQL is still incrementing the field. How can I avoid this if it is possible? I am trying to avoid to use the function MAX() to get the last ID inserted. Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
Thanks Eric, but I can let it increment because I cannot have a gave in the numbers. I think I will need to use MAX() in this case. Thanks. Andre On 1/17/05 8:14 PM, Eric Bergen [EMAIL PROTECTED] wrote: Just let it increment. Keeping it incremented is MySQL's way ot insuring that the same id doesn't get used twice for different records. It's doing everything correctly. -Eric On Mon, 17 Jan 2005 19:06:45 -0500, Andre Matos [EMAIL PROTECTED] wrote: Hi List, I have a field in one of my tables that uses auto-increment from MySQL 4.1.8-nt (Windows XP). My problem is to get the last insert ID when the insert fails and I use rollback. The MySQL is still incrementing the field. How can I avoid this if it is possible? I am trying to avoid to use the function MAX() to get the last ID inserted. Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rollback and INSERT_ID() or LAST_INSERT_ID()
Yes, I know about this. What I plan is to remove the auto_increment. I will do this by hand locking the entire table just right before inserting the field. Actually, it is running like this. I am just upgrading the PHP. I will keep the auto_increment in other tables that does not have this kind of problem. Thanks a lot for your help. Andre On 1/17/05 9:53 PM, Paul DuBois [EMAIL PROTECTED] wrote: At 21:27 -0500 1/17/05, Andre Matos wrote: Thanks Eric, but I can let it increment because I cannot have a gave in the numbers. I think I will need to use MAX() in this case. Using MAX() won't guarantee that you won't have gaps. What you're describing cannot be achieved in the general case. Consider this scenario: - Transaction T1 begins, generates an AUTO_INCREMENT value n. - Transaction T2 begins, generates an AUTO_INCREMENT value n+1. - Transaction T2 commits. - Transaction T1 rolls back. You now have a gap at value n, and a used value of n+1. MAX() at this point returns n+1, not n, so that won't help you reuse n. With more than two transactions running simultaneously, each of which can roll back or commit, the situation becomes more complex. Might be worth reconsidering whether you really require no gaps. It's generally better to try to design an application not to have that dependency. Thanks. Andre On 1/17/05 8:14 PM, Eric Bergen [EMAIL PROTECTED] wrote: Just let it increment. Keeping it incremented is MySQL's way ot insuring that the same id doesn't get used twice for different records. It's doing everything correctly. -Eric On Mon, 17 Jan 2005 19:06:45 -0500, Andre Matos [EMAIL PROTECTED] wrote: Hi List, I have a field in one of my tables that uses auto-increment from MySQL 4.1.8-nt (Windows XP). My problem is to get the last insert ID when the insert fails and I use rollback. The MySQL is still incrementing the field. How can I avoid this if it is possible? I am trying to avoid to use the function MAX() to get the last ID inserted. Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Grant question
Hi list, I have many databases on my MySQL server such as: dbexample dbexample_clients_abc dbexample_clients_def dbexample_clients_ghi dbexample_local_abc dbexample_local_def dbexample_local_ghi Is it possible to grant all these databases in just one GRANT instruction such as: GRANT SELECT,INSERT,UPDATE,DELETE ON dbexample*.* TO 'user'@'localhost' BY 'password'; I tried this instruction but did not work. Is someone knows how to do if it is possible? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL configuration file on Mac OS X
Hi List, I installed the MySQL 4.1.7 on my Windows XP and for that I have used the my.ini to set the startup configuration for using a different language and to use the old_password. Now I am installing on my Mac and I am wondering if there is any my.ini or my.cnf on Mac OS X. Does anyone knows about this? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL configuration file on Mac OS X
I just have finished to install the version 4.1.7 on my Mac and I looked at these directories below and I couldn't find the file my.cnf as described on the like that you gave me: /etc/my.cnf DATADIR/my.cnf ~/.my.cnf Do I need to create it or MySQL is suppose to do the job? Thanks. Andre On 11/29/04 1:55 PM, Michael Stassen [EMAIL PROTECTED] wrote: Mac OS X is Unix, so you follow the directions for Unix, which means you use my.cnf. See the manual for details http://dev.mysql.com/doc/mysql/en/Option_files.html. Michael Andre Matos wrote: Hi List, I installed the MySQL 4.1.7 on my Windows XP and for that I have used the my.ini to set the startup configuration for using a different language and to use the old_password. Now I am installing on my Mac and I am wondering if there is any my.ini or my.cnf on Mac OS X. Does anyone knows about this? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 and PHP5 with old client
Hi, I have installed MySQL 4.1.7-nt + Apache 2 + PHP 5.0.2 + PHPMyAdmin 2.6.0-pl1 on my Windows XP Pro SP2. I am trying to solve the problem with MySQL PHP Old Clients using OLD_PASSWORD instead of just PASSWORD. How can I set on my.ini to start MySQL using as a default the OLD_PASSWORD? I tried to insert: [mysqld] --old-passwords and [mysqld] old-passwords but both didn't work. Does anybody know how to do this? Is any other better way to make PHP5 work with the new password format without recompiling (It is easy on Linux/Unix but not on Windows XP). Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT question
Hi Roger, I took the suggestion from Brad Eacker and use BETWEEN and now works without problem. However, I decided to do a couple more tests and what I found was that the problem occurs on MySQL version 4.0.18-standard using InnoDB on Linux but does not occur on Mac OS X using the same MySQL version. Andre On 9/20/04 5:52 PM, Roger Baklund [EMAIL PROTECTED] wrote: * Andre Matos I am performing a SELECT and I am getting 0 rows when I run the SELECT direct in the MySQL database and getting 1 when I run using PHP. This is my select: SELECT * FROM scan WHERE TimePointID = 3 AND ScanQCResult = 'n' AND (ScanStatusID 90 OR ScanStatusID 98); Looks ok. I realized latter analyzing this select that I made a mistake using OR at this point: (ScanStatusID 90 OR ScanStatusID 98), it should be AND. Are you sure about that? However, in both cases, I am still getting 0 rows from the database, which is correct. My problem is using the PHP to run the SELECT, if I use OR using the PHP, I got 1 as a result, and if I use AND I got 0 as a result. This is correct, if you have one record with ScanStatusID in the range 90-98. Is anyone can tell me what is going on? You seem to be misinterpreting how logical expressions work. A SQL select statement is a description of the (sub-)set of data you wish to retrieve from the database. This description often includes a WHERE clause, describing wanted records, which again often includes a logical expression. The expression is built up by operands and operators. The logical operators relevant in SQL is AND, OR and NOT. NOT is a negation, this operator takes one operand, the the result is the opposite of the operand. NOT true is false, and NOT false is true. The other two operators, AND and OR, need two operands, one on each side. For the AND operator, BOTH sides of the operator must be true for this part of the expression to be true. For the OR operator, ANY of the sides of the operator must be true for that part of the expression to be true. So, for your expression above, you can not say ...ScanStatusID 90 AND ScanStatusID 98..., because ScanStatusID can not be below 90 AND above 98. ScanStatusID is a single number, it can be below 90 OR above 98. Not both at the same time. -- Roger -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT question
Hi List, I am performing a SELECT and I am getting 0 rows when I run the SELECT direct in the MySQL database and getting 1 when I run using PHP. This is my select: SELECT * FROM scan WHERE TimePointID = 3 AND ScanQCResult = 'n' AND (ScanStatusID 90 OR ScanStatusID 98); I realized latter analyzing this select that I made a mistake using OR at this point: (ScanStatusID 90 OR ScanStatusID 98), it should be AND. However, in both cases, I am still getting 0 rows from the database, which is correct. My problem is using the PHP to run the SELECT, if I use OR using the PHP, I got 1 as a result, and if I use AND I got 0 as a result. Is anyone can tell me what is going on? Thanks in advance for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Scripts
Hi List, Let's suppose that I have a script to create one database (tables, indexes, etc). How can I call another script from this script? For example: DROP DATABASE test; CREATE DATABASE test; (here call another script) back to the previous script Thanks. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Scripts - ERROR
Hi, I tried but didn't work. Here is my script: # # @Name : NTT_dbcreator.2004-06-07.v1-001.andre.sql # @DESCRIPTION : NTT Database Creator # @CREATED : Jun 07, 2004 by Andre Matos - [EMAIL PROTECTED] # @VERSION : 1.01 # SET FOREIGN_KEY_CHECKS=0; DROP DATABASE IF EXISTS `database1`; CREATE DATABASE `database1`; commit; SOURCE ntt_dbcreator.2004-06-07.v1-001.andre.sql; commit; DROP DATABASE IF EXISTS `database2`; CREATE DATABASE `database2`; commit; SOURCE ntt_dbcreator.2004-06-07.v1-001.andre.sql; commit; DROP DATABASE IF EXISTS `database3`; CREATE DATABASE `database3`; commit; SOURCE ntt_dbcreator.2004-06-07.v1-001.andre.sql; commit; DROP DATABASE IF EXISTS `database4`; CREATE DATABASE `database4`; commit; SOURCE ntt_dbcreator.2004-06-07.v1-001.andre.sql; commit; DROP DATABASE IF EXISTS `database5`; CREATE DATABASE `database5`; commit; SOURCE ntt_dbcreator.2004-06-07.v1-001.andre.sql; commit; DROP DATABASE IF EXISTS `database6`; CREATE DATABASE `database6`; commit; SOURCE ntt_dbcreator.2004-06-07.v1-001.andre.sql; commit; SET FOREIGN_KEY_CHECKS=0; # end of the script NB: ntt_dbcreator.2004-06-07.v1-001.andre.sql is another script that is responsable for creating the tables, indexes, relations, etc... This is the error when I tried to run the main script using phpMyAdmim: Database mysql running on localhost Error SQL-query : SOURCE ntt_dbcreator.2004 -06 - 07.v1 - 001.andre.sql MySQL said: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SOURCE ntt_dbcreator.2004-06-07.v1-001.andre.sql' at line 1 Thanks. Andre On Thu, 8 Jul 2004, Victor Pendleton wrote: Have you tried writing the script using the source command? drop database; create database; source populate_db; -Original Message- From: Andre MATOS To: [EMAIL PROTECTED] Sent: 7/8/04 12:20 PM Subject: Scripts Hi List, Let's suppose that I have a script to create one database (tables, indexes, etc). How can I call another script from this script? For example: DROP DATABASE test; CREATE DATABASE test; (here call another script) back to the previous script Thanks. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ER Diagrams with mysql
Hi, Have you tried DBDesigner4? Take a look, it's an OpenSource. http://www.fabforce.net/dbdesigner4/ Andre On Tue, 6 Jul 2004, Daniel Kasak wrote: Leandro Melo wrote: Hi, i created a database with around 20 tables. However i created the tables by script writting all create table statements, create constraints, etc... I need now a Entity Relationship diagram so i have a visual information about my database. I tried MySQL Comand Center but i doens't have such tool. Does any one know either a tool i can throw a database creation script and it gives me the relationship diagram or a mysql tool that can use my installed database and generate the relationship diagrams??? Thanks, Data Architect, by 'The Kompany' does ER diagrams. http://www.thekompany.com/products/dataarchitect/ It has a 'reverse engineer' feature that lets you suck a schema from a DB server. It also outputs a schema in SQL scripts. I'm not sure if you can feed it SQL scripts, but it seems like a logical function for it to have. I haven't used it for quite some time... It's not free. I don't know of any free ER tools. Or at least none I've found and investigated were good enough to remember. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subtracting two Dates and getting days
Hi, How can I subtract two dates and get the number of days? (date1 - date2) = days Thanks. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subtracting two Dates and getting days - SOLVED
Hi All, I found a function named TO_DAYS that solved my problem. Thanks. Andre On Sun, 23 May 2004, Andre MATOS wrote: Hi, How can I subtract two dates and get the number of days? (date1 - date2) = days Thanks. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sub-SELECT
Hi list, does MySQL accept a SELECT inside another SELECT, like this: SELECT t.Trial_ID_Code AS Trial, CONCAT(s.Site_Primary_Investigator_Title, , s.Site_Primary_Investigator_Last_Name) AS 'Investigator', CONCAT(sc.Site_ID_Number, -, s.Site_ID_Code, -, sc.Scanner_ID_Number) AS Site, sc.Scan_Subject_ID AS 'SubjectInitials', sc.Scan_Screening_ID AS 'ScreeningNumber', tp.TimePoint_Code AS 'TimePoint', sc.Scan_Acquired_On AS 'ScanAcquiredOn', IF(sc.QCResult_ID_Number = 2, 'Passed', 'Faild') AS 'QCResult', IF(sc.Scan_Screening_Result='n', 'Failed', 'Passed') AS 'GDScreeningResult' (SELECT Scan_Screening_Result FROM ScanQuantification WHERE sc.Trial_ID_Number = Trial_ID_Number AND sc.Site_ID_Number = Site_ID_Number AND sc.Scanner_ID_Number = Scanner_ID_Number AND sc.Scan_ID_Number = Scan_ID_Number AND sq.Quantification_ID_Number = 1 AND ProcessType_ID_Number = 5) AS 'GDScreeningValue' FROM Scan sc, Trial t, Site s, TimePoint tp WHERE sc.Trial_ID_Number = t.Trial_ID_Number AND sc.Site_ID_Number = s.Site_ID_Number AND sc.TimePoint_ID_Number = tp.TimePoint_ID_Number AND sc.TimePoint_ID_Number = 8 AND (sc.Scan_Receipt_On = '2004-05-10' AND sc.Scan_Receipt_On = '2004-05-14') AND sc.QCResult_ID_Number 1 ORDER BY sc.Site_ID_Number Thanks. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Case Sensitive
Hi List, I have three MySQL servers. One is running on Windows XP, another on MacOS X, and finally one on Linux. All servers have the same version: 4.0.18. Every time when I create tables on the servers Windows XP and MacOS, MySQl creates the database without creating problems with the tables names (is not case sensitive). However, in the Linux I have problems (it follows exactly the name uppercase and lowercase is case sensitive). So, for example: On Windows/Mac: TestABC is created as testabc On Linux: TestABC is created as TestABC 1) How can I disable this in the MySQL server running on Linux? 2) What is better? Disable or enable the others two? By the way, I have the same problem with the usernames and passwords. How can I enable/disable MySQL case sensitive for data? Thanks. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT HELP
Hi, Is it possible to create a Select performing a math formula? For example: First I need to add two values come from the same table but from different records. The result will be divided from one number got from another table. Now, the new result will be added with another value got from another table creating the final result. Like this: ((value_from_record_1_from_table_A + value_from_record_15_from_table_A) / value_from_table_B ) + value_from_table_C Is this possible? Is there anyone who can help me to create this SELETC? Thanks. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT HELP
Hi Robert, the criteria for the record_1 and record_15 is that both are in the same table, but in different records and to find each one it is necessary to perform a WHERE clause. Let's I give you the real example: My problem is while inserting a new record in my table named ScanQuantification, I will need to update another table named Scan, IF a value from the field ScanQuantificationValue from table ScanQuantification is equal or greater than a calculate value. The calculate value comes from this formula: ( ( A + B ) / 2 + C) where: A is a value find from the field ScanQuantificationValue from table ScanQuantification where the TimePoint = 8 B is a value find from the field ScanQuantificationValue from table ScanQuantification where the TimePoint = 9 C is a value find from the field TrialBaseValue from table Trial This is easy to do using the PHP language. However I will have different Trials and each one has different formula. That's why I want to put the SELECT to work for me. Thanks Andre On Fri, 30 Apr 2004, Robert J Taylor wrote: Andre MATOS wrote: Hi, Is it possible to create a Select performing a math formula? For example: First I need to add two values come from the same table but from different records. The result will be divided from one number got from another table. Now, the new result will be added with another value got from another table creating the final result. Like this: ((value_from_record_1_from_table_A + value_from_record_15_from_table_A) / value_from_table_B ) + value_from_table_C Not knowing what the criteria for selecting the different records from table_A (1 and 15), I'll forgo a join clause and just illustrate a simple alias with where clause: SELECT (( a1.value + a2.value ) / b.value ) + c.value AS final_result FROM table_A a1, table_A a2, table_B b, table_C c WHERE a1.key = 1 AND a2.key = 15 AND /* guessing here */ b.key = a1.key AND c.key = a2.key AND a1.key a2.key AND a2.key IS NOT NULL AND a1.key IS NOT NULL; That's not correct as I am guessing your actual criteria, etc., but it gives the idea. Can you be more specific on the criteria for relating (joining) tables A, records 1 and 15, with themselves and with tables B and C? Is this possible? Is there anyone who can help me to create this SELETC? Thanks. HTH, Robert Taylor [EMAIL PROTECTED] -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The MySQL data folder can be an virtual link?
Hi, Is it possible to move the folder data where there are all databases to another hard driver and then create a virtual link using ln -s? If yes, is it just shutdown the MySQL server and then move the folder, create the link and finally start up the server? Thanks! Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I upload dumped data
Hi List, Once I have dumped my database using mysqldump how can I upload the data again using the mysql command line (MySQL in ansi mode)? Thanks. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I upload dumped data (fwd)
Hi List, Once I have dumped my database using mysqldump how can I upload the data again using the mysql command line (MySQL in ansi mode)? Thanks. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I upload dumped data
I tried it, but it didn't work. ERROR 1217 at line 3483: Cannot delete or update a parent row: a foreign key constraint fails I am using InnoDB... that's my problem... Thanks. Andre On Fri, 19 Mar 2004, Egor Egorov wrote: Andre MATOS [EMAIL PROTECTED] wrote: Hi List, Once I have dumped my database using mysqldump how can I upload the data again using the mysql command line (MySQL in ansi mode)? mysql -u user_name -p database_name dump_file.sql -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Backup advice
Hi Ware Adams, thanks for your answer. Here's another question: Is it possible to set MySQL to save all the changes that can happen, for example, if some one insert a new record into table X, MySQL save a log about this task performed and all data that was inserted. I another person update one field from table Y, MySQL save all the information about this change. So, If something happens after the backup, we can recover the database without go back and perform again the insert and the update? If yes, where can I find documentation about this? Thansk a lot. Andre On Wed, 3 Mar 2004, Ware Adams wrote: Andre MATOS wrote: What is the best way to make a good and trustable backup from a live database, in other words, without shutdown the database? Is there any free open source tool for this also? There's no free/open source tool that makes a true hot backup when you're using InnoDB. mysqldump can be scripted to only dump one table at a time, but that means the tables will not be consistent. You can dump the entire dataset, but that will lock out other users. mysqldump files are easily readable and if you backup a table at a time you can restore only a single table. InnoDB Hot Backup makes a consistent backup across all tables without disturbing users. It's not free and you can only restore the entire data set (to which you could then apply binlogs from the backup time forward to bring the data set up to current time). You also cannot view or edit the files with a text editor. Also, InnoDB Hot Backup only backs up the InnoDB table space and logs. You must also back up your table definitions and binlogs separately. This is all described in the InnoDB manual at http://www.innodb.com/ We use both methods as they are helpful under different circumstances. Good luck, Ware Adams -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Client connection error
Hi Sasha, after I sent this email for the list, I did some tests and I realized that the problem could be my firewall, so I just turned off for some tests and it worked. Now I need to reconfigure the firewall to permit access for the MySQL. Thanks a lot. Andre On Tue, 2 Mar 2004, Sasha Pachev wrote: Andre MATOS wrote: Hi, I am trying to connect from one computer that has MySQL installed (client) in another one that also has MySQL (server). Using this command: C:\mysql\binmysql.exe -u root -h server.com -p Enter password: * ERROR 2003: Can't connect to MySQL server on 'server.com' (10060) However I am also using Apache+PHP and it is working withou problem. Does anyone know what is the problem? The error means connection timed out. Possibly a restrictive firewall? -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Backup advice
Hi, I am using MySQL 4.0.18 with InnoDB and I would to have some suggestions about how can I backup the data. I read in this list that some people are using mysqldump, others are using some tools such as Hot Backup. What is the best way to make a good and trustable backup from a live database, in other words, without shutdown the database? Is there any free open source tool for this also? Thanks. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inserting and Grant users
Hi,
I insert a new user to access the MySQL database. I have 4 databases plus
the mysql database. I want that this user can run the select instruction
just into one database, so I did this:
[prompt]$ mysql u root p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3147 to server version: 4.0.18-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
INSERT INTO user (Host, User, Password, Select_priv) VALUES ('%',
'myuser', password('hispassword'), 'Y');
mysql GRANT ALL ON database2.* TO myuser;
mysql FLUSH PRIVILEGES;
mysql quit;
However, when I log using this new user myuser, I still can see the others
database and a can insert, update and delete records into all databases
instead of just have the rights to select in the database2. What I did was
correct or it is missing something?
Best Regards,
Andre
--
Andre Matos
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL speed problem
Hi, I faced a speed access problem today with the MySQL. In the morning was working well and fast, but after lunch when I tried to access the MySQl using Apache+PHP it was terrible. For just a select, it took 2 a 3 minutes to process. I checked the log files from Apache and PHP and I could not find any problem. The unique thing that I found was the hard-drive space. It was 1.7 GB. I am using Mac OS 10.3. So I cleaned and I got 3.1 GB and after 1 hour the fast speed came back. Does MySQL use a lot the swap area? I am using Innodb. Where I can find a tutorial or a good documentation that can help to set up a good MySQL environment, because I will hold a lot of data in near future. Thanks. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inserting and Grant users
On Tue, 2 Mar 2004, Michael Stassen wrote:
Andre MATOS wrote:
Hi,
I insert a new user to access the MySQL database. I have 4 databases plus
the mysql database. I want that this user can run the select instruction
just into one database, so I did this:
[prompt]$ mysql u root p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3147 to server version: 4.0.18-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
INSERT INTO user (Host, User, Password, Select_priv) VALUES ('%',
'myuser', password('hispassword'), 'Y');
mysql GRANT ALL ON database2.* TO myuser;
mysql FLUSH PRIVILEGES;
mysql quit;
However, when I log using this new user myuser, I still can see the others
database and a can insert, update and delete records into all databases
instead of just have the rights to select in the database2. What I did was
correct or it is missing something?
Best Regards,
Andre
Great! Works well now. Thanks Michael!!!
This is why it's best to use GRANT and REVOKE instead of trying to
manually edit the mysql tables. You gave global select privilege in
your insert into user. The correct (and simpler) way to have done this
would have been the one statement
I heard about this, but I was following a tutorial. It's clear now that
the tutorial is not good.
GRANT ALL ON database2.* TO myuser IDENTIFIED BY 'hispassword';
That will create the user entry, if necessary, and grant the specified
privileges (ALL on database2.*). It also handles the FLUSH for you.
There are a couple ways to fix what you have. I'd suggest
REVOKE ALL PRIVILEGES,GRANT OPTION FROM myuser;
GRANT ALL ON database2.* TO myuser;
See http://www.mysql.com/doc/en/GRANT.html for more.
Michael
--
Andre Matos
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Client connection error
Hi, I am trying to connect from one computer that has MySQL installed (client) in another one that also has MySQL (server). Using this command: C:\mysql\binmysql.exe -u root -h server.com -p Enter password: * ERROR 2003: Can't connect to MySQL server on 'server.com' (10060) However I am also using Apache+PHP and it is working withou problem. Does anyone know what is the problem? Thanks a lot. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join two tables with Select
Hi List, I need to create a complex Select joining 2 tables. Table Product, I have these fields: Code (PK) Description Records: 01 Product A 02 Product B Table Price, I have these fields: Code (PK/FK) Sequence (PK) Price Records: 01 1 10.00 01 2 12.00 01 3 14.00 01 4 15.00 02 1 20.00 02 2 22.00 03 3 23.00 04 4 24.00 I need to combine these two tables to have this layout: Product First Price Second PriceThird Price Forth Price Product A 10.00 12.00 13.00 14.00 Product B 20.00 22.00 23.00 24.00 Is this possible to do if the Select instruction? If yes, is anyone can help me to build this Select? Thanks a lot. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database user access
Hi, Is it possible to allow one user to access MySQL using for example PHP but stop him when he tried to access MySQL directly? Thanks -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database user access
Hi, Is it possible to allow one user to access MySQL using for example PHP but stop him when he tried to access MySQL directly? Thanks -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Field name length
Hi, Is there any length limit for the field name or it is not good to define a field name like my_field_name_is_this_size_plus_a_little_bit_more ? Thanks. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Privileges
Hi List, Is it possible to GRANT an USER to just UPDATE one specific FIELD instead of the entire TABLE? Is it also possible to create a GROUP with all GRANTs and then create the USERs linked with a specific GROUP? Thanks. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Record only readable
Hi List, Is anybody knows how to lock (make just readable) a field from a record or the whole record? This is the idea: I need to create a table that some people will insert records. One person will access a record to validate the values inserted into it. Once a field was validated, nobody else can change the value again. The people can still look it, but just look. If it is not possible to make readable the fields, is it possible the whole record? Is this possible to do in MySQL? If it is, how can I do it and which version of MySQL should I use? Thanks. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
