There is one issue. MySQL changed the way it parsed SQL join statements
in 5.03. Specifically JOINS in the older syntax using implicit joins
{tables seperated by ,} i.e.
FROM table_a, table_b
are parsed differently than they are in versions prior to 5.03. If you
have any statements that do any
Let's try to characterize the load and sizing. If the posts are mostly
text.
100 forums X 100,000 posts X 2,000 bytes per post = 20GB large but not
huge
We have people reading the posts. Even speed readers will take minutes
to read the new posts. Maybe 1 in 10 - 100 readers will do a new post. A
If you want to see all of the products {even those that have never been
ordered} then you need to SELECT ... FROM products ... LEFT JOIN orders
I think you also have to do a LEFT JOIN on order_items
And pull prod_name from products {don't know what the column name in
products is}.
SELECT o.id,
If the order of the bytes is opposite between big-endian and
little-endian, then if you can get the bytes in a string REVERSE()
should flip the order.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 14, 2006 3:25 PM
To: gerald_clark
Cc:
You can use a user variable [EMAIL PROTECTED] in the sample below} to number
the rows in the result set.
mysql set @row:=0;
Query OK, 0 rows affected (0.00 sec)
mysql select @row:[EMAIL PROTECTED], city_Name from citiesw limit 10;
+--+-+
| @row:[EMAIL PROTECTED] |
I have a simple stored procedure which works as intended.
As soon as I add a UNION in the SELECT I get the error message
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'select phon_Lvl INTO
If you are on 5.0.x you can use
SELECT column_Name
FROM INFORMATION_SCHEMA.columns;
INFORMATION_SCHEMA is a set of VIEWS that lets you access the database
structure. See
http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
-Original Message-
From: Mike OK [mailto:[EMAIL
I just added a user variable @fdata to get visabilility outside of the
procedure and this is what I get.
mysql delimiter //
mysql create procedure test20 ()
-BEGIN
- DECLARE fdata BLOB;
- DECLARE foffset INT UNSIGNED;
- DECLARE flength INT UNSIGNED;
-
One huge problem with this approach. The new table doesn't have any of
the indexes that were present in the previous table. You may be better
off to build a script that renames the current tables followed with the
full CREATE TABLE statement(s).
Do a SHOW CREATE TABLE current table;
Then take
You probably want a multi table update assuming you are running at least
4.0.x.
Add the column(s) to A with an ALTER TABLE
UPDATE A
INNER JOIN B
ON (...
SETA.col_name = B.col_name,
A.col_name_2 = B.col
WHERE ..
You can copy the join structure from the
If by Decimal you mesn HEXIDECIMAL you can use CONV
where the 1st arg is the HEX value,
2nd arg is From Base
and 3rd arg is To Base.
You will have to suround the aliases with `'s if you really want
the names to be numeric.
mysql select MID(CONV('A5',16,2),1,1) AS `7`,
-
Actually CONV converts from any base to any base so if it is base 10
then just replace the 16's with 10's.
Too much time looking at dump's.
-Original Message-
From: Bill Dodson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 3:09 PM
To: Gordon Bruce
Cc: Ed Reed; mysql
Try this
UPDATE people
SETphone = CASE
WHEN LEFT(phone,4) = '405_' THEN MID(phone,5,20)
WHEN LEFT(phone,3) = '405' THEN MID(phone,4,20)
ELSE phone
END
FROM people
WHERE LEFT(phone,3) = '405'
AND LENGTH(phone)
Why not just use the ABS Function
update ev98nv_tm
set mome=ABS(b)
where tm.tr=tr and tm.ra=ra
and tm.ke=ke
and tm.moti=moti ;
12.4.2. Mathematical Functions
All mathematical functions return NULL in the event of an error.
ABS(X)
Returns the absolute value of X.
Why not just use the ABS Function
update ev98nv_tm
set mome=ABS(b)
where tm.tr=tr and tm.ra=ra
and tm.ke=ke
and tm.moti=moti ;
12.4.2. Mathematical Functions
All mathematical functions return NULL in the event of an error.
ABS(X)
Returns the absolute value of X.
Try this
SELECT replace(left(history.time_sec,7),'-','') AS month
FROM history
WHERE history.account_id = 216
GROUP BY month
ORDER BY history.time_sec DESC;
This is what I get on 1 of my tables with no index on perm_user_ID , 80,000
rows in the table and 7,000 rows where perm_user_ID =
Generally MySQL does format conversion for you to match data types.
You can also force the CONCAT result to be integer by the following
where concat(year,period,week) + 0 2007031
^^^
-Original Message-
From: Jay Paulson (CE CEN) [mailto:[EMAIL PROTECTED]
I have 2 almost identical SQL statements {copied except 1 is a LEFT join and
the other is an INNER join}.
The INNER join gives me values for all of the fields. The LEFT join gives me
NULL's for all of the prec_... {the LEFT join table} fields.
If the INNER JOIN gives me values and not an empty
The following takes a little effort, but it should get you close to dump
file size.
On 5.0.x you can use Information_schema.columns to get average row
length for MyISAM tables. Take that plus the punctutation {~35 + 3* #
cols for insert per row if you enclose your columns in 's} in the
insert
This is kind of ugly, but with the multitable limitations of 3.23 it
probably works.
CREATE TABLE TEMP
SELECT table_1 columns except age, table_2 age
FROM table_1
INNER JOIN table_2
USING (name);
TRUNCATE table_1;
INSERT INTO table_1
SELECT *
FROM TEMP;
DROP TABLE_1;
Sorry the DROP TABLE Should be TEMP not table1
-Original Message-
From: Gordon Bruce [mailto:[EMAIL PROTECTED]
Sent: Friday, December 09, 2005 3:51 PM
To: Sachin Bhugra; mysql@lists.mysql.com
Cc: [EMAIL PROTECTED]
Subject: RE: copying data!!!
This is kind of ugly
What is the source of the data that is displayed on the screen. If it is
a field in a MySQL table and the data type for that field is either DATE
or DATETIME then it will intsert/update without any manipulation.
Try doing a
SELECT datefield
FROM table
Limit 15;
outside of your ASP.NET
Try this
{I wasn't sure whether cd_nature_ltr is in ligne_trans
or transaction. This assumes ligne_trans. If it is in
transaction thatn move cd_nature_ltrsn = 2 into the ON clause.}
SELECT CASE id_ltrsn
WHEN NULL THEN 0
ELSE id_ltsrn
END AS id_ltrsn,
CASE
Is it possible to change the geocodes table to look like
CREATE TABLE `geocodes` (
`ip` int(10) unsigned zerofill NOT NULL default '00',
`lat` double default NULL,
`lon` double default NULL,
PRIMARY KEY (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Then you could do
After reading one of the recent posts from Gobi [EMAIL PROTECTED]
I took his successful query and modified it for one of my tables. It indeed
produce the correct result, but in the process raised some questions.
1. Why do list_ID and acct_ID not have to be qualified with a table
name
If your MySQL server is a *nix system than table names are case
sensitive.
SELECT A.*,
CASE CounselorOnly
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS CO
FROM Activities A
ORDER BY Activity
I also just noticed, remove the CO = and add AS CO following the END
of
You might try UNION with the 1st statement pulling all products with
groupid = 0 and the 2nd pulling 1 product with groupid 1.
-Original Message-
From: John Nichel [mailto:[EMAIL PROTECTED]
Sent: Monday, October 10, 2005 3:31 PM
To: MySQL List
Subject: [SPAM] - Query help - Bayesian
IF you are on 5.0.x you can use INFORMATION_SCHEMA
21.1.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
http://dev.mysql.com/doc/mysql/en/key-column-usage-table.html
-Original Message-
From: Operator [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 05, 2005 3:02 PM
To:
Try this
mysql select distinct secname, date
- from optresult
- where secname like 'swap%'
-and date like '2005-09-2%'
- order by if(secname like 'swap%',
- (mid(secname,5,20)+0),
- secname);
+--++
| secname | date
What am I missing
INSERT INTO table1 (column names.)
SELECT VALUES..
FROM table2
WHERE primary id = insert value
You will have to put in your real table name and column names.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, September 23,
If you have the 5.0.x version of MySQL then INFROMATION SCHEMA can give
you what you want. i.e.
SELECT a.*, b.*
FROM INFORMATION_SCHEMA.COLUMNS AS a
INNER JOIN _SCHEMA.COLUMNS AS b
ON (a.column_name = b.column_name)
WHERE a.TABLE_NAME = 'foo_1'
AND b.TABLE_NAME =
It's getting late on Friday, but couldn't you build a table with all of
the parameter combinations and then just join against that table?
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, August 26, 2005 4:25 PM
To: mysql@lists.mysql.com
Subject: Union vs
I think you misunderstand how auto_increment works. Primary keys using
auto_increment are NOT row numbers.
If your table has a primary key that is an auto_increment field then
when you add a row to the table the value of the primary key of the new
row is 1 greater than the max(Value) before the
Do You know about INTERVAL?
Use it in an exprecssion or funtion as
..INTERVAL expr type
where expr is any numerical value
* The INTERVAL keyword and the type specifier are not case
sensitive.
The following table shows how the type
If you want to have all values except the primary key be the same and
say your is foo_ID
You can simply do
INSERT INTO foo
(foo_ID... {rest of columns list})
SELECT new primary key value,
{rest of columns list}
FROM foo
WHERE foo_ID = {primary key value of row you want to copy}
If
You can use INTERVAL i.e.
Lets say you have a table
mysql CREATE TABLE foo (bar int(14), fdate date );
Query OK, 0 rows affected (0.27 sec)
mysql INSERT INTO foo
VALUES (1, now()),
(25,now() - INTERVAL 1 DAY),
(15,now() - INTERVAL 2 DAY);
mysql SELECT f1.bar
If you are on a version prior to 4.1.2 the max index size is 500 bytes
{not sure why the error mentions 1024}
From section 14.1 of documention
The maximum key length is 1000 bytes (500 before MySQL 4.1.2). This can
be changed by recompiling. For the case of a key longer than 250 bytes,
a larger
Multi Table UPDATES are first supported in 4.0.x
-Original Message-
From: Brendan Gogarty [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 09, 2005 12:16 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: RE: JOIN QUERY - UPDATE ... help?!
Brendan Gogarty [EMAIL PROTECTED]
We have a relatively small web site running 4.0.20 on a Dell 2850
running RedHat.
We rarely see any SQL statements visible when we run SHOW PROCESSLIST
and typically use 30 - 40 concurrent connections.
The Number of SQL Queries graph in MySQL Administrator usually is in the
0 to 10 range
This is out of the MySQL class and is called the Max-Concat trick.
What is the biggest country in each continent?
The Rows Holding the Group-wise Maximum of a Certain Field
MySQL Select Continent,
-
Something like this
SELECT CompanyName,
WhatToShip,
SUM(IF(TrackingNumber = '',
IF(SerialNumber = '',
1,
0),
0)
) AS READY,
SUM(IF(TrackingNumber '',
IF(SerialNumber = '',
Here is one way.
Some time ago I set up a table named count with one field named count
and built 5000 rows of incrementing values .
I think I originally populated it by originally createing it with a 2nd
field
CREATE TABLE `count` (
`count` int(10) unsigned NOT NULL auto_increment,
A database in MySQL is simply a directory.
So just rename the directory with appropriate tool for your platform.
On my test box this becomes
mysql show databases;
++
| Database
|
++
| information_schema |
|
lois
|
| mailprint |
|
select count(distinct ordr_ID) from store
-Original Message-
From: Gana [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 07, 2005 3:01 PM
To: mysql@lists.mysql.com
Subject: Count(*)
select count(*) from store group by orederId.
For the above sql, I am not getting the count of unique
If you really need more than 20 digits of accuracy and can move to 5.0.3
+
you can use the Decimal data type without losing precision.
This is out of Chapter 23. Precision Math
The maximum value of 64 for M means that calculations on DECIMAL values
are accurate up to 64 digits. This limit of
When I have seen this error it was caused by a field defined in the
MySQL database as NOT NULL in the Create table and the value in Access
is NULL or usually for us an empty field in EXCEL which is appears to be
intreped as NULL when you do a PASTE APPEND.
Ours is often times a datetime field
If you are runing binary log and do a
FLUSH LOGS
mysqldump --opt --skip-lock-tables MyISAM table names
FLUSH LOGS
mysqldump --opt --single-transaction INNODB table names
You have a recoverable state with the combination of the mysqldump file
and the binary log file that was started by the 1st
RTFMs with links.
Gordon Bruce wrote:
If you are runing binary log and do a
FLUSH LOGS
mysqldump --opt --skip-lock-tables MyISAM table names
FLUSH LOGS
mysqldump --opt --single-transaction INNODB table names
You have a recoverable state with the combination of the mysqldump
file
If you have c values in the table currently you can just do an
ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','c','x')
DEFAULT a NOT NULL
then
UPDATE tablename SET columname = 'x' WHERE columname = 'c'
Then
ALTER TABLE tablename CHANGE columnname columnname
only a int(11) as unique key and the enum field..
suposing now i have enum(a, b) only, and did a ALTER TABLE
tablename CHANGE columnname columnname ENUM('a','b','c');
thanks,
Gabriel
2005/6/16, Gordon Bruce [EMAIL PROTECTED]:
If you have c values in the table currently you can just do
In some hierarchies I have seen people put the the current id in the
parent_ID Field {basicaly pointing to them self} to represent the top of
the hierarchy.
I don't know how much this would affect the rest of your application but
it would get rid of the null's
-Original Message-
From:
It always helpd me to change MySQL's user to connection in my head
when I begin to think about access control. Then in most database
designs that I have seen, row access control is just as important as
database/table/column. Then the question becomes does the user have
direct access to the
I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows and they
both seem to look fine {see the SHOW CREATE TABLE's following the CREATE
TABLE statements}
RUN ON 4.0.20
mysql CREATE TABLE ID (
- mat INT UNIQUE PRIMARY KEY,
- ID_firstname CHAR(35) DEFAULT 'filler',
-
If you are on 5.0.n there is an INFORMATION_SCHEMA which you can query
like this. A casual scan of the mysql tables don't show any sizes and I
don't know of a way to get table/database size via SQL.
mysql select table_schema, sum(DATA_LENGTH) from
information_schema.tables group by 1;
You can have any number of timestamp columns, but only one of them can
be set to autoupdate. As of 4.1 you are not limited to this being the
1st one in the table and CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), or
NOW() can be used in the DEFAULT. Read
You can try
if($queryID = mysql_query(SELECT *
FROM WhInventory
WHERE Booking like ('%15%')
ORDER BY Booking,$dbLink))
-Original Message-
From: Don [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June
56 matches
Mail list logo
