Re: Changing a field's data in every record

[Brad Barnett]

Erm.

I've seen some weird responses to this.  Yes, you can do this.

First -- get the data into a usable format.  Then, put it into a usable
format (eg, timestamp for datetime field).

Read up on how mysql interprets date/time data on fields.  And, create a
new timestamp or date field.

Then, do something like this:

update table set timestamp_field=concat(
SUBSTRING_INDEX(bah,'|',1),"/",
SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-5),'|',1),"/",
SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-4),'|',1)," ",
SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-3),'|',1),":",
SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-2),'|',1),":",
SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-1),'|',1));

All the data will then be in that timestamp field or datatime column.  A
datetime column is very readable.

As others have mentioned (nicely, and not so nicely), you can easily
format the output of a timestamp or datetime as wanted.






 On Sat, 18 Feb 2017 13:13:38 -0800
debt  wrote:

>   I’ve been asked to post a question here for a friend.
> 
>   Is there a formula to change the format of the data in a single
> field in every record of a table?  She has a "timestamp” in a text
> field formatted as 2017|02|16|04|58|42 and she wants to convert it to a
> more human readable format like  2017-02-16 @ 04:58:42
> 
>   How does one "grab" the existing data and then change it?  Can
> this be done solely in MySQL, or will she have to grab the data and
> then manipulate it in PHP or something?
> 
> Thanks,
> Marc
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: io thread very slow copying binlogs

[Brad Barnett]

Hey Morgan,

Thanks for the tip.  Might come in handy.

But, I'm positive it's not a disconnect / reconnect thing.  Or, at least
not one affected by that timeout.

I can do a watch ls -lh in the binlog dir, and see the relay log
increasing in size by a M every 4 or 5 seconds or so.  About
200kbyte/sec / 1.6Mbit/sec right now.

It seems very steady too.  As in, if I look at bytes, they're constantly
increasing.. just, slow..

On Fri, 20 Jan 2017 10:19:57 -0500
"Morgan Tocker"  wrote:

> Hi Brad,
> 
> > MySQL community edition 5.6.29, running Linux.
> > 
> > Binlogs never seem to get caught up on slaves.
> > 
> > I've done all I can, to validate that this isn't network or disk
> > related.
> > 
> > Disk tests (using iostat and other methods) show lots of bandwidth
> > left on the slave and master.
> > 
> > Network tests, such as:
> > 
> > - using scp to copy binlogs directly
> > - using different NICs to copy binlogs
> > - using mysqlbinlog to snag logs (the most 'real' way I can think to
> >   simulate the replication thread copying binlogs from the master)
> > 
> > All seem to show that network speed is blazingly fast.
> > 
> > Yet, MySQL is barely getting 4mbit/sec across the network, and onto
> > the
> disk.
> > And that's on a good day.
> > 
> > Any immediate suggestions here?  This seems very weird, and SQL
> > thread is constantly running out of stuff to process.
> 
> Networking is not my strong-suit, but I have a suggestion:
> 
> Try lowering slave-net-timeout
> http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option
> _mysqld_slave-net-timeout
> 
> I remember that we lowered the default in MySQL 5.7 (from 1hr to 60
> seconds) so that the connection between master/slave would be
> considered broken faster.  If you have the throughput on a graph it
> might better explain if it is a constant 4mbit/sec or more broken.
> 
> 
> - Morgan

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: io thread very slow copying binlogs

[Brad Barnett]

I should add that I've turned the SQL thread off, it makes no difference
from what I can see...

On Fri, 20 Jan 2017 10:31:38 -0500
Brad Barnett <b...@l8r.net> wrote:

> 
> 
> 
> On Fri, 20 Jan 2017 10:30:09 -0500
> Brad Barnett <mysql-general-l...@l8r.net> wrote:
> 
> > 
> > 
> > Hey Morgan,
> > 
> > Thanks for the tip.  Might come in handy.
> > 
> > But, I'm positive it's not a disconnect / reconnect thing.  Or, at
> > least not one affected by that timeout.
> > 
> > I can do a watch ls -lh in the binlog dir, and see the relay log
> > increasing in size by a M every 4 or 5 seconds or so.  About
> > 200kbyte/sec / 1.6Mbit/sec right now.
> > 
> > It seems very steady too.  As in, if I look at bytes, they're
> > constantly increasing.. just, slow..
> > 
> > On Fri, 20 Jan 2017 10:19:57 -0500
> > "Morgan Tocker" <morgan.toc...@oracle.com> wrote:
> > 
> > > Hi Brad,
> > > 
> > > > MySQL community edition 5.6.29, running Linux.
> > > > 
> > > > Binlogs never seem to get caught up on slaves.
> > > > 
> > > > I've done all I can, to validate that this isn't network or disk
> > > > related.
> > > > 
> > > > Disk tests (using iostat and other methods) show lots of bandwidth
> > > > left on the slave and master.
> > > > 
> > > > Network tests, such as:
> > > > 
> > > > - using scp to copy binlogs directly
> > > > - using different NICs to copy binlogs
> > > > - using mysqlbinlog to snag logs (the most 'real' way I can think
> > > > to simulate the replication thread copying binlogs from the
> > > > master)
> > > > 
> > > > All seem to show that network speed is blazingly fast.
> > > > 
> > > > Yet, MySQL is barely getting 4mbit/sec across the network, and
> > > > onto the
> > > disk.
> > > > And that's on a good day.
> > > > 
> > > > Any immediate suggestions here?  This seems very weird, and SQL
> > > > thread is constantly running out of stuff to process.
> > > 
> > > Networking is not my strong-suit, but I have a suggestion:
> > > 
> > > Try lowering slave-net-timeout
> > > http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option
> > > _mysqld_slave-net-timeout
> > > 
> > > I remember that we lowered the default in MySQL 5.7 (from 1hr to 60
> > > seconds) so that the connection between master/slave would be
> > > considered broken faster.  If you have the throughput on a graph it
> > > might better explain if it is a constant 4mbit/sec or more broken.
> > > 
> > > 
> > > - Morgan

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

io thread very slow copying binlogs

[Brad Barnett]

Hey all,

I have a weird issue.

MySQL community edition 5.6.29, running Linux.

Binlogs never seem to get caught up on slaves.  

I've done all I can, to validate that this isn't network or disk related.

Disk tests (using iostat and other methods) show lots of bandwidth left
on the slave and master.

Network tests, such as:

- using scp to copy binlogs directly
- using different NICs to copy binlogs
- using mysqlbinlog to snag logs (the most 'real' way I can think to
  simulate the replication thread copying binlogs from the master)

All seem to show that network speed is blazingly fast.

Yet, MySQL is barely getting 4mbit/sec across the network, and
onto the disk.  And that's on a good day.

Any immediate suggestions here?  This seems very weird, and SQL thread is
constantly running out of stuff to process.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Strange queries

[Brad Barnett]

 SELECT
url,newwebsites.description,newwebsites.title,newwebsites.catid,category.fullname,MATCH
newwebsites.description AGAINST ('aliens') as GOO from newwebsites,category LEFT
JOIN userrestrictions ON userrestrictions.name REGEXP
'[[::]]username|GLOBALAUTHADMIN[[::]]' AND
newwebsites.catid=userrestrictions.catid where category.catid=newwebsites.catid
AND userrestrictions.catid IS NULL AND category.groupid='1' ORDER BY
newwebsites.groupid DESC limit 10;

Can anyone tell me why the above query works, and responds with a column
labelled "GOO", but if I take the exact same query, and add "AND GOO  0" to it,
it complains that there is :

ERROR 1054: Unknown column 'GOO' in 'where clause'


What gives?  If the column GOO exists when MYSQL does an output from that query,
why can't I address it in the query?

Thanks for any help anyone may have!












Quoting Andrew Schmidt [EMAIL PROTECTED]:

 Have you tried this under a 'stable' version of freebsd?
 
 In FreeBSD 4.2 beta, mysql would crash with user locks.  Not mysql's
 fault.
 
 now, I understand RC's are generally stable; but I would still make sure
 that this bug doesn't show up in a stable os.
 
 regards,
 
 -- Andrew
 
 - Original Message -
 From: [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Monday, April 09, 2001 3:34 PM
 Subject: mysql uses 99% cpu under freebsd 4.3
 
 
  Description:
  mysql uses 99% cpu and becomes extremely unresponsive under high load
 
  How-To-Repeat:
  send about 300 simultaneous visitors to www.chicagobusiness.com and
 tell
 them to click around.
 
 
  Fix:
  restart mysql.  this usally helps, at least for a few minutes. 
 sometimes
 it climbs back to 99% though.
 
  Submitter-Id: submitter ID
  Originator: Jon Nathan
  Organization:
  Chaffee Interactive
  MySQL support: extended email support
  Synopsis: mysql uses 99% cpu and becomes unresponsive
  Severity: serious
  Priority: high
  Category: mysql
  Class: support
  Release: mysql-3.23.36 (Source distribution) from freebsd ports
 
  Environment:
  System: FreeBSD d1.crain.com 4.3-RC FreeBSD 4.3-RC #1: Tue Apr  3
 16:17:52
 GMT 2001 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/CHAFFEE  i386
 
 
  Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
  GCC: Using builtin specs.
  gcc version 2.95.2 19991024 (release)
  Compilation info: CC='cc'  CFLAGS='-O2 -pipe -mpentiumpro '  CXX='c++'
 CXXFLAGS='-O2 -pipe -mpentiumpro  -felide-constructors -fno-rtti
 -fno-except
 ions'  LDFLAGS=''
  LIBC:
  -r--r--r--  1 root  wheel  1170734 Apr  3 12:24 /usr/lib/libc.a
  lrwxr-xr-x  1 root  wheel  9 Apr  3 12:24 /usr/lib/libc.so -
 libc.so.4
  -r--r--r--  1 root  wheel  559764 Apr  3 12:24 /usr/lib/libc.so.4
  Configure command:
 ./configure  --localstatedir=/data/db --without-perl --without-debug
 --witho
 ut-readline --without-bench --with-mit-threads=no --with-libwrap
 --with-low-
 memory --enable-assembler --with-berkeley-db --with-charset=latin1
 --prefix=
 /usr/local i386--freebsd4.3
  Perl: This is perl, version 5.005_03 built for i386-freebsd
 
  -
  Before posting, please check:
 http://www.mysql.com/manual.php   (the manual)
 http://lists.mysql.com/   (the list archive)
 
  To request this thread, e-mail [EMAIL PROTECTED]
  To unsubscribe, e-mail
 [EMAIL PROTECTED]
  Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 
 
 
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 
 



---

 Dynamic Hosting
   HTTP://www.L8R.net/ 
  "We Provide Static Hostnames for Dynamic IP's"

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php