Re: MySQL server has gone away

[Ken D'Ambrosio]

Basically, it says that MySQL is not responding to queries. So it likely has 
died, or perhaps is mis-configured.

On April 3, 2017 7:07:25 AM EDT, Mahmood N  wrote:
>Hi,I am using Moodle which itself uses SQL for the database. Problem is
>that, when I run the email plugin and execute the command, the refresh
>time of the page becomes high (in the order of 3-5 minutes) and at the
>end, I see this message
>Debug info: MySQL server has gone away
>SELECT id, sid, state, userid, lastip, timecreated, timemodified FROM
>mdl_sessions WHERE sid = ?
>[array (
> 0 => 'jqfbgd5b0q6e2l81bb5gb87mn3',
>)]
>Error code: dmlreadexceptionStack trace:
>  - line 479 of /lib/dml/moodle_database.php: dml_read_exception thrown
>- line 1175 of /lib/dml/mysqli_native_moodle_database.php: call to
>moodle_database->query_end()
>- line 1551 of /lib/dml/moodle_database.php: call to
>mysqli_native_moodle_database->get_records_sql()
>- line 1523 of /lib/dml/moodle_database.php: call to
>moodle_database->get_record_sql()
>- line 1502 of /lib/dml/moodle_database.php: call to
>moodle_database->get_record_select()
>- line 286 of /lib/classes/session/manager.php: call to
>moodle_database->get_record()
>- line 82 of /lib/classes/session/manager.php: call to
>core\session\manager::initialise_user_session()
>   - line 785 of /lib/setup.php: call to core\session\manager::start()
>   - line 27 of /config.php: call to require_once()
>   - line 30 of /index.php: call to require_once()
>
>
>Although it looks like a bug in Moodle, but the guys said it is a MySQL
>issue. I am confused about that. If you have any idea please let me
>know. What does this error say exactly?
>
> Regards,
>Mahmood

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Long time for client to connect; not for script.

[Ken D'Ambrosio]

Can someone help explain something to me?  When I use mysql to connect 
to my server, it can take upwards of 15 seconds.  When I use any of the 
Ruby scripts I've written, it's so fast it's not even obvious it's 
querying a remote host.  Any idea what might cause this disparity?  (My 
initial suspicion would be a reverse DNS resolution issue -- which I 
can't easily check from the DB host -- but wouldn't that affect both 
script and client the same?)


Thanks!

-Ken

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

Help optimizing settings?

[Ken D'Ambrosio]

I've got a fairly large -- 100+ GB -- MySQL database.  It isn't accessed
often -- it's acting more as an archive right now than anything else. 
That being said, when it does get accessed, the indeces seem to take
forever to load.  Being as I just bumped the RAM from 2 GB to 6 GB, what,
generically, would be the best way to go forward to take advantage of the
extra RAM?

Thanks!

-Ken


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


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

Database fundamentals: wanna learn.

[Ken D'Ambrosio]

Hey, all.  I've been using databases clear back to xBase days; that being
said, I've never had a solid foundation for relational databases.  While I
can muddle by in SQL, I really don't have a good understanding of exactly
how keys are set up, the underpinnings of indexing, and, oh, lots of
ground-level stuff.  Call me a user, and you'd be right -- an
administrator of databases?  Not so much.

So, any suggestions -- books, courses, web sites, what-have-you -- that I
should be hitting up so I can have a better grasp of what's going on
behind the scenes?

Thanks!

-Ken


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


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

Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

[Ken D'Ambrosio]

On Fri, December 11, 2009 7:38 am, Johan De Meersman wrote:
 This only works for MyISAM :-)

Good to know -- thanks!

 However, there's another solution where you don't need to shut down, and
 that works for any engine afaik:

 rename table oldschema.table to newschema.table;

Just to be 100% clear -- I assume you have to first create the destination
database, and then do this for all the tables in the source database?

 I agree that it's a silly thing to not have, but I can't say that I've
 encountered a whole lot of instances where I needed it, either.

Agreed.

Thanks much!

-Ken


 On Fri, Dec 11, 2009 at 7:04 AM, Ken D'Ambrosio k...@jots.org wrote:


 Uhhh... wow.  Unless I'm very, very, very mistaken, I think you're
 missing something pretty obvious: I believe you can simply a) shut down
 the database b) mv the directory to a different directory name.

 *DONE*  Your database now has a different name.  Boy, that 30 seconds
 of hard labor was sure faster than waiting a week for SQL dumps.
 Granted, I
 can't swear that this is Officially Sanctioned And Approved(tm), but
 I've
 done it many times, myself (and, indeed, just verified it under 5.1 to
 be sure it still worked).

 Since you are talking such a significant volume of data, I would
 suggest either testing, or hearing from someone more knowledgeable than
 I, but I
 think this problem is substantially smaller than you've let yourself
 believe.

 -Ken



 On Thu, December 10, 2009 11:35 pm, Daevid Vincent wrote:

 How can it possibly be that mySQL doesn't allow you to rename a
 database? I
 can't fathom how this can be a difficult task at all to do. Aren't
 mySQL databases stored in a directory of the DB name? And for INNODB,
 can't you just find the spot in the ibdata file and alter whatever
 needs to be changed? This is absolutely absurd. Not even 5.1 has this
 most basic of features.

 We have nearly a billion rows. Exporting to a .sql file and importing
  again can take nearly a week to do (3 days each way and that doesn't
 even begin to touch on the fact the server would be down)! WTF!?

 We're running Ubuntu LTS 8.04 w/ Ver 14.12 Distrib 5.0.51a, for
 debian-linux-gnu (i486) using readline 5.2

 Even the manual for 5.1 says this can lose data:
 http://dev.mysql.com/doc/refman/5.1/en/rename-database.html
 This statement was added in MySQL 5.1.7 but was found to be dangerous

 and
 was removed in MySQL 5.1.23...However, use of this statement could
 result in loss of database contents, which is why it was removed. Do
 not use RENAME DATABASE in earlier versions in which it is present.


 Seriously? Please explain why a simple rename of a database is such a
  daunting task to mySQL/Sun that all their brilliant minds can't
 figure this one out?

 Why isn't there even a bug report for this?




 http://bugs.mysql.com/search.php?search_for=rename+databaseboolean=on;
 st
 at
 us[]=Activeseverity=limit=Allorder_by=cmd=displayphpver=os=0os
 _det
 ai
 ls=bug_age=0tags=similar=target=defect_class=allworkaround_viab
 ilit y= allimpact=allfix_risk=allfix_effort=alltriageneeded=


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




 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is believed to be clean.





 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is believed to be clean.


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




 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is believed to be clean.





-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


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

Re: Are you serious? mySQL 5.0 does NOT have a RENAME DATABASE?

[Ken D'Ambrosio]

Uhhh... wow.  Unless I'm very, very, very mistaken, I think you're missing
something pretty obvious: I believe you can simply
a) shut down the database
b) mv the directory to a different directory name.

*DONE*  Your database now has a different name.  Boy, that 30 seconds of
hard labor was sure faster than waiting a week for SQL dumps.  Granted, I
can't swear that this is Officially Sanctioned And Approved(tm), but I've
done it many times, myself (and, indeed, just verified it under 5.1 to be
sure it still worked).

Since you are talking such a significant volume of data, I would suggest
either testing, or hearing from someone more knowledgeable than I, but I
think this problem is substantially smaller than you've let yourself
believe.

-Ken


On Thu, December 10, 2009 11:35 pm, Daevid Vincent wrote:
 How can it possibly be that mySQL doesn't allow you to rename a database?
 I
 can't fathom how this can be a difficult task at all to do. Aren't mySQL
 databases stored in a directory of the DB name? And for INNODB, can't you
  just find the spot in the ibdata file and alter whatever needs to be
 changed? This is absolutely absurd. Not even 5.1 has this most basic of
 features.

 We have nearly a billion rows. Exporting to a .sql file and importing
 again can take nearly a week to do (3 days each way and that doesn't even
 begin to touch on the fact the server would be down)! WTF!?

 We're running Ubuntu LTS 8.04 w/ Ver 14.12 Distrib 5.0.51a, for
 debian-linux-gnu (i486) using readline 5.2

 Even the manual for 5.1 says this can lose data:
 http://dev.mysql.com/doc/refman/5.1/en/rename-database.html
 This statement was added in MySQL 5.1.7 but was found to be dangerous and
  was removed in MySQL 5.1.23...However, use of this statement could
 result in loss of database contents, which is why it was removed. Do not
 use RENAME DATABASE in earlier versions in which it is present.


 Seriously? Please explain why a simple rename of a database is such a
 daunting task to mySQL/Sun that all their brilliant minds can't figure
 this one out?

 Why isn't there even a bug report for this?


 http://bugs.mysql.com/search.php?search_for=rename+databaseboolean=onst
 at
 us[]=Activeseverity=limit=Allorder_by=cmd=displayphpver=os=0os_det
 ai
 ls=bug_age=0tags=similar=target=defect_class=allworkaround_viabilit
 y= allimpact=allfix_risk=allfix_effort=alltriageneeded=


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



 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is believed to be clean.





-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


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

Intro to indexing?

[Ken D'Ambrosio]

Hey, all.  I'm trying to get indexing -- like, when do you specify an
index name during index creation, is index use implicit or explicit, and,
honestly, how exactly does it work, anyway?  I've been RTFM'ing, but
haven't found anything that really laid it out in black and white;
usually, they'd give an example or two, but were awfully sparse on the
whys and wherefores.

So, if anyone has something they could point me to -- electronic or dead
tree -- I'd be deeply appreciative.

Thanks!

-Ken


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


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

RE: Indexing? (Warning: relative newbie.)

[Ken D'Ambrosio]

After a few off-list e-mails with Tim, I issued

ALTER TABLE dbmail_messageblks ADD INDEX ( blocksize AND physmessage_id );

which took almost 11 hours to index.  Once done, however, my select
statement went from a hair over 50 minutes to 15 seconds.  (1.69 seconds
after the index was cached.)

Wow.

Thanks for the help, all!

-Ken


On Wed, June 24, 2009 12:03 pm, Little, Timothy wrote:
 To answer your questions in no particular order, YES you can speed it up
 with indexing.

 You might want to first create an index on ( blocksize AND
 physmessage_id ).

 Why, you might ask, index on physmessage_id?  Because then the db won't
 have to do a fetch on items from the table since it's in the INDEX itself,
 saving any unnecessary reads.

 Realistically, I can't see that taking more than a few seconds, at most,
 to execute.  However, making the index might take a serious bit of time.

 Please let us all know how it does or does not work.


 Tim...


 -Original Message-
 From: Ken D'Ambrosio [mailto:k...@jots.org]
 Sent: Wednesday, June 24, 2009 11:07 AM
 To: mysql@lists.mysql.com
 Subject: Indexing? (Warning: relative newbie.)


 Hi, all.  I'm a long-time MySQL user who's only recently had to start
 learning some administrative stuff, largely because I finally have a
 decently-sized database.  My database is about 100 GB; I'm using it -- via
  dbmail (www.dbmail.org) -- as a mail server for my company.  While dbmail
  is well-and-good with its IMAP front-end, I'm thinking of writing a
 Python
 front-end to do some queries directly against MySQL.  But some of them take
 a l-o-n-g time.  As an example, I've got a table with slightly over a
 million records; I'd like to be able to show (say) only IDs of messages
 under a half-MB.  The query would look something like this:

 select physmessage_id,blocksize from dbmail_messageblks where blocksize 
 50;


 That query takes 50 minutes.  A smidge long to wait.


 So I said, Huh.  That's impressive.  And I tried it without the
 physmessage_id:
 select blocksize from dbmail_messageblks where blocksize  50;

 That took 14 seconds.  A bit more in my timeframe.  Can I optimize this
 with indexing?  Should I be using a different DB engine?  Is there a
 site/book I should be learning DBA fundamentals from that might offer me
 direction for stuff like this?

 Sorry for all the newbie questions, but I haven't done serious database
 stuff since Foxbase/dBase III days.  Things have changed a little since
 then.

 Thanks!


 -Ken



 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is believed to be clean.


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



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



 --
 This message has been scanned for viruses and
 dangerous content by MailScanner, and is believed to be clean.





-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


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

Indexing? (Warning: relative newbie.)

[Ken D'Ambrosio]

Hi, all.  I'm a long-time MySQL user who's only recently had to start
learning some administrative stuff, largely because I finally have a
decently-sized database.  My database is about 100 GB; I'm using it -- via
dbmail (www.dbmail.org) -- as a mail server for my company.  While dbmail
is well-and-good with its IMAP front-end, I'm thinking of writing a Python
front-end to do some queries directly against MySQL.  But some of them
take a l-o-n-g time.  As an example, I've got a table with slightly over a
million records; I'd like to be able to show (say) only IDs of messages
under a half-MB.  The query would look something like this:

select physmessage_id,blocksize from dbmail_messageblks where blocksize 
50;

That query takes 50 minutes.  A smidge long to wait.

So I said, Huh.  That's impressive.  And I tried it without the
physmessage_id:
select blocksize from dbmail_messageblks where blocksize  50;

That took 14 seconds.  A bit more in my timeframe.  Can I optimize this
with indexing?  Should I be using a different DB engine?  Is there a
site/book I should be learning DBA fundamentals from that might offer me
direction for stuff like this?

Sorry for all the newbie questions, but I haven't done serious database
stuff since Foxbase/dBase III days.  Things have changed a little since
then.

Thanks!

-Ken


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


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