recentpopularlog in

kme : dba   334

« 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 
6 weeks ago 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 
7 weeks ago 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 
7 weeks ago 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 
7 weeks ago 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 
7 weeks ago 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 
8 weeks ago by kme
Problems with sort order (UTF8 locales don't work) · Issue #216 · PostgresApp/PostgresApp
you can see the reason for this with ls -l /usr/share/locale/de_DE.UTF-8 you see that LC_COLLATE only symlinks to la_LN.US-ASCII. You get the same if you sort something on the shell, so it's a OS specific, not PG specific problem. AFAIR this affects all BSD OS.

Some ML posts of Tom Lane in that topic:

http://www.postgresql.org/message-id/16510.1263450305@sss.pgh.pa.us
http://www.postgresql.org/message-id/22721.1264203310@sss.pgh.pa.us
http://www.postgresql.org/message-id/23053.1337036410@sss.pgh.pa.us

It seems we will have to wait for http://wiki.postgresql.org/wiki/Todo:ICU , use a different OS or use a function for sorting, that can internally use ICU form pl/perl, unaccent contrib, or your own implementation.

To me this is the biggest drawback of using PostgresApp (PostgreSQL on OSX in general) and something that should be clearly highlighted in the documentation.

@macarthy in principle, yes. You'd just need to create a new database with the collation. However, as a word of caution, UTF-8 locales seem to be fundamentally broken on OSX. Postgres uses the strcoll API, which unfortunately does not support multibyte encodings on OSX.
macos  collation  unicode  sorting  brokenness  postgresql  dba 
11 weeks ago by kme
Python SQLite: database is locked - Stack Overflow
Got this message when trying to use my 'ffhist' Python script while Firefox was open.
python  sqlite  dba  database  locking  errormessage 
june 2019 by kme
Floating windows not docking in main window · Issue #984 · sqlitebrowser/sqlitebrowser · GitHub | https://github.com/
Details for the issue I can't get a floating window to dock at the main window's borders. Hovering the dock above the main window has no effect. Useful extra information Double clicking on the title bar of a floating window correctly doc...
sqlitebrowser  qt  gui  sqllite  dba  database  bug  annoyance  needshelp 
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
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
python - SQLAlchemy default DateTime - Stack Overflow | https://stackoverflow.com/
You could use 'server_default=func.now()', but that will calculate a timestamp once per transaction, which may not be what you want.

This probably works fine:

<code class="language-python">
import datetime
from sqlalchemy import Column, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Test(Base):
__tablename__ = 'test'

id = Column(Integer, primary_key=True)
created_date = Column(DateTime, default=datetime.datetime.utcnow)

</code>
python  sqlalchemy  sql  timeanddate  timestamp  dba  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
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
python - get table columns from sqlAlchemy table model - Stack Overflow | https://stackoverflow.com/
You get all of the columns from __table__.columns:
<code class="language-python">myTable.__table__.columns</code>

or
<code class="language-python">myTable.__table__.c</code>

The columns would be in format myTable.col1 (table name is included). If you want just column names, get the .key for each column:
<code class="language-python">
[column.key for column in myTable.__table__.columns]
</code>
python  sqlalchemy  dba  sql  solution  reference  dammitbrain 
august 2018 by kme
Sqlalchemy: Truncate all tables | https://gist.github.com/
In order to get the Table instance, use ModelName.__table__. The '.delete()' method on the Table is the same as a SQL 'TRUNCATE TABLE tablename', except you don't have to do any introspection (or hard-code) the table name.

<code class="language-python">
def truncate_db(engine):
# delete all table data (but keep tables)
# we do cleanup before test 'cause if previous test errored,
# DB can contain dust
meta = MetaData(bind=engine, reflect=True)
con = engine.connect()
trans = con.begin()
con.execute('SET FOREIGN_KEY_CHECKS = 0;')
for table in meta.sorted_tables:
con.execute(table.delete())
con.execute('SET FOREIGN_KEY_CHECKS = 1;')
trans.commit()</code>
sqlalchemy  dba  database  sql  python  solution 
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
GitLab.com database incident | GitLab | https://about.gitlab.com/
Yesterday we had a serious incident with one of our databases. We lost six hours of database data (issues, merge requests, users, comments, snippets, etc.) for GitLab.com. Git/wiki repositories and self-hosted installations were not affected. Losing production data is unacceptable and in a few days we'll publish a post on why this happened and a list of measures we will implement to prevent it happening again.
backupandrecovery  backups  sysadmin  dba  database  fail  postmortem 
june 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
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
The GITS Blog » Using custom functions with SQLAlchemy and SQLite | http://ginstrom.com/
I think this is too old to work with current (1.2.x) versions of SQLAlchemy. I don't see any documentation for connection.create_function.
sqlalchemy  customfunction  dba  database  maybesolution 
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
python - method of iterating over sqlalchemy model's defined columns? - Stack Overflow | https://stackoverflow.com/
From here you can simply call JobStatus.__table__.columns._data.keys() which gives you a nice, clean list.

Also:
<code class="language-python">
from sqlalchemy import inspect
inspect(JobStatus).columns.keys()
</code>
sqlalchemy  sql  dba  introspection  solution 
march 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 - Is primary key unique by default? - Stack Overflow | https://stackoverflow.com/
Primary key is always unique in every SQL. You dont have to explicitly define it as UNIQUE.

On a side note: You can only have one Primary key in a table and it never allows null values. Also you can have only one primary key constraint in the table(as the point of creating a primary key is to uniquely identify the row in your table) but you can more than one unique key constraint in your table.

Example:

An employee details table having EmpID as Primary key and EmpPhoneNo as unique key.
database  dba  sql  newbie  question  answered  primarykey 
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
python - Using a different schema for the same declarative Base in sqlalchemy - Stack Overflow | https://stackoverflow.com/
Adding this
<code class="language-python">__abstract__ = True</code>
to the base "Base" class fixed the problem I was having, I think.

The error message I got was
sqlalchemy.exc.InvalidRequestError: Class <class 'models.BaseModel'> does not have a __table__ or __tablename__ specified and does not inherit from an existing table-mapped class.
Class <class 'models.BaseModel'> does not have a __table__ or __tablename__ specified and does not inherit from an existing table-mapped class.
sqlalchemy  webdevel  dba  database  python  abstractbaseclass  maybesolution 
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
Basic Relationship Patterns — SQLAlchemy 0.9 Documentation | http://docs.sqlalchemy.org/
The bidirectional version adds backrefs to both relationships:
<code class="language-python">
class Association(Base):
__tablename__ = 'association'
left_id = Column(Integer, ForeignKey('left.id'), primary_key=True)
right_id = Column(Integer, ForeignKey('right.id'), primary_key=True)
extra_data = Column(String(50))
child = relationship("Child", backref="parent_assocs")

class Parent(Base):
__tablename__ = 'left'
id = Column(Integer, primary_key=True)
children = relationship("Association", backref="parent")

class Child(Base):
__tablename__ = 'right'
id = Column(Integer, primary_key=True)
</code>

Working with the association pattern in its direct form requires that child objects are associated with an association instance before being appended to the parent; similarly, access from parent to child goes through the association object:
<code class="language-python"># create parent, append a child via association
p = Parent()
a = Association(extra_data="some data")
a.child = Child()
p.children.append(a)

# iterate through child objects via association, including association
# attributes
for assoc in p.children:
print assoc.extra_data
print assoc.child</code>
sqlalchemy  dba  webdevel  flask-admin  samplecode  maybesolution 
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
sql - Update one MySQL table with values from another - Stack Overflow | https://stackoverflow.com/

<code class="language-sql">UPDATE tobeupdated
INNER JOIN original ON (tobeupdated.value = original.value)
SET tobeupdated.id = original.id</code>

That should do it, and really its doing exactly what yours is. However, I prefer 'JOIN' syntax for joins rather than multiple 'WHERE' conditions, I think its easier to read

As for running slow, how large are the tables? You should have indexes on tobeupdated.value and original.value

EDIT: we can also simplify the query
<code class="language-sql">UPDATE tobeupdated
INNER JOIN original USING (value)
SET tobeupdated.id = original.id
</code>

USING is shorthand when both tables of a join have an identical named key such as id. ie an equi-join - http://en.wikipedia.org/wiki/Join_(SQL)#Equi-join
dba  sql  innerjoin  solution  newbie 
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
« earlier      
per page:    204080120160

Copy this bookmark:





to read