Optimised Schema for City-to-City Distance Table

Most database management systems have a hard limit on either the number of columns you're allowed to use, the number of bytes in a row, or both. So your single table won't work in the general case, because you'll eventually either end up with either too many columns or too many bytes in a row.

To find out what the limitations are for your platform, Google something like

SQL Server limitations
PostgreSQL limitations
Oracle limitations

All you need to store your data is a single table of distances. A table of cities can be used to validate the values in the table of distances, but it's not essential. (I'd use one, though.)

But this won't work in the general case either, because it's subject to the same limitations as a base table. To see how it works for relatively small data sets, look at this example. And look closely at the CHECK() constraints.

create table distances (
  city_from varchar(25) not null,
  city_to varchar(25) not null,
  check (city_from < city_to),
  distance integer not null check (distance >= 0),
  primary key (city_from, city_to)
);

insert into distances values
('Bangalore', 'Chennai', 100),
('Bangalore', 'Hyderabad', 200),
('Bangalore', 'Mumbai', 300),
('Bangalore', 'Delhi', 400),
('Bangalore', 'Kolkata', 500),
('Chennai', 'Hyderabad', 150),
('Chennai', 'Mumbai', 250),
('Chennai', 'Delhi', 450),
('Chennai', 'Kolkata', 550);

Now you can create a pivot table with a SELECT statement. (SQL Server. Other dbms have different ways of dealing with pivot tables.)

SELECT *
FROM (
  SELECT city_from, city_to, distance
  FROM distances
  union all
  SELECT city_to, city_from, distance
  FROM distances
) AS t1
PIVOT
(
  max(distance)
  FOR [city_to]
  IN (
    [Bangalore], [Chennai], [Delhi], [Hyderabad], [Kolkata], [Mumbai]
  )
) AS t2
ORDER BY city_from;

Because of the limitations on the number of columns and the number of bytes per row, I think you're better off returning the base table (with or without the UNION ALL that you see in the query above) to the application, and letting the application format it for display. Application code doesn't generally have limits on the number of columns or the number of bytes.


Mysql Slow when join query running increaed

With MyISAM, an INSERT/UPDATE/DELETE will request an exclusive lock, and have to wait for any SELECTs to finish. If another SELECT comes in after the write, then it will be blocked waiting for the write to get its lock and finish its action.

That is, one simple write can snowball into the mess you are seeing.

Changing to InnoDB is likely to avoid the problem, since locks are taken at the row level, not the table level.

Let's see the EXPLAIN for your SELECT (and provide one that does not have syntax errors).  Plus tell us how big the table is.  And SHOW CREATE TABLE for each relevant table.

Speeding up the query will help avoid the locks.


Can I reduce MySQL memory usage, or do I need to add more RAM?

Strictly speaking the calculation of the recommended buffer pool size above is not correct. It assumes InnoDB works actively with all data. In practice, InnoDB could only touch a fraction of the database.

Check how Innodb_buffer_pool_reads and Innodb_buffer_pool_read_requests changes over time.

mysqladmin -r -i 1 ext | grep -e Innodb_buffer_pool_read_requests -e Innodb_buffer_pool_reads

if Innodb_buffer_pool_reads doesn't change much then all working set (a part of the database InnoDB actively works with) fits in the buffer pool.

Ideally the buffer pool should be as large so Innodb_buffer_pool_reads per second is zero.

You can make the buffer pool smaller, then some reads will be served from the disk, not from the memory.


Data Model: Parent, Child, Grandchild with Child Being Optional

Ask yourself what is the difference between a Department and a Section?  Are they just organizational objects that may contain either employees or other, smaller organizational objects?  Do they have any other differences in terms of their relationships and attributes?

If the answer is no, then you might want to consider replacing both with a single organization table containing an unleveled hierarchy.  This can be represented with an involuted (self-referencing) foreign key (i.e. part_of or reports_to etc.)

Of course, in a relational database, unleveled hierarchies can be awkward to work with.  If you decide to go this route, you should do some research on hierarchical data management techniques, like visitation numbers or adjacency lists.

If you really need to keep Department and Section separate because they have markedly different definitions, then you will need to consider implementing a dummy or null Section for each Department to handle the situation where there is no analogue in reality.  If you go this route, you could consider adding a flag or some other indicator to differentiate between real Sections and pro-forma Sections.


Issues regarding phpMyAdmin - character set

MySQL did not have utf8mb4 until version 5.5.3; are both versions newer than that?

Is the exported file a text file?  If so, look in it for utf8 and see if it says utf8 or utf8mb4.

Did you use any "character set" argument on mysqldump when exporting?  What about on importing?  For mysql and phpmyadmin it needs to be utf8mb4.  For the rest of the world, UTF-8 or maybe UTF8.


Some sort of benevolent magic occurred after doing the following:

Remove MongoDB Community Edition as a Windows Service, see here.

sc.exe delete MongoDB

Then when I went into my favourite MongoDB GUI (Robo3T), the databases were just there!

So i ran:

mongodump

To get a "proper" dump of all my databases.

I'm confused how this could happen because the databases are still at their original location:

C:\data\

And the new MongoDB installation data location was:

C:\Program Files\MongoDB\Server\4.0\data\

¯_(ツ)_/¯

Atleast I have a proper mongodump backup now.


MongoDB Reverse SSH Replica Set Config Fails when leader changes

You should use TLS/SSL for connections between those machines. Then you don't need to play around with ssh. With SSL connections between nodes are encrypted and IP addresses don't change.


Do you think using ObjectID as field name is a bad practice? What can go wrong if I follow this design?

Yes, it is not a good idea to use a dynamic (changing value) like an ObjectId as a field name. To query a specific bookmark you are looking for a specific value for a specific field. And also, to index bookmarks fields it is not possible as the field names are changing.
You can use something like this, for the bookmarks field:
bookmarks: [
    { post_id: "606182bff8a94f5405c45268", like_dislike_users: [ 3, 5, 8, 12 ] },
    { ... },
     ...
]

Where bookmarks is a field of type array. And, each element in the array is a sub-document (a.k.a. embedded document).
Now, you can query the bookmarks for posts or/and like/dislike users by their ids (see Query an Array of Embedded Documents). In addition, for an efficient querying you can index on these fields (indexes on array fields are known as Multikey Indexes).


The pgAdmin 4 server could not be contacted

Found these.  We'll give it a shot.
https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD
https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows

Windows debugging.
Open Environment Variables (WINDOWS key + BREAK)
Set environment variable "_NT_SYMBOL_PATH" for 4.2.
C:\Program Files\PostgreSQL\12\debug_symbols;SRV*c:\localsymbols*http://msdl.microsoft.com/download/symbols

open pgAdmin 4 > look for process in Task Manager and get process id
open Microsoft Visual Studio (run as administrator) > Debug > Attach to Process > check "Show processes from all users" > sort by process id > Attach
now go back in pgAdmin

Got a new popup I've never seen before from Visual Studio!

Created new virtual environment based on Python 3.8.

Created a new Visual Studio Project using Python, not C++.  I might need a newer version of Python based on this fix.
https://github.com/microsoft/ptvs/issues/5853
https://bugs.python.org/issue37633
Since this new version does not show in the Base Interpreter, I created a new environment based on the zip file.

None of that worked.  Then I stumbled upon the registry configuration for pgAdmin.  I read that pgAdmin uses Python version 2.7.  And the PythonPath variable was blank, so I set it to C:\Python\Python27.  So it must be using a value from somewhere else to get the path.  It's looking in sys.path in Python whatever that is.

Then I set ApplicationPath to C:\Program Files\pgAdmin 4\v4\web, to the folder where pgadmin4.py is located.
Here is the icon I'm clicking on each time.
"C:\Program Files\pgAdmin 4\v4\runtime\pgAdmin4.exe"
"C:\Program Files\PostgreSQL\12\pgAdmin 4\bin\pgAdmin4.exe"

ERROR flask.app: Error starting the app server: (<class 'OSError'>,
OSError(10013, 'An attempt was made to access a socket in a way
forbidden by its access permissions', None, 10013, None),

There are two pgAdmin folders.  There are traces left over from the other pgAdmin uninstall.
C:\Program Files (x86)\pgAdmin 4\files.txt
C:\Program Files (x86)\pgAdmin 4\v4
C:\Program Files (x86)\pgAdmin 4\v4\venv
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\collections
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\encodings
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\importlib
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\json
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\site-packages
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\collections\__pycache__
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\collections\__pycache__\__init__.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\encodings\__pycache__
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\encodings\__pycache__\aliases.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\encodings\__pycache__\cp1252.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\encodings\__pycache__\latin_1.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\encodings\__pycache__\utf_8.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\encodings\__pycache__\__init__.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\importlib\__pycache__
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\importlib\__pycache__\abc.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\importlib\__pycache__\machinery.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\importlib\__pycache__\util.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\importlib\__pycache__\__init__.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\json\__pycache__
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\json\__pycache__\decoder.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\json\__pycache__\encoder.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\json\__pycache__\scanner.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\json\__pycache__\__init__.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\site-packages\__pycache__
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\site-packages\__pycache__\_virtualenv.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\abc.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\codecs.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\contextlib.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\copyreg.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\datetime.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\enum.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\functools.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\genericpath.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\heapq.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\io.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\keyword.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\ntpath.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\operator.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\os.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\re.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\reprlib.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\signal.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\site.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\sre_compile.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\sre_constants.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\sre_parse.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\stat.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\subprocess.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\threading.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\types.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\warnings.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\_bootlocale.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\_collections_abc.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\_sitebuiltins.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\_weakrefset.cpython-38.pyc
C:\Program Files (x86)\pgAdmin 4\v4\venv\Lib\__pycache__\__future__.cpython-38.pyc

FINALLY SOLVED IT!  Flask within the Qt framework is NOT getting the port!  I put some troubleshooting code into the source code.

C:\Program Files\pgAdmin 4\v4\web\pgAdmin4.py

# Initialize Flask service only once
# If `WERKZEUG_RUN_MAIN` is None, i.e: app is initializing for first time
# so set `use_reloader` = False, thus reload won't call.
# Reference:
# https://github.com/pallets/werkzeug/issues/220#issuecomment-11176538
with open("myfile.txt", "w") as file1:
    file1.write("Testing")
    file1.write(config.DEFAULT_SERVER)
    file1.write(config.EFFECTIVE_SERVER_PORT)
try:
    app.run(
        host=config.DEFAULT_SERVER,
        port=config.EFFECTIVE_SERVER_PORT,
        use_reloader=(
            (not app.PGADMIN_RUNTIME) and app.debug and
            os.environ.get("WERKZEUG_RUN_MAIN") is not None
        ),
        threaded=config.THREADED_MODE
    )

except IOError:
    app.logger.error("Error starting the app server: %s", sys.exc_info())


Data Warehouse: Remove legacy data after conversion into new system

I would suggest dropping the database once it is no longer needed.

DROP DATABASE LegacyDatabase_0001;
DROP DATABASE LegacyDatabase_0123;

The database space is freed for the use of other expanding databases and/or for creating further new databases.

If you are concerned about data loss you could archive the LegacyDatabases to some backup location, a file share, tape backups, whatever your choice may be.


It isn't refusing to start because of the missing ib_log files: in fact that trace shows it created blank versions in response to not finding them [presumably this is similar to attaching a detached DB in MSSQL without it s log file(s)].

The reason it is reporting for failing to start appears to be unknown option '--skip-locking' which implies that this option is specified on the command line or in the config file but is not being understood.

Why there would be a wrong option in a fresh install I'm not sure, and I'm no expert at all on MySQL so can't be much more help. To help other people (who may have much greater MySQL knowledge) help you it would be a good idea to document the steps you took install and configure MySQL (including where you obtained any install files or source code) - that will help identify if you are hitting a known problem with MacOS that is a seen in certain releases or setups.

Edit:

A quick search for info about the option indicates that it is now deprecated as it has been renamed to --skip-external-locking (see this SO question+answer) and is apparently the default behaviour anyway. The changelog linked to suggests is changed somewhere in the 4.x line though, so why v5.1.x accepted it and v5.5.9 doesn't is not obvious (unless it was a bug that v5.1.x didn't give the error/warning).


SQL optional where clauses without exponentially large number of only slightly different queries

Given that the phrase column IN () is not valid SQL syntax, I do not believe there is a way to do this with SQL. If there is any SQL syntax to do this, it is going to be exceptionally convoluted, likely lead to sub-optimal query plans, and bloat your queries by requiring you to specify the same argument list multiple times -- once to check the argument list in SQL, and once to actually use it.

In Postgres, the database should not treat a single-item IN clause differently than an equality clause. Rewriting a single-item IN clause as an equality clause gains you nothing.

Since you are using SQLAlchemy already, you can conditionally add these conditions to your query based on what is in the argument lists:

# your values need to be tuples for Postgres to properly
# convert them to lists for IN clauses; Python lists will convert
# to Postgres ARRAY types
args1 = ("value",)
args2 = ()

# the "1=1" is an always-true check so that you can
# specify a WHERE clause, even with no provided arguments
clauses = ["1=1"]
if len(args1) > 0:
    clauses.append("t.first_id IN :args1")
if len(args2) > 0:
    clauses.append("t.second_id IN :args2")
...

query = """
SELECT *  -- in reality, something more complicated
FROM myTable t
WHERE
""" + " AND ".join(clauses)

result = conn.execute(text(query), args1=args1, args2=args2, ...).fetchall()

SQLAlchemy does not care if you provide unused parameters. In other words, if args2 is empty, passing it to execute() will work here, even though the query will not have a placeholder for it.

If you are using the SQLAlchemy Core API for generating queries, you can chain your where() clauses in a similar way:

s = select([myTable])
if len(args1) > 0:
    s = s.where(myTable.c.first_id.in_(args1))
if len(args2) > 0:
    s = s.where(myTable.c.second_id.in_(args2))
...
result = conn.execute(s).fetchall()


You never actually reduce your balance here. I think you mean to do this:

SET @BALANCE = @FP_CALC_BEG_BAL_AMT;
OPEN VINTAGE;
FETCH NEXT FROM VINTAGE INTO @PAYMENT;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @BALANCE = @BALANCE - @PAYMENT;
FETCH NEXT FROM VINTAGE INTO @PAYMENT;
END
CLOSE VINTAGE;
DEALLOCATE VINTAGE;

In this example, you set your starting balance to the initial loan amount. Then on each iteration of the cursor you reduce the balance by the payment amount.

I'm not sure why you chose to use a cursor, I am sure you have your reasons, but would the below not achieve the desired result without using one?

DECLARE @TOTAL_PAYMENTS INT;
    SET @TOTAL_PAYMENTS = (SELECT SUM(PMNT_AMT)
                             From LOOPDATA_NEW
                            Where FP_MERCH_ID = 2359
                         Group by FP_MERCH_ID, PMNT_SEQ_ID, PMNT_AMT);
    SET @BALANCE = @FP_CALC_BEG_BAL_AMT - @TOTAL_PAYMENTS;

Also, in your code you are casting your balance and payments to INT, this loses the decimal precision which could cause you to lose money if this were to be a real-world example. Remember to use an accurate data type such as DECIMAL so that you capture these values correctly.

EDIT BASED ON COMMENT

The reason you only every return the largest loan is because of this line in your code:

DECLARE @FP_CALC_BEG_BAL_AMT INT
    SET @FP_CALC_BEG_BAL_AMT = (SELECT MAX(DEAL_FP_CALC_BEG_BAL_AMT)
                                  From LOOPDATA_NEW
                                 where FP_MERCH_ID = 2359);

You only ever fetch the largest loan here. If you want to loop through each loan and then each payment you can do this:

DECLARE @loan DECIMAL(9,2) = 0;
DECLARE @balance DECIMAL(9,2) = 0;
DECLARE @payment DECIMAL(9,2) = 0;

DECLARE loans CURSOR
    FOR (SELECT DISTINCT DEAL_FP_CALC_BEG_BAL_AMT
           From LOOPDATA_NEW
          where FP_MERCH_ID = 2359);
 OPEN loans;
FETCH NEXT FROM loans INTO @loan;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @balance = @balance + @loan;

  DECLARE payments CURSOR
      FOR (SELECT PMNT_AMT
            From LOOPDATA_NEW
           Where FP_MERCH_ID = 2359
        Group by FP_MERCH_ID, PMNT_SEQ_ID, PMNT_AMT);

   OPEN payments;
  FETCH NEXT FROM payments INTO @payment;
  WHILE @@FETCH_STATUS = 0 AND @balance > 0
  BEGIN
    SET @balance = @balance - @payment;
  FETCH NEXT FROM payments INTO @payment;
  END
  CLOSE payments;
  DEALLOCATE payments;
FETCH NEXT FROM loans INTO @loan;
END
CLOSE loans;
DEALLOCATE loans;
PRINT @balance;

Aaron Bertrand gives a fantastic answer on the different methods you can use to calculate running totals for this question. I highly recommend checking it out as I feel it will help you in the long run.


After new row INSERT statement, use newly created PK as FK in a second INSERT statement within the same Stored Procedure

I think that if you just change this line it should work for you.

SET @order_id = SCOPE_IDENTITY();

Your method of grabbing the max id and adding 1 is not safe for concurrent access, scope_identity() is the correct way of getting the ID of the just inserted row.


Yes there is, and it's definitely not "a new standard". It's been in the SQL standard since SQL:2003

The definition is:

<identity column specification> ::=
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
    [ <left paren> <common sequence generator options> <right paren> ]

The optional part with the sequence generator options lets you define the characteristics of the underlying sequence (=generator), you could do something like:

id integer GENERATED ALWAYS AS IDENTITY (start with 42 increment by 10 cycle)

This syntax is supported (at least) by PostgreSQL 10, Oracle 12.1, DB2, Apache Derby, HSQLDB, Firebird and NuoDB.


SQL INSERT and UPDATE queries run far to slow

Database Indexes are a major benefit to performance of queries.  I work almost exclusively with Microsoft SQL Server, but the principles apply on pretty much all flavors of SQL servers.

Because the indexes are smaller than the overall table and have the needed value to be searched in a sorted order, creating indexes (to a reasonable degree) is the quickest way to speed up queries.

You can overdo it, of course, since too many indexes can drag down performance.


What is wrong with this statement? 'Unknown column'?

A relational sub-select is logically processed in the following sequence:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause
LIMIT...OFFSET clause or the equivalent

Subsequently, the column labels defined in the SELECT clause are not yet known when the FROM clause, containing the joins in your example, is dealt with.


Design: Multiple Values in One Column or Many Columns?

If you created an OverTimeScore table, you'd then need to add an INT or BIT field to GameScore or Game table to check if overtime exists or not. Are you also going to store the total score? Or just each quarter score and calculate the total in your app/query? If this were me, I would add a total score field and an overtime field into my GameScore table, I don't see a reason to break it out into its own table. Populate the field if there is overtime, otherwise leave it NULL. You can then just work your queries to check is null. 101 ways...


MySQL 5.1.73 change default log location

A). To change Mysql innodb_log file to a new location.

 1. Change my.cnf settings for innodb_log file or innodb_fast_shutdown=0
   (so it does full flush of log files from buffer).

 2. Stop mysql.

 3. Change innodb_log_group_home_dir = /mysql/new_location/ in my.cnf.

 4. Start Mysql, it should be up now.

B). To change Mysql log_error file to a new location.

 1. Create a new file for log_error file OS level.
     - copy nul > /mysql/NEW_LOCATION/mysql_error
     - Give mysql permission to /mysql/NEW_LOCATION/mysql_error to read and write.

 2. Change my.cnf log_error = /mysql/NEW_LOCATION/mysql_error

 3. Stop Mysql.

 4. Start Mysql.

You may do A and B in one shot, I will let you do organize which ever way you wanted.


Is there any open source GUI tool to monitor cassandra?

Have you tried Helenos:
https://github.com/tomekkup/helenos

It seems good


SQL Server Deadlock on two updates due to index lock order

The optimal index for those two queries is not far from the existing definition of the IK_HTT_ACTION_LOG_1 index (add ACTION_UUID as an INCLUDE to the improved index below):

CREATE INDEX nc1
ON dbo.HTT_ACTION_LOG
(
    TRANSITION_UUID,
    STATUS,
    ACTION_SEQ
);

The first query is:

UPDATE dbo.HTT_ACTION_LOG
SET [STATUS] = 'ABORTED',
    CLOSED = GETUTCDATE()
WHERE
    TRANSITION_UUID = '{F53ADDDA-E46B-4726-66D8-D7B640B66597}'
    AND [STATUS] = 'OPEN';

Giving the following execution plan:

The second query can be expressed this way:

UPDATE ToUpdate
SET [STATUS] = 'RUNNING {36082BCD-EB52-4358-E3D3-4D96FD5B9F0F} 1360094342'
FROM
(
    SELECT TOP (1)
        hal.[STATUS]
    FROM dbo.HTT_ACTION_LOG AS hal
    WHERE
        hal.transition_uuid = '{F53ADDDA-E46B-4726-66D8-D7B640B66597}'
        AND hal.[STATUS] = 'OPEN'
    ORDER BY
        hal.ACTION_SEQ ASC
) AS ToUpdate;

Giving this execution plan:

Both queries now access the same resources in the same order, while locking many fewer rows on the read side of the plan. The execution engine will automatically take UPDLOCKs when reading the new index, providing the serialization you are looking for.


Seems to be many errors
do this and then have a look on log again
Decrease the value of innodb-buffer-pool size in the config file /etc/mysql/my.cnf

innodb_buffer_pool_size = 10M

Looks like /var/lib/mysql is owned by the mysql user and the group is set to mysql as well.

$ sudo chown -R mysql /var/lib/mysql
$ sudo chgrp -R mysql /var/lib/mysql
$ sudo chmod 755 /var/lib/mysql

Have you checked that the directory /var/lib/mysql does actually exist and corresponds to your data directory? If it does not, then you probably must specify otherwise the datadir parameter under the mysqld section

[mysqld]
datadir=/var/lib/mysql

Then you can chose between configuring apparmor to allow MySQL to read\execute\modify the new data directory and sub directories or, at your very own risk, you can remove apparmor. You may need to be root to do the following:

/etc/init.d/apparmor stop
/etc/init.d/apparmor teardown
update-rc.d -f apparmor remove
apt-get purge apparmor
reboot

Source:
https://dba.stackexchange.com/questions/106085/cant-create-file-var-lib-mysql-user-lower-test


High Availability with Transactional Replication

Yes. See the documentation for a detailed list of steps.

  SQL Server Replication, change data capture (CDC), and change tracking (CT) are supported on Always On availability groups. Always On availability groups helps provide high availability and additional database recovery capabilities.


Why does this CTE return the bulk of its results immediately, but then take several minutes to finish?

The anchor part of the recursive SQL query produces all 7488 rows. On my machine, that part of the query finishes in under 100 ms. SSMS does not immediately show all 7488 rows in the grid results. It only shows 7480 for me as well. I suspect this happens because the results are sent in packets and the remaining 8 rows aren't enough to fill a packet.

The second half of the query is extremely expensive compared to the first. Looking at the execution plan you can see six clustered index scans on the inner side of a nested loop. Those scans will execute at least once for every row from the anchor part. SQL Server does a total of 44928 scans over a few minutes to ultimately produce 0 rows from that part. Once that part of the execution is complete SQL Server sends the remaining packet of rows to the client and you see all 7488 rows in the results grid of the completed query. It's not true that it takes three minutes to find the last 8 eight rows. It finds them almost instantly. It just takes three minutes to send them.

You can see a large amount of work performed in the bottom half of the query by looking at the actual plan. Note the thickness of the arrows and the number of executions for the scan operators:

That's why the query is slow. I don't have any advice for you to make it fast because I don't understand what you're trying to accomplish with this query.


Extract all objects from json array using MariaDB 10.3

You can accomplish this by using a dedicated numbers_table. This is a table with a single column number and can have as many values as you require (I have 0 to 999)
For your question, there are two scenarios: The first is if you do not know the keys, or they change. The second is if you do know the keys and in this case you can get each key of the json objects as a column.
Scenario 1:
SELECT
    JSON_VALUE(JSON_KEYS(@json_doc,CONCAT('$[',n1.number,']')),CONCAT('$[',n2.number,']')) AS json_column,
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),CONCAT('$.',JSON_VALUE(JSON_KEYS(@json_doc,CONCAT('$[',n1.number,']')),CONCAT('$[',n2.number,']')))) AS json_data,
    n2.number AS column_index,
    n1.number AS array_index
FROM numbers_table AS n1
JOIN numbers_table AS n2
WHERE
    n1.number < json_length(@json_doc) AND
    n2.number < json_length(json_keys(@json_doc,CONCAT('$[',n1.number,']')));

-------------------------------------------------------
|json_column     |json_data |column_index |array_index|
|DatabaseName    |master    |0            |0          |
|Schema Name     |dbo       |1            |0          |
|Object Name     |CommandLog|2            |0          |
|column_index_id |1         |3            |0          |
|Buffer size(MB) |0.02      |4            |0          |
|BufferCount     |2         |5            |0          |
|Row Count       |3         |6            |0          |
|Compression Type|NONE      |7            |0          |
|DatabaseName    |master    |0            |1          |
|Schema Name     |dbo       |1            |1          |
|Object Name     |xevents   |2            |1          |
|column_index_id |0         |3            |1          |
|Buffer size(MB) |0.16      |4            |1          |
|BufferCount     |21        |5            |1          |
|Row Count       |0         |6            |1          |
|Compression Type|NONE      |7            |1          |

Scenario 2:
SELECT
    n1.number AS array_index,
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.DatabaseName') AS 'DatabaseName',
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.Schema Name') AS 'Schema Name',
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.Object Name') AS 'Object Name',
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.column_index_id') AS 'column_index_id',
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.Buffer size(MB)') AS 'Buffer size(MB)',
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.BufferCount') AS 'BufferCount',
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.Row Count') AS 'Row Count',
    JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.Compression Type') AS 'Compression Type'
FROM numbers_table AS n1
WHERE
    n1.number < json_length(@json_doc);

Output:
|array_index|DatabaseName|Schema Name|Object Name|column_index_id|Buffer size(MB)|BufferCount|Row Count|Compression Type|
|0          |master      |dbo        |CommandLog |1              |0.02           |2          |3        |NONE            |
|1          |master      |dbo        |xevents    |0              |0.16           |21         |0        |NONE            |


How to rollback changes made to the database by a specific user?

It would be difficult to do that using Native tools or you will have to look for third party tools.

Natively:

Default trace

Can be used to track Object Altered, Object Created and Object Deleted along with other stuff. Refer to the link below.

Refer to : The default trace in SQL Server - the power of performance and security auditing

Note this is very limited as it will just give you info related to who did what e.g. Who dropped the table, etc.

fn_dblog function ==> [undocumented]

Note: You need to transaction log backups available or the transaction should not have been cleared from the active portion of the log

Excellent reading at : Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN and
Joining sys.dm_tran_database_transactions to fn_dblog Results

Third Party tools - the best one out is ApexSQL Log

As a side note:

  I want to preserve changes made by other users

Depending on your version of sql server and your business requirements, you can implement

Change Data Capture
Change Tracking
Run SQL Server Audit
Server-Side Tracing and Collection - with appropriate events selected.


WITH cte AS (SELECT *,
                    ROW_NUMBER() OVER (PARTITION BY channel_id
                                       ORDER BY created_at DESC) rn
             FROM Videos)
SELECT *
FROM Channels c
LEFT JOIN cte ON c.id = cte.channel_id AND cte.rn = 1

fiddle


Inserting rows into other table whilst preserving IDENTITY

While it doesn't automatically prevent duplicates, you can disable the identity temporarily using the following, and then you would likely just want to set the identity seed to the highest value in the table:

 SET IDENTITY_INSERT dbo.tablename ON;

 INSERT ...

 SET IDENTITY_INSERT dbo.tablename OFF;

 DECLARE @sql NVARCHAR(MAX);

 SELECT @sql = N'DBCC CHECKIDENT(''dbo.tablename'', RESEED, '
   + RTRIM(MAX(id_column_name)) + ');' FROM dbo.tablename;

 EXEC sp_executesql @sql;

I'm not sure what your best course of action would be to correct duplicates. If you insert 1000 new rows after reseeding, it is likely that the source system will generate new identity values that will conflict. What you might consider doing is simply setting one of the identity values to generate numbers well above the range that the other table won't ever get to (say 1 billion). You can still use IDENTITY_INSERT to merge, but there will never be a conflict. This also makes it very easy to determine whether a row was generated locally or imported.


You wouldn't be able to persist this computed column, as its value needs to change over time.  But having such a non-persisted computed column is a recipe for poor performance.

For this scenario you should decide how often you need to recalculate whether a row is "active" and run a scheduled batch process and/or a trigger to maintain the values.


Isolate database performance on an instance

You're question is very broad.  But in general the answer is yes.  A single instance has to share resources across all databases unless configured specifically not to.
By default, SQL uses all available CPU's for all operations.  You can configure MAXDOP (max degree of parallelism) on the database so a single query will only have access to that many CPU's.
IO is another tricky issue.  Generally it's best to seperate your workloads on physical hardware whenever possible.  Logs, data and tempdb all on seperate physical storage.  In addition, if you are trying to create a seperation of resources for the databases, you can use filegroups to assign the objects or the entire database to a separate storage device.
There are many, many other answers but again.  The question of 'how can I avoid that?'(in terms of cross database resource allocation) is way too broad for a specific answer.  If you are looking at one aspect, say IO, or CPU usage, it's an easier, more specific answer.


DB2 does not implement the DBMS_CRYPTO module in its Oracle compatibility code.  DB2 does still offer the ENCRYPT() and DECRYPT() UDFs, but I would recommend against these as they use a weak encryption algorithm and require the use of passwords in SQL.

So, you would have to implement your own solution (via UDFs) for application-level encryption like this.

It might make more sense to implement this in your application code rather than in the database to avoid having to send encryption keys across the wire to the DBMS.  This would certainly be a more platform-independent solution.


Creating a view with a Left Outer Join and Where Condition

In this particular case what you have isn't a WHERE condition.  It's actually part of the join.

select user.*,user_record.external_identifier
from [User] user
left outer join [User Record] user_record
    on user_record.USER_ID = user.ID
    AND user_record.source = 'Peoplesoft'

In English, you want to return every row from [User] and any information from [User Record] where there is a match based on USER_ID/ID AND the [User Record].source is Peoplesoft.

Putting it in the where clause would mean you want to return every row from [User] and any information from [User Record] where there is a match based on USER_ID/ID and from that result set return only those rows from this output that meet the condition [User Record].source = Peoplesoft.

CREATE TABLE #user (id int, col2 int)
CREATE TABLE #userrec (user_id int, source varchar(50))

INSERT INTO #user values (1,1),(2,2),(3,3)
INSERT INTO #userrec values (2,'Peoplesoft'),(3,'test')

-- Without the condition at all
SELECT *
FROM #user #user
left outer join #userrec user_record
    ON user_record.USER_ID = #user.ID

-- Condition is in the WHERE clause
SELECT *
FROM #user #user
left outer join #userrec user_record
    ON user_record.USER_ID = #user.ID
WHERE user_record.source = 'Peoplesoft'

-- Condition is in the JOIN
SELECT *
FROM #user #user
left outer join #userrec user_record
    ON user_record.USER_ID = #user.ID
   AND user_record.source = 'Peoplesoft'


Your T-SQL BACKUP LOG command does not use the INIT option.  If you do not specify the INIT option, and the file target backup file already contains one or more backups, the backup you are taking is appended to the end of the file.

This would explain how your log backup seems larger than the log it backed up.

You can tell if there is more than one backup in the target file by looking at the results of:

RESTORE HEADERONLY
FROM DISK =  N'g:\sqlbackup\LOG\myDB_LOG_20160329_084501.trn';

See the "Media Set Options" in the Backup MSDN page.


can't connect to mysql via command line but workbench works, ssh tunneling required

Wrestled this morning with a very similar issue. This worked for me:

ssh -N -L LOCALPORT:localhost:REMOTEPORT -i MYKEYFILE USER@REMOTE
mysql --port LOCALPORT --protocol=TCP -p


Efficiently computing aggregate functions over subqueries with incremental data

The standard deviation can be calculated knowing the number of values, the sum of the values, the sum of the square of the values. Each of these can be updated incrementally as new values arrive and stored in a work table. The work table will look something like

DailyTotals (
  OrderDate,
  NumberOfValues,
  SumOfValues,
  SumOfSquareOfValues);

Since the work table is keyed by date, your desired fourteen day sliding window can be achieved. Since each value is a sum, summing again for each date's value is not a mathematical problem. Yes, there is a calculation at runtime. It is much lighter than the full standard deviation one, however.

When new values arrive the work table can updated synchronously (it's a 1-row update), or asynchronously or in batch depending on the application's appetite for stale data.


How to export Sql Server DB schema as an ALTER statements?

I would write something like this, and first run in on the new database - to get the migration code, and then running resulting migration code on the old database - to alter the schema.
It's just a part to add missing columns and not complete. You should also check for is_identity, constraints, other data types and etc.
Maybe there is a complete code in the internet...
SELECT 'IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('''+t.name+''') AND name='''+c.name+''')
ALTER TABLE ' + QUOTENAME(t.name) + '
ADD ' + QUOTENAME(c.name) + ' ' + tt.name
       + CASE WHEN tt.name IN ('varchar','nvarchar') THEN '(' + CAST(c.max_length as varchar(10)) + ')'
              ELSE '' END
       + ' '
       + CASE WHEN c.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + char(13)+char(10)+char(13)+char(10)
FROM sys.tables t
JOIN sys.columns c ON c.object_id = t.object_id
JOIN sys.types tt ON tt.user_type_id = c.user_type_id

Result - migration code:
IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('users') AND name='id')
ALTER TABLE [users]
ADD [id] int NOT NULL IDENTITY

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('users') AND name='login')
ALTER TABLE [users]
ADD [login] varchar(100) NOT NULL

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID('users') AND name='email')
ALTER TABLE [users]
ADD [email] varchar(100) NULL


Is using CRUD stored procedures against a view with NOLOCK bad?

Using NOLOCK blindly is idiotic. There is no best practice at all for this. This could be construed as utter arrogance: that someone knows better than MS who chose READ COMMITTED as the default

From SO: "Using NOLOCK Hint in EF4?". As well as me calling the DBA a muppet, a guy from the MS EF team answers too. And SQL - when should you use “with (nolock)”

In addition, NOLOCK is ignored for updates, inserts, deletes.

Using 1:1 views is idiotic too. It adds no value. From programmers.se "What popular “best practices” are not always best, and why?".

Using a view to hide table changes ad interim is OK, but having the extra layer based on dogma is pointless. And I bet you don't have WITH SCHEMABINDING so the view can differ from the table anyway (SO)


Look for

SELECT *
FROM ( SELECT events.*,
              CASE WHEN in_event
                   THEN @tmp := level
                   ELSE @tmp
              END delta
       FROM events,
            (select @tmp := 0) var -- or another value for "unknown"
       ORDER BY date ASC
     ) tmp
ORDER BY date DESC;


How to map similar camelcased, pascalcased, underscored, lowerscored words to the same id?

Database design

Either you already have the look-up table and forgot to mention it, or you should create one.

CREATE TABLE map (
  map_id int PRIMARY KEY
 ,map    text UNIQUE NOT NULL
);

-- And that's how I would shape your original table:
CREATE TABLE word (
  word_id serial PRIMARY KEY
 ,map_id  int REFERENCES map(map_id)
 ,word    text
);

Here and here is why I normally prefer text over varchar(n).
Here is why I put the two integer columns first in table word.

INSERT INTO map(map_id, map) VALUES
  (1, softwaredesign)
 ,(2, civilengineering)
 ,(3, industrialdesign)
 , ... ;

Map data

Define a function like @Daniel already suggested.

Depending on your exact requirements, I would use pre-defined character classes where possible in a regular expression instead of rolling my own. Postgres uses locale information from your OS to identify characters, digits etc.

[:alnum:] includes all numbers and digits, depending on your locale. Unlike [a-zA-Z0-9] this also identifies ä or é as characters - depending on your locale.

[^[:alnum:]] is the negation, i.e. all other characters.
There is also the similar class shorthand \W, but that would include the underscore _.

CREATE FUNCTION map_word(text) RETURNS text AS
$$SELECT lower(regexp_replace($1, '[^[:alnum:]]', '', 'g'))$$
LANGUAGE sql IMMUTABLE;

This update would be most efficient then:

UPDATE word w
SET    map_id = m.map_id
FROM   map m
WHERE  m.map = map_word(w.word)
AND    w.map_id IS DISTINCT FROM m.map_id -- avoid empty updates


Replication has failed; how to get going on again?

Yes you will have to give the slave a new base backup (for streaming replication only steps 1 to 4) of the master.

Your problem has probably occured because the value of wal_keep_segments is to low. The value needs to be high enough that when the slave is down for some time the master won't start recycling segments the slave hasn't processed yet.


Make encrypted differential backup to same media

As Aaron noted, there's no reason to retain multiple differential backups, as you would only use the latest, and only as an optimization.

The limitation on one encrypted backup per file is in the main doc page for Encrypted Backups:

  The following are restrictions that apply to the encryption options:

  . . .

  Appending to an existing backup set option is not supported for
  encrypted backups.

Backup Encryption


Granting access only to standby database

You could do an OS firewall on the production side, but that wouldn't work if they need production for more limited purposes.
An after logon database trigger would work, but unfortunately it would also have to run on the primary to disconnect the sessions there.
You could make it part of your procedures to alter the password on the primary of the user they will use to connect the standby immediately after the standby is opened read-only but before the users are notified of the new password.  In that way they will be able to connect to the standby, but will not be able to connect to the primary.  The password just set for the primary would then be the password you delivered to them after the next open of the standby.
You could enable the user just before the standby is opened and then disable the user immediately after before the users are notified that the standby is open.  This would allow the password to stay the same, but does introduce a window of vulnerability that you may be able to mitigate if you have an outage window on the primary and can bring down the listener or something.


Local user login access in sql server 2008

To figure out exactly what is happening, you should check the State that is reported in the message, either in the event log or SQL Server log. For example:

  Error: 18456, Severity: 14, State: 8.

The state will usually help narrow down the exact cause. Here is a list of the states and their meanings:

Err  Message
 2   Invalid userid
 5   Invalid userid
 6   Attempt to use a Windows login name with SQL Authentication
 7   Login disabled or password mismatch
 8   Password mismatch
 9   Invalid password
11   Valid login but server access failure
12   Valid login but server access failure
13   SQL Server service paused
18   Change password required


Handle development issues when dealing with different collation for the instance/database setting

Schemas have nothing to do with collation. Also, it would certainly help to know what is meant by "scripts starts failing". Please post the exact and full error message(s) to the question.
For the moment, based on the following two pieces of info:

The scripts are failing, not just returning unexpected / fewer results,
and:

You said that you can have two sets of scripts, not that you already do have (or at least have tried)

I am guessing that the procs have inconsistent references to the table and/or schema names in terms of casing. For example, the actual object being Sales.Table1 but referenced in one or more procs as Sales.table1 (only difference is the t in Table1). That is not something that can be fixed via a COLLATE clause in a query. That requires fixing the code to use the proper casing. In fact, if you are going to deploy to instances that are case-sensitive, then it would be best to update your development (and test / QA / Staging) instances to also use a case-sensitive default collation at the database level. If your code can be deployed on instances using a binary collation, then perhaps use a binary collation. This will help you catch these types of errors very early in the development cycle.
Additionally, using a case-sensitive or even binary collation (ending in _BIN2, not _BIN) for the database and leaving the instance-level collation as case-insensitive (or just something different than the database default collation) will help you identify potential errors in queries related to temp tables.
In the end, you definitely shouldn't need multiple sets of scripts to handle different collations, especially if this is an issue with creating procedures that fail due to using a different casing on columns names but not tables (which results in an immediate error as it will find the table but not the column), or an issue with executing the procedure if there is a difference in casing for the table name as deferred name resolution will allow the proc to be created but will error upon execution.
NOW, regarding temp tables, the answer is not necessarily to use COLLATE DATABASE_DEFAULT. It all depends on what your app is trying to accomplish for each string column affected. (And FYI: this part is the same, even if you deploy the full database setting a specific collation and aren't having a problem running a script that creates schemas / objects in a DB with any random collation).
If any affected column uses the database's default collation (i.e. does not specify a collation in the CREATE TABLE statement), and the app doesn't require a particular collation for this specific operation/query, then yes, use COLLATE DATABASE_DEFAULT next to each predicate / concatenation / reference in the query containing the temp table.
However, if an affected column is given a specific collation when the table is created, or if the operation / query requires a specific collation (i.e. a filter that must be case-insensitive no matter what), then you should use COLLATE {specific_collation_name} next to each predicate / concatenation / reference in the query containing the temp table.
Based on info added to the question:
The scenario is pretty much what I had guessed based on the previous info:

Your scripts are being deployed to existing databases (outside of your control)
The errors are coming from column names being referenced in the stored procedures with different casing than they were created with.

Hence, the advice doesn't really change: You only need one set of scripts, the set with the column references matching the casing used when creating the tables. So if a table has a column named TimeStamp, then referencing that column as Timestamp in the code is an error (as it would be in many / most programming languages). You need to fix your source procs, leaving you will a single, correct set of scripts that will work everywhere.
You should also consider either changing the development instance (or just database at the very least, which is easier to change anyway) to use a binary collation. Or, deploy to an instance (or database) using a binary collation as part of your CI process (if you have one), or just as a matter of practice if you don't have an automated process. In either case, you will catch casing differences much earlier in the development process.
Not using temp tables will avoid collation differences in [tempdb]. As for table variables, those should be fine as their string columns, by default, use the database's default collation. You can still run into an issue if you have a string column set to a collation different from the database's default and compare it to a column in a table variable that wasn't created with that same collation. In that case, you just use COLLATE {collation_name} everywhere that issue is happening, specifying the collation used for the column from the user table.


A possible infinite recompile was detected for SQLHANDLE

According to Infinite recompile message in the errorlog on the SQL Programmability & API Development Team Blog, this message is triggered when a statement in a batch recompiles 100 times in a row.
This message does not necessarily mean there is a problem; it exists to help troubleshoot statements that might legitimately be recompiling that often (for example, due to rapid changes in statistics), as well as real infinite compilation loops (which would be rare in the extreme).
You should start by identifying the triggering statement from the information provided, and evaluate it in the context of the numeric code giving the reason for the recompilations. There is a table of these codes and their meanings in several places in Books Online, including under the SP:Recompile Event Class.
There is more information available in Plan Caching and Recompilation in SQL Server 2012.


How to add a check constraint in sql azure data warehouse?

This and constraints in general are currently unsupported in Azure SQL Data Warehouse. See https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-overview#unsupported-table-features for full list.

If the constraint is not enforced at the source or if you have multiple sources, you can implement during the ETL or ELT process. For small datasets, ETL would probably work better. With large datasets, you're likely to get better performance via ELT-L. As in extract then load to a temp table (heap is fast), perform the check/cleansing then load to the final table.


The solutions to this problem is as follows

Added the following to my.cnf

 collation_server=utf8mb4_bin
 skip-character-set-client-handshake

Then in the Connection string I added the following

;old guids=true

I realize this is a hack, but there is a large amount ofstored procedures that compare data such as "where fieldx = 'example'" where the phrase 'example' in the field has trailing spaces.


MIN, DATEADD, DATEPART, DATEDIFF functions cause table scan

Try this approach instead (assuming the point of all the dateadd/datediff operations are just to set the millisecond component to 0). The query was adapted to the AdventureWorks database:

SELECT
  CONVERT(DATETIME, CONVERT(CHAR(19), MIN(OrderDate), 121)),
  CONVERT(DATETIME, CONVERT(CHAR(19), MAX(OrderDate), 121))
FROM Sales.SalesOrderHeader GROUP BY ();

The trick is to find the rows before you start messing with the column values applying all kinds of formatting and date manipulation. This results in a much more efficient plan as long as there is a supporting index on the OrderDate column (and no worse than your current query if there is no supporting index):

Yes, they are still scans, and there are two instead of one. But the impact on actual execution is undeniable:


How to structure IF condition in MySQL trigger?

I assume you will value TableB first as it contains the order_no. In that case you need to use an update statement in your trigger instead of an insert statement:

SQL Fiddle

MySQL 5.6.6 m9 Schema Setup:

CREATE TABLE TableA(order_id INT, sku VARCHAR(10));

CREATE TABLE TableB(order_id INT, order_no VARCHAR(10),sku_copy VARCHAR(10));

GO
CREATE TRIGGER trigger_name
    AFTER INSERT ON TableA
    FOR EACH ROW BEGIN

    UPDATE TableB
    SET sku_copy = NEW.sku
    WHERE order_id = NEW.order_id;
END;
GO
INSERT INTO TableB(order_id, order_no)VALUES(1,'111');
INSERT INTO TableB(order_id, order_no)VALUES(2,'222');
INSERT INTO TableB(order_id, order_no)VALUES(3,'333');
GO
INSERT INTO TableA(order_id, sku)VALUES(1,'AAA'),(2,'BBB');

(The GO in this example is used as a batch separator and not send to MySQL.)

Query 1:

SELECT * FROM TableB;

Results:

| ORDER_ID | ORDER_NO | SKU_COPY |
----------------------------------
|        1 |      111 |      AAA |
|        2 |      222 |      BBB |
|        3 |      333 |   (null) |

If you also want to react to updates, just create an AFTER UPDATE trigger like this:

SQL Fiddle

MySQL 5.6.6 m9 Schema Setup:

CREATE TABLE TableA(order_id INT, sku VARCHAR(10));

CREATE TABLE TableB(order_id INT, order_no VARCHAR(10),sku_copy VARCHAR(10));

GO
CREATE TRIGGER TableA_AfterInsert
    AFTER INSERT ON TableA
    FOR EACH ROW BEGIN

    UPDATE TableB
    SET sku_copy = NEW.sku
    WHERE order_id = NEW.order_id;
END;
GO
INSERT INTO TableB(order_id, order_no)VALUES(1,'111');
INSERT INTO TableB(order_id, order_no)VALUES(2,'222');
INSERT INTO TableB(order_id, order_no)VALUES(3,'333');
GO
INSERT INTO TableA(order_id, sku)VALUES(1,'AAA'),(2,'BBB');
GO
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
GO
CREATE TRIGGER TableA_AfterUpdate
    AFTER UPDATE ON TableA
    FOR EACH ROW BEGIN

    IF (OLD.sku != NEW.sku)
    THEN
      UPDATE TableB
      SET sku_copy = NEW.sku
      WHERE order_id = NEW.order_id;
    END IF;
END;
GO
UPDATE TableA
  SET sku = 'NEW'
WHERE order_id = 2;
GO

Query 1:

SELECT * FROM TableB;

Results:

| ORDER_ID | ORDER_NO | SKU_COPY |
----------------------------------
|        1 |      111 |      AAA |
|        2 |      222 |      NEW |
|        3 |      333 |   (null) |

In both cases the NEW and OLD virtual tables refer to the table the trigger is defined on. NEW contains the new version of the row that was inserted or changed. OLD contains the pre-change version of the row. OLD is only defined in an update trigger as there is no old version on an insert.


One thing that sometimes works is to rewrite a series of OR conditions into a sequence of UNIONs:

CREATE TABLE restriction
AS
SELECT '100' as to_cost, a.nextedge as target_id, a.currentedge as via_path
FROM adjacency a
 JOIN adjacency b ON a.nextedge = b.currentedge
WHERE a.sourcenode = b.sourcenode AND (a.degrees < 45.0 OR a.degrees > 315.0)
UNION
SELECT '100' as to_cost, a.nextedge as target_id, a.currentedge as via_path
FROM adjacency a
 JOIN adjacency b ON a.nextedge=b.currentedge
WHERE a.targetnode = b.targetnode AND (a.degrees < 45.0 OR a.degrees > 315.0)
UNION
SELECT '100' as to_cost, a.nextedge as target_id, a.currentedge as via_path
FROM adjacency a
 JOIN adjacency b ON a.nextedge=b.currentedge
WHERE a.targetnode = b.sourcenode AND (a.degrees BETWEEN 46.0 AND 224.0)
;

You don't need an additional distinct here, as UNION will take care of that. I also replace the ancient, outdated implicit join syntax with an explicit JOIN operator.

You can additionally try indexes on:

adjacency (nextedge, sourcenode)
adjacency (currentedge, sourcenode)
adjacency (nextedge, targetnode)
adjacency (currentedge, targetnode)


No, while the XML schema contains a shortcut element, the fact is that proper shortcut support was never implemented (several of the other elements are not visible outside the XML, either). The following Connect item was closed as "Won't Fix" back in 2013:

Connect #731728 : SQL Server Management Studio 2012 code snippet shortcut

You may be able to raise awareness of this issue on another place where the SSMS team is paying attention: trello. But I'll warn you: even with an accelerated dev and release cycle, they have a lot on their plates, and I'm not sure this could make any significant dent into the top, say, 50 enhancements.

(Personally, I haven't found that I've need to use my mouse to insert snippets - all that is needed is keystrokes. Obviously YMMV.)


There is no such object as SYS.V$OSSTAT, because V$OSSTAT is a public synonym.
Because of this, there is no need to use a schema specifier for it.
The actual view is called V_$OSSTAT (same for other V$ views as well).
If you want a user to be able to select from that view, grant the privilege on that:

grant select on sys.v_$osstat to user;


Very close:

select
  T.X.value('./kind[1]', 'nvarchar(50)') as kind,
  T.X.value('./sortid[1]', 'int') as sortid
from @out.nodes('/row') as T(X);


Cannot drop anonymous user from mysql.user

DELETE FROM mysql.user WHERE user='' AND host='WOPR';
FLUSH PRIVILEGES;

This should do it for you.

Give it a Try !!!

CAVEAT

MySQL has certain users preinstalled into mysql.user. Also, mysql.db comes with two users that have anonymous access and full privileges to test databases.

Just do this

SELECT * FROM mysql.db \G

and you will see that anyone that connects to test or any database starting with test_ can pretty much do everything in the test database. This is bad since a person with full access to any test database can eat up a disk in matter of minutes.

Example:

use test
CREATE TABLE junk (INT a) ENGINE=MyISAM;
INSERT INTO junk VALUES (1);

OK, big deal. It makes a table with 4 bytes.

Now trying running this SQL statement 30 times:

INSERT INTO junk SELECT * FROM junk;

Hey an instant table with 1,073,741,824 rows (4GB+ file) !!! Imaging having full rights to a test database where you can wreak this kind of havoc on a disk.

My advice to you is to run this to clean out test user access:

DELETE FROM mysql.db WHERE db LIKE 'tes%' AND user='';
FLUSH PRIVILEGES;

For further clarification, see my post MySQL : Why are there "test" entries in mysql.db?

Cheers !!!


Why does dropping decimal columns not free up any space?

For a rowstore table you should not expect a drop column operation to free up any space. Data is stored on pages in row format. I'm oversimplifying a bit, but for most rowstore tables you'll end up with many rows stored on a single page.
Consider what would happen to the pages if you removed a column. Would you end up with any completely empty pages that could be returned to SQL Server? No, instead you'd get a bunch of small "holes" in the pages. SQL Server won't give you back any space unless you take a maintenance action on the table. For example, rebuilding the table will copy over all of the data into new pages. That should reduce the space needed by the table after dropping a column.
Rowstore demo
Here's a simple demo for rowstore in which dropping a column only frees up space after a REBUILD is completed on the table:
CREATE TABLE [dbo].[X_TBL_159269](
       [NUM] [int] NOT NULL,
       [COL1] decimal(18,0) NULL,
       [COL2] decimal(18,0) NULL,
       [COL3] decimal(18,0) NULL,
CONSTRAINT [PK_X_TBL_159269] PRIMARY KEY CLUSTERED
(
       [NUM] ASC
)
);

-- insert 1000000 rows
WITH
       L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
       L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
       L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
       L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
       L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),
       L5 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L4 B),
       NUMS AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NUM FROM L5)
       INSERT INTO [X_TBL_159269] WITH (TABLOCK)
       SELECT TOP (1000000) NUM, NUM AS [COL1], NUM AS [COL2],  NUM AS [COL3]
       FROM NUMS
       ORDER BY NUM;

sp_spaceused 'X_TBL_159269';

/*
reserved    data    index_size  unused
39752 KB    39608 KB    80 KB   64 KB
*/

ALTER TABLE [X_TBL_159269] DROP COLUMN COL2;

sp_spaceused 'X_TBL_159269';

/*
reserved    data    index_size  unused
39752 KB    39608 KB    80 KB   64 KB
*/

-- rebuild clustered index to free up space
ALTER TABLE [X_TBL_159269] REBUILD;

sp_spaceused 'X_TBL_159269';

/*
reserved    data    index_size  unused
30728 KB    30656 KB    72 KB   0 KB
*/

Columnstore demo
For columnstore tables, the data is stored in column format. Dropping a column will immediately free up space:
DROP TABLE IF EXISTS [dbo].[X_TBL_159269];

CREATE TABLE [dbo].[X_TBL_159269](
       [NUM] [int] NOT NULL,
       [COL1] decimal(18,0) NULL,
       [COL2] decimal(18,0) NULL,
       [COL3] decimal(18,0) NULL,
       INDEX cci CLUSTERED COLUMNSTORE
);

-- insert 1000000 rows
WITH
       L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
       L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
       L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
       L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
       L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),
       L5 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L4 B),
       NUMS AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NUM FROM L5)
       INSERT INTO [X_TBL_159269] WITH (TABLOCK)
       SELECT TOP (1000000) NUM, NUM AS [COL1], NUM AS [COL2],  NUM AS [COL3]
       FROM NUMS
       ORDER BY NUM
       OPTION (MAXDOP 1);

sp_spaceused 'X_TBL_159269';

/*
reserved  data
10760 KB  10672 KB
*/

ALTER TABLE [X_TBL_159269] DROP COLUMN COL2;

sp_spaceused 'X_TBL_159269';

/*
reserved  data
8200 KB   8008 KB
*/


Generate a list from columns that do not contain a zero for each row

Something like (untested):

select
  id,
  rtrim(
    case when c1 != 0 then 'column1,' end
 || case when c2 != 0 then 'column2,' end
 || case when c3 != 0 then 'column3,' end
 || case when c4 != 0 then 'column4,' end
 || case when c5 != 0 then 'column5' end, ',') as descr
from tab1;


how to select rows which are mapped to a single record in a one to many relationship?

Unless you are needing to return data that is in the user table you don't really need to involve it in your query. In that case you could implement a query such as

SELECT c.user_ID, c.col1, c.col2
FROM consumer c
GROUP BY c.user_ID, c.col1, c.col2
HAVING COUNT(c.user_ID) > 1

Were you to need a result set including results from both tables I would use a query similar to the one Marco suggests.

SELECT c.user_ID, c.col1, c.col2, h.col1, h.col2
FROM consumer c
INNER JOIN user_table u
      ON c.user_ID = u.user_ID
GROUP BY c.user_ID, c.col1, c.col2, h.col1, h.col2
HAVING COUNT(c.user_ID) > 1


MySQL Workbench, how to refresh EER diagram?

From the MySQL Workbench 5.2 window, select Database -> Synchronize model which will allow you to synchronize the model (EER diagram) with the database (you need to create a saved connection) for it.

I recommend this method over just reverse engineering the model from the database since it maintains the layout of the EER diagram. One caveat is that you need to add new tables to the EER diagram manually.


How can I speed up a query that orders by a calculated field?

If you don't really need zero-second actuality, you could just run your query time to time and cache the results.

If you still need to have real-time data on this (sacrificing insert performance), I would do this:

Since self-joins are not allowed in indexed views, you need to create two copies of each table:

CREATE TABLE personBrother
        (
        personId INT NOT NULL,
        brotherName INT NOT NULL
        )

CREATE TABLE personBrother2
        (
        personId INT NOT NULL,
        brotherName INT NOT NULL
        )

Create an indexed view on their join:

CREATE VIEW
        commonBrothers
WITH SCHEMABINDING
AS
        SELECT  p1.personId AS p1,
                p2.personId AS p2,
                COUNT_BIG(*) AS cnt
        FROM    dbo.personBrother p1
        JOIN    dbo.personBrother2 p2
        ON      p1.brotherName = p2.brotherName
        WHERE   p1.personId < p2.personId
        GROUP BY
                p1.personId, p2.personId

CREATE UNIQUE CLUSTERED INDEX
        ux_commonBrothers_p1_p2
ON      commonBrothers (p1, p2)

CREATE INDEX
        ix_commonBrothers_cnt
ON      commonBrothers (cnt)

Same for sisters.

You should manually maintain these tables to have same data (write a trigger, insert/update/delete both etc).

Now we can easily get pairs with the most brothers and most sisters:

SELECT  TOP 1 WITH TIES
        *
FROM    commonBrothers
ORDER BY
        cnt DESC

All we need now is to fetch a greatest sum. Unfortunately, we cannot index a join of these views (it's a pure implementation flaw, there's no theoretical limitation for this).

So we need to do the following: the top pair cannot have less brothers than the top sis pair. Same holds for the sisters. So we have this query:

SELECT  TOP 1 WITH TIES
        cb.p1, cb.p2, cb.cnt + cs.cnt AS totalCnt
FROM    commonBrothers cb
JOIN    commonSisters cs
ON      cs.p1 = cb.p1
        AND cs.p2 = cb.p2
WHERE   cs.cnt >=
        (
        SELECT  MAX(cst.cnt)
        FROM    (
                SELECT  TOP 1 WITH TIES
                        p1, p2
                FROM    commonBrothers
                ORDER BY
                        cnt DESC
                ) cbt
        JOIN    commonSisters cst
        ON      cst.p1 = cbt.p1
                AND cst.p2 = cbt.p2
        )
        AND cb.cnt >=
        (
        SELECT  MAX(cbt.cnt)
        FROM    (
                SELECT  TOP 1 WITH TIES
                        p1, p2
                FROM    commonSisters
                ORDER BY
                        cnt DESC
                ) cst
        JOIN    commonBrothers cbt
        ON      cbt.p1 = cst.p1
                AND cbt.p2 = cst.p2
        )
ORDER BY
        totalCnt DESC

If the numbers of common brothers and sisters are correlated, this query will be very fast.

This solution has two drawbacks:

DML performance: if you insert or delete a record for a name shared by million brothers, the indexed view will get 2M inserts or delete. This is the price you pay for real-time query: the kind of data you are asking for cannot be easily indexed.
Persons with 0 brothers or 0 sisters will not be indexed. If there's a chance that top pair will not have brothers or sisters, you should amend the last query a little.


Why can't I set oracle sessions value to 35?

SESSIONS

  Default value Derived: (1.5 * PROCESSES) + 22

  ...

  Oracle uses the default value of this parameter as its minimum. Values
  between 1 and the default do not trigger errors, but Oracle ignores
  them and uses the default instead.

If you want to decrease sessions, you need to decrease processes as well. Still, if you decrease processes, you will have plenty of 'free' sessions, and you will need to open several sessions until you receive the error.

Easiest way to simulate the error without messing with processes and opening several connections:

alter system set dispatchers = '(PROTOCOL=TCP)';
alter system set shared_servers = 1;
alter system set shared_server_sessions = 0;

$ sqlplus user/password@\'127.0.0.1:1521/orcl:shared\'

SQL*Plus: Release 12.1.0.2.0 Production on Sat May 21 11:38:41 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-00018: maximum number of sessions exceeded


Read Committed Snapshop Isolation Level - Effect of NOLOCK

If I understand correctly, a query that uses WITH (NOLOCK) changes the
  isolation level. When this is used in a query, is it now possible for
  a reader to block a process attempting to update the data being read?

No, you think wrong.
Both in RCSI and Read Uncommitted readers don't block writers.

The mechanism is different: RCSI uses last committed copy of modifying data for the reader, and a SELECT using with (nolock) will access "dirty" data, but there is no blocking in both the cases.

Here is an example. You make an update like this:

begin tran
   update dbo.t
   set col1 = 20 where col1 = 10;
-- transaction is still open, X lock is held on a row

In the second session you make SELECT * from dbo.t;

In the case of RCSI you'll get back col1 = 10, in case of Read Uncommitted you'll get col1 = 20, but session 2 will return data in both cases immediately, without waiting when transaction from 1st session commits/rolls back


Change the default version of MSSqlLocalDB for Visual Studio 2015

It took me 2 days to find the solution to this problem.

Posting here for anybody who faces the issue in future.

I basically removed all instances of LocalDB from my system and reinstalled them.
Following are the steps that were required to finally solve the issue

Remove all the databases attached to the LocalDB 2016
Remove LocalDB 2016
Remove LocalDB 2014
Restart the system
Reinstall LocalDB 2014

For installation of LocalDB 2014, either use the standalone file from "https://www.microsoft.com/en-in/download/details.aspx?id=42299" OR
Select Express from standard Sql Server Setup and then select LocalDB in the component list


How to design a table/ unique index where uniqueness is temporal?

I would add a column is_current or something similar that indicates if the movie is a "current" one, then you can create a unique index on only the "current ones"

CREATE UNIQUE INDEX cinema_movie_name_cinema_id_name_idx
    ON cinema_movie_name(cinema_id, name)
where is_current;

You can have a regular job that sets old movies to is_current = false


Optimal data types for UUID and ENUM AWS Aurora for MySQL table DDL

The string 0a3687fr-612f-2345-984c-65c100076267 is only 36 characters long. Why would you need varchar(255) or varchar(192) for that?
If you need it to be even more compact, you can use VARBINARY(16). Then for your UUID, remove the - characters and convert the string to bytes using UNHEX().
For your other columns pattern_type, uses_type, and object_type, I don't recommend using ENUM for columns that are likely to require new values from time to time. I'd use ENUM only when the column is certain to be unchanging.
It would be better to define a lookup table for those types, with a shorter ID. For example, either an integer or a short code. Then you can make reference to it in your table shown above, using a smaller data type.


Automatic Page Repair without AlwaysOn or Mirroring

Yes, if the database participates in Database Mirroring.

But otherwise, no. If you want page repair, you'll need to do it manually with page level restores.


Recommendations on replicating an RDS PostgreSQL instance?

I think that pglogical extension could be be used to solve your issue - in the future, when it is extended to allow a replication user. Currently it requires superuser privileges.

  pglogical is a logical replication system implemented entirely as a
  PostgreSQL extension.

Also, from their documentation:

  Fully integrated, it requires no triggers or
  external programs. This alternative to physical replication is a
  highly efficient method of replicating data using a publish/subscribe
  model for selective replication.

  Use cases supported are:

  Upgrades between major versions (given the above restrictions)
  Full database replication
  Selective replication of sets of tables using replication sets
  Data gather/merge from multiple upstream servers


SQL Server mistaken update rows without backup

DISCLAIMER
  Please be advised that these steps are by no way complete and do not guarantee a restore of your database and/or data. Please use them as a guideline in your specific situation and be aware that a multitude of unknown causes, could result in complete data loss.

  Step 1.) and Step 2.) can be used in any situation

  Further additional steps may be required to bring your data back.

Welcome to DBA.SE. Sorry to hear you're having a bad time.

Handling Your Current Situation

If FULL Recovery model is enabled for this database do the following:

Stop all transactions/connections to the database. NOW !!
Think about your next steps.
Take the database OFFLINE and make a copy of the *.MDF and *.LDF files.
Bring the database back ONLINE.
Perform a Transaction Log Backup (TLOG backup) with the option WITH ... NO_TRUNCATE .... This ensure the data isn't deleted from the TLOG.
This step will only work if the database has previously been backed up with a FULL backup
Restore database to point-in-time on a different database server using the initial FULL backup (before step 1.) and TLOG backup from the second step. The time should be before the accident happened.
Search for the modified data.
Extract data.
Import data into production server after confirming it is correct.
Bring production back online.

If you didn't have the database in FULL recovery model, then changing it now will have no impact. You won't be able to rescue the data, because it probably has already been committed to the database and is no longer available in the TLOG file. If no initial FULL backup has been performed, then these steps won't work.

Cleaning Up

Talk to management about what happened.
Implement a Change Management process.
Implement a solid backup procedure for your database systems.

DBA Tips

Here a couple of basic tips:

Always think about your next steps.
Ensure you have a backup, before you modify data.
If the data is important/productive, ensure you have FULL recovery model enabled and Transaction Log backup is being performed.
Only change data in a productive environment after management has told you to do so and if you have an official Change Request that has been signed off.
Never touch productive data manually, unless it is during a down-time and you have met the requirements for 1. and 2. and 3.


Is possible to restore BD SQL Server without key encryption from 2008 R2?

Is there a means to perform the restoration if I do not know the encryption key?

No.  Currently if you lose the old server you lose the database.  So immediately decrypt the database and take a backup.
The SQL Server service account has access to the Server Master Key, which grants access to the Database Master Key in the Master database, which in turn grants access to the Database Encryption Key of the TDE database.
So assuming that you have the old server still running,  you can log into that server and remove database encryption from the database, then wait for the database files to be decrypted (it's a background process), and take a backup, which will be unencrypted.
See eg https://www.sqlservercentral.com/steps/stairway-to-tde-removing-tde-from-a-database and https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver15


Get a list of SQL scripts that ran on my server

No it doesn't.  SQL Server doesn't really have any concept of script files, it just processes commands that are run against it.

You could potentially set up some kind of automated process and/or tracing to track this, maybe setting the application name to the name of the script and tracing that but there's nothing in the box that I'm aware of which will do this automatically.


When inserting utf8 strings, an invalid byte combination will cause truncation.

You have two different functions; I assume one blindly left a D7 at then end, leading to truncation; and the other stripped it off?


Can I seed Percona XtraDB cluster from the existing backup?

Yes you can.

For do this first start XtraDB Cluster on current server.
Then create an incremental backup from current full backup with --galera-info switch.(It's very important to use --galera-info)
Install Percona server in second server
Prepare the backup files (both backups.Full and Incremental) on second server
Open xtrabackup_galera_info file in your full backup directory and copy uuid and seqno (for example e0d744f3-c88a-11e6-ad0d-6ed1e3c29e6d:0)
create grastate.dat file in full backup directory and add the following lines

version: 2.1

uuid:    e0d744f3-c88a-11e6-ad0d-6ed1e3c29e6d

seqno:   0

cert_index:
Back up is ready to be copied over to the MySQL data directory.
Stop mysql service
Delete all contents in /var/lib/mysql
copy-back prepare backup

innobackupex --copy-back /restore/full
chown mysql directory to mysql.mysql
Config galera variables in my.cnf
Start mysql

You can find more information in this topic:

http://severalnines.com/blog/how-avoid-sst-when-adding-new-node-galera-cluster


I'm very familiar with cryptocurrency and databases, and I can tell you it's not a great DB engine at all.

Using the blockchain as a live database:

Think of it as a first normalized form without any really good built in search capability or indexing as far as the blockchain goes.  Basically a excel sheet without any computation capabilities that just gives you 'read/write' capabilities with lots of verification and validation.  A blockchain is a great way to validate your data is sanitized and correct before you put it in a database which let's you query it differently, index it, etc.

Benefits of the blockchain:

The blockchain in this case is purely a ledger and an API for PUT and GET requests.  That's about it.  The blockchain is interesting because you need a majority of nodes to pass the transaction as valid and there aren't any rollbacks, once it's committed it's committed.  Thus if someone tries to put in a fake transaction it will be caught unless the person doing it has a pool which has a strong majority share.  Then they can validate it in their pool before someone can reject it.  That is the strong point of the blockchain.  Verification that the data is accurate.  It is also typically pretty slow.  You're looking at about 10 minutes under normal load for it to get validated.  Under heavy load the time goes up quite a bit.

After you have validated that the transactions are valid and not fraudulent using the blockchain, you can then import that data into a database and work with it however you like.  I have some experience with this but note that every single transaction on the current bitcoin architecture will be recorded thus it has some interesting info to analyze.

Querying data out of the blockchain schema in a DBMS:

Here is the bitcoin diagram you can use to create the schema in PostgreSQL.  Using this you can then put it in a relational DBMS:
https://bitcointalk.org/index.php?topic=38246

This code repo is also helpful if you want to import the data into a real RDBMS:
https://github.com/bitcoin-abe/bitcoin-abe

As far as what DBMS you should put it in, that's up to your use case.  If you want to analyze the transactions/wallet IDs to see some patterns or do B.I. work I would recommend a relational DB.  If you want to setup a live ingest with multiple cryptocoins I would recommend something that doesn't need the transaction log so a MongoDB solution would be good.  I don't think you need to worry about Elastic Search unless you want to start doing live recording of all cryptocoins at the same time and will use it to do auto trading or something equally crazy. :)


Can't connect with UDL file to SQL Server 2016 but with SSMS it works without problems

I found what caused it - UDL connections doesn't allow using a different Windows NT authentication other than the current user.
The "Custom user" is only for SQL authentication.


SQL Server 2012 Client Statistics Incorrect

Retrieving the execution plan is a call as well (this is why you can see an additional 1 row(s) affected message in the messages pane as well). Try again without the execution plan option.


Creating an array from multiple columns without NULL elements

I would suggest not using a JSON array, and instead using the native SQL array syntax which is likely much faster and more efficiently stored. It's also stronger typed. The JSON array is "possibly-heterogeneously-typed" per the docs.

I also wouldn't do this routinely. I would alter the table's schema to have an ARRAY (preferably SQL) on the table itself to store the tags without ever storing null in columns. This can put you down the path of correcting the schema.

Building arrays

Strictly-typed PostgreSQL array

Just use the ARRAY literal constructor.

 SELECT id, ARRAY[tag_1,tag_2,tag_3,tag_4] FROM foo;
 id |             array
----+-------------------------------
  1 | {Router,Networking,Sale,NULL}
  2 | {NULL,Router,Networking,Sale}
  3 | {NULL,NULL,Networking,Sale}
  4 | {NULL,NULL,NULL,NULL}

A JSON array

SELECT id, json_build_array(tag_1,tag_2,tag_3,tag_4) FROM foo;
 id |            json_build_array
----+----------------------------------------
  1 | ["Router", "Networking", "Sale", null]
  2 | [null, "Router", "Networking", "Sale"]
  3 | [null, null, "Networking", "Sale"]
  4 | [null, null, null, null]
(4 rows)

Filtering Nulls without manual coalesce

Strictly-typed PostgreSQL array

You can easily filter nulls in a single pass by wrapping the above in array_remove.

SELECT id, array_remove(ARRAY[tag_1,tag_2,tag_3,tag_4], null)
FROM foo;

 id |       array_remove
----+--------------------------
  1 | {Router,Networking,Sale}
  2 | {Router,Networking,Sale}
  3 | {Networking,Sale}
  4 | {}

JSON array

SELECT id,jsonb_agg(elem)
FROM (SELECT id, ARRAY[tag_1,tag_2,tag_3,tag_4] FROM foo) AS g
CROSS JOIN LATERAL unnest(g.array)
  WITH ORDINALITY AS t(elem,ord)
WHERE elem IS NOT NULL
GROUP BY id
ORDER BY id;

 id |            jsonb_agg
----+----------------------------------
  1 | ["Router", "Networking", "Sale"]
  2 | ["Router", "Networking", "Sale"]
  3 | ["Networking", "Sale"]


How can I make UPDATE to return an error if no rows are affected in Postgresql?

It would be possible to put the UPDATE into a CTE and then do something with the number of results:
WITH cte AS (
  UPDATE ...
  RETURNING ...
)
SELECT 1 / COUNT(*) FROM cte;

If the division by zero is too obscure for you, write a function that raises a proper error.
Personally, I would use a plain UPDATE, and then in my application, wrap all such SQL calls in a function that checks the number of rows. (PG returns that number from every UPDATE, but not every database driver supports this.)


How to remotely manage (with PhpMyAdmin) a MySQL RDS on a load-balanced AWS Elastic Beanstalk setup?

Ok, so I finally figured it out. I was close. It turns out I was doing the SSH tunneling wrong, and that's because I had no idea what I was doing. So after a bit more research I realized that I needed to change the remote host to be the RDS, not the EC-2.

So I ended up running the ssh command like this:

ssh -L 3306:my-rds-endpoint:3306 ec2-user@my-ec2-endpoint -i /path/to/aws-eb/key

Or add -fN if you want to run it in the background:

ssh -fNL 3306:my-rds-endpoint:3306 ec2-user@my-ec2-endpoint -i /path/to/aws-eb/key

The config.inc.php ended up being configured like so:

$cfg['Servers'][$i]['host']          = '127.0.0.1';
$cfg['Servers'][$i]['port']          = '3306';
$cfg['Servers'][$i]['connect_type']  = 'tcp';
$cfg['Servers'][$i]['extension']     = 'mysqli';
$cfg['Servers'][$i]['compress']      = FALSE;
$cfg['Servers'][$i]['auth_type']     = 'cookie';

So yeah, my issue was understanding the ssh command, and realizing that I needed to change it so it would tunnel to the RDS endpoint through the EC2 instance. Also, I had some weird issue with my mac's local MySQL still running in the background, so it wouldn't allow a new connection to the 3306 port. Once I ran mysqladmin -u root -p shutdown PhpMyAdmin could finally connect to the RDS!


Problem with MySQL Master/Slave reboot when in replication

Step 01) STOP SLAVE; on all Slaves
Step 02) service mysql stop on the Master
Step 03) Reboot Master
Step 04) For each Slave

Reboot each Slave
If /etc/my.cnf has skip-slave-start, then run START SLAVE;

Step 05) Check that each Slave is fully running

Run the following on the command line

SLAVETHREADCOUNT=`mysql -u... -p... -AN -e"SHOW PROCESSLIST" | grep -c "system user"`
echo ${SLAVETHREADCOUNT}

If SLAVETHREADCOUNT is 2, replication is fully operational.

Give it a Try !!!


Group data by Custom Period Ranges Using a Reference Date

I hope I understand this right and you can use this for at least a start.

SELECT x.startdate + INTERVAL (x.days + 1) DAY enddate,
       x.startdate,
       (SELECT sum(t.volumes)
               FROM tab t
               WHERE t.createdAt >= x.startdate
                     AND t.createdAt < x.startdate + INTERVAL (x.days + 1) DAY) volume
       FROM (SELECT '2018-04-12' startdate,
                    12 days
             UNION
             SELECT '2018-03-29' startdate,
                    12 days
             UNION
             SELECT '2018-03-15' startdate,
                    12 days
             UNION
             SELECT '2018-03-01' startdate,
                    12 days
             UNION
             SELECT '2018-02-15' startdate,
                    12 days
             UNION
             SELECT '2018-02-01' startdate,
                    12 days
             UNION
             SELECT '2018-01-18' startdate,
                    12 days) x;

I haven't quite gotten on how you'd like to specify the dates and intervals though. Maybe you can clarify that, than I can probably edit this in.


VACUUM returning disk space to operating system

To return space to the OS, use VACUUM FULL. While being at it, I suppose you run VACUUM FULL ANALYZE. I quote the manual:

FULL
Selects "full" vacuum, which can reclaim more space, but takes much
longer and exclusively locks the table. This method also requires
extra disk space, since it writes a new copy of the table and doesn't
release the old copy until the operation is complete. Usually this
should only be used when a significant amount of space needs to be
reclaimed from within the table.

Bold emphasis mine.
CLUSTER achieves that, too, as a collateral effect.
Plain VACUUM does not normally achieve your goal ("one or more pages at the end of a table entirely free"). It does not reorder rows and only prunes empty pages from the physical end of the file when the opportunity arises - like your quote from the manual reveals.
You can get empty pages at the end of the physical file when you INSERT a batch of rows and DELETE them before other tuples get appended. Or it can happen by coincidence if enough rows are deleted.
There are also special settings that might prevent VACUUM FULL from reclaiming space. See:

How to optimize a table for a very high-frequency updates?

Prepare empty pages at the end of a table for testing
The system column ctid represents the physical position of a row. You need to understand that column:

How do I decompose ctid into page and row numbers?

We can work with that and prepare a table by deleting all rows from the last page:
DELETE FROM tbl t
USING (
   SELECT (split_part(ctid::text, ',', 1) || ',0)')::tid     AS min_tid
        , (split_part(ctid::text, ',', 1) || ',65535)')::tid AS max_tid
   FROM   tbl
   ORDER  BY ctid DESC
   LIMIT  1
   ) d
WHERE  t.ctid BETWEEN d.min_tid AND d.max_tid;

Now, the last page is empty. This ignores concurrent writes. Either you are the only one writing to that table or you need to to take a write lock to prevent race conditions.
The query is optimized to identify qualifying rows quickly. The second number of a tid is the tuple index stored as unsigned int2, and 65535 is the maximum for that type (2^16 - 1), so that's the safe upper bound.
db<>fiddle here  (reusing a simple table from a different case.)
Old sqlfiddle
Tools to measure row / table size:

Measure the size of a PostgreSQL table row

Disk full
You need wiggle room on disk for any of these operations. There is also the community tool pg_repack as replacement for VACUUM FULL / CLUSTER. It avoids exclusive locks but needs free space to work with as well. The manual:

Requires free disk space twice as large as the target table(s) and indexes.

As a last resort, you can run a dump/restore cycle. That removes all bloat from tables and indexes, too. Closely related question:

I need to run VACUUM FULL with no available disk space

The answer over there is pretty radical. If your situation allows for it (no foreign keys or other references preventing row deletions), and no concurrent access to the table), you can just:
Dump the table to disk connecting from a remote computer with plenty of disk space (-a for --data-only):
From a remote shell, dump table data:
pg_dump -h <host_name> -p <port> -t mytbl -a mydb > db_mytbl.sql

In a pg session, TRUNCATE the table:
-- drop all indexes and constraints here for best performance
TRUNCATE mytbl;

From remote shell, restore to same table:
psql -h <host_name> -p <port> mydb -f db_mytbl.sql
-- recreate all indexes and constraints here

It is now free of any dead rows or bloat.
But maybe you can have that simpler?

Can you make enough space on disk by deleting (moving) unrelated files?

Can you VACUUM FULL smaller tables first, one by one, thereby freeing up enough disk space?

Can you run REINDEX TABLE or REINDEX INDEX to free disk space from bloated indexes?

Whatever you do, don't be rash. If in doubt, backup everything to a secure location first.