recentpopularlog in

kme : mysql   236

« earlier  
linux - Getting warning ignoring option '--databases' due to invalid value 'testdatabase' in mysql while importing data - Stack Overflow
tl;dr: the 'database=' option from the '[client]' section is getting mistaken for the '--databases' (boolean) option to 'mysqldump'. It's harmless.

See also: https://bugs.mysql.com/bug.php?id=41209
mysql  errormessage  dba  bug  solution 
august 2019 by kme
passwords - MySQL Utilities - ~/.my.cnf option file - Stack Overflow
The gist is that the other utilities ('mysqldump', for example) use different "defaults groups."
mysql  commandline  cli  sysadmin  dba  newbie  question  answered  solution 
august 2019 by kme
sql - In MySQL: How to pass a table name as stored procedure and/or function argument? - Stack Overflow
<code class="language-sql">CREATE PROCEDURE `test1`(IN tab_name VARCHAR(40) )
BEGIN
SET @t1 =CONCAT('SELECT * FROM ',tab_name );
PREPARE stmt3 FROM @t1;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
END $$</code>
sql  mysql  dba  preparedstatements  storedprocedures  solution 
august 2019 by kme
DBD::mysql - MySQL driver for the Perl5 Database Interface (DBI) - metacpan.org
Additionally, turning on this flag tells MySQL that incoming data should be treated as UTF-8. This will only take effect if used as part of the call to connect(). If you turn the flag on after connecting, you will need to issue the command SET NAMES utf8 to get the same effect.


This did *not* work as advertised, and I ended up setting it *after* the connect, along with 'SET NAMES', which actually *did* seem to fix the error messages I was getting about "COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1'":

<code class="language-perl"># source https://www.gyford.com/phil/writing/2008/04/25/utf8-mysql-perl/
# see also: https://stackoverflow.com/a/6423280/785213
$dbh->{mysql_enable_utf8} = 1;
# $dbh->do('SET NAMES utf8');</code>
perl  dbi  dbd  mysql  dba  devel  reference  errormessage  workaround  solution 
august 2019 by kme
perl - Why CONCAT() does not default to default charset in MySQL? - Stack Overflow
This did NOT work (for me):
It probably is DBD::mysql issue/peculiarity. Try enabling utf8 in database handle as described in POD for DBD::mysql (mysql_enable_utf8 part).
perl  dbi  mysql  dba  database  query  collation  errormessage  maybesolution 
august 2019 by kme
How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query - Stack Overflow
For sorting by chromosome then start coordinate, I used:

<code class="language-sql">SELECT [...]
ORDER BY SUBSTRING(chr from 4) + 0 ASC, start</code>
sql  dba  mysql  solution 
august 2019 by kme
MySQL Bugs: #59891: Cmake-gui's error | https://bugs.mysql.com/
Couldn't even get the SRPM package to build, because of this same error, which led me to believe it was a CMake problem, but it also failed with an update CMake.

So maybe you need an *old* CMake? Or an *older* version of MySQL (5.1.72?).
mysql  mysql5.1  centos6  bug  build  compiler  errormessage 
july 2019 by kme
mysql - Can't connect to localhost using Python's MySQLdb - Stack Overflow | https://stackoverflow.com/
I've managed to solve this by setting my host to "127.0.0.1" rather than "localhost".

Turns out if the socket isn't in the default location (/tmp/mysql.sock), the MySQLdb client doesn't take any special measures (lazy!) to discover where it's ACTUALLY at, according to the /etc/my.cnf.

For example, on CentOS (6?), using the SCL version of MySQL, it's at /var/lib/mysql/mysql.sock.
if your mysql socket is not placed in /tmp/mysql.sock, you can specify it with
<code class="language-python">conn = MySQLdb.connect (unix_socket = 'path_to_your_socket', host = 'localhost', user = 'erin', passwd = 'erin', db = 'sec')</code>

edit: for mamp on macosx the mysql socket path should be something like /Applications/MAMP/tmp/mysql/mysql.sock

To figure out where the heck the socket actually *is*, this might work:
using <code class="language-bash">mysql_config --socket</code> in your shell should do, or /path/to/mysql/bin/mysql_config if you installed it in /opt or something – roman Jan 11 '11 at 20:56
python  sqlalchemy  mysql  client  errormessage  webdevel  solution  annoyance 
march 2019 by kme
libmysql - Cheapest way to to determine if a MySQL connection is still alive - Stack Overflow | https://stackoverflow.com/
In this case you can use 'DO 1' as an alternative to SELECT 1; it is marginally faster -- shorter to parse, and it does not return actual data (although you will get the TCP acks, so you will still do the roundtrip validating that the connection is still established.)
mysql  mysqlcppconn  dba  devel  cplusplus  solution 
february 2019 by kme
linux - C++ error: undefined reference to 'clock_gettime' and 'clock_settime' - Stack Overflow | https://stackoverflow.com/
Got this error building MySQL 5.6 from source (in the 'libmysql' subdirectory) and this seemed to fix i: add '-lrt' to the end of '-DCMAKE_CXX_FLAGS=' command line option when initially running 'cmake'.
Add -lrt to the end of g++ command line. This links in the librt.so "Real Time" shared library.
build  toolchain  compiler  error  mysqlclient  mysql  solution 
february 2019 by kme
CheatSheetSeries/SQL_Injection_Prevention_Cheat_Sheet.md at master · OWASP/CheatSheetSeries · GitHub | https://github.com/
The OWASP Cheat Sheet Series was created to provide a concise collection of high value information on specific application security topics. - OWASP/CheatSheetSeries
sqlinjection  mysql  webmaster  webdevel  security  bestpractices  reference  cheatsheet 
february 2019 by kme
How to pass password to mysql command line - Unix & Linux Stack Exchange | https://unix.stackexchange.com/
It doesn't look like you can send a password to 'mysql' on stdin, even though that's where it seems to be expecting it from with the '-p' (no password) option.

So I ended up using the '--defaults-file' option.

Another thing to note is that if the password is blank (null), it looks like you have to use '--password=', because '-p' has no way of knowing that you are giving it a blank password (the shell eats the quoted empty string).
mysql  cli  dba  commandline  pipes  subprocess  python  workaround  solution 
november 2018 by kme
MySQL :: Re: repair by sorting? | https://forums.mysql.com/
"Repair by sorting" is MyISAM's way of updating the indexes after a bulk insert. When inserting Millions of rows of data into a MyISAM table, it is normal for the index updates to take longer than the actual inserts.
mysql  dba  errormessage  solution 
november 2018 by kme
innodb - Internal reason for killing process taking up long time in mysql - Database Administrators Stack Exchange | https://dba.stackexchange.com/
Possible causes: InnoDB takes forever to roll back mass inserts. Also, innodb_buffer_pool_size might've been too small (default is only like 128 MB).
innodb  mysql  performance  optimization  errormessage  itsslow  maybesolution 
november 2018 by kme
UCSC MySQL | http://lh3lh3.users.sourceforge.net/
The binning algorithm is in section 5.3 of the SAM specification (https://samtools.github.io/hts-specs/SAMv1.pdf?page=18).
A note on binning

For small tables like refGene, UCSC simply creates MySQL indices on the chromosomal coordinate of a record. For large tables like snp129, UCSC cacluates a bin number for each record and only indexes chromosome name and the bin number without indexing chromosomal coordinates. Doing in this way is faster than simply relying on the MySQL indices and saves disk space as well. Detailed algorithm can be found in Kent et al. (2002) or from the SAM format specification. You can also read the source code of this script to see how the bin number is calculated and used in query.
binning  mysql  ucsc  genomebrowser  dba  optimization 
november 2018 by kme
MySQL show status: How to show open database connections | alvinalexander.com | https://alvinalexander.com/
<code class="language-bash">
$ mysqladmin status
$ mysql dbname -e "show status like '%onn%';"
$ mysql dbname -e "show processlist;"</code>
mysql  dba  mysqladmin  admin  solution 
november 2018 by kme
sql - Can I concatenate multiple MySQL rows into one field? - Stack Overflow | https://stackoverflow.com/
You can use GROUP_CONCAT:
<code class="language-sql">SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies GROUP BY person_id</code>
dba  sql  query  mysql  solution  fuckina 
august 2018 by kme
Remove DEFINER clause from MySQL Dumps - Stack Overflow | https://stackoverflow.com/
Solution for error messages like
ERROR 1449 (HY000) at line 1434: The user specified as a definer ('superman'@'%') does not exist
I don't think there is a way to ignore adding DEFINERs to the dump. But there are ways to remove them after the dump file is created.

- Open the dump file in a text editor and replace all occurrences of DEFINER=root@localhost with an empty string ""

- Edit the dump (or pipe the output) using perl:
<code class="language-bash">perl -p -i.bak -e "s/DEFINER=\`\w.*\`@\`\d[0-3].*[0-3]\`//g" mydatabase.sql</code>

- Pipe the output through sed:
<code class="language-bash">mysqldump ... | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > triggers_backup.sql</code>
mysql  sql  dba  annoyance  errormessage  solution 
july 2018 by kme
MySQL Bugs: #19611: missleading error message while using defaults-extra-file parameter | https://bugs.mysql.com/
The options file handling options (like '--defaults-extra-file' and '--defaults-group-suffix') need to come FIRST on the command line.

This isn't really mentioned in the manual (that I noticed), contrary to what this bug report says.
dba  mysql  mysqldump  errormessage  solution 
may 2018 by kme
MySQL :: MySQL 8.0 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program | https://dev.mysql.com/
The "options file handling" options need to come *first* on the command line, otherwise you get an error like what's described in this bug report[1] like "mysql: unknown variable 'defaults-group-suffix=:whatever'"

[1] https://bugs.mysql.com/bug.php?id=19611
dba  sysadmin  mysql  mysqldump  configfile  optionsfile  tipsandtricks  errormessage  solution 
may 2018 by kme
Strange MySQL database that I can't select - Database Administrators Stack Exchange | https://dba.stackexchange.com/
In my case it was 'autojump's fault; the directory was /var/lib/mysql/.local, and the database that couldn't be selected (with 'mysqldump') was called "#mysql50#.local".
dba  mysql  errormessage  solution 
may 2018 by kme
how do I allow mysql connections through selinux - Server Fault | https://serverfault.com/

To check SELinux
<code class="language-bash">sestatus</code>

To see what flags are set on httpd processes
<code class="language-bash">getsebool -a | grep httpd</code>

To allow Apache to connect to remote database through SELinux
<code class="language-bash">setsebool httpd_can_network_connect_db 1</code>

Use -P option makes the change permanent. Without this option, the boolean would be reset to 0 at reboot.
<code class="language-bash">
setsebool -P httpd_can_network_connect_db 1
</code>

See also https://codedump.io/share/HBJCXpCN1VLr/1/permission-denied-when-trying-to-connect-to-mysql-installing-mediawiki, because I got that exact error message:

<code>
/var/www/html/Wiki/includes/limit.sh: line 61: ulimit: cpu time: cannot modify limit: Permission denied

/var/www/html/Wiki/includes/limit.sh: line 90: ulimit: file size: cannot modify limit: Permission denied</code>

If you want to create a policy file that allows whatever was failing to not fail, try this:

<code class="language-bash">grep httpd /var/log/audit/audit.log | audit2allow -M mypol
semodule -i mypol.pp</code>
selinux  centos  apache  httpd  mysql  security  sysadmin  webmaster  solution  errormessage 
may 2018 by kme
MySQL :: MySQL 5.5 Reference Manual :: 2.11.1 Upgrading MySQL | https://dev.mysql.com/
<code class="language-bash">
mysqldump -u root -p \
--add-drop-table --routines --events \
--all-databases --force > data-for-upgrade.sql

# then
# (possibly /usr/libexec/mysqld, if there's no mysqld_safe script)
mysqld_safe --user=mysql --datadir=/path/to/5.5-datadir

# then
mysql -u root -p --force < data-for-upgrade.sql

# finally
mysql_upgrade -u root -p
</code>
mysql  migration  centos  sysadmin  dba  reference  solution 
may 2018 by kme
sql server 2008 r2 - Referencing a calculated column in the where clause SQL - Stack Overflow | https://stackoverflow.com/
There is no way to use a calculated column as a WHERE condition. Either repeat the expression, use a derived table, or whatever.
dba  sql  mysql  sortof  solution 
april 2018 by kme
How to add levenshtein function in mysql? - Stack Overflow | https://stackoverflow.com/
NB: ENDIF / ENDWHILE needs to be "END IF" / "END WHILE" in MySQL 5.x.

<code class="language-sql">DELIMITER $$

CREATE FUNCTION levenshtein( s1 varchar(255), s2 varchar(255) ) returns int deterministic
BEGIN
DECLARE s1_len,
s2_len,
i,
j,
c,
c_temp,
cost int;
declare s1_char char;
-- max strlen=255
declare cv0,
cv1 varbinary(256);
set s1_len = char_length(s1),
s2_len = char_length(s2),
cv1 = 0x00,
j = 1,
i = 1,
c = 0;
if s1 = s2 THEN
RETURN 0;
elseif s1_len = 0 THEN
RETURN s2_len;
elseif s2_len = 0 THEN
RETURN s1_len;
else
WHILE j <= s2_len do
SET cv1 = concat(cv1, unhex(hex(j))),
j = j + 1;
endWHILE;WHILE i <= s1_len do
SET s1_char = substring(s1, i, 1),
c = i,
cv0 = unhex(hex(i)),
j = 1;WHILE j <= s2_len do
SET c = c + 1;IF s1_char = Substring(s2, j, 1) then
SET cost = 0;
else
SET cost = 1;ENDIF;SET c_temp = conv(hex(substring(cv1, j, 1)), 16, 10) + cost;IF c > c_temp then
SET c = c_temp;ENDIF;SET c_temp = conv(hex(substring(cv1, j+1, 1)), 16, 10) + 1;IF c > c_temp then
SET c = c_temp;ENDIF;SET cv0 = concat(cv0, unhex(hex(c))),
j = j + 1;ENDWHILE;SET cv1 = cv0,
i = i + 1;ENDWHILE;ENDIF;RETURN c;
END;

DELIMITER ;</code>
dba  mysql  database  stringmatching  editdistance  sql  solution 
april 2018 by kme
permissions - MySQL error 1449: The user specified as a definer does not exist - Stack Overflow | https://stackoverflow.com/
In my case, the mysql.proc table had definers from some other database, probably because I'd imported a SQL dump where I'd accidentally included stored procedures in addition to just the *one* table I wanted.

This fixes that.
How to change the definer for views

Run this SQL to generate the necessary ALTER statements

<code class="language-sql">SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW ",
table_name, " AS ", view_definition, ";")
FROM information_schema.views
WHERE table_schema='your-database-name';</code>
sql  mysql  dba  storedprocedures  errormessage  solution 
april 2018 by kme
python - Dealing with duplicate primary keys on insert in SQLAlchemy (declarative style) - Stack Overflow
You should handle every IntegrityError the same way: roll back the transaction, and optionally try again. Some databases won't even let you do anything more than that after an IntegrityError. You could also acquire a lock on the table, or a finer-grained lock if the database allows it, at the beginning of the two conflicting transactions.

Using the with statement to explicitly begin a transaction, and automatically commit (or rollback on any exception):
<code class="language-python">from schema import Session
from schema.models import Bike

session = Session()
with session.begin():
pk = 123 # primary key
bike = session.query(Bike).filter_by(bike_id=pk).first()
if not bike: # no bike in DB
new_bike = Bike(pk, "shiny", "bike")
session.add(new_bike)</code>
sql  sqlalchemy  mysql  dba 
april 2018 by kme
MySQL Bugs: #5159: Views: Comments are not preserved | https://bugs.mysql.com/
This feature request is 18 years old now. People still express interest about this and other related feature requests. You may not consider it vital, but please don't understimate what Bruno Aquino Filardi Filho wrote: self documenting database is really important.
mysql  sql  views  documentation  comments  featurerequest  bug  watchthisspace 
april 2018 by kme
mysql - Increment autoincrement id field by one - Stack Overflow | https://stackoverflow.com/
I see no good reason for this. Only problems. Before running the folowing statement, check if you have FOREIGN keys defined, that reference this id. Are they set to ON UPDATE CASCADE? Also, do you have any triggers that are related to this table?

But first consider, why you (think you) need this. Is it going to be used for ordering the table? In that case, as @Mark pointed, you should use a separate column to specify your desired order.

If, however, you decide you really want this, use:
<code class="language-sql">
UPDATE myTable
SET id = id + 1
WHERE id >= 53
ORDER BY id DESC;</code>
mysql  sql  dba  primarykey  autoincrement  solution 
march 2018 by kme
mysql - Differences between "Unique Key" and "Primary Key" - Database Administrators Stack Exchange | https://dba.stackexchange.com/
A table can have at most one PRIMARY KEY constraint but it can have as many as you want UNIQUE KEY constraints.

Columns that are part of the PRIMARY KEY must be defined as NOT NULL. That is not required for columns that are part of UNIQUE KEY constraints. If the columns are not Nullable, then there is no difference between Unique and Primary Keys.

Another minor difference is that you can choose the name you want for a UNIQUE KEY constraint (and index). On the other hand, the PRIMARY KEY has the default name: PRIMARY.

Also helpful:

Most important difference is in their purpose.

* Primary Key: Purpose of Primary Key is to act as a “KEY”. A primary key is a key in a relational database used to identify records.
* Unique Index: Unique Index is an “INDEX” intended for performance. Optimizer knows that for a condition "where x =:x” there will be only one record as a result – so it can prepare a plan suitable for that.
* Unique Constraint: It is a “CONSTRAINT” which makes sure that there are no duplicate values in that column. It is a constraint for data integrity.

Apart from their purpose, following points are noteworthy.

* Unless otherwise specified PRIMARY KEY will try to create a CLUSTERED INDEX (This point is about SQL Server though, as mentioned in the comment)
* There can be only one PRIMARY KEY per table; but there can be many unique constraints and unique indexes
* PRIMARY KEY is always not null but columns with unique constraint can hold NULL values
dba  primarykey  database  mysql  sql  thisvsthat  newbie  answered 
march 2018 by kme
Mechanics of Flite - Hash-based workarounds for MySQL unique constraint limitations | http://mechanics.flite.com/
In the past I have written about MySQL’s limitations that prevent you from creating an index over a certain length, or with more than a certain number of columns. I’ve offered some solutions for those problems, but many of my solutions hit another limit further down the line. For example innodb_large_prefix allows you to create an index on larger columns, but only up to 3072 bytes, and my workarounds for the 16 column limit per index will also hit that 3072 byte limit once you add lots of columns or add some very long columns.
mysql  indexing  primarykey  workaround  dba  webdevel  devel  solution 
march 2018 by kme
python - SQLAlchemy blocked on dropping tables - Stack Overflow | https://stackoverflow.com/
In my case the "waiting for table metadata lock" was because I had an uncommitted session in another IPython session.

In another case, it was exactly as described here; I had to 'session.close()' before I did 'Model.metadata.drop_all()'


call session.close() (or commit(), or rollback()) before you do the drop_all(). the session is still sitting on an open transaction.

the tutorial is against sqlite which doesn't have aggressive table locking (I'm assuming your MySQL DB is using InnoDB here).
python  dba  sqlalchemy  mysql  errormessage  solution 
march 2018 by kme
How do you set a default value for a MySQL Datetime column? - Stack Overflow

In version 5.6.5, it is possible to set a default value on a datetime column, and even make a column that will update when the row is updated. The type definition:

<code class="language-sql">CREATE TABLE foo (
`creation_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP
)</code>
mysql  dba  timestamps  dateandtime  mysql56  sortof  solution 
march 2018 by kme
MySQL Bugs: #45634: --init-connect option for mysql CLI | https://bugs.mysql.com/
It doesn't seem like you can put 'init_command' in an option file, though.
dba  mysql  bug 
march 2018 by kme
database - Update MySQL version from 5.1 to 5.5 in CentOS 6.2 - Stack Overflow | https://stackoverflow.com/
You can use a yum plugin made by the IUS community to switch mysql-libs with mysql55w-libs. (the following assuming you still use Webtatic)
<code class="language-bash">yum install mysql yum-plugin-replace
yum replace mysql --replace-with mysql55w</code>

That should allow you to replace mysql with mysql55w-libs, whilst maintaining the older libmysqlclient for any dependencies.
yum  centos5  mysql  centos55  dba  sysadmin  tipsandtricks  maybesolution 
february 2018 by kme
[CentOS-announce] Announcing release for MySQL 5.5 and 5.6 on CentOS Linux 6 x86_64 SCL | https://lists.centos.org/
Both the collections include no client library libmysqlclient.so and client applications are advised to use the client library available in mysql-libs package from base system.
mysql  mysql55  centos6  libmysqlclient  library  build  errormessage  annoyance  solution 
february 2018 by kme
mysql - Insert auto increment primary key to existing table - Stack Overflow | https://stackoverflow.com/
This is interesting, but I don't know how it could work:

You can add a new Primary Key column to an existing table, which can have sequence numbers, using command:
<code class="language-sql">ALTER TABLE mydb.mytable ADD pk_columnName INT IDENTITY
</code>
dba  mysql  sql  newbie  syntax  primarykey  altertable  solution 
february 2018 by kme
MySQL's INSERT IGNORE INTO & foreign keys - Stack Overflow | https://stackoverflow.com/
INSERT IGNORE turns INSERTS that don't meet a primary key constraint into warnings (try SHOW WARNINGS right after0.
dba  mysql  sql  newbie  solution 
february 2018 by kme
MySQL :: MySQL 5.7 Reference Manual :: 10.3.1 Collation Naming Conventions | https://dev.mysql.com/
<code>Table 10.1 Collation Case Sensitivity Suffixes
Suffix Meaning
_ai Accent insensitive
_as Accent sensitive
_ci Case insensitive
_cs case-sensitive
_bin Binary</code>
mysql  dba  collation  casesensitivity  reference  solution 
february 2018 by kme
mysql - Roughly how long should an "ALTER TABLE" take on an 1.3GB INNODB table? - Server Fault | https://serverfault.com/
My problem was a pending metadata lock; see https://stackoverflow.com/a/23757255/785213.

I actually just needed to stop the web server, because the Flask-Admin application had a lock on the table metadata, and I was trying to ALTER TABLE changing column data types... wouldn't let me.
dba  mysql  performance  altertable 
february 2018 by kme
mysql - How do I find which transaction is causing a "Waiting for table metadata lock" state? - Stack Overflow | https://stackoverflow.com/
All I actually had to do was stop the web server. The Flask-Admin web application had a lock on the table metadata, and I was trying to ALTER TABLE changing the data type/size of the columns. No go while the application was running, and that makes sense in retrospect.
If you cannot find the process locking the table (cause it is alreay dead), it may be a thread still cleaning up like this

section TRANSACTION of
<code class="language-sql">show engine innodb status;</code>

at the end
<code>---TRANSACTION 1135701157, ACTIVE 6768 sec
MySQL thread id 5208136, OS thread handle 0x7f2982e91700, query id 882213399 xxxIPxxx 82.235.36.49 my_user cleaning up</code>

as mentionned in a comment in Clear transaction deadlock?

you can try killing the transaction thread directly, here with
<code class="language-sql">KILL 5208136;</code>

worked for me.
dba  mysql  sql  processmanagement  solution  flask-admin 
february 2018 by kme
How to connect MySQL database using Python+SQLAlchemy remotely? - Stack Overflow | https://stackoverflow.com/
n my setup (I'm using mysql-python) just using 127.0.0.1 instead of localhost in the MySQL SQLAlchemy url works. The complete url I'm using exactly for that scenario (tunnel with local port 3307) is:

mysql:/user:passwd@127.0.0.1:3307/

I'm using SQLAlchemy 1.0.5, but I guess that doesn't matter too much...
python  mysql  dba  networking  sqlalchemy  errormessage  annoyance  solution 
february 2018 by kme
mysql - Is it not possible to show warnings when using mysqlimport? - Stack Overflow | https://stackoverflow.com/
I used the mysql CLI tool, passing an equivalent "LOAD DATA ..." command followed by a semicolon, followed by "SHOW WARNINGS". The resulting display is helpful in finding detail on each warning.

For example:
<code class="language-bash">mysql <options> -e "LOAD DATA LOCAL INFILE 'foo' INTO TABLE bar; SHOW WARNINGS"</code>

P.S.: You will probably also need to add the '--local-infile=1' option or you'll get an error message about something not being supported in this version of MySQL.

What worked (finally) for me was:
<code class="language-sql">
mysql --login-path=dbserver --local-infile=1 --protocol=TCP \
-e 'load data local infile "zz_ensembl_genes.tsv" into table zz_ensembl_genes; show warnings;' \
the_database
</code>
dba  mysql  importexport  solution  debugging  logging  errormessage 
february 2018 by kme
MySQL :: MySQL Workbench Manual :: 5.3.5 Remote Management | https://dev.mysql.com/
The "SSH login based management" is greyed out if the host is "localhost" or 127.0.0.1. Workaround: add an alias called something else ("dev.local", "localhoerst", whatever) to /etc/hosts. Voila!
dba  mysql  mysqlworkbench  annoyance  workaround  solution 
february 2018 by kme
Table comment length in MySQL - Stack Overflow | https://stackoverflow.com/
Doh! It gets silently truncated after an export / import!

Solution: install MySQL 5.5.something (>3).
MySQL 5.1 CREATE TABLE reference: • Tables up to 60 characters. • Columns up to 255 characters. || MySQL 5.5 CREATE TABLE reference: • Tables up to 2048 characters (60 characters before MySQL 5.5.3). • Columns up to 1024 characters (255 characters before MySQL 5.5.3). – Birchlabs Jan 4 at 19:57
mysql  mysql51  dba  database  annoyance  solution 
february 2018 by kme
database - what is a reasonable value for max_allowed_packet for Drupal 7 with moderate traffic? - Drupal Answers | https://drupal.stackexchange.com/
Regarding your situation, you should find out what is the biggest BLOB in your database, multiple that number by 11 and set your max_allowed_packet to that number. You should be able to set it for the server without a mysql restart (Personally, I would set it 256M because it would address other problems regarding migration and replication, which is beyond the scope of this forum). To set it to 256M for your database for all incoming connections, please run this:

<code class="language-sql">SET GLOBAL max_allowed_packet = 1024 * 1024 * 256;</code>

Afterwards, add this setting to my.cnf under the [mysqld] section:
<code class="language-ini">
[mysqld]
max_allowed_packet = 256M</code>
mysql  performance  errormessage  my.cnf  configuration  dba  maybesolution 
february 2018 by kme
Can error 1231 after importing a MySQL database be a compatibility issue? - Database Administrators Stack Exchange | https://dba.stackexchange.com/
This really did seem to have something to do with max_allowed_packet, because it worked between two VMs where they were both set to 64M, and failed on the MySQL server where it was only set to 16M.

Maybe try:
<code class="language-sql">/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHAR */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLL */;</code>
mysql  importexport  errormessage  dba  maybesolution 
february 2018 by kme
MySQL Count distinct values from one column - Stack Overflow | https://stackoverflow.com/
Have you tried
<code class="language-sql">SELECT COUNT(DISTINCT B) FROM mytable WHERE C = 2;</code>
dba  sql  syntax  mysql  dammitbrain  newbie  solution 
february 2018 by kme
Find duplicate records in MySQL - Stack Overflow | https://stackoverflow.com/
The key is to rewrite this query so that it can be used as a subquery.
<code class="language-sql">
SELECT firstname,
lastname,
list.address
FROM list
INNER JOIN (SELECT address
FROM list
GROUP BY address
HAVING COUNT(id) > 1) dup
ON list.address = dup.address;
</code>


Also useful
<code class="language-sql">SELECT date FROM logs group by date having count(*) >= 2
</code>

but note
Be careful with this answer. It returns only one of the duplicates. If you have more than 2 copies of the same record you wont see them all, and after deleting the record returned you will still have duplicates in your table.
dba  sql  mysql  datamunging  duplicates  solution 
february 2018 by kme
mysql command for showing current configuration variables - Stack Overflow | https://stackoverflow.com/
What you are looking for is this:
<code class="language-sql">SHOW VARIABLES;</code>

You can modify it further like any query:
<code class="language-sql">SHOW VARIABLES LIKE '%max%';</code>
dba  mysql  sql  syntax  newbie  dammitbrain  solution 
january 2018 by kme
MySQL :: MySQL 5.6 Reference Manual :: 20.5.3 Updatable and Insertable Views | https://dev.mysql.com/
I think MySQL Workbench (6.x, as of 31 January 2018) can't deal with updateable views, regardless. Sequel Pro works like a champ though.

See this related bug: https://bugs.mysql.com/bug.php?id=83195
dba  mysql  sql  views  reference  updateableviews 
january 2018 by kme
sql - How to get the next auto-increment id in mysql - Stack Overflow | https://stackoverflow.com/
Two solutions, but only the second works if you just TRUNCATEd the table:

<code class="language-sql">SELECT LAST_INSERT_ID()</code>

<code class="language-sql">
SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'table_name'
AND table_schema = DATABASE( ) ;

-- or if you do not wish to use information_schema you can use this

SHOW TABLE STATUS LIKE 'table_name'
</code>
dba  mysql  sql  syntax  newbie  solution 
january 2018 by kme
mysql - How to truncate a foreign key constrained table? - Stack Overflow | https://stackoverflow.com/
<code class="language-sql">SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;</code>

This will handily reset the AUTO_INCREMENT as well, while maintaining the table structure and comments.
mysql  dba  sql  syntax  newbie  solution 
january 2018 by kme
php - SQL Update query based on another table's field - Stack Overflow | https://stackoverflow.com/
In MySQL, you can use join with update. This is how I interpret your question:

<code class="language-sql">update t1_users u join
t2_settings s
on u.uid = s.value and s.name = 'beta_givebeta'
set t1.beta = 'yes';</code>
sql  mysql  syntax  maybesolution 
january 2018 by kme
mysql - SQL set values of one column equal to values of another column in the same table - Stack Overflow | https://stackoverflow.com/
Sounds like you're working in just one table so something like this:
<code class="language-sql">
update your_table
set B = A
where B is null</code>
mysql  sql  newbie  syntax  solution 
january 2018 by kme
sql - Can a stored procedure/function return a table? - Stack Overflow | https://stackoverflow.com/
In MySQL 5.x, a stored procedure can't be in a FROM clause. Apparently this is OK in SQL Server and Postgres, though.
dba  sql  mysql  annoyance  hellopostgres 
january 2018 by kme
« earlier      
per page:    204080120160

Copy this bookmark:





to read