recentpopularlog in

kme : database   199

« earlier  
UTF8, MySQL, Perl and PHP (Phil Gyford: Writing)
Only these two together seemed to work for me (CentOS 7, using vendor-supplied perl-DBD-MySQL-4.023-6.el7.x86_64):

<code class="language-perl">$dbh->{'mysql_enable_utf8'} = 1;
$dbh->do('SET NAMES utf8');</code>

The error message I would get otherwise was
SQL execution failed: COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1' (1253)

Even if the table, server, column, collation were all 'utf8'. The *client* was the missing part, and 'SET NAMES utf8' fixes that, seemingly.
perl  dbd  dbi  database  errormessage  devel  solution 
8 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 
8 weeks ago by kme
python - Is it possible to open a locked sqlite database in read only mode? - Stack Overflow
SQLITE_OPEN_READONLY flag - look into this. Probably only part of the C API, which Python's sqlite3 won't necessarily support.

This is the solution I ended up using:
You could try just copying the database file (e.g., with the system utility cp) and using that snapshot for reading purposes
python  sqlite  concurrency  database  locking  errormessage  maybesolution 
june 2019 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 |
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
Sqlalchemy: Truncate all tables |
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('SET FOREIGN_KEY_CHECKS = 1;')
sqlalchemy  dba  database  sql  python  solution 
august 2018 by kme database incident | GitLab |
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 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
How to add levenshtein function in mysql? - Stack Overflow |
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
DECLARE s1_len,
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
elseif s1_len = 0 THEN
RETURN s2_len;
elseif s2_len = 0 THEN
RETURN s1_len;
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;
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,

DELIMITER ;</code>
dba  mysql  database  stringmatching  editdistance  sql  solution 
april 2018 by kme
The GITS Blog » Using custom functions with SQLAlchemy and SQLite |
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
MySql - Is primary key unique by default? - Stack Overflow |
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.


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 |
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
python - Using a different schema for the same declarative Base in sqlalchemy - Stack Overflow |
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
Table comment length in MySQL - Stack Overflow |
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
How to import CSV file to MySQL table - Stack Overflow |

phpMyAdmin can handle CSV import. Here are the steps:

1. Prepare the CSV file to have the fields in the same order as the MySQL table fields.
2. Remove the header row from the CSV (if any), so that only the data is in the file. (not necessary --Ed.)
3. Go to the phpMyAdmin interface.
4. Select the table in the left menu.
5. Click the import button at the top.
6. Browse to the CSV file.
7. Select the option "CSV using LOAD DATA".
8. Enter "," in the "fields terminated by".
9. Enter the column names in the same order as they are in the database table.
10. Click the go button and you are done.

This is a note that I prepared for my future use, and sharing here if someone else can benefit.
database  dba  mysql  import  automation  csv  tabdelimited  solution  importexport 
january 2018 by kme
sql - Using a table to provide enum values in MySQL? - Stack Overflow |
The enum type is handy as a one-off, but it doesn't scale well to multiple tables and isn't standard SQL either. Best thing to do here is to use normal tables and relations:

Define a new table to hold the list of possible values; let's call it Master1
In the other two tables (let's call them Table1 and Table2), don't make the field an enum; just make it a normal field with a foreign key relation to Master1.

The foreign key relation will do the job of restricting to a list of possible values; and because foreign keys and relations are absolutely standard SQL, this approach will have other benefits - for example reporting tools can recognise the foreign key and understand how to use the related data.
dba  database  mysql  foreignkey  constraint  solution 
december 2017 by kme
python - Sqlacodegen generates mixed models and tables - Stack Overflow |
It looks like what you're describing is a feature itself. sqlacodegenwill not always generate class models.

It will only form model classes for tables that have a primary key and are not association tables, as you can see in the source code:

<code class="language-python">
# Only form model classes for tables that have a primary key and are not association tables
if noclasses or not table.primary_key or in association_tables:
model = self.table_model(table)
model = self.class_model(table, links[], self.inflect_engine, not nojoined)
classes[] = model

Furthermore, in the documentation it is stated that

A table is considered an association table if it satisfies all of the following conditions:

has exactly two foreign key constraints
all its columns are involved in said constraints
python  flask-admin  flask  sqlalchemy  dba  database  model 
december 2017 by kme
Howard Chu - LMDB [The Databaseology Lectures - CMU Fall 2015] - YouTube |
For icing on the cake, packages build by default with useful compile-time features such as consistent use of the ELF rpath header, allowing programs to run in the target environment without requiring LD_LIBRARY_PATH to be set at all, since the built binaries contain internal annotations telling the runtime linker where its library dependencies are to be found.
openldap  lmdb  database  dba  conference  talk 
november 2017 by kme
python 2.7 - Is possible to mapping view with class using mapper in SqlAlchemy? - Stack Overflow
<code class="lang-python">from sqlalchemy import orm

class ViewName(object):
def __init__(self, name): = name

orm.mapper(ViewName, v, primary_key=[])

Session = orm.sessionmaker(bind=engine)
for r in Session().query(ViewName):
print, r.number</code>
sqlalchemy  flask  dba  database  view  solution 
june 2017 by kme
How to disable secure-file-priv MySQL Ubuntu - Super User
The mysql documentation on secure_file_priv states that the variable is set to a default, platform-specific filesystem location, to make it secure by default.

That said, you need to explicitly set the variable to an empty string in your configuration to disable this security feature.

For Ubuntu, edit the file /etc/mysql/mysql.conf.d/mysqld.cnf and add the following line at the end:
mysql  dba  database  ubuntu  debian  linux  errormessage  annoyance  configfile  solution 
january 2017 by kme
agate 1.5.5 — agate 1.5.5 documentation
This project gets the "landing page" for their documentation exactly right. It has links directly to the docs, repo, and issue tracker above the fold on index page for the documentation.
python  sql  database  dba  library  documentation  inspiration 
january 2017 by kme
« earlier      
per page:    204080120160

Copy this bookmark:

to read