You should use a simpl data path and create a separate tablespace for each
InnoDB file
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table
This way, ibdata1 only contains the metadata and MVCC control data for all
InnoDB files and transactions
Awhile back, you ran a query to get
nine [mailto:an...@oire.org]
Sent: Monday, February 14, 2011 3:40 PM
To: Rolando Edwards
Cc: João Cândido de Souza Neto; mysql@lists.mysql.com
Subject: Re: Insert only if the entry doesn't exist
Hello Rolando,
Sorry, but if I do INSERT IGNORE INTO, then I must indicate a key
(typically a unique
Be Careful. REPLACE INTO mechanically does DELETE and INSERT under mysqld's
hood.
If you want to do nothing if row exists already then do:
INSERT IGNORE instead of REPLACE INTO
Rolando A. Edwards
MySQL DBA (SCMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
2
I wrote an article in www.stackoverflow.com about how to convert absolutely
every InnoDB table to .ibd and permanently shrink the ibdata1 file
http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261
Enjoy !!!
Rolando A. Edwards
MySQL DBA (SCMDBA)
1
Do you have innodb_file_per_table turned on ???
If this is off, then all your InnoDB data is going in /var/lib/mysql/ibdata1
You actually need to the following to recover all free space from all InnoDB
tables
I commented on this in
http://stackoverflow.com/questions/3927690/howto-clean-a-mysql
SELECT name, city, state, phone, prods_done, cancels, miles FROM
(SELECT name, city, state, phone, prods_done, cancels, miles, ((prod_done -
cancels) * 100 / prod_done) reliability
FROM volunteer_search WHERE project_id = 5653) A
ORDER BY reliability DESC, miles ASC
Give it a try !!!
Rolando A.
innodb_flush_log_at_trx_commit is the parameter to tweek
The following is an excerpt from "MySQL 5.0 Certification Study Guide" (ISBN
0-672-32812-7)
http://www.amazon.com/MySQL-5-0-Certification-Study-Guide/dp/0672328127/ref=sr_1_1?ie=UTF8&qid=1296851778&sr=8-1
Chapter 29, Pages 429,430
InnoDB t
Remember, the InnoDB table has a full table lock now since it is doing and
ALTER TABLE.
You may want to kill it and try this instead:
In this example, the table with 12M rows is called BigTable
1) CREATE TABLE BigTable2 LIKE BigTable;
2) ALTER TABLE BigTable MODIFY COLUMN VARCHAR();
3) INSERT I
In reality, you better off manipulating the MyISAM tables via SQL.
However, if you really are that adventurous about low-level reads of MyISAM
tables, I have a crazy suggestion for you !!!
The old MySQL book used as reference to MySQL 3.23/4.0 describes manipulating a
MyISAM table with an API t
Since that table is a MyISAM table, you must do the following:
01. In mysql, CREATE DATABASE IF NOT EXISTS mystuff;
02. service mysql stop
03. cd /var/lib/mysql/mysql
04. mv general_log.* ../mystuff
05. comment out general log from my.cnf
06. service mysql start
07. In mysql, DELETE FROM mystuff.g
To verify that root has a password, do the following:
1) service mysql restart --skip-grant-tables
2) In MySQL, SELECT CONCAT(,user,'''@''',host,) mysql_user,password
from mysql.user where user='root';
This will show every host that root can login as along with the PASSWORD
function-encr
MySQL, by design, cannot do that.
A DB Server can be Master to Multiple Slaves
Think of the CHANGE MASTER TO command.
Its internal design cannot accommodate reading from more than one set of relay
logs.
You could attempt something convoluted, like
1) STOP SLAVE;
2) CHANGE MASTER TO
3) START SLA
anks.
Rolando Edwards wrote:
> I ran these commands:
>
> use test
> DROP TABLE IF EXISTS mydata;
> CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1
> CHAR(1),column2 CHAR(2));
> INSERT INTO mydata (column1,column2) VALUES ('a','z')
I ran these commands:
use test
DROP TABLE IF EXISTS mydata;
CREATE TABLE mydata (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,column1
CHAR(1),column2 CHAR(2));
INSERT INTO mydata (column1,column2) VALUES ('a','z'), ('b','y'), ('c','x'),
('d','w'), ('e','v');
SELECT * FROM mydata;
UPDATE mydata A L
Install the mysql client on the web server and run the same mysqldump command
from within the web server.
Give it a try !!!
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redw
Information_schema is a read-only memory database.
When you create'MySqlMonitor'@'localhost', you will still see the
information_schema.
However, the information_schema will only contain table info
'MySqlMonitor'@'localhost' can access.
Information_schema behaves more like a restricted view of
I think this is normal because stored procedures live in mysql.proc.
You would have to filter out mysql.proc by adding this to /etc/my.cnf
replicate-ignore-table=mysql.proc
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-66
ards
-Original Message-
From: Rolando Edwards [mailto:redwa...@logicworks.net]
Sent: Thursday, May 27, 2010 12:39 PM
To: Carlos Mennens; MySQL
Subject: RE: Verify User Privileges
SHOW GRANTS FOR 'carlos'@'localhost';
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenu
SHOW GRANTS FOR 'carlos'@'localhost';
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards
-Original Message---
DELETE FROM mysql.user WHERE user='';
FLUSH PRIVILEGES;
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards
-O
If you have a lot of InnoDB transactions, maybe your ib_logfile0 and
ib_logfile1 are too small.
Transactional data are written in these files in a circular fashion. You may
have run out of space in these.
If you want to make these files bigger, simply do the following:
1) Use the setting "inno
SELECT * FROM `table_anw` WHERE DAYOFWEEK(datum) = 3;
SELECT * FROM `table_anw` WHERE WEEKDAY(datum) = 2;
SELECT * FROM `table_anw` WHERE DAYNAME(datum) = 'Tuesday';
SELECT * FROM `table_anw` WHERE DATE_FORMAT(datum,'%W') = 'Tuesday';
SELECT * FROM `table_anw` WHERE DATE_FORMAT(datum,'%a') = 'Tue'
If your table testtab is populated, neither suggestion is efficient.
You could the following instead:
#
# Create an empty table `testtab_copy`
#
1) CREATE TABLE testtab_copy LIKE testtab;
2) Do either of you suggestions:
ALTER TABLE testtab ADD COLUMN c_col char(4) FIRST,ADD COLUMN b_col char(
If you have stored procedures, don't forget to update the db column with the
new db in mysql.proc as that does not automatically change.
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLo
I have good news and bad news for you when it comes to MySQL 5.x.
Good News if you are counting against MyISAM
Bad News if you are counting against InnoDB
Good News
-
For MyISAM
Just use either
SELECT table_rows FROM information_schema.tables WHERE table_schema='' and table_name='';
Or
SE
ate < B.DT2
group by week(A.mydate);
Give this one a Try !!!
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwa...@logicworks.net
-Original Message-----
From: Rolan
SELECT DT DT1,DATE_ADD(DT,INTERVAL 1 WEEK) DT2 FROM (SELECT
DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR
HOUR) DT FROM (SELECT 4 DOW,9 HR) AA) A;
This query will produce the previous Wed at 9AM to the next Wed 9AM.
Run it in the MySQL Client and note the output:
If you do not want to use InnoDB at all, add this line to /etc/my.cnf under the
[mysqld] section
skip_innodb
Then do the following
service mysql stop
rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile[01]
service mysql start
This will totally and cleanly disable InnoDB on the DB server
Give
A significant gain you have with innodb_file_per_table is that of shrinking the
tablespaces.
You can do that with "OPTIMIZE TABLE ;"
You ibdata1 file should only contain metadata and some transaction logging info.
If your ibdata1 is gigantic, you have to do the following to shrink it:
01) In my
Do 'service mysql restart --skip-networking'
This prevents all TCP/IP connections
You can login as r...@localhost and the client program will use the socket file
rather than TCP/IP.
Do all your DDL work.
When done, 'service mysql restart'
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the
T_ATON(NEW.Value));
ENDIF (<<-- Missing Semicolon)
END;
DELIMITER ;
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwa...@logicworks.net
-Original Messa
DELIMITER $$
CREATE TRIGGER bgp.tglobal BEFORE UPDATE on bgp.global
FOR EACH ROW BEGIN
IF STRCMP(NEW.Variable_name,'ASN') != 0 THEN
set NEW.Variable_name=NULL;
ELSEIF STRCMP(NEW.Variable_name, 'RouterId') != 0 THEN
set NEW.Variable_name=NULL (<<-- Missing Semicolon)
ELSEIF STRCMP
Turn On General Logging
In my.ini insert this option in the [mysqld] section
log=C:\MySQLLogging.txt
and restart mysql
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM : RolandoLogicWorx
Skype : RolandoL
The DROP TRIGGER command is in the wrong place. You changed the delimiter to $$
but still tried to use the semicolon(;) with DROP TRIGGER.
Your code should read like this:
DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos;
DELIMITER $$
CREATE TRIGGER cfe_tg_calcular_consumos
AFTER I
Check to make sure the binary log index is not out-of-sync.
Here is how you can do this:
Run 'SHOW BINARY LOGS;'
Look to see if any of the entries reports a zero length in it or includes a
binary log that does not exist.
Every time you shutdown mysqld, mysqld writes what it has in its memory ra
Make sure the tables that the information_schema are not locked.
This is because MyISAM tables, that are constantly being inserted into (Each
insert, update, delete on a MyISAM table does a table lock), must update the
TABLE_ROWS column in information_schema.tables to have the latest count.
If
The table mysql.proc always contains the hard code of stored procedures and
functions.
Triggers in 5.0 are stored in a .TRG file
Make sure, if you used mysqldump, to include --triggers as a dump option
Rolando A. Edwards
MySQL DBA (CMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 100
Ever since MySQL 5.0.27, I have never been able to install the MySQL Service
either.
I think this has something to do with the way Microsoft compiles its OS
Services.
I use the "no installer" version now.
1. Go to a DOS Window and create a directory called C:\ MySQL_5.1.32
2. Click Start, Run,
Have you tried disabling indexes while loading?
Here is what I mean...
CREATE TABLE tb1 (A INT NOT NULL AUTO INCREMENT PRIMARY KEY,B VARCHAR(20),C
VARCHAR(10));
Load tb1 with data
Create a new table, tb2, with new structure (indexing B and C columns)
CREATE TABLE tb2 LIKE tb1;
ALTER TABLE tb2 A
In the topology you just illustrated, you need to be specific about your scheme
using arrows. Here are some examples:
==
Example 1: This is MultiMaster Replication among 4 servers
Master1--->Master2
^ |
|
Actually, that will not reduce the size of the ibdata1 file at all.
Here is a GUARANTEED WAY to shrink that ibdata1 file, which YOU WILL NOW HAVE
TO REPEAT EVER AGAIN !!!
1. Perform mysqldump of all databases, routines and triggers from the mysql
server to /root/MyData.sql
2. Drop all databases
This one was complicated but here is what you want and the three different sets
of test data to prove it (cut and paste this code as is into MySQL and see the
desired results):
USE test
DROP TABLE IF EXISTS SCHOOL;
CREATE TABLE SCHOOL (study VARCHAR(10),symbol CHAR(1), KEY StudySymbolIndex
(stu
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='';
RESET SLAVE only clears away relay logs.
CHANGE MASTER TO MASTER_HOST='' will delete the master.info
Replication is eliminated from that point going forward
-Original Message-
From: Claudio Nanni [mailto:[EMAIL PROTECTED]
Sent:
Try mysqldump !!!
On this web page, http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
It says the following:
--fields-terminated-by=..., --fields-enclosed-by=...,
--fields-optionally-enclosed-by=..., --fields-escaped-by=...
These options are used with the -T option and have the same meani
At the Linux prompt, enter this : cat mysql-bin.index
Make sure every binlog is in the mysql-bin.index. If the list is incorrect,
then the expire logs feature won't work.
To test this out, try using PURGE MASTER LOGS TO ' mysql-bin.55';
If this doesn't work, this verifies that the contents
You are better off running it this way:
create table t1 (x int);
create table t2 (x int);
create table t3 (x int);
create table t_merge (x int) engine=merge union=(t1,t2,t3);
drop table t1;
drop table t_merge;
create table t_merge (x int) engine=merge union=(t2,t3);
-Original Message-
h during a mysqldump.
From: Mark Maunder [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 05, 2008 12:17 PM
To: Rolando Edwards
Cc: mysql@lists.mysql.com
Subject: Re: Possible bug in mysqldump?
Thanks for the reply Rolando.
In both the examples I provided (pipe and t
When you use --master-data=1, it executes the CHANGE MASTER command first
before adding data.
Do the following to verify this:
Run 'mysqldump --single-transaction --master-data=1 -h... -u... -p... >
DataDump1.sql
Run 'mysqldump --single-transaction --master-data=2 -h... -u... -p... >
DataDump2
Yes and No !!!
I say no because temp table are visible neither in the INFORMATION_SCHEMA nor
in "SHOW TABLES".
I say yes because you can do "show create table \G" or "describe
;" to a temp table even if you cannot see it.
So the answer is YES !!!
-Original Message-
From: Jay Blanchard
Please change your password right away !!!
It's in the message below !!!
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, July 11, 2008 2:53 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: FW: MySQL License
Hi,
This is re
Try your query with either back quotes around Company
SELECT * FROM Contacts WHERE Categories="Services" and BusinessCodes REGEXP
"^R" and gold_id="2" ORDER BY `Company` ASC
Or no quotes around Company
SELECT * FROM Contacts WHERE Categories="Services" and BusinessCodes REGEXP
"^R" and gold_id="
SELECT events_groups_id, events_groups_name FROM events_groups
ORDER BY IF(events_groups_id=1,0,1),events_groups_name ASC;
-Original Message-
From: Andrew Martin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 01, 2008 9:20 AM
To: mysql@lists.mysql.com
Subject: force row to appear at top of
My first impression is to say: "Sounds like the InnoDB internal data dictionary
still has the table recorded somewhere." According to Page 566 Paragraph 3 of
"MySQL Administrator's Guide and Language Reference" (ISBN 0-672-32870-4),
"InnoDB always needs the shared tablespace because it puts its
If you are using MySQL 5.0 or later, use the INFORMATION_SCHEMA database.
It has an in-memory table of table names called (as you would expect) 'tables'.
SELECT table_rows,table_name FROM information_schema.tables
WHERE table_schema = '';
If you are using a current database then do this:
SELECT
LOAD DATA LOCAL INFILE
http://dev.mysql.com/doc/refman/5.0/en/load-data.html
http://www.mysql.com/news-and-events/newsletter/2002-05/a12.html
-Original Message-
From: Velen [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 22, 2008 2:24 PM
To: mysql@lists.mysql.com
Subject: Load d
No. You need to use MySQL 5.1 as that is now a standard feature.
Or, if all your functionality is 100% MySQL, just run a stored procedure in an
infinite loop and check every 60 seconds for
DELIMITER $$
DROP PROCEDURE IF EXISTS `rolando`.`runjob` $$
CREATE PROCEDURE `rolando`.`runjob` (scheduled
There is a huge difference !!!
When You Load a Table with a Primary Key, the Primary get built automatically.
Not even ALTER TABLE DISABLE KEYS has an effect since it disables
non-unique indexes. Hence, loading the table is a one-pass operation.
In contrast, loading a table with two non-uniqu
If you just created the /data/mysql folder and moved the ib* files to that
folder from /var/lib/mysql, you may have to contend with the internal data
dictionary with ibdata1.
You do the following:
1) Put the mysql data back into /var/lib/mysql
2) Configure my.cnf to set datadir=/var/lib/mysql
3
In this case, the command for the second suggestion is
gzip -d < slavesetup.sql.gz | mysql -u --password=
-Original Message-
From: Eramo, Mark [mailto:[EMAIL PROTECTED]
Sent: Monday, May 05, 2008 3:40 PM
To: Mysql
Cc: Kieran Kelleher
Subject: RE: Import of a mysldump file fails
Hi Kier
You are better off with an UPDATE JOIN
UPDATE pdata A,pdata B
SET A.pvalue = B.pvalue
WHERE A.pentrytime = 117540
AND B.pentrytime = 1207022400;
Give it a try !!!
-Original Message-
From: Albert E. Whale [mailto:[EMAIL PROTECTED]
Sent: Friday, May 02, 2008 4:06 PM
To: mysql@lists.m
Use --skip-extended-insert as another mysqldump option
-Original Message-
From: dr_pompeii [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 20, 2008 2:43 PM
To: mysql@lists.mysql.com
Subject: doubt: mysqldump in linux like windows
Hello guys
i have this situation
in widnows with the mysq
> AllGrants.sql
AllGrants.sql will have all GRANTS but each line has no semicolon at the end
Just append a semicolon at the end of every line like this:
sed -i 's/$/;/' AllGrants.sql
Give it a try !!!
-Original Message-
From: Tim McDaniel [mailto:[EMAIL PROTECTED]
Sent: Wed
Yes !!!
mysqldump -h... -u... -p... mysql > MySQLSchema.sql
Give it a try !!!
-Original Message-
From: Tim McDaniel [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 19, 2008 2:17 PM
Cc: mysql@lists.mysql.com
Subject: RE: mysql privileges
On Wed, 19 Mar 2008, Rolando Edwards <
Yes you will have all the GRANTS for every user sitting in the 'mysql' schema
(from mysq.user) if you use the --all-databases option of mysqldump.
Here is something radical if you want to record the grants yourself:
Run the following query
SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''
Yes. Just use --all-databases as mysqldump option and the 'mysql' schema is
included.
-Original Message-
From: Brown, Charles [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 19, 2008 12:15 PM
To: mysql@lists.mysql.com
Subject: Are user privileges included in MYSQLDUMP
Using mysqldump, I
What is your default storage ?
Do this : SHOW ENGINES;
You should see something like this:
mysql> show engines;
++-++
| Engine | Support | Comment
|
+--
You can take this a step further
nohup mysqldump -h -u -p
--all-databases --routines --triggers | mysql -h -A
This will pipe all the data directly to Windows machine without an intermittent
file. Even if you logout of Linux, it should keep going
-Original Message-
From: Daniel Brown [m
If you are calling this stored procedure from PHP or Java, then you can fetch
each number one at a time from the result set.
-Original Message-
From: Rolando Edwards [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 06, 2008 12:43 PM
To: Paul DuBois; puntapari; mysql@lists.mysql.com
I have a more masochistic way to do this without a table
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`sp_NumberList` $$
CREATE PROCEDURE `test`.`sp_NumberList` (LastNumber INT)
BEGIN
DECLARE j INT;
DECLARE SQLPiece TEXT;
DECLARE SQLStmt TEXT;
SET j = 0;
SET SQLStmt = 'SELECT 0
GRANT [ALL PRIVILEGES|Appropriate Privileges] *.* TO root@'192.168.1.50'
IDENTIFIED BY '' WITH GRANT OPTION;
Or if you want a root user from a subnet
GRANT [ALL PRIVILEGES|Appropriate Privileges]ON *.* TO root@'192.168.1.%'
IDENTIFIED BY '' WITH GRANT OPTION;
http://dev.mysql.com/doc/refman/5.
Dynamic SQL would work for Imbedding String for the IN clause.
That would be too messy for such a little query.
Here is a crazy suggestion
CREATE PROCEDURE additems ()
BEGIN
DECLARE grammatures VARCHAR(128) DEFAULT '7,12,1,13,2,5,14,21';
DECLARE newids VARCHAR(128);
SELECT GROU
What you need is Dynamic SQL via the PREPARE statement
Like This :
CREATE PROCEDURE `myDB`.`sp_test` (myTable varchar(20))
BEGIN
DECLARE SQLCommand VARCHAR(1024);
SET SQLCommand = CONCAT('SELECT * FROM ',myTable);
SET @SQLStmt = SQLCommand;
PREPARE s1 FROM @SQLStmt;
EXECUTE s1;
DEALLOCATE
Original Message-
From: Rolando Edwards [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 22, 2008 10:23 AM
To: David Ruggles; 'mysql'
Subject: RE: Packing list sort
Try One of These:
SELECT name,species,birth FROM animalsORDER BY
IF(species='hamster',0,1),species;
OR
Try One of These:
SELECT name,species,birth FROM animalsORDER BY
IF(species='hamster',0,1),species;
OR
SELECT name,species,birth FROM
(SELECT name,species,birth,IF(species='hamster',0,1) sortorder
FROM animals) A ORDER BY sortorder,species;
-Original Message-
From: David Ruggles [mail
uary 17, 2008 3:04 AM
To: Rolando Edwards
Cc: MySql
Subject: Re: Innodb gets disabled
I did the same as you have written, but innodb storage engine is not available
now. Even the skip-innodb is commented in my.cnf
On Jan 16, 2008 9:43 PM, Rolando Edwards < [EMAIL PROTECTED]<mailto:[EMAIL
1) 'mysqldump' all databases to an SQL file
2) Drop all databases
3) Shutdown mysqld
4) Delete the ibdata1, ib_logfile0, ib_logfile1
5) Add innodb_file_per_table to my.cnf (which you already did)
6) Make sure you gave this setting in [mysqld] group of my.cnf
innodb_data_file_path=ibdata1:10M:autoe
ORDER is a reserved word in standard SQL
Example : SELECT FirstName,LastName FROM Names ORDER BY LastName,FirstName;
If the column name in the table is ORDER, then put backquotes (`) around the
word ORDER when using it as a column name
INSERT INTO sections (`order`,edit,remove,section,type) VA
The CSV engine requires that every field be surrounded by double quotes.
If your text has double quotes, escape them or change them.
If you don't have that kind of time...
You may want to do this the weird way using Microsoft Access.
1. Install MyODBC (Takes like 30 seconds or less)
2.
The variable 'group_concat_max_len' has a default of 1024 (1K)
Add this to you're my.cnf to make it 8K
[mysqld]
group_concat_max_len=8192
--
Another way without altering 'group_concat_max_len' is
To manually concatenate the string pieces with blanks in between
The only limit is
Not in MySQL 5.0. There is no error trapping mechanism to escape triggers as
currently implemented. You are better off writing a stored procedure to do the
INSERTs and have your application call the stored procedure.
-Original Message-
From: Ed Reed [mailto:[EMAIL PROTECTED]
Sent: Thursd
You could convert all the tables on the slave to MyISAM after all the table
creates are done on the slave. Here are the six(6) steps to do this:
Step 1 : Make sure binary logging is turned off on the slave
Step 2: Copy the data over to the slave
mysqldump -h -u... -p... --routines --triggers |
Today, I installed 5.1.22RC and 6.0.3-alpha on my PC
I used 5.0.45's client successfully to connect to both.
I don't about 5.0.14
Give it a try !!!
-Original Message-
From: Jeffrey M. Johnson [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 05, 2007 4:36 PM
To: mysql@lists.mysql.com
S
'errno 32 on write'
That's a broken pipe error (Run 'perror 32' in Linux)
Maybe too much data is being pumped in through the pipe.
Instead of this :
/usr/bin/mysqldump --all-databases -u root | /usr/bin/gzip -c
/backups/mysql_backup.gz
Try it this way :
/usr/bin/mysqldump --all-databases -u roo
You may want to check to see if the index exists first.
Just query the table INFORMATION_SCHEMA.STATISTICS:
SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = ''
AND table_name = ''
AND index_name = '';
This returns the number of columns the index contains.
If this query ret
Further reason DELETE FROM on a Merge Table is so inefficient is this:
DELETE FROM tbl without a WHERE clause on a MyISAM Table is mapped internally
to TRUNCATE TABLE, which would be instatntaneous
DELETE FROM tbl WHERE 1=1 would clearly make even a MyISAM table terribly
ineffeicient as it mus
Did you remember to switch your SQL delimiter ?
MySQL default delimter for SQL is the semicolon ( ; )
MySQL's stored procedure language also uses the semicolon to delimter
statements.
These two rules cannot peacefully coexist.
You can get around this in three steps:
1) Change
Try this:
mysql> SELECT LENGTH('0 0 0 1 0 1 0 25 7 0 139 0 9') - LENGTH(REPLACE('0 0 0 1
0 1 0 25 7 0 139 0 9',' ','')) + 1;
+-+
| LENGTH('0 0 0 1 0 1 0 25 7 0 139 0 9') - LENGTH(REPLACE('0 0 0 1 0
SELECTs do lock the tables implicitly.
According to Page 400 (Section 28.1 : Locking Concepts) of MySQL 5.0
Certification Study Guide (ISBN 0-672-32812-7), here is what the first
bulletpoint says under the heading "A lock on data can be acquired implicitly
or explicitly":
For a client that doe
Think about how your going to make backups.
1) Would you backup one database with all the mailing lists together ?
2) Would you keep the backups of each user separate ?
3) Could users ask you to restore mailing lists from the past ?
You could make one mysqldump for everybody from one database if
You may want to consider tuning the 'his-priority' server instance by giving it
larger
values for the following:
temp_table_size
query_cache_size
query_cache_limit
Ben Clewett <[EMAIL PROTECTED]> wrote:
Sorry if this has been asked many times before...
I have 5 MySql systems running
ndo on this.
Rolando,
Although I agree with you, I was only trying to answer Terry's question :)
Cheers,
Jay
Rolando Edwards wrote:
> You must be very careful when granting permissions on every database this way.
>
> Here is why:
>
> By giving a user permissions on all data
You must be very careful when granting permissions on every database this way.
Here is why:
By giving a user permissions on all databases this way,
you also give away permissions to the 'mysql' schema.
This is where the grant tables live.
A person could
1) insert new users into mysql.user like t
select distinct email_address
from people P,registered_products A,registered_products B
where P.person_id = A.person_id
and A.product_type = "Product A"
and P.person_id = B.person_id
and B.product_type = "Product B"
;
Give it a try !!!
- Original Message -
From: "Benjamin Ventura"
c |
| 2 | d |
+--+--+
4 rows in set (0.00 sec)
- Original Message -
From: "Rolando Edwards" <[EMAIL PROTECTED]>
To: "Stefan Kuhn" <[EMAIL PROTECTED]>
Cc: "MySQL"
Sent: Monday, July 16, 2007 9:44:48 AM (GMT-0500) America/New_York
Su
drop table if exists test.flipdata;
create table test.flipdata (f1 int,f2 char(1),f3 char(1));
insert into test.flipdata values (1,'a','b'),(2,'c','d');
create view test.RotatedData as select f1,f2 from test.flipdata union select
f1,f3 from test.flipdata order by 1;
select * from test.RotatedData;
When the mysqldump ran against all databases, the USE command should
have appeared above each section of that databases dump. Try using Perl or the
head or tail Unix command to hunt down the Database you are dumping. Read all
lines until the next USE command.
Example: If you mysqldumped datab
Are you using MySQL Administrator for Windows ?
Are you using it against MySQL running in Linux ?
If so, you may prefer MySQL Administrator for Linux.
Just a couple more thoughts ...
- Original Message -
From: "cfaust-dougot" <[EMAIL PROTECTED]>
To: "Weiqi Wang" <[EMAIL PROTECTED]>, mysq
Run
explain select * from A,B where A.col1=B.col1;
The explain plan for your query will tell you what indexes are chosen.
If your explain plan says what you do not like, definitely add an index on col1
in B.
Make sure you run OPTIMIZE TABLE on both tables.
The, run explain select * from A,B wh
What was the last release of MySQL 5.0.x that supported Cluster ???
- Original Message -
From: "Jimmy Guerrero" <[EMAIL PROTECTED]>
To: "C K" <[EMAIL PROTECTED]>
Cc: mysql@lists.mysql.com
Sent: Wednesday, June 20, 2007 5:43:28 PM (GMT-0500) America/New_York
Subject: Re: MySQL cluster for w
Login to mysql
Perform this on the table
ALTER TABLE tbl-name MODIFY COLUMN Rating varchar(10);
Then recreate the ODBC link in OpenOffice
Give it a try.
Colleen Beamer <[EMAIL PROTECTED]> wrote:
Hi,
I apologize for posting here, because I only use MySQL at a very basic
level
1 - 100 of 182 matches
Mail list logo
