From looking at the MySQL 5 docs, it doesn't look like there is any way
to select a column with a regular expression applied to it?
I have a column that has ended up with some non ascii characters in it,
probably vertical tabs and things like that from MS Excel. I need to
sort by this field
the non-ascii characters removed, for example.
-Ryan
Darryle Steplight wrote:
Hi Ryan,
MySql does have regular expressions. See Link
http://dev.mysql.com/doc/refman/5.0/en/regexp.html
On Thu, Sep 11, 2008 at 9:52 AM, Ryan Stille [EMAIL PROTECTED] wrote:
From looking at the MySQL 5 docs
I'm trying to delete some orphaned records from a table that has about
150K records. Here is my delete:
|CREATE TEMPORARY TABLE deleteids AS (
SELECT subTable.ID from subTable
LEFT OUTER JOIN parentTable ON subTable.ID = parentTable.ID
WHERE parentTable.ID IS NULL
);
DELETE FROM subTable
Also the table seems to be locked while running this delete... thats not
going to be very good when I need to run it on production. Is there a
way to have it not be locked during this delete?
I'm thinking of creating a script to delete in 10 row increments until
they are all gone.
Thanks for the help, I rewrote it as a subselect and it deleted all 10K
records in two seconds.
DELETE subTable FROM subTable LEFT OUTER JOIN parentTable ON
subTable.parentID = parentTable.ID WHERE parentTable.ID IS NULL
-Ryan
--
MySQL General Mailing List
For list archives:
Ryan Stille wrote:
Thanks for the help, I rewrote it as a subselect and it deleted all
10K records in two seconds.
DELETE subTable FROM subTable LEFT OUTER JOIN parentTable ON
subTable.parentID = parentTable.ID WHERE parentTable.ID IS NULL
-Ryan
Whoops, I meant that I rewrote it as a JOIN
This should do it. Save it in a file then run it like perl newfile.pl.
Save the output into a text file, then run it against your database.
$records = 100;
for ($i=1;$i=$records;$i++) {
print INSERT INTO MyTable (forumid, styleid, title, title_clean, .
description, description_clean, options)
I use a script like this:
#!/bin/bash
DATE=`date +%A`
DESTFILE=/home/mysql-backups/mysql-dump-$DATE
/usr/bin/mysqldump --skip-extended-insert -uroot -ppassword
mydatabase $DESTFILE.sql
/usr/bin/zip -qjTm $DESTFILE.zip $DESTFILE.sql
I end up with:
mysql-dump-Friday.zip
Perl would be my language of choice to do something like this.
-Ryan
Thufir wrote:
what would be the quickest, easiest way to import, for example, the rss feed
http://rss.gmane.org/gmane.comp.db.mysql.general into mysql?
what would be some different approaches, pls? I'm more interested in
Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Ryan Stille [mailto:[EMAIL PROTECTED]
Sent: Saturday, February 24, 2007 4:28 PM
To: mysql@lists.mysql.com
Subject: Re: MySQL Daylight Savings Time Patch - easy check
Ryan Stille wrote:
Paul DuBois
[EMAIL PROTECTED] wrote:
On Wed, February 28, 2007 14:10, Ryan Stille said:
I am on 4.1.20-1.
Maybe your OS isn't patched?
Try this: SELECT @@global.time_zone;
Won't help if you are on debian which is still on 4.0.
If you get back SYSTEM, then MySQL is looking to the OS for timezone
data
Whats wrong with using the --single-transaction switch for backing up
InnoDB tables? What does the Hot Backup product do that this doesn't?
Thanks,
-Ryan
Juan Eduardo Moreno wrote:
Ananda,
For Innodb the best is Innodb Hot Backup ( www.innodb.com (US$) )
For MyISAM you can use a simple
Ryan Stille wrote:
Paul DuBois wrote:
At 4:40 PM -0600 2/20/07, Ryan Stille wrote:
Is there an easy way to test to see if MySQL already has the proper
tables loaded?
-Ryan
Yes, reload them. :-) After that, they're current! ...
After digging around on the net for a while I found
|
+---+---+
Thx's
Mickalo
- Original Message - From: Ryan Stille [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Saturday, February 24, 2007 3:28 PM
Subject: Re: MySQL Daylight Savings Time Patch - easy check
Ryan Stille wrote:
Paul
Paul DuBois wrote:
At 4:40 PM -0600 2/20/07, Ryan Stille wrote:
Is there an easy way to test to see if MySQL already has the proper
tables loaded?
-Ryan
Yes, reload them. :-) After that, they're current! ...
My timezone tables appear to be empty. At least the time_zone_name
Ryan Stille wrote:
Paul DuBois wrote:
At 4:40 PM -0600 2/20/07, Ryan Stille wrote:
Is there an easy way to test to see if MySQL already has the proper
tables loaded?
-Ryan
Yes, reload them. :-) After that, they're current! ...
My timezone tables appear to be empty. At least
Is there an easy way to test to see if MySQL already has the proper
tables loaded?
-Ryan
Paul DuBois wrote:
At 4:17 PM -0600 2/20/07, Paul DuBois wrote:
At 4:36 PM -0500 2/20/07, Sun, Jennifer wrote:
Any answers for the question below ?
Is there a DST patch for MySql 4.0.20? Thanks.
boll wrote:
Hello-
I'm working with an unfamiliar application, trying to figure out where
my data is going.
Is it possible to form a query to select from all the columns in
several tables at once? Something equivalent to:
SELECT FROM * WHERE * = 'john smith'; If that's not possible I'd
C.R.Vegelin wrote:
Dear List,
I have a MySQL database (V5.0.x) and I need to make graphs.
Does anyone know about good utilities to make graphs ?
I would appreciate your expertise or links.
TIA, Cor
Perl with GD? MS Access/MyODBC could do it too. You may have to be
more specific.
Marten Lehmann wrote:
that is the sort of thing that I expected and was afraid of. Also, I
think the mysql documentation doesn't really point out that temporary
tables are always kept in memory. Couldn't mysql create them in
/tmp/proc-id.table-name instead?
Actually I was just reading through
I came up with a work around when we encountered this. I don't remember
exactly (and I don't have access to that code anymore), but I think we
manually put a piece of code in our SQL setup scripts, before any of our
insert statements. This 'mysql command' would set the next available ID
to
We have an intense data process that runs every few minutes, clearing
and then loading a database with thousands of records of data, which are
then queried on from a website. The periodic load takes about 20
seconds. Some of the front end select queries take a second or two.
This is all
Maybe I should just be looking at using a HEAP table?
-Ryan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
My current thinking is to break the entering of the property
info into 3 separate forms, the first with the mandatory info
does a insert to create the row with the remaining 100
columns being seeded with $t=''. Group 2 3 will them be a
update to the row using the 'set' keyword just touching
Are my emails not coming through? Or is this question way too 'newbie'
and no one wants to touch it?
-Ryan
Ryan Stille wrote:
I'm still hoping someone can else can share their input on this.
What do other people usually do as far as the collation setting?
Thanks,
-Ryan
Ryan Stille
I'm still hoping someone can else can share their input on this.
What do other people usually do as far as the collation setting?
Thanks,
-Ryan
Ryan Stille wrote:
When we migrated to MySQL from MS SQL, I left everything set
to the default as far as collations - latin1_swedish_ci
What else can I do to observe myslqd?
mytop may be of some use: http://jeremy.zawodny.com/mysql/mytop/
-Ryan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Yes, look at the PURGE LOGS command.
Rithish Saralaya wrote:
Hello.
Can I delete off the binary log files manually? I do not want
to 'RESET MASTER', as it will clear all the binary logs, and
that's not what I want to do. The database is backed up every
midnight, and I wouldn't want to keep
Shut it down with mysqladmin, from another machine using the -h (host)
option.
When you are using the CLI locally it connects through the socket, but
if you do it from another machine it will use TCP, getting around the
need for the missing socket file.
You may also be able to connect via TCP
When we migrated to MySQL from MS SQL, I left everything set to the
default as far as collations - latin1_swedish_ci. This was based on
digging through the manual and google. But now I am migrating the
application to a newer version of ColdFusion and am running into some
issues with charsets on
I am working with an existing compilcated query someone wrote years ago.
When I dump the data from the query to try to figure out why I'm getting
unexpected data, I have three fields named id. Is there anyway to
tell mysql to name the fields with the table name when they are
returned, so they
Put SET SQL_LOG_BIN=0 at the top of your dump file. That will turn
off logging just for your session.
-Ryan
-Original Message-
From: sheeri kritzer [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 02, 2006 11:56 AM
To: Rithish Saralaya
Cc: MySQL general mailing list
Subject: Re:
' column.
--
Rhino
- Original Message -
From: Ryan Stille [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, March 02, 2006 12:42 PM
Subject: Want mysql to return tablename.fieldname format
I am working with an existing compilcated query someone wrote
years ago.
When I
did u start mysql with --skip-name-resolve ???
Kishore Jalleda
Kim Christensen wrote:
On 2/16/06, Ryan Stille [EMAIL PROTECTED] wrote:
Nope.
Well, have you tried it? Did it solve your problem?
Sorry, I misunderstood. I thought Kishore was saying that if I had
started the server
I am trying to connect to my MySQL 4.x server from a new machine. I am
getting this error:
Got error: 1045: Access denied for user 'user'@'192.168.2.56' (using
password: YES) when trying to connect
BUT I have a user setup in mysql with allowed access from
'%.willcomminc.com'. The machine I'm
Nope.
_
From: Kishore Jalleda [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 16, 2006 4:32 PM
To: Ryan Stille
Cc: mysql@lists.mysql.com
Subject: Re: User is rejected because of IP, but hostname is
allowed
did u start
You can also install MyODBC and then hook an Excel spreadsheet into your
database. Editing the spreadsheet will update data in your database.
This isn't a good solution if you are going to be creating new tables
often. But for manipulating data in a known set of tables it's great.
-Ryan
--
Furthermore, would it make more sense to have the data dump locally,
and then use a script to move the contents of the dump to a machine
on the network, perhaps even to a machine located on an alternate
network accessed via a second ethernet card?
This would be the simplest approach. That is
I have a timestamp stored in a datetime field. It gets set using Now()
when the record was inserted. I would like to pull the data back out
and have it in UTC time. Is there an easy way to do this? I've been
through the manual and on google but haven't come up with anything.
This is in a PHP
I'm getting ready to do a roll out next week on converting an
application from MySQL to MSSQL. I'm installing the perl drivers on the
production machines today. The perl DBD driver requires mysql to be
installed. Everything's been going fine on all the machines until I got
to the last one. On
I thought I would get around the whole thing by just installing the RPM
instead of compiling. So I downloaded MySQL-devel-4.1.14-0.i386.rpm and
installed it. Then I went to install the DBD-mysql module and got this
error during the compile:
/usr/bin/ld: cannot find -lz
collect2: ld returned 1
I fixed the whole problem by typing this one line:
`ln -s libz.so.1.1.3 /usr/lib/libz.so`
Linux is such a pain sometimes.
-Ryan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I posted this on the ODBC list, but there is not much traffic there and
no one replied. Hopefully someone here has dealt with this.
I am setting up ColdFusion to access a MySQL 4.1.x database. This
required new MyODBC drivers to be installed on the system for
ColdFusion. After a lot of trial
I am converting some code from MSSQL to MySQL. In one place I need to
have a conditional query depending on if a table exists or not. There
are different versions of this application and the table only exists in
some of them. Here is how it was done in MSSQL:
IF OBJECT_ID('cfgbiz') IS NOT NULL
If 'SHOW COLUMNS FROM tablename' returns error 1146 (42S02), the
table doesn't exist.
This causes my application (ColdFusion) to throw an exception.
If I have to, I could resort to doing another query in my application
(SHOW TABLES) and seeing if my table was returned in that list. But I
was
Maybe you could use
SHOW TABLES LIKE 'your_table';
That's a great idea, I just tried it in several ways, like:
IF EXISTS (SHOW TABLES LIKE 'cfgbiz') THEN
SELECT siacnotifyto FROM cfgbiz
ELSE
SELECT '' as siacnotifyto
END IF;
-and-
select IF((SHOW TABLES LIKE
Josh Chamas wrote:
Ryan Stille wrote:
Has anyone ever used this MSSQL-to-MySQL converter? It's pretty
reasonable at $40, and the demo output I got looked pretty good. But
I wanted to see if there is anything I should be weary about.
http://www.convert-in.com/mss2sql.htm
I have done
OK, you confused me. How can 16 be bigger than 16? For character-based
fields, the number in the parentheses shows the length of the field,
does it not? The first part, the text or char or varchar or ...,
tells the engine what kind of data a field will contain.
How can TEXT(16) hold more
I am migrating from MS SQL Server to MySQL 4.1.
I have code in my ColdFusion/SQL Server application that went something
like this:
cfquery name=insertquery
SET NOCOUNT ON
INSERT INTO TABLE ()
SELECT @@IDENTITY AS adid
SET NOCOUNT OFF
/cfquery
That whole block was processed in coldfusion as a
it might be best to ask this in the cf forums, however did
you try to separate the statements with a ; ? I haven't tried
2 query statements for MySQL in cfquery...yet ;-) Also I
believe MySQL 3.23+ supports @@identity
Yes I've tried semicolons after the statements, and I've tried setting
I am trying to connect to MySQL from within ColdFusion on a linux box.
ColdFusion (version 5) came with a Merant driver, but it is too old to
support transactions.
I've downloaded libmyodbc_mysql.so (MyODBC 2.5) and also libmyodbc3.so
(MyODBC 3.5) and could not get either of these to work. But
Has anyone ever used this MSSQL-to-MySQL converter? It's pretty
reasonable at $40, and the demo output I got looked pretty good. But I
wanted to see if there is anything I should be weary about.
http://www.convert-in.com/mss2sql.htm
Thanks,
-Ryan
--
MySQL General Mailing List
For list
Carlos J Souza wrote:
Sirs,
When i use  mysqldump on Version 4.1.x, all tables had a one
record insert generated in script. When i use mysqldump on a
4.0.x version this problem does not occurs.
How to solve it?
Not 100% sure what you are looking for, but try using --skip-extended-insert.
I would really like to hear how some of you are handling backups on
high-availability servers. The DBA in my company is skeptical about
switching from MSSQL Server to MySQL, this is one of his reasons
(backups). If someone is making MySQL work in a high-availabity
environment, let's hear about
I'm trying to get a handle on MySQL backups and hot backups using MyISAM
and InnoDB tables together. We plan to switch from SQL Server to MySQL
soon.
How are you guys handling full-backups of databases with mixed MyISAM
and InnoDB tables? From what I've read (and I've been reading a lot),
if we
55 matches
Mail list logo
