LAST_INSERT_ID() returns the latest ID for the current connection. As
long as you yourself can guarantee that no other queries are executed
using that connection, you're fine. If another record others is inserted
using another connection, that connection will return a different
LAST_INSERT_ID()
On 28-06-2014 19:11, Tim Dunphy wrote:
Hello,
I'm trying to use a very basic alter table command to position a column
after another column.
This is the table as it exists now:
mysql> describe car_table;
+-+--+--+-+-++
| Field | Type | Null |
Or use the LOCAL diective to have the client send the csv file contents
to the server.
/ Carsten
On 23-06-2014 16:59, Scott Helms wrote:
I generally drop them into /tmp for easy access and cleanup after the data
load, but you can put them any place that the mysql daemon process has
access to
On 29-03-2014 19:26, william drescher wrote:
I am given a table: ICD9X10 which is a maping of ICD9 codes to ICD10
codes. Unfortunately the table contains duplicate entries that I need
to remove.
...
I just can't think of a way to write a querey to delete the duplicates.
Does anyone have a sugg
On 17-03-2014 16:21, Mister Vlad wrote:
I am looking at building a dedicated MySQL server... was wondering about the
downside to using SSD drives?
My thoughts was going 2 servers, with 4 drives each in raid 5 (3+1)
configuration.
Is this a good idea? I was originally thinking about going Raid
achiel Richards wrote:
Hi,
the queries are done by connecting to the database using mysql
workbench or otherwise after ssh to server by using straight mysql
connection.
regards
On 19/02/2014 12:51, Carsten Pedersen wrote:
If you're doing this from the cmd-line client, try running it usi
If you're doing this from the cmd-line client, try running it using --quick.
Best,
/ Carsten
On 19-02-2014 09:03, Machiel Richards wrote:
Hi guys
I am hoping that someone might have experienced this before or
might know why we are getting this.
We regularly need to run some quer
On 30-07-2013 01:16, Rick James wrote:
Elevator... If the RAID _controller_ does the Elevator stuff, any OS
optimizations are wasted. And there have been benchmarks backing that
up. (Sorry, don't have any links handy.)
RAID 5/10 ... The testing I have done shows very little difference.
...r
This may be a naive question, but I'm not sure I can see you've covered
this: Have you tried "USE logs" before DROP TABLE `#sql-ib203` (without
the "logs/" bit)?
/ Carsten
On 19-06-2013 21:00, Franck Dernoncourt wrote:
Hi all,
A table `logs/#sql-ib203` appeared after a MySQL crash due to dis
per
primary key
On Fri, May 31, 2013 at 11:03 AM, Carsten Pedersen mailto:cars...@bitbybit.dk>> wrote:
Again: Unless you can give some idea as to the kind of lookups you
will be performing (which fields? Temporal values? etc.), it is
impossible to give advice on the table str
e applying.
On Fri, May 31, 2013 at 9:58 AM, Carsten Pedersen wrote:
On 30-05-2013 09:27, Neil Tompkins wrote:
Hi,
I've created a Audit table which tracks any changed fields for multiple
tables. In my Audit table I'm using a UUID for the primary key. However
I
need to have a referen
On 30-05-2013 09:27, Neil Tompkins wrote:
Hi,
I've created a Audit table which tracks any changed fields for multiple
tables. In my Audit table I'm using a UUID for the primary key. However I
need to have a reference back to the primary key(s) of the table audited.
At the moment I've a VARCHA
On 19.04.2013 06:49, Kapil Karekar wrote:
Though I would recommend not using such names. Some poor guy working
on your application six months down the line is going to wonder why
his queries are failing, spend a day trying to figure out and will
post the same question again to this list :-)
.
You don't specify how many different types (including min/max values)
you expect to be using. If you expect to end up with a few hundred, then
you should perhaps consider using an ENUM or SET column directly in the
data table.
/ Carsten
On 10.08.2012 10:51, Gaston Gloesener wrote:
Hello,
On 10.07.2012 13:16, Darek Maciera wrote:
2012/7/10 Ananda Kumar :
can u show the explain plan for your query
Thanks, for reply!
Sure:
mysql> EXPLAIN SELECT * FROM books WHERE LOWER(ksd)=LOWER('4204661375');
That's definitely not the query you showed the first time around. The
query you'
Alternatively, you can copy the data into another table easily:
http://www.bitbybit.dk/carsten/blog/?p=115
Best,
/ Carsten
On 14.05.2012 09:34, P.R.Karthik wrote:
Hi Rafal,
If there are more slow queries in your server and logging them into a table
will increase the IO of the server.
It is b
On 30.04.2012 18:53, Don Wieland wrote:
Hello,
I have a client who needs the ability to do statistical reporting on
their mySQL db data. Is there an app that provides an easy UI that will
allow my client to build a line item query, specify fields to be include
in the result of the query, and the
On 15-09-2011 10:31, Chris Tate-Davies wrote:
Adarsh,
1)
When restoring a mysqldump you have the option of which database to
restore.
mysql database1 < backup.sql
Admittedly, it's been a few years since I last used mysqldump, but I
suspect that it will contain USE commands - as such, it wil
`userTable.userid` => `userTable`.`userid`
/ Carsten
On 09-09-2011 23:01, Dotan Cohen wrote:
Now that I've got the syntax right, MySQL is complaining that a field
does not exist, which most certainly does:
mysql> UPDATE
-> `userTable`
-> INNER JOIN `anotherTable`
->ON `u
On 22.04.2011 22:41, Larry McGhaw wrote:
It does appear to be some type of bug to me.
I agree. I was thrown by Daniels "first and third" comment, which I
guess should read "second and third"
I reproduced the behavior in 5.1.53-community on Windows.
/ Carsten
--
MySQL General Mailing List
F
On 22.04.2011 21:37, Daniel Kraft wrote:
Hi all,
I'm by no means a (My)SQL expert and just getting started working with
VIEWs and stored procedures, and now I'm puzzled by this behaviour:
DROP DATABASE `test`;
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `mytable`
(`ID` SERIAL,
`Type` INTE
Den 23-02-2011 18:41, Jim McNeely skrev:
Is there a way to set the auto-increment for a particular table to increase by
some number more than one, like maybe 10?
Thanks in advance,
Jim McNeely
CREATE TABLE t (
...
) AUTO_INCREMENT=10;
/ Carsten
--
MySQL General Mailing List
For list archi
ehr...
Den 23-01-2011 15:36, Carsten Pedersen skrev:
Seeing from later posts that you're using InnoDB, why don't you simply
wrap the INSERT/UPDATE into a transaction? You don't avoid the UPDATE,
but I'm not sure I understand the need to mess w/ triggers.
BEGIN
INSERT INTO
Seeing from later posts that you're using InnoDB, why don't you simply
wrap the INSERT/UPDATE into a transaction? You don't avoid the UPDATE,
but I'm not sure I understand the need to mess w/ triggers.
BEGIN
INSERT INTO t(id) NULL
UPDATE t SET xxx=last_insert_id()
COMMIT
Best,
/ Carsten
Den
Usually this is caused by DNS-based authentication, where the
reverse-DNS lookups are hanging for one reason or another.
If you can, switch to IP-based authentication and use --skip-name-resolve.
/ Carsten
On 05.01.2011 08:26, Yogesh Kore wrote:
Hi,
What is unauthenticated user seen in mysql
It's been a long time sine I used mysqlimport, but you might want to try:
- using "--fields-terminated-by" rather than "--fields-terminated"
- losing (or escaping) the backticks in --columns=
- checking my.cnf to see if the client settings are the same for mysql>
and mysqlimport
- checking user
Den 28-11-2010 21:02, Grant skrev:
I'm trying to run mysql_upgrade but I get:
# mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306'
'--socket=/var/run/mysqld/mysqld.sock'
mysqlcheck: Got error: 1045: Ac
On Mon, 6 Sep 2010 15:02:24 +0200, Thorsten Heymann
wrote:
> Digging through mysqld source, I found this behaviour handled in
> sql/handler.cc and changed from printing key_nr to key.name between this
> versions. :(
>
> Is there a possible better, reliable way to detect what key is
duplictated
>
On Mon, 6 Sep 2010 06:36:02 -0400 (EDT), "Robert P. J. Day"
wrote:
> no, i don't want to start a flame war, i just want some feedback on
> a current list of mysql "drawbacks" WRT postgresql.
>
> in the context of a fully open-source, java based ECM product, there
> is a FAQ entry that summarize
On Fri, 25 Jun 2010 06:31:11 -0500, Jim Lyons
wrote:
> I think you're confusing table size with data base size. The original
post
> grouped by schema so it appears the question concerns database size. I
> don't believe mysql imposes any limits on that. Is there a limit on the
> number of
Brad Scott skrev:
Any line beginning with just a number (ie 9, 10, 16) causes a failure. What am
I missing?
backticks. Use `9`, `10`, etc.
Having column names that begin with numbers is a really bad design
decision. "9a123" (unquoted, of course) will work, as you've noticed,
but e.g. "
Haven't done this in a while, but I'm guessing that you can't create
both a constraint and an index with the same name?
Type mismatch will in my experience most often generate an errno 150.
/ Carsten
j...@msdlg.com skrev:
I'm trying to create a foreign key by executing the following statement
There are SETs and ENUMs, but I've always found that dealing with them is
annoying.
YMMV
/ Carsten
On Fri, 14 May 2010 13:54:29 +0530, "Samrat Kar"
wrote:
> Hello,
>
>
>
> How to store multiple values in a single field? Is there any array data
> type
> concept in mysql?
>
Gving the full error message would be helpful.
Check that the sql mode settings for ALLOW_INVALID_DATE, NO_ZERO_DATE
and NO_ZERO_IN_DATE are the same on both master and slave.
/ Carsten
Prabhat Kumar skrev:
Hi,
I have setup replication between 2 servers, on both there is different
versions o
Keith Clark skrev:
I have the following statement:
select chart_of_accounts.accountname as Account,
concat('$',format(coalesce(sum(sales_journal_entries.debit),0),2)) as
Debit,
concat('$',format(coalesce(sum(sales_journal_entries.credit),0),2)) as
Credit,
concat('$',format(coalesce(sum(sales_jou
On Thu, 22 Apr 2010 13:12:16 +0200, Johan De Meersman
wrote:
> Kudos for managing to drag up such an obscure piece of functionality :-)
I
> can see where it would be useful, though.
>
> As to your question, though: given that that page indicates that it will
> reuse deleted sequence numbe
Lentes, Bernd skrev:
hello ML,
i'm new to MySQL, so i have a very basic question. I have to install a database
server for about 15 persons. The server is intended for testing and evaluating.
The users should be able to create their own databases and tables.. And they
should be able to give gr
Carsten Pedersen skrev:
Jim Lyons skrev:
Has anyone tried using the log_output option in mysql 5.1 to have the
general log put into a table and not a flat file? I used it for a while
before having to downgrade back to 5.0 but thought it was a great
idea. I'm
curious to see if anyone
Jim Lyons skrev:
Has anyone tried using the log_output option in mysql 5.1 to have the
general log put into a table and not a flat file? I used it for a while
before having to downgrade back to 5.0 but thought it was a great idea. I'm
curious to see if anyone feels it helps analysis.
I tried
On Fri, 16 Apr 2010 11:44:42 +0200, Jørn Dahl-Stamnes
wrote:
> The server does not know if the browser is closed or not (or if the
> network
> connection is losted). It will continue to execute the code until
> finnished.
Not quite true. If it decides to flush its output buffer and not
http://php.net/manual/en/function.ignore-user-abort.php
/ Carsten
On Fri, 16 Apr 2010 18:39:07 +0900, Antonio PHP
wrote:
> This maybe a newbie question.
>
> Consider the following concept,
>
> ~/index.php
>
> #1. Fetch data from an external webpage using PHP Curl;
> #2. Preg_mat
mos skrev:
At 01:20 PM 4/14/2010, Carsten Pedersen wrote:
Been there, done that. It's a maintenance nightmare.
Why is it a maintenance nightmare? I've been using this technique for a
couple of years to store large amounts of data and it has been working
just fine.
In a prev
Check out the DECIMAL type.
/ Carsten
Sebastien MORETTI skrev:
Hello,
I have a row which is defined as double unsigned (MySQL 5.0.26-Max,
OpenSuse).
Values in this row can go from a single digit, like 1, to values like
0.0006872207 or 1.2513e-18.
I want to store exact numbers.
But I wo
Been there, done that. It's a maintenance nightmare.
Another idea: Have a separate "deleted" table with the IDs of the rows
that you consider deleted. Re-write your queries to do a
left-join-not-in-the-other-table agains the "delete" table. Then, either
wait for a maintenance window to delete
If you'll excuse the shameless plug: I once created a tool to help find
the exact parameters to use for PHPs date() and MySQLs DATE_FORMAT().
Please see
http://bitbybit.dk/php/date_format/
(Yes, it looks horrible. But it works)
/ Carsten
Martin Gainty skrev:
Good Afternoon All
following t
don't have a space between '-p' and 'password', i.e. -ppassword
/ Carsten
alba.albetti skrev:
I've just installed MySQL on Windows 2000.
I've opened the MS-DOS windows and I've written
>C:\Programs\MySQL\...\bin\mysqladmin -u root -p password mysql2010
After the enter the prompt says >Enter pa
The MySQL ODBC driver?
/ Carsten
On Thu, 8 Apr 2010 10:42:28 +0530 (IST), Vikram A
wrote:
> Hi,
>
> I would like to export my table structure from MYSQL from a particular
db.
> Is there any tool for doing this?
>
> Please guide me.
>
> Thank you
>
> VIKRAM A
>
>
>
>
AFAIR, MySQL 4.x supports LIKE, e.g.
SHOW TABLE STATUS LIKE 'tab_%'
/ Carsten
spacemarc skrev:
hi all,
in MySQL 4.1.x i want to obtain the status of more tables with one only query.
In 5.x i use "SHOW TABLE STATUS WHERE Name IN ('tab_1', tab_2, 'tab_3')"
In 4.1.x i tried to use but it doesn'
You can do some trickery with auto increment fields and multiple-column
indexes. Have a look at http://www.bitbybit.dk/carsten/blog/?p=131
Beware that this is apparently a MyISAM-specific trick.
/ Carsten
On Tue, 6 Apr 2010 16:02:48 +0530, "Suryanarayanan"
wrote:
> I am new to mysql a
InnoDB won't give you much in terms of disk crash recovery. That's what
backups are for.
Where InnoDB does excel is if your database server dies while updating
rows. If that happens, your database will come back up with sane data.
For both table types, once the data has been flushed to disk,
Pavel Gulchouck skrev:
Hi!
Is there any way to get sequence row number in request?
I need row number calculated before "having" but after "group by"
and "order", so "select @row := @row+1" unsuitable in my case
(it executed before grouping).
something along the lines of this:
mysql> select *
OPTIMIZE TABLE sometimes helps, ymmv.
http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html
/ Carsten
Nico Sabbi skrev:
Hi,
I noticed that over the months the dump of my databases (very
subject to modifications, but not subject to increase significantly in
size) gets progressively slo
You might want to read the comments to this posting:
http://www.bitbybit.dk/carsten/blog/?p=116
Several tools/methods for controlling and analyzing the slow query log are
suggested there.
Best,
/ Carsten
On Tue, 23 Feb 2010 14:09:30 +0530, Ananda Kumar
wrote:
> slow query log wil
Generally, you should find that removing and re-adding the indexes will
speed up your operation.
I do not believe that ALTER TABLE with just index additions will require
a table rebuild, but even if it does, doing a table copy will be a
fairly fast operation (much faster than loading from othe
Is the CREATE TABLE you show the result of SHOW CREATE TABLE or your own
create statement? If the latter, please show the output of SHOW CREATE.
Does SELECT succeed if you remove the INSERT part of the statement?
You might want to consider adding an index on transactionlogid, this
could bring
Using multiple columns to hold essentially the same data is generally a
bad idea: Business requirements may change over time, forcing you to
change both the schema and your programming logic.
Better to use a table consisting of username/changedate/password. One
year from now, when your boss/c
mysql> create table t (sizes
set('Extra-small','Small','Medium','Large','XLarge','XXLarge','XXXLarge'),
colorsShadesNumbersShort
set('blue:99','gray:465945','purple:50404D','navy-blue:CC7722','fuchsia:FF77FF','aqua:7FFFD4','maroon:B03060','black:FF','yellow:9ACD32'));
Is there any particular reason not to use the MySQL ODBC driver to
import the data directly into Excel?
/ Carsten
Jim Lyons skrev:
A command to convert the table mytab in database mydb into a tab-delimited
file mytab.txt might be:
mysql -e'select * from mydb.mytab' -sss > mytab.txt
The -sss
I would encourage everyone on this list to at least read the two articles
"Fox in the henhouse" (http://helpmysql.org/en/theissue/foxinthehenhouse)
and
"GPL is not the answer"
(http://helpmysql.org/en/theissue/gplisnottheanswer)
You may or may not agree with those, but at least then you'll hav
I would encourage everyone on this list to at least read the two articles
"Fox in the henhouse" (http://helpmysql.org/en/theissue/foxinthehenhouse)
and
"GPL is not the answer" (http://helpmysql.org/en/theissue/gplisnottheanswer)
You may or may not agree with those, but at least then you'll hav
David Giragosian skrev:
On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso wrote:
Will anything ever be equal to NULL in a SELECT query?
...
What's so special about NULL?
http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html
Should answer some of your questions, Dante.
Oddly e
Gary Smith skrev:
...
An example of where it wouldn't be: Although ID is auto_increment, you
could define a row as, say, '10005583429'. This would be a valid input.
Selecting max(id) would return that number. However, auto_increment
wouldn't change - it would still be '34' (or whatever) for t
y is on http://tinyurl.com/6m7ul
/ Carsten
--
Check out the MySQL Certification FAQ:
http://www.mysql.com/certification/certfaq.html
Carsten Pedersen, Certification Manager
MySQL AB, http://www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
tion can seriously increase your wealth!
http://www.mysql.com/certification
Carsten Pedersen, Certification Manager
MySQL AB, http://www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
r
address.
Best regards,
/ Carsten
--
Warning: Certification can seriously increase your wealth!
http://www.mysql.com/certification
Carsten Pedersen
Coordinator of Development, Certification Manager
MySQL AB, http://www.mysql.com
Office: +45 56 36 16 10
--
MySQL General Mailing List
For list
Hi!
In April, the "MySQL Certification Study Guide" was published by MySQL
Press. By now, it has arrived in bookstores across the world.
The main parts of the book are written by Paul DuBois, Stefan Hinz and
yours truly. Paul needs no introduction; Stefan has translated several
MySQL books to Ger
On Sun, 2004-01-18 at 20:51, Johannes Franken wrote:
> * Marc Dver <[EMAIL PROTECTED]> [2004-01-18 18:30 +0100]:
> > 1. What is the format of the test questions? I.e., are they multiple
> > choice, free answer, essay, etc.?
>
> They are multiple-choice (but very tricky) and fill-in-the-gaps.
>
On Sat, 2004-01-10 at 12:04, Bernard Clement wrote:
> Hello Aman,
>
> For instructions on taking the exam in India goto the URL:
> http://www.vue.com/mysql/ and click on "test center" of "To register for exams
> in India, please contact the test center directly. This will bring you a
> window
On Mon, 2004-01-05 at 19:56, Douglas Sims wrote:
> Thanks, Stefan. Mike's article was interesting.
>
> The test was a bit harder than I anticipated. I should have paid more
> attention to column types and database name, among other things. But I
> did pass
Congratulations :-)
> - at least,
ing.
I hope you found this input helpful -- you (and anyone else reading this
reply) are very welcome to contact me directly if you want further
details on our certification program.
Best regards,
Carsten Pedersen
Certification Manager, MySQL AB
--
Warning: Certification can seriously increas
is sent to
the server for processing.
Note that just because a command is a "client command", this does not
necessarily infer that no client/server communication takes place.
Best regards,
Carsten H. Pedersen
Certification Manager, MySQL AB
--
Warning: Certification can seriously increase
owing queries will not benefit from indexes?
> 9) Which of the following queries corresponds to the following query
> with a NOT EXISTS subquery?
> 10) Match the following filenames to their corresponding table
> structures.
>
> And so on. Heck, the commonly asked questions on this list would make a
> pretty good test!
>
> Bruce Feist
> (retired database instructor / courseware designer)
>
--
Warning: Certification can seriously increase your wealth!
http://www.mysql.com/certification
Carsten Pedersen, Certification Manager
MySQL AB, http://www.mysql.com
Office: +45 56 36 16 10
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>Description:
mysql --help states that the -e option produces output similar
to that of --batch. This is not the case.
>Fix:
Either the text or the program should be changed to conform
to each other.
>Release: mysql-3.23.33 (Official MySQL RPM)
>Environme
73 matches
Mail list logo