Re: ENUM() vs TINYINT

[Basil Daoust]

I'm sure your enum is a fine option, but 0,1,2,3,4 would do the same thing.
And you could add a comment to the table to describe it if desired.

On Mon, Sep 21, 2015 at 8:03 AM, Richard Reina  wrote:

> I have a column name quarter which I need to have 5 possible inputs; 1, 2,
> 3, 4, or OT. Because of the OT possibility I am leaning towards ENUM.
> Hence, I am also thus considering ENUM('first', 'second', 'third',
> 'fourth', 'overtime') as the input will primarily be used in written
> descriptions. Is this a wise or unwise way to design a table column?
>
> Thanks
>

Waiting for table metadata lock

[Basil Daoust]

InnoDB question

So I have a rename table command that sometimes locks up with the waiting
message.
We have tried a few things, and nothing has worked well yet.
So I was wondering is their a method in reducing the time this lock last
for?
Can I change any MySQL settings, increase space, get faster drives, more
memory, anything?

Waiting for table metadata lock
RENAME TABLE A to A_old, A_temp to A

Re: the best way compare String

[Basil Daoust]

For the best chance to have a efficient query you would need to loose the
leading % if possible.
If you could then the query can optimize using a index.

On Sun, Dec 4, 2011 at 2:50 PM, Reindl Harald h.rei...@thelounge.netwrote:

 you THINK the engine is? WTF?

 fulltext if you need fulltext-search
 please read the manuals i linked!

 Am 04.12.2011 21:20, schrieb Rafael Valenzuela:
  Hi Reindl,
  Thanks for your quick answer, but i think  the engine is MySAM but I'm
 not
  sure, What type of index recomendais me, unique, full-text, etc..?
  Thanks a lot
 
  2011/12/4 Reindl Harald h.rei...@thelounge.net
 
 
  Am 04.12.2011 20:25, schrieb Rafael Valenzuela:
  Hi all,
  In one query,  I am comparing a string with a 'like=%patron%', but is
  very
  slow and expensive .What is the best way to compare a string? like or
  regular expression?
  The data base is too big table 1TB, any idea?
  thanks a lot
 
  http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html
 
  without indexes is big tables is no fast search possible
  if you are useing innodb you have currently lost

Re: Can't run MySQL under Cygwin : connect to server at 'localhost' failed (only when using password)

[Basil Daoust]

Maybe this is the wrong place to ask, but why would you want to do this?
Mysql has binaries for Windows, just use one of them?

On Fri, Nov 18, 2011 at 4:16 PM, Franck Houssen f...@hotmail.com wrote:


 Hello mysql-list,

 I try to install MySQL under Cygwin : I can build (mysql-5.5.17 on windows
 7 using Cygwin), I can start and stop the server (only using mysqld.server
 - mysqladmin fails to connect).
 I can not connect to the server when I want to use a password (if I don't
 use any password the connection to the server succeeds).

 I need client AND server. I followed the on line mysql doc. Some comments
 about the installation / running process that I would underline :I do NOT
 use --without-server option in configure (I need the server)I use readline
 (ccmake configuration) and not libedit : could this be a problem ?I used
  mysql_install_db with --basedir, --datadir and --skip-name-resolve
 optionsI use a user dummy (that belongs to the mysql group) : I can not
 create the mysql user (Windows prevent me from creating a mysql user for a
 reason I can't figure out : I stopped fighting with Windows. As mentionned
 in the on-line doc, MySQL should work with any user : dummy is my user -
 dummy belongs to the group mysql - and the password is dummy)the root
 user doesn't exist in Cygwin (as far as I understand) : I can't use any
 mysqlamdin -u root ... as described in the on-line mysql doc
 When I use mysql or mysqladmin triggering a connection using a
 password, the connection fails (but succeeds if no password is used). May
 be someone could find a clue to solve this problem !... Could someone help
 me ?

 Thanks

 Franck

 Here after is a detailed description of the problem :  $ mkgroup -l 
 /etc/group (update groups Windows - Cygwin)



 $ mkpasswd -l  /etc/passwd (update passwords Windows - Cygwin) $ more
 etc/group | grep mysql (check OK)
 mysql:S-1-5-21-4028741454-3406211479-1246761672-1004:1004:

 $ more passwd | grep dummy (check OK)

 dummy:unused:1000:513:dummy,U-dummy-PC\dummy,S-1-5-21-4028741454-3406211479-1246761672-1000:/home/dummy:/bin/bash

 $ chgrp -R None /tmp (give read / write access to all users)



 $ chgrp -R None /var (give read / write access to all users)



 $ ll (check OK)

 total 229

 drwxrwxrwt+ 1 dummy None   0 Nov 14 11:57 tmp

 drwxr-xr-x+ 1 dummy None   0 Aug 17 20:58 var



 $ chgrp -R mysql /usr/local/mysql (give read / write access to users of
 mysql group)



 $ chown -R dummy /usr/local/mysql (give read / write access to users of
 mysql group)



 $ ps (check : no server)

  PIDPPIDPGID WINPID  TTY  UIDSTIME COMMAND

 3400   13400   3400  con 1000 11:10:39 /usr/bin/bash

 675634006756   5400  con 1000 12:14:46 /usr/bin/ps



 $ ll /tmp (check : no mysql.sock)

 total 12

 drwxrwxrwt+ 1 dummy None  0 Nov 14 11:57 .

 drwxr-xr-x+ 1 dummy Administrateurs   0 Nov  5 00:37 ..

 drwxrwxrwt+ 1 dummy None  0 Nov  9 18:40 .X11-unix

 drwxr-xr-x+ 1 dummy None  0 Aug 18 00:00 hsperfdata_dummy

 -rw-r--r--  1 dummy None316 Oct 26 09:13 xkb_4Di75h

 -rw-r--r--  1 dummy None316 Oct 27 00:08 xkb_4NrKCL

 -rw-r--r--  1 dummy None316 Oct 27 00:09 xkb_ThcsMy

 -rw-r--r--  1 dummy None316 Oct 27 00:10 xkb_shbOiY



 $ mysqld --user=dummy  (launch server : OK)

 14 12:15:54 InnoDB: The InnoDB memory heap is disabled

 14 12:15:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins

 14 12:15:54 InnoDB: Compressed tables use zlib 1.2.5

 14 12:15:54 InnoDB: Initializing buffer pool, size = 128.0M

 14 12:15:54 InnoDB: Completed initialization of buffer pool

 14 12:15:54 InnoDB: highest supported file format is Barracuda.

 14 12:15:54  InnoDB: Waiting for the background threads to start

 14 12:15:55 InnoDB: 1.1.8 started; log sequence number 1595675

 14 12:15:55 [Note] Event Scheduler: Loaded 0 events

 14 12:15:55 [Note] mysqld: ready for connections.

 Version: '5.5.17'  socket: '/tmp/mysql.sock'  port: 3306  Source
 distribution



 $ ll /tmp (check: mysql.sock is created and can be accessed - read / write
 permissions)

 total 13

 drwxrwxrwt+ 1 dummy None  0 Nov 14 12:15 .

 drwxr-xr-x+ 1 dummy Administrateurs   0 Nov  5 00:37 ..

 srwxrwxrwx  1 dummy None  0 Nov 14 12:15 mysql.sock



 $ ps (check: server launched OK)

  PIDPPIDPGID WINPID  TTY  UIDSTIME COMMAND

 472034004720   6576  con 1000 12:15:53
 /usr/local/mysql/bin/mysqld

  $ mysql -u dummy -p (when I hit return as a password = connection OK)
 Enter password:
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 mysql show tables;
 ERROR 1046 (3D000): No database selected
 mysql quit
 Bye

 $ mysql -u dummy -p (when I type a real password : dummy, YES... or
 anything else)
 ERROR 1045 (28000): Access denied for user 'dummy'@'localhost' (using
 password: 

Re: Cleaning up old files

[Basil Daoust]

If you search on mysql-bin.01 you get a lot of good info.
o They are mysql log files it contains all the queries u can view
these files with the command mysqlbinlog just man it for more details
o These are your binary log files.. you might not want to switch them
off depending on your setup - but you can purge them (look in the
manual for PURGE MASTER LOGS)

I've heard people talk about using them for recovery.
I know they are used for replication.
You can delete/purge the old ones that you have a backup for, because
trying to recover by playing them all back is probably not a realistic
solution.

Basil

On Mon, Nov 14, 2011 at 12:01 PM, Rob Tanner rtan...@linfield.edu wrote:
 Hi,

 In my MySQL directory, I have more than a few gig and a half sized files, 
 mysql-bin.01, mysql-bin.01 and et cetera.  They date from today all 
 the way back to early 2010.  I don't know exactly what those files are but I 
 would like to delete as many as are no longer is use since I had a 40GB 
 partition fill up over the weekend which resulted in bringing down our web 
 server.  So what are those files and can I delete all but the most recent?

 Thanks.


 Rob Tanner
 UNIX Services Manager
 Linfield College, McMinnville Oregon



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: How to select the id of 2+ records for given user?

[Basil Daoust]

For me given the sample data the following worked.
The inner select says find all first messages, the outer says give me all
messages that are thus not first messages.

select * from table1 where messageID NOT IN (
select messageID from table1
group by userID
)

Some times just playing with the data will result in an aha moment.
I'm assuming where you show row 7 you meant row 8?


On Wed, Oct 19, 2011 at 12:43 PM, Derek Downey de...@orange-pants.comwrote:

 You could do a GROUP_CONCAT to get you close:

 SELECT userID, SUBSTRING_INDEX(GROUP_CONCAT(messageID), ',', 1) messageList
 FROM table
 GROUP BY userID

 | userID | messageList |
 |--|---|
 | 71| 984|
 | 73| 441, 489|
 | 74| 483, 723|

 Or some such. Note I haven't tested the actual query. It's just a general
 direction.

 - Derek Downey

 On Oct 19, 2011, at 1:32 PM, Michael Dykman wrote:

  I'm afraid that what you are looking for simply cannot be done with MySQL
  alone.  You will need to pare your results at the application layer.
  Remember that rows have no inherent order except for conforming to any
  ORDER BY clause contained within the query.
 
  - md
 
  On Wed, Oct 19, 2011 at 1:27 PM, Dotan Cohen dotanco...@gmail.com
 wrote:
 
  Assuming a table such this:
  | ID |  messageID  | userID |
  ||-||
  | 1  | 345 | 71 |
  | 2  | 984 | 71 |
  | 3  | 461 | 72 |
  | 4  | 156 | 73 |
  | 5  | 441 | 73 |
  | 6  | 489 | 73 |
  | 7  | 483 | 74 |
  | 8  | 523 | 74 |
  | 9  | 723 | 74 |
 
  I need the second, third, fourth, etc messageID for each userID. So I
  would get a results table such as:
  | ID |  messageID  | userID |
  ||-||
  | 2  | 984 | 71 |
  | 5  | 441 | 73 |
  | 6  | 489 | 73 |
  | 7  | 483 | 74 |
  | 9  | 723 | 74 |
 
  I've tried playing with count and group by and limit, but I've not
  found a solution. I can easily get all the rows and then remove the
  rows that I don't need in PHP, but I'd still like to know if an
  all-MySQL solution is possible.
 
  Thanks!
 
  --
  Dotan Cohen
 
  http://gibberish.co.il
  http://what-is-what.com
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com
 
 
 
 
  --
  - michael dykman
  - mdyk...@gmail.com
 
  May the Source be with you.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=bdao...@lemonfree.com

Re: Inefficient query is melting my server!!

[Basil Daoust]

Can you rotate that table daily, thus keeping it small?
You can then move yesterdays data somewhere for post processing.
try an insert if it fails generate the new table from the template, done.
Thus no checking other then once a day when the insert fails.


On Wed, Oct 19, 2011 at 2:54 PM, Johan De Meersman vegiv...@tuxera.bewrote:

 - Original Message -
  From: Brian Dunning br...@briandunning.com
 
  Can someone tell me if what I'm trying to do can be done more
  efficiently? I just got off the phone with Rackspace when my server
  was hung up, and they found a whole bunch of this one same query was
  all stacked up with waiting queries and locked.

 Yes, but locked by *what*? You can have a kazillion hung queries, but it's
 the one right before them that is holding the lock that's the important one.

  $query = insert ignore into stats (ip,id,type,date,time) values
  ('.$_SERVER['REMOTE_ADDR'].','$id','listen',NOW(),NOW());

 Switching to InnoDB will allow concurrent inserts, but may impact your
 performance and application on other points. MyISAM also allows concurrent
 inserts provided you have no gaps in the table (that is, only ever inserted
 into it). Neither of those is going to help if the troublesome lock is held
 by a statement that locks the entire table, though.

  CREATE TABLE IF NOT EXISTS `stats` (

 A stats table? Are any automated maintenance jobs running on that? I
 recently had a run-in with Cacti, which did an OPTIMIZE TABLE every day at
 midnight - presumably to rebalance the indices after cleanup jobs - which,
 due to the sheer volume of data, took over an hour. That's an hour that
 table is fully locked. Not saying that's your problem, just a pointer.

 Remote hands are useful, but not as much as being able to get onto a
 machine and see what's happening for yourself.


 --
 Bier met grenadyn
 Is als mosterd by den wyn
 Sy die't drinkt, is eene kwezel
 Hy die't drinkt, is ras een ezel

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=bdao...@lemonfree.com

Re: [setting value when INSERT for auto increment]

[Basil Daoust]

since your listing the columns, you could just leave off `idlog` from 
the named columns and thus not also need to include the null in the 
inserted values.


INSERT INTO `friendlyCMS`.`log`
(`imepriimek`, `clock`, `action`, `onfile`, `filesize`)
VALUES ($_COOKIE['user'], CURRENT_TIMESTAMP, 'saved', $filename, 
filesize($filename));



On 16/05/2011 9:59 AM, Johan De Meersman wrote:

If you're asking what I think you're asking, then yes, both NULL and 0 will 
trigger an autoincrement field to put in the next value.

- Original Message -

From: Grega Leskovšeklegr...@gmail.com
To: mysql@lists.mysql.com
Sent: Monday, 16 May, 2011 4:49:43 PM
Subject: [setting value when INSERT for auto increment]

Should it be null?
INSERT INTO `friendlyCMS`.`log` (`idlog`, `imepriimek`, `clock`,
`action`, `onfile`, `filesize`) VALUES (NULL, $_COOKIE['user'],
CURRENT_TIMESTAMP, 'saved',$filename, filesize($filename));
idlog is primaryk ey auto inrement not null...
When insertin the value what should I pass it? NULL? Thanks in
advance!!



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

MySql - crashes daily

[Basil Daoust]

Any help greatly appreciated.

Our mysql is restarting a LOT!
Here is what is in the log, it doesn't seem to give much help.

--start of log--
InnoDB: Warning: a long semaphore wait:
101203 15:12:40 - mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=1073741824
read_buffer_size=16773120
max_used_connections=601
max_connections=600
threads_connected=27
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 
20706971 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=(nil)
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
frame pointer is NULL, did you compile with
-fomit-frame-pointer? Aborting backtrace!
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
--end of log--

So before you ask if we have 20G of memory we are running this on amazons :
High-Memory Quadruple Extra Large Instance 68.4 GB of memory, 26 EC2 Compute 
Units (8 virtual cores with 3.25 EC2 Compute Units each), 1690 GB of local 
instance storage, 64-bit platform

We are using Fedora Core 8, with MySQL 5.0.45.
these mysql packages are installed.
mysql.x86_64 
5.0.45-6.fc8   installed
mysql-libs.x86_64
5.0.45-6.fc8   installed
mysql-server.x86_64  
5.0.45-6.fc8   installed

It seems odd to me that we get a InnoDB error when this server doesn't have a 
single InnoDB table.
I also did notice the max_used_connections but from the last 20 or so restarts 
only 2 hit 601, others where as low as approx 150.

I'm hopeful that someone has a idea or two.
Basil





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Hard? query to with group order by group head's name

[Basil Daoust]

On Thu, Jul 16, 2009 at 1:20 AM, Elim PDTe...@pdtnetworks.net wrote:

My table group_member looks like this:
+---+---+-+
| member_id | name | head_id |
+---+---+-+
| 1 | Elim | NULL |
| 2 | Ann | 1 |
| 3 | David | NULL |
| 4 | John | 3 |
| 5 | Jane | 3 |
+---+---+-+

Record with null head_id means
the member is a group head.
Record with head_id k are in the
group with head whoes id equals k.

I like to fetch the rows in the following ordaer

| 3 | David | NULL |
| 4 | John | 3 |
| 5 | Jane | 3 |
| 1 | Elim | NULL |
| 2 | Ann | 1 |

That is
(1) A head-row follewed by the group members with that head
(2)head rows are ordered alphabetically by name.

What the query looks like?

Thanks


I hope this is not a school assignment.

What I came up with was to create a new order column that I populated  
with the name of the HEAD.

Then I can order by the head, head_id, and the member_id

mysql select t1.member_id, t1.name, t1.head_id from (
select m1.*, IF ( m2.name IS NULL, m1.name, m2.name) as groupName from  
group_member as m1

left outer join group_member as m2 ON ( m1.head_id = m2.member_id )
order by groupName, m1.head_id, m1.member_id ) AS t1;
+---+---+-+
| member_id | name  | head_id |
+---+---+-+
| 3 | David | NULL|
| 4 | John  | 3   |
| 5 | Jane  | 3   |
| 1 | Elim  | NULL|
| 2 | Ann   | 1   |
+---+---+-+
5 rows in set (0.01 sec)

It seemed to work without the order by member_id but I'll assume that  
is a fact of the small sample size.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

using mysqld_multi - reboot only session 1 starts.

[Basil Daoust]

We are using mysqld_multi to start three instances, the command /usr/ 
bin/mysqld_multi --config-file=/etc/my.cnf start 1-3 works just fine.
But when I tried to put that in rc.local only 3306 would start.  Ok  
fine.


So I created 3 scripts that I put in /etc/init.d called mysqld1, 2,  
and 3.


So now /sbin/service mysqld start | stop | status all work just fine.

I then added them to run level 3.

I can stop, start, and status any or all but when I reboot I have a  
problem.


$ sudo /sbin/service mysqld status
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
$ sudo /sbin/service mysqld2 start
$ sudo /sbin/service mysqld status
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is not running
$ sudo /sbin/chkconfig --list | grep mysql
mysqld  0:off   1:off   2:off   3:off   4:off   5:off   6:off
mysqld1 0:off   1:off   2:off   3:on4:off   5:off   6:off
mysqld2 0:off   1:off   2:off   3:on4:off   5:off   6:off
mysqld3 0:off   1:off   2:off   3:on4:off   5:off   6:off
$ sudo tail /var/log/mysql/mysqld3.log
090513 16:06:50 [ERROR] Can't start server: Bind on TCP/IP port:  
Permission denied
090513 16:06:50 [ERROR] Do you already have another mysqld server  
running on port: 3308 ?

090513 16:06:50 [ERROR] Aborting

090513 16:06:50  InnoDB: Starting shutdown...
090513 16:06:52  InnoDB: Shutdown completed; log sequence number 0  
195828884

090513 16:06:52 [Note] /usr/libexec/mysqld: Shutdown complete

090513 16:06:52  mysqld ended

I have this same error but for port 3307 for mysqld2 but as you can  
see it starts just fine when I run it after the server is up, why?


Any help greatly appreciated.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org