Is it possible to use a stored procedure to read the file inside the blob column, loop on each line and insert the data in a table?

Definitely.

However, I would consider rethinking this plan.

A SQL Server database generally isn't a great place to be storing BLOBs, particularly given that you're just going to turn around and process them into row data later. It's a lot of extra disk activity and (presumably more expensive) storage that you just don't need to use. Also, the kind of processing being proposed will almost certainly perform worse than the many direct-processing alternatives. Generally speaking, the less processing you need to do (and the simpler the process itself), the better performance you'll get out of the system. And it will probably be more reliable as well.

Why not turn the files into row data immediately? Are you concerned about blocking or latency of the client application? If that's the only concern, consider setting up an asynchronous queuing system, possibly by using Service Broker. You can use BULK INSERT to turn the CSV files into row data directly from the file system without first loading the files as BLOBs. If this is going to blast the CPU during load when you need to run other things on the same server, consider using Resource Governor if you're on Enterprise Edition.

If you have to process the files in batch at night due to other constraints, it may be better to simply direct the raw files into a named (YYYYMMDD) folder on a network share during the day, and then once/day use an SSIS package with a Foreach File Enumerator container to process the files. I suppose this could also work in a job-based scenario where you just fire it up every 15 minutes or so to process and remove files that landed in the folder in the last period. SSIS may also be a good solution if you need some kind of transformation process to happen between the raw files and the row data.

There are lots of different possibilities here depending on your exact requirements, but I think I've given enough of the more common elements that you can piece together a solution that will work best for your situation.


Would you use MongoDB in high-transaction environment?

MongoDB can survive high concurrency writes, so long as you engineer for it.

INSERTs are about the same size.
Very little UPDATE going on.
Shard to add write concurrency scalability.

The things that will kill you on write scalability will be updates that go past the built in padding factor, as that triggers a double write as the block is relocated to a larger free spot. If your document sizes are within a narrow range and updates don't change the size much, it can scale quite far.


guessing unused junk space in table. (innodb mysql/mariadb )

After digging the question, I went with a SELECT like this one:

SELECT table_name from information_schema.tables WHERE table_schema = 'mydb' AND DATA_FREE > 0 ORDER by DATA_FREE DESC;

So I have a list of table to process.

a scripted form would be :

#! /bin/bash
[ -z "$1" ] && echo usage "$0 [database_name]"
[ -z "$1" ] && exit 1
for T in $(mysql -ss -e "SELECT table_name from information_schema.tables WHERE table_schema = \"$1\" AND DATA_FREE > 0 ORDER by DATA_FREE DESC;");do echo \"$T\";time mysql -e "SET sql_log_bin=0;alter table $1.$T ENGINE=InnoDB";done 2>&1


Cannot connect Power BI Desktop to Azure SQL!

To get around this problem, notice that "Windows" is highlighted at the left of the dialog box. I clicked on Database, entered the database credentials, and then it worked.


PostgreSQL: Trigger for updating view doesn't work

Because update trigger is invoked once per row being updated, it is not invoked at all if no rows are going to be updated.

See for yourself:

create table test(i int);
create view test_view as select i from test;

CREATE OR REPLACE FUNCTION test_trigger_function()
RETURNS trigger AS
$$
    BEGIN
        RAISE NOTICE 'hi';
        RETURN NEW;
    END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER test_trigger
INSTEAD OF INSERT OR UPDATE
    ON test_view
        FOR EACH ROW
            EXECUTE PROCEDURE test_trigger_function();

insert into test_view(i) values(1);

SELECT * FROM TEST;
SELECT * FROM test_view;

UPDATE test_view set i=1;
-- trigger did not fire

insert into test(i) values(1);

UPDATE test_view set i=3;
-- trigger fired

SELECT * FROM test_view;


SQL Server Database Keeps Semi-Detaching Itself

You have "auto close" switched on probably

Turn it off: it isn't a good idea And again

ALTER DATABASE MyProblemDatabase SET AUTOCLOSE OFF;


As I understand, you are performing a full backup and log backups on server 1, then you perform a fail over to server 2.

When you start performing the log backups on server 2 it follows the log chain.

So to answer your question: No it is not needed to perform a full backup job every time your availability group fails over.

I suggest that you test this in a pre-production environment. Do full backup on server 1, perform log backups on server 1, fail over, do log backup on server 2. Restore on another server the full backup and the log backups from server 1 followed with the log backups from server 2.

Good luck.

Stan


Access 2010 linked table - solve the "decimal field's precision" error

I had a similar problem, the fix was a linked table refresh.  Access was not aware that the precision had changed in my Oracle table and kept giving me the 'precision too small' error.


I believe this sentiment was true at one point, but not in current versions of SQL Server. The whole problem was that in the old days ad hoc SQL statements could not be properly optimized because SQL Server could only optimize / compile at the batch level. Now we have statement-level optimization, so a properly parameterized query coming from an application can take advantage of the same execution plan as that query embedded in a stored procedure.

I still prefer stored procedures from the DBA side for the following reasons (and several of them can have a huge impact on performance):

If I have multiple apps that re-use the same queries, a stored procedure encapsulates that logic, rather than littering the same ad hoc query multiple times in different codebases. Applications that re-use the same queries can also be subject to plan cache bloat, unless they are copied verbatim. Even differences in case and white space can lead to multiple versions of the same plan being stored (wasteful).
I can inspect and troubleshoot what a query is doing without having access to the application source code or running expensive traces to see exactly what the application is doing.
I can also control (and know in advance) what queries the application can run, what tables it can access and in what context, etc. If the developers are writing queries ad-hoc in their application, they're either going to have to come tug my shirt sleeve every time they need access to a table that I didn't know about or couldn't predict, or if I'm less responsible/enthused and/or security-conscious, I'm just going to promote that user to dbo so they stop bugging me. Typically this is done when the developers outnumber the DBAs or the DBAs are stubborn. That last point is our bad, and we need to be better about providing the queries that you need.
On a related note, a set of stored procedures is a very easy way to inventory exactly what queries may be running on my system. As soon as an application is allowed to bypass procedures and submit its own ad-hoc queries, in order to find them, I have to run a trace that covers an entire business cycle, or parse through all of the application code (again, that I might not have access to) to find anything that looks like a query. Being able to see the list of stored procedures (and grep a single source, sys.sql_modules, for references to specific objects) makes everyone's lives much easier.
I can go to much greater lengths to prevent SQL injection; even if I take input and execute it with dynamic SQL, I can control a lot of what is allowed to happen. I have no control over what a developer is doing when constructing inline SQL statements.
I can optimize the query (or queries) without having access to application source code, the ability to make changes, the knowledge of the application language to do so effectively, the authority (never mind the hassle) to re-compile and re-deploy the app, etc. This is particularly problematic if the app is distributed.
I can force certain set options within the stored procedure to avoid individual queries from being subject to some of the Slow in the application, fast in SSMS? problems. Meaning that for two different applications calling an ad hoc query, one could have SET ANSI_WARNINGS ON, and the other could have SET ANSI_WARNINGS OFF, and they would each have their own copy of the plan. The plan they get depends on the parameters in use, stats in place, etc. the first time the query is called in each case, which could lead to different plans and hence very different performance.
I can control things like data types and how parameters are used, unlike certain ORMs - some earlier versions of things like EF would parameterize a query based on the length of a parameter, so if I had a parameter N'Smith' and another N'Johnson' I would get two different versions of the plan. They've fixed this. They've fixed this but what else is still broken?
I can do things that ORMs and other "helpful" frameworks and libraries are not yet able to support.

That all said, this question is likely to stir up more religious arguments than technical debate. If we see that happening we'll probably shut it down.


Make Customer_Id a foreign key referencing User_Id. Employee_Id will also be a foreign key referencing User_Id. As a new user is INSERTed and the User_Id generated use that same value in Customer_Id or Employee_Id (or both) as appropriate.

If name and age are common to both employees and customers, hold them in the super-type (user). Never ever hold age. It changes all the time. Hold Date_of_birth, which never changes, and calculate age when it is required. Even if you need the user's age when the row was written you would be better off with Date_of_birth and Row_created_date.

A customer should be allowed to have many purchases. Storing them all in a single column breaks first normal form. If your implementation is in a document DBMS then that's OK, but should be acknowledged. If you plan to use a relational database you really should pull it into its own entity type early on, during design.

Don't store the password in plain text. It should only ever be the salted, encrypted version that is persisted. There are plenty of articles on the web that explain why and how to implement this.


Does concating a string like this open me up to SQL injection?

No. That value will always be interpreted as a string and there's no way to put anything into the emaildomain column that could make the query execute unexpected code. It's either going to find matches or it's not.

Of course, this is relevant to the specific code sample you posted, and only that. You should always be concerned about any data you accept from a user... since you might not know where else it might be used now or in the future.


Query with two LEFT JOIN's is not working as expected

Let's assume your tables are defined:

CREATE TABLE affiliate_guests
(
    user_id serial not null primary key,
    code text
    -- etc.
) ;

CREATE TABLE transfers
(
    transfer_id serial not null primary key,
    user_id integer not null references affiliate_guests(user_id),
    amount numeric not null
    -- etc.
) ;

CREATE TABLE deposits
(
    deposit_id serial not null primary key,
    user_id integer not null references affiliate_guests(user_id),
    amount numeric not null,
    type text
    -- etc.
) ;

... and we populate them with some sample data:

INSERT INTO
    affiliate_guests (user_id, code)
VALUES
    (1, 'code') ;
INSERT INTO
    transfers (user_id, amount)
VALUES
    (1, 1000.00),
    (1, 2000.00),
    (1, 1234.00) ;

INSERT INTO deposits (user_id, amount, type)
VALUES
    (1, 1000.00, 'deposit'),
    (1, 2345.00, 'ignoreit'),
    (1, 3456.00, 'ignoreit as well'),
    (1, 9999.00, 'deposit') ;

In order to understand why your query is not working as you expect, check first the result of executing your same query, but without GROUP BY, and changing your aggregates by just values (I've also added some extra ids for ease of interpretation):

SELECT
    aff.user_id, transfer_id, t.amount AS t_amount, deposit_id, d.amount AS d_amount
FROM
    affiliate_guests aff
    LEFT JOIN transfers t ON aff.user_id = t.user_id
    LEFT JOIN deposits d ON aff.user_id = d.user_id AND d.type = 'deposit'
WHERE
    aff.code = 'code' ;

This is what you'll get:

|---------+-------------+----------+------------+----------|
| user_id | transfer_id | t_amount | deposit_id | d_amount |
|---------+-------------+----------+------------+----------|
|    1    |      1      | 1000.00  |     1      | 1000.00  |
|---------+-------------+----------+------------+----------|
|    1    |      1      | 1000.00  |     4      | 9999.00  |
|---------+-------------+----------+------------+----------|
|    1    |      2      | 2000.00  |     1      | 1000.00  |
|---------+-------------+----------+------------+----------|
|    1    |      2      | 2000.00  |     4      | 9999.00  |
|---------+-------------+----------+------------+----------|
|    1    |      3      | 1234.00  |     1      | 1000.00  |
|---------+-------------+----------+------------+----------|
|    1    |      3      | 1234.00  |     4      | 9999.00  |
|---------+-------------+----------+------------+----------|

As there are not any relationship between your tables t and d, what you actually get is the cartesian product of both of them. You clearly see it because you have all possible combinations of transfer_id and deposit_id.

You need to make this by using subquerys, instead:

SELECT
    aff.user_id,
    coalesce( (SELECT sum(amount)
                 FROM transfers t
                WHERE t.user_id = aff.user_id), 0) AS total_bet,
    coalesce( (SELECT sum(amount)
                 FROM deposits d
                WHERE d.user_id = aff.user_id AND d.type='deposit'), 0) AS total_deposit
FROM
    affiliate_guests aff
WHERE
    aff.code = 'code' ;

That will get you what you expect:

|---------+-----------+-----------|
| user_id | total_bet | total_bet |
|---------+-----------+-----------|
|    1    |  4234.00  | 10999.00  |
|---------+-----------+-----------|

See Identifying and Eliminating the Dreaded Cartesian Product


How to profile the PostgreSQL query planner diagnose what is causing it to take up large amounts of memory and time

The first stop to investigating this be would the system tool "perf".  You can get the pid of the running backend (by using "top", for example--make sure you get the pid of the backend, not the client program) then do sudo perf top -p <pid>.  You might get more useful output by first installing the debug symbols. How you do that is going to depend on your OS and how you installed it.

But once you identify the bottleneck, I wouldn't hold out much hope that anyone will do anything about it, given the far-out nature of your query.  If you are willing to dig into the source code to propose our own solution, then I would suggest you start by compiling your own server with version 13dev.  You aren't going to get very far in your investigations using only packaged versions, and especially not older packaged versions.  (Also, the problem might already be fixed in 12 or 13dev).


How to do a while loop to delete rows based on rowcount?

As I understand the problem, you want to leave 1000 rows in a table that starts with > 1 million.   In SQL Server, I ran I to problems with the delete statement locking the table, and also taking a long time to execute.  All the deleted records have to go into the transaction log.

What worked better was to select the records I wanted to keep into a new table, and then rename the tables.

With Oracle, your mileage may vary


Mysql fulltext search my.cnf optimization

I have an interesting surprise for you.
The only Optimizing for FullText Indexing you can do is not something at the my.cnf level. It is all about two things:

The Stopword List
The Query

STOPWORDS
There are 543 stopwords that you may or may not want filtered out of FULLTEXT indexes. The list of stopwords was built at compile time. You can override that list with your own list as follows:
OK, now let's create our stopword list. I usually set the English articles as the only stopwords.
echo "a"    > /var/lib/mysql/stopwords.txt
echo "an"  >> /var/lib/mysql/stopwords.txt
echo "the" >> /var/lib/mysql/stopwords.txt

Next, add the option to /etc/my.cnf plus allowing 1-letter, 2-letter, and 3 letter words
[mysqld]
ft_min_word_len=1
ft_stopword_file=/var/lib/mysql/stopwords.txt

Finally, restart  mysql
service mysql restart

If you have any tables with FULLTEXT indexes already in place, you must drop those FULLTEXT indexes and create them again.
QUERY
Here is a little known fact about MySQL queries using a Full Table Index: There are occasions when the MySQL Query Optimizer stops using FULLTEXT indexes altogether and perform full table scans.
Here is an example:
use test
drop table if exists ft_test;
create table ft_test
(
    id int not null auto_increment,
    txt text,
    primary key (id),
    FULLTEXT (txt)
) ENGINE=MyISAM;
insert into ft_test (txt) values
('mount camaroon'),('mount camaron'),('mount camnaroon'),
('mount cameroon'),('mount cemeroon'),('mount camnaroon'),
('mount camraon'),('mount camaraon'),('mount camaran'),
('mount camnaraon'),('mount cameroan'),('mount cemeroan'),
('mount camnaraon'),('munt camraon'),('munt camaraon'),
('munt camaran'),('munt camnaraon'),('munt cameroan'),
('munt cemeroan'),('munt camnaraon'),('mount camraan');
select * from ft_test WHERE  MATCH(txt) AGAINST ("+mount +cameroon" IN BOOLEAN MODE);

Here is that sample data loaded:
mysql> use test
Database changed
mysql> drop table if exists ft_test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table ft_test
    -> (
    ->     id int not null auto_increment,
    ->     txt text,
    ->     primary key (id),
    ->     FULLTEXT (txt)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into ft_test (txt) values
    -> ('mount camaroon'),('mount camaron'),('mount camnaroon'),
    -> ('mount cameroon'),('mount cemeroon'),('mount camnaroon'),
    -> ('mount camraon'),('mount camaraon'),('mount camaran'),
    -> ('mount camnaraon'),('mount cameroan'),('mount cemeroan'),
    -> ('mount camnaraon'),('munt camraon'),('munt camaraon'),
    -> ('munt camaran'),('munt camnaraon'),('munt cameroan'),
    -> ('munt cemeroan'),('munt camnaraon'),('mount camraan');
Query OK, 21 rows affected (0.00 sec)
Records: 21  Duplicates: 0  Warnings: 0

mysql>

Here is a sample query and its EXPLAIN plan
mysql> select * from ft_test WHERE  MATCH(txt) AGAINST ("cameroon" IN BOOLEAN MODE);
+----+----------------+
| id | txt            |
+----+----------------+
|  4 | mount cameroon |
+----+----------------+
1 row in set (0.00 sec)

mysql> explain select * from ft_test WHERE  MATCH(txt) AGAINST ("cameroon" IN BOOLEAN MODE)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ft_test
         type: fulltext
possible_keys: txt
          key: txt
      key_len: 0
          ref:
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

mysql>

OK Great the FULLTEXT Index is used.
Now, let's change the query a slight bit
mysql> select * from ft_test WHERE  MATCH(txt) AGAINST ("cameroon" IN BOOLEAN MODE) = 1;
+----+----------------+
| id | txt            |
+----+----------------+
|  4 | mount cameroon |
+----+----------------+
1 row in set (0.00 sec)

mysql> explain select * from ft_test WHERE  MATCH(txt) AGAINST ("cameroon" IN BOOLEAN MODE) = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ft_test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 21
        Extra: Using where
1 row in set (0.00 sec)

mysql>

OMG What happened to the FULLTEXT index? The MySQL Query optimizer basically barfed at it. If you were performing a JOIN with the ft_test table, once the WHERE clause on the fulltext search is issued and it does the same then, who knows what on earth will happen to the rest of the query.
The solution would be to refactor the query ans attempt to isolate the FULLTEXT search and gather the keys only. Then LEFT JOIN those keys to the original table.
EXAMPLE
SELECT B.*
FROM (SELECT id from ft_test
WHERE MATCH(txt) AGAINST ("+cameroon" IN BOOLEAN MODE)) A
LEFT JOIN ft_test B USING (id);

For this query, here is the result and its EXPLAIN
mysql> SELECT B.*
    -> FROM (SELECT id from ft_test
    -> WHERE MATCH(txt) AGAINST ("+cameroon" IN BOOLEAN MODE)) A
    -> LEFT JOIN ft_test B USING (id);
+----+----------------+
| id | txt            |
+----+----------------+
|  4 | mount cameroon |
+----+----------------+
1 row in set (0.00 sec)

mysql> explain SELECT B.*
    -> FROM (SELECT id from ft_test
    -> WHERE MATCH(txt) AGAINST ("+cameroon" IN BOOLEAN MODE)) A
    -> LEFT JOIN ft_test B USING (id)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: B
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra:
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: ft_test
         type: fulltext
possible_keys: txt
          key: txt
      key_len: 0
          ref:
         rows: 1
        Extra: Using where
3 rows in set (0.00 sec)

mysql>

Notice that in the DERIVED2 part of the EXPLAIN plan, the FULLTEXT index was indeed used.
MORAL OF THE STORY
You will have to get into the habit of deciding how many stopwords your database will have, creating that stopword list, configuring it, and then create/recreate all FULLTEXT indexes. You must also get into the habit of refactoring your FULLTEXT search queries in such a way that the MySQL Query Optimizer does not generate a bad EXPLAIN plan or nullify indexes for the rest of the query participating in the EXPLAIN plan.


What is the best configuration for a MySQL instance with a lot of databases and lot of tables?

There is a reason you will need more RAM ... the INFORMATION_SCHEMA database.
What has to get populated in it ??? Let pick just two tables as an example:

INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.COLUMNS

Imagine (all the columns in the tables + the number of tables) multiplied by 3000 ???
The metadata would be need lots of RAM to map them out. This is not to mention mapping indexes, constraints, views plus dozens of other database objects.
A client my employer had once had a Galera Cluster with 11000 databases with close to 2 million tables.

It took at least 24 hours to start mysqld and the Galera components.
Crash recovery made this nightmare scenario worse

See my old posts where I discussed memory consumption by the INFORFMATION_SCHEMA

How is INFORMATION_SCHEMA implemented in MySQL?
Adding new tables -- memory usage increases
Do dormant databases impact server performance?
#1041 - Out of memory issue in mysql

WHAT YOU COULD DO
Please do the following:

Create a new MySQL instance on another server.
While the MySQL instance is empty, record how much RAM the server has free.
Create all the databases and tables in that instance but do not load any data.
After creating the databases and tables, record how much RAM the server has free.
Shutdown mysql
Wait like 1-2 minutes
Startup mysql
Record how much RAM the server has free and how long it took to start back up.

This will given a general idea (or a ballpark figure) how much RAM you will need to add.
Whatever number you choose to increase by for RAM, DOUBLE THAT PLEASE !!!
Increasing RAM will not reduce crash recovery time. Also, do not forget that DB Connections need memory love too (How costly is opening and closing of a DB connection?)


How can I check the path of my.ini, through the command line?

mysqld.exe does not report the location of the my.ini it is using. I learned over the years that the my.ini file is supposed to be in the parent folder of the data and bin directories:

Feb 06, 2012 : MySQL Database and Table Loss
Jan 14, 2014 : MySql InnoDB tables are missing after restarting (See INVESTIGATION #2)

What can you do form the command line ?

From the command line, run this

C:\> mysqld --help --verbose > C:\mysqld_options.txt
notepad C:\mysqld_options.txt

Go look for the line that says

Default options are read from the following files in the given order:

The line underneath will tell you the places where mysqld looks for my.ini

If you don't see one, then use the parent folder of the data and bin directory to make one.

Give it a Try !!!


ORA-04031: unable to allocate 4708660 bytes of shared memory

I'd recommend limiting the number of rows you fetch in your BULK COLLECT and wrapping the whole thing inside a loop:

 -- create our output table
 create table temp as
 select
   object_type as biz_hierarchy,
   object_name as project_team,
   object_name as user_roles,
   object_name as username,
   object_name as sso
 from all_objects where 1=0;
/

DECLARE
  TYPE t_varchar IS TABLE OF varchar(512 char);
  biz_hierarchy t_varchar := t_varchar();
  project_team  t_varchar := t_varchar();
  user_roles    t_varchar := t_varchar();
  username      t_varchar := t_varchar();
  sso           t_varchar := t_varchar();
  cursor cur is
    select object_type as biz_hierarchy,
           object_name as project_team,
           object_name as user_roles,
           object_name as username,
           object_name as sso
      from all_objects
     where rownum <= 25000;
BEGIN
  open cur;
  loop
    fetch cur BULK COLLECT
      INTO biz_hierarchy,
           project_team,
           user_roles,
           username,
           sso limit 1000;
    FORALL i IN biz_hierarchy.FIRST .. biz_hierarchy.LAST
      INSERT INTO temp
      VALUES
        (biz_hierarchy(i),
         project_team(i),
         user_roles(i),
         username(i),
         sso(i));
    exit when cur%notfound;
  end loop;

END;

see Asktom on large bulk collect for further info


One-to-many dictionary (lookup) table vs varchar vs enum?

Well, I am an advocate of ENUM -- at least in limited use.

I would use it for status with a small, reasonably static, list of possible values.  I would start with unknown to catch things that are typos.  ALTER TABLE has long been optimized to add a new option on the end of an ENUM list.

I would not use ENUM for continents.  If there are standard abbreviations, I would use a short VARCHAR for such.  For countries, I advocate

country CHAR(2) CHARACTER SET ascii

Using a lookup table can be very bad for efficiency.  A "star" schema in Data Warehousing can be terribly inefficient.  This happens when multiple JOINs are needed; ENUM avoids the inefficiency.  VARCHAR is bulky, which is a big issue in DW applications.

An ENUM acts like a string in many situations:  WHERE status = 'OK' is more readable than either of the alternatives.

Comparing two ints versus two strings: Ints is not enough faster to matter.

VARCHAR -- Use when bulkiness is not a problem
ENUM -- Use when bulkiness is a problem, and the number of choices is quite small and stable.
Dictionary lookup -- Use when multiple tables need to reference the same set of stuff, and/or when there is extra info in the Dictionary.


DBMS_SCHEDULER event schedule jobs not executed when triggered almost at the same time

And this documented behaviour can be bypassed by setting this attribute to the job before its enabled:

DBMS_SCHEDULER.set_attribute ('my_job_name', 'parallel_instances', TRUE);


Corrupt MySQL log file - cannot start the database

Was able to do a mysqldump, remove everything. Do a clean reinstall of mysql and import it back. Thanks for the help !


pgAdmin 4 - Can't Create Script on a database

This CREATE Script option won't display in the context menu if you are connecting to the server with a user that does not have the SUPERUSER
privilege or which does not have the CREATEDB privilege.


Not something I would recommend doing, but:

select test.id, nvl(test.name, list.column_value) as name, test.ddate
from
  test
  full outer join table(sys.odcivarchar2list('b', 'e')) list
on test.name = list.column_value
where nvl(ddate, date'2018-02-21') = date'2018-02-21'
order by id nulls first
;

        ID NAME       DDATE
---------- ---------- --------------------
           e
         2 b          21-FEB-18
         3 c          21-FEB-18


select count(author) from message where room=12;

Because author can not be null just do SELECT count(*). Doing so may result in this being an index-only scan on either message_room_created or message_room_id. If you need the unique authors, which you don't have you'll have to use count( DISTINCT author ).

Also this being planned as a parallel query. That's relatively new functionality. You may want to try SET max_parallel_workers_per_gather = 0 to disable parallel query functionality and reposting the results.

Either way

  this one is slow (same duration, about 45ms)

If you've got millions rows an index scan and heap fetches, it's not really fair to call 45ms "slow."


No issues with using the slave DB if you are ok with having the site in read-only until the cutover.

You could turn the READ_ONLY=1 in my.cnf (set global read_only=1) in the slave just to make sure there are no unintentional writes to the slave.


PostgreSQL Streaming Replication - LOG: archive command failed with exit code 1

There are two possibilities:

You have archive_mode = always on the standby.

Then the standby server will try to archive WAL segments (redundantly).
The standby server is not really a standby server, but a standalone database. That means that you have made a mistake setting up the standby server.

The way to determine is that is the case, run

SELECT pg_is_in_recovery();

If that returns FALSE, the server is not a standby.


How to investegate MySQL crash on windows?

I figured out a solution.

I run MySQL via the command line

I disabled this line log-error=.... from the my.ini file.

I stopped MySQL service.
Then I opened a command line and run this

mysqld.exe --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" --console

This showed me that I run into a bug with MySQL


SQL Server: Is it possible to create a calculated index

Yes it is possible to do this, but not with the syntax you used. The key is that you need to create a computed column in the table and then index that:

ALTER TABLE customers ADD MonthOfBirth AS MONTH(dob) PERSISTED;
CREATE INDEX customermonth ON customers (MonthOfBirth);

Computed column is SQL Server's name for what other products call a generated column, and in place of STORED (cf. VIRTUAL) we use PERSISTED.

See How to create indexes on computed columns in SQL Server by Atif Shehzad.

Naturally, this requires that you have permission to alter the schema of the table, and you'll need to ensure that there won't be any nasty side effects of adding a column (like breaking reports that use SELECT * FROM customers;)

Computed columns do not always have to be PERSISTED before you create an index on them. See the documentation on the topic.


Timezone considerations when choosing an appropriate datatype

Use the datetimeoffset SQL Server data type which supports timezone details:

USE tempdb;

DROP TABLE IF EXISTS dbo.timetest;
CREATE TABLE dbo.timetest
(
    t datetimeoffset(0) NOT NULL
);

INSERT INTO dbo.timetest(t)
VALUES ('2018-04-18 00:00:00 +03:00');

SELECT [Time at CEST] = t AT TIME ZONE 'Central European Standard Time'
    , [Time at RST] = t AT TIME ZONE 'Russian Standard Time'
FROM dbo.timetest;

The results:

╔════════════════════════════╦════════════════════════════╗
║        Time at CEST        ║        Time at RST         ║
╠════════════════════════════╬════════════════════════════╣
║ 2018-04-17 23:00:00 +02:00 ║ 2018-04-18 00:00:00 +03:00 ║
╚════════════════════════════╩════════════════════════════╝

In the above example, we're inserting data in the Russian Time Zone, and then displaying it in both Central European Standard Time, as well as Russian Standard Time.

You can use the SYSDATETIMEOFFSET() function to obtain the current time at the server, including the servers time zone.


Capitalize only the first letter of a name

Another way is to use STUFF to replace the first character directly:

-- Demo
SELECT
    STUFF
    (
        O.name,
        1, 1,
        UPPER(LEFT(O.name, 1))
    )
FROM sys.objects AS O;


DDL Trigger not working as expected SQL Server 2012

Your trigger has no problem at all (the only question I have is why do you open another transaction inside your trigger if all you do is 1 SELECT only?)

When you alter a table in such a way it has to be recreated (for example adding a column not at the end of the existing columns), the actions server takes are following (simplified):

create a new table called dbo.Tmp_old_tbl having the new
structure (note, it's not temporary table that lives in tempdb, it's
just a new table residing in your db)
insert all the data from your old table into the new one
EXECUTE sp_rename N'dbo.Tmp_old_tbl', N'old_tbl', 'OBJECT'

So that "temp table" that you saw created is in fact your new table, renamed from Tmp to your old_tbl


using *tables* as Table-Valued Parameters (TVP)

No, you can't leverage an existing table definition, you need to define an explicit type. This was asked for back in 2007 and was closed as "won't fix" but I still strongly encourage you to up-vote and leave a comment describing your use case and how this will help your business be more productive. You could even point to this question to demonstrate how tedious it can be to try and automate this.

Item on UserVoice (Formerly Connect #294130)

You can do this today, dynamically, though... for example for your simple definition:

-- you would pass these two in as parameters of course:
DECLARE
  @TableName SYSNAME = N'LocationTable',
  @TypeName  SYSNAME = N'LocationTypeTable';

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + N',' + CHAR(13) + CHAR(10) + CHAR(9)
    + QUOTENAME(c.name) + ' '
    + s.name + CASE WHEN LOWER(s.name) LIKE '%char' THEN
        '(' + CONVERT(VARCHAR(12), (c.max_length/
        (CASE LOWER(LEFT(s.name, 1)) WHEN N'n' THEN 2 ELSE 1 END))) + ')'
        ELSE '' END
        -- need much more conditionals here for other data types
    FROM sys.columns AS c
    INNER JOIN sys.types AS s
    ON c.system_type_id = s.system_type_id
    AND c.user_type_id = s.user_type_id
    WHERE c.[object_id] = OBJECT_ID(@TableName);

SELECT @sql = N'CREATE TYPE ' + @TypeName
    + ' AS TABLE ' + CHAR(13) + CHAR(10) + '(' + STUFF(@sql, 1, 1, '')
    + CHAR(13) + CHAR(10) + ');';

PRINT @sql;
-- EXEC sp_executesql @sql;

Results:

CREATE TYPE LocationTypeTable AS TABLE
(
    [LocationName] varchar(50),
    [CostRate] int
);

Disclaimer: This does not deal with all kinds of other things like MAX types, precision and scale for numerics, etc. The final solution would have to be more robust to account for all potential column definitions but this should give you a start.

In SQL Server 2012 there are new DMVs and stored procedures that will make it much easier to derive column metadata from existing tables (or stored procedures or even ad hoc queries) without having to mess with all the conditional logic against sys.types and sys.columns. I blogged briefly about these enhancements in December. It's still tedious, but it's somewhere between the awful unmaintainable spaghetti above and the ability to just say " as copy of [table x]"...


Difference between sql agent account logged in as NTLM and UPN?

Microsoft SQL Servers do not allow the use of User Principle Name (UPN) addresses for service accounts.  There is a post from a few years ago in Microsoft Connect and a more recent post:

https://connect.microsoft.com/SQLServer/feedback/details/519008/upn-user-principal-name-as-services-account-name#details

http://www.ryanjadams.com/2014/02/error-authenticating-proxy-sql-and-upns/#axzz2x0igTI3n

Therefore you need to stick with the DOMAIN\USERNAME format for the service accounts.

(Original post failed to commit.  Repost.)


Yes, you can do it. Be sure to keep innodb_lock_wait_timeout reasonably low, for example, 2 seconds. Deadlocks that last for 2 seconds are completely harmless, if they are rare (and usually they are very rare). Remind developers to avoid long transactions, and avoid foreign keys ON ... CASCADE (because they propagate locks, making deadlocks more likely).

Of course I am assuming that you can keep a very low value for those timeouts. But can you? I don't know your workload but, for example, a statement may lock some rows for 10 seconds every hour. In such cases, can you afford a low timeout? If not, then everything I wrote above does not apply to you.


When did sp_OACreate et. al. become deprecated?

While I have been unable to find any official notice of deprecation for the OLE Automation stored procedures (sp_OA*), I believe they are generally regarded as deprecated as of the release of SQL Server 2005. Extended Stored Procedures, on the other hand, were officially deprecated as of SQL Server 2005. Both of those features were obsoleted by the introduction of the CLR Integration feature (i.e. SQLCLR).

When features become deprecated, they are listed in the following documentation:

Deprecated Database Engine Features in SQL Server 2005

That link takes you to the SQL Server 2005 version of the document. You can use the drop-down towards the top to check other versions. No version seems to list either "OLE Automation" or "sp_OA", so it would appear that the feature is not officially deprecated.

Yes, you heard correctly that the OLE Automation stored procedures are buggy and leaky (or at least have been at various times / service pack levels). They are less secure than SQLCLR (the "OLE Automation Stored Procedures" feature is either enabled or disabled; there is no control over who uses the feature if enabled, or how it is used, both of which are controllable -- to a degree -- in SQLCLR).

Also, as far as I have seen, the OLE Automation API (i.e. COM) has not been updated to work with any datatypes introduced as of SQL Server 2005 (i.e. MAX types, XML, DATE, TIME, DATETIMEOFFSET, CLR types, etc.).

You should be using SQLCLR instead of sp_OA* stored procedures for any new code. For existing code that is working, it is probably fine to leave it as is. But if that existing sp_OA* code should break, then it would be better to rewrite as SQLCLR instead of trying to get the sp_OA* approach working.


Oracle Performance - Varchar2 (x char) x Varchar2 (x byte)

Assuming that your database character set is AL32UTF8 (if there is a difference between the two, this is almost certainly your character set) and that all the data is characters that exist in the US7ASCII character set ("english" might constitute more than that depending on the definitions we're using) there won't be any noticable performance differences between a varchar2(n CHAR) and a varchar2(n BYTE).

Based on your database character set, Oracle has to do a tiny bit more work reading a string to figure out that every byte represents a single character rather than being part of a multi-byte character.  But checking the length of a string in either characters or bytes is a pretty trivial operation.  Unless you're doing something like running a TPC benchmark where you're already doing a crazy number of things that you'd never do in reality to get tiny fractions of performance improvement, it's not something worth worrying about.

If you were going to go crazy with a TPC benchmark level of effort to get every last thousandth of a percent, it would probably be slightly more efficient to use a database character set of US7ASCII and declare everything using byte semantics given that you only had to store English.  Practically, though, if your application is so efficient that this sort of thing matters, though, you've already solved every performance problem so you should be focusing much more heavily on supportability and maintainability which would argue for an AL32UTF8 character set and character length semantics.


Multiple calculations in a select statement with CASE

Using a derived table, as already shown, is one way. Another is to use CROSS APPLY:

SELECT
  CASE
    WHEN x.Pnature + x.Ptype < 100 THEN 20
    ELSE ...
  END,
  ...
FROM
  dbo.product AS f
  CROSS APPLY
  (
    SELECT
      CASE
        WHEN f.nature = 'box' THEN 1
        WHEN f.nature = 'bag' THEN 3
        ELSE 0
      END,
      CASE
        WHEN f.type = 'electronics' THEN 0
        WHEN f.type = 'cloths'      THEN 3
        ELSE 0
      END
  ) AS x (Pnature, Ptype)
;

The two method are somewhat similar: in both cases you are defining the expressions in a nested SELECT. The APPLY method is different in that you are not nesting the main query, only the bits that you want to additionally reference in the main SELECT.


Why are the passwords in MySQL (5.x) not salted?

No, there isn't.

If this is a major issue for you, there is a PAM plugin available from Percona that works with MySQL if you want to authenticate against something external rather than using the internal mechanism.  MariaDB has a different solution.

Oracle also has a proprietary (non-free) PAM plugin for people who just like to give them money.

Although, unless you have some kind of compliance issue to deal with, the practicalities of the MySQL authentication protocol are such that this seems like a non-issue.  After all, your database server should generally not be accessible on the Internet, and if it is, it should be restricted to the hosts that can connect, and those hosts should be required to use SSL.

The server stores, essentially, the output of this expression:

CONCAT('*',UCASE(SHA1(UNHEX(SHA1('password')))));

mysql> select PASSWORD('password');
+-------------------------------------------+
| PASSWORD('password')                      |
+-------------------------------------------+
| *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select CONCAT('*',UCASE(SHA1(UNHEX(SHA1('password')))));
+--------------------------------------------------+
| CONCAT('*',UCASE(SHA1(UNHEX(SHA1('password'))))) |
+--------------------------------------------------+
| *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19        |
+--------------------------------------------------+
1 row in set (0.00 sec)

But that's apparently not just a misguided attempt along the lines of "if hashing once is good, then hashing twice is better."  It has to do with the way the authentication protocol works.

When a client tries to authenticate, the server sends a 20-byte random challenge string.

The client takes that string, and calculates this:

SHA1('password') XOR SHA1( "20-bytes random data from server"<concat>SHA1(SHA1('password')))

So the client is calculating the value that should be stored in the password column (if the correct password has been entered), appending that value to the 20 random bytes, taking the SHA1 of the whole thing, XORing it with the SHA1 of the password itself, and sending that back to the server.

Given that the inverse function of XOR is also XOR, The server can then take the response, and, having also calculated the SHA1() of the string including the 20 bytes, can XOR it back into SHA1('password'), then SHA1() that value...

SHA1(client-response XOR SHA1(CONCAT("same random 20-bytes",mysql.user.password)))

...and the output would be the value stored in the password column of the mysql.user table.

So in this exchange, neither the plaintext password nor what's stored in the password field is ever sent over the wire, just a 20-byte random challenge and a 20-byte response, which is itself the XOR of two hashed values and would also not be usable in a replay attack against the same account or on another account with the same password unless the server sent the same challenge and the passwords are the same.

Since two clients with the same password would arguably "never" get the same challenge, they would just as infrequently "never" send the same response for authentication.

Unless the server sent the salt to the client, I don't readily see how such an exchange could take place.  On the other hand, if the server were to send the salt to the client, that implies that the salt would have to exist on the server... and if the salt exists on the server, and I am in possession of one known password/salt combination, then identifying other accounts whose salt and my password matches their password hash, then identifying other accounts where my password could be used is no less trivial that it is under the current scheme.


Trying to recover a database with just .ibd and .frm files, but no idbata1 file

Answer copied from this site, but abridged and adjusted so the path matches your directory structure.

Run cmd command
Type C:  and hit enter
C:>cd wamp\bin\mysql\mysql5.6.12\bin
C:\wamp\bin\mysql\mysql5.6.12\bin>mysql.exe -u root


I think this should solve the question.

I've numbered Person table, partitioning by DistrictID and ordered by SingUpDate and IDNo.

IDNo    FirstName   LastName    DistrictID    SignUpDate   Row Number
---------------------------------------------------------------------
123     bob         smith       1             1/19/2017         2
234     john        willis      1             1/18/2017         1
456     john        doe         2             1/17/2017         2
678     jane        doe         2             1/16/2017         1
890     jeff        bridges     3             1/19/2017         1
135     bill        doe         3             1/19/2017         2
246     sally       abby        4             1/14/2017         2
357     michael     jordan      4             1/12/2017         1

Then adds District information to all rows where row number is equal 1.

This should works for MySql:

select pn.IDNo, pn.FirstName, pn.LastName, pn.DistrictID, pn.SignUpDate,
       case when pn.rn=1 then d.DistrictName else '' end as DistrictName,
       case when pn.rn=1 then d.BigDistrict else '' end as BigDistrict,
from
    (select
           @rn := case when @district = DistrictID then @rn + 1 else @rn := 1 end as rn,
           @district := DistrictID as DistrictID,
           p.IDNo, p.FirstName, p.LastName, p.SignUpDate
    from
        (select @rn := 1) i,
        (select @district := DistrictID as DistrictID, IDNo, FirstName, LastName, SignUpDate
         from Person order by SignUpDate , IDNo) p
    ) pn
    inner join District d on d.DistrictID = pn.DistrictID

And this for other DBMS that allows CTE:

WITH p AS
(
    SELECT IDNo, FirstName, LastName, DistrictID, SignUpDate,
           ROW_NUMBER() OVER (PARTITION BY DisctictID ORDER BY SignUpDate, IDNo) RN
    FROM Person
)
SELECT IDNo, FirstName, LastName, DistrictID, SignUpDate,
       CASE WHEN RN = 1 THEN d.DistrictName ELSE '' END AS DistrictName,
       CASE WHEN RN = 1 THEN d.BigDistrict ELSE '' END AS BigDistrict,
FROM p
     JOIN District d ON p.DistrictID = d.DistrictID
ORDER BY p.IDNo;


Private access for a SP working on a table

Are there any tools within SQL Server that will allow me to isolate access

DDL statements are covered by transactions as well as DML ones, so wrapping the whole process in a transaction may bu the solution there.

Stored Procedure that is dropping, recreating and then applying keys and indexes to a table
its automated to run overnight

Could you edit your question to describe why you are doing this that way?
If it is being run every night (or otherwise on a regular basis) this seems an unusual task to be performing. If you are just looking to optimise the indexes then you can rebuild or reorganise them without needing to drop and recreate (and even then you may want to reconsider: stale stats, fragmentation, and other issues, are not likely to be bad enough that you need to do this as regularly).
If you are dropping the whole table to clear the data (your sentence could be read as just rebuilding the indexes+keys+constraints or rebuilding everything) then using TRUNCATE TABLE should do the job and do so far more efficiently - no need to recreate the other objects at all. IF you can't truncate the table because there are foreign keys referring to it then you'll need to disable those and recreate them at the end of the process, being sure to wrap the process in a transaction with relevant locks to stop applications potentially creating data that breaks the constraints while they are inactive), but that should be a lot quicker than rebuilding everything.


Unable to connect to MongoDB replica set using public IPs

Check your firewall settings!
If FW is open to outside, you can connect single node even with telnet. Of course check that you haven't configure mongod to listening only private IP network adapter.

If you are behind NATed gateway, there should be redirection from outside network to servers.


No.  This is not possible.  There are various options that might help you recover from this situation or even prevent it from happening in the first place though.

Think about it like this: how do you determine when the "data gets corrupted"?

Imagine that you could decode and scan through the archived redo logs, before they get shipped to the standby.  How would you determine what caused any "corruption" without knowledge of the database structure, contents of the tables and such?

Some options are:

Set a delay on your standby receiving the data.  If a logical (data) corruption is discovered during this window, stop the redo being shipped to the standby so it doesn't get affected.
Set up flashback on either the primary or standby databases for an appropriate period.  If a logical corruption is detected during this time, you may be able to recover using flashback query or flashback database - but it won't be easy if it's discovered a long time after.
Set the database up so that logical corruptions are impossible/difficult to manage without intentionally introducing them.  e.g. referential integrity, correct data types and c.


How SQL server database reorganize memory

SQL Server does its own database file management - it does grow your .mdf file every time you need a few bytes more space.

Instead, for each database file, you've defined an initial size and how it will grow - by percent or by a fixed size. You can see these values by issuing this query:

SELECT
    name, physical_name,
    size, max_size, growth, is_percent_growth
FROM sys.database_files

If your database file is set to e.g. 180 MB in size, and the growth is set to 50 MB, then as soon as the space in the allocated 180 MB is no longer sufficient, the file will be grown by 50 MB (to 230 MB).

By default, SQL Server also never shrinks data files - what for? Just to grow them again later on? It keeps track of unused space inside the file, and will reuse that space, until again, the file size isn't sufficient anymore, at which point it will be grown again (by that settings that's defined per file).


Random selection of a row within a function

A Computed Column is a bad (probably even very bad) place to have a random value.

First, if you want this "default" value to remain consistent upon the row being inserted (as opposed to changing every time the row and field are selected), you would need to use the PERSISTED keyword so that the computed column is physical stored and not recomputed each time.

Second, while you can usually do either of the following in a computed column, they cannot be used in a PERSISTED computed column due to being non-deterministic:

directly use NEWID() and CRYPT_GEN_RANDOM()
pass NEWID() or CRYPT_GEN_RANDOM() as a parameter to a User-Defined Function

Third, you could potentially create a SQLCLR function that would be able to call .NET randomization methods, but due to the requirement of a PERSISTED computed column needing to reference only deterministic functions, you would have to mark it incorrectly on purpose as IsDeterministic = true, and the effect(s) of this could be bad. At the very least it would allow SQL Server to cache the function output, in which case multiple rows being inserted in the same query (either via an INSERT...SELECT construct or an INSERT INTO VALUES (), (), ()... construct) could easily have the same "default" value and hence not be random at all.

Fourth, you could attempt the "hiding NEWID() in a View so it can be referenced in a UDF" trick as shown in @Julien's answer, but you would need to test that to see if it actually does what you are wanting. You need to particularly test scenarios that insert multiple rows in a single statement since SQL Server does not guarantee that it will run a UDF more than 1 time.

In the end, I think you are FAR better off having this "default" field being a regular field (i.e. not computed) and either determining its value in the Stored Procedure that does the INSERT, or determined and set in an AFTER INSERT Trigger that updates this "default" field for all new rows.


Basically, SQL Server will automatically create an index for your primary key.

Other than that, I would recommend to create indices on your foreign key columns - if you have a foreign key relationship, that's most likely because you need to JOIN two tables together, and your JOIN operations will benefit significantly if an index is present on the foreign key column.

From there on - observe your system, check your system performance, gather server traces to see what queries cause the biggest CPU and/or I/O load on your system. Armed with that information, design additional indices - but sparingly! An "over-indexed" system is often worse than one without any indices!

If you see an index that would make sense and that would help one or multiple of your "heavy" queries, add that index, and observe again.

Repeat this "observe - gather information - analyse information" cycle - basically forever...

See Kimberly Tripp's excellent Indexes: just because you can, doesn't mean you should! blog post on the dangers of over-indexing


How to implement many-to-many relationship with one table

You cannot do this with one additional column You need to create a new table for relations between employees.

CREATE TABLE IF NOT EXISTS emp_relations(
emp_id1 INT UNSIGNED NOT NULL,
emp_id2 INT UNSIGNED NOT NULL,

FOREIGN KEY (emp_id1 )
      REFERENCES emp_list(id),
FOREIGN KEY (emp_id2 )
      REFERENCES emp_list(id),

UNIQUE KEY(emp_id1 ,emp_id2)
 );

Link both columns to emp_list.id. Make emp_id1 and emp_id2 together unique.

This way you will have hierarchy and many to many relations.

Mysql - Foreign keys

Mysql - Unique keys


I figured it out. Location on Windows for my.ini C:\ProgramData\MySQL\MySQL Server 5.6\my.ini


In MySQL Workbench 6.1 (OS X), can you make the Result Grid taller?

You mean like this:

Place your mouse on the line between the grid toolbar and the editor bottom. It's only a few pixel so watch out. The cursor image should change to a split cursor and you can drag that line to resize both, editor and grid. On Mac the split cursor sometimes does not appear, but otherwise it still works moving the split position.


Sql Server: how to validate schema against a textual representation (XML or the like) natively?

As Aaron mentioned, there is no click - click - finish and export to xml  option in sql server.

Rather you can use FOR XML AUTO, XMLDATA to do that using T-SQL.

use databaseName
go
SELECT SCHEMA_NAME(t.schema_id) AS schema_name,
        t.name AS table_name,
        c.name AS column_name,
        st.name as data_type,
        c.max_length
        ,c.Is_ANSI_Padded
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
inner join sys.types st on c.system_type_id = st.system_type_id
where t.is_ms_shipped = 0 and t.name <> 'sysdiagrams'
order by t.name
FOR XML AUTO, XMLDATA

Refer to : Specifying the XMLDATA Schema Option in a Query

  Generating an XML-Data schema is an overhead on the server, is likely to affect performance, and should be used only when data types are needed.

Alternatively, if you just want to script out the schema, then refer to this answer for more details.


pass selected variable value to inside select query MySQL

SELECT product.*
FROM product
JOIN ( SELECT product_id id, MIN(price) price
       FROM product_stock
       GROUP BY 1 ) min_prices USING (id)
ORDER BY min_prices.price

If some products are absent in product_stock they won't be listed. If you need in them then use LEFT JOIN.


Survey database design: first version. Are there errors?

I offer a more complete model, from an actual solution.

The 'type' is associated to the question per-se (not to its items, as stated at the fourth item from the question).

A question may be of single-line or multiple-line textual box or composed by several items. For each item may be assigned a complementary question (eg. 'Country?' - 'Other' - 'Which: ___').

HOW the question and its items will be presented to the user depends on their type.

The model has been made flexible enough to represent several structures of questions.

The 'FormQuestionType' entity has an attribute to identify the ASCX (ASP.net component) responsible of rendering questions of this type.

It's possible to make a question dependent of one or more of the previous ones (eg. 'How many children?' may be made dependent of 'Do you have any children?').

There's prevision for data validation, from 'required' constraint to regular expressions.

For every major entity has been defined the mandatory and unique attribute named 'identification', allowing queries based upon textual codes instead of numerical ids (eg. 'How many children?' question, of id '351', is also identified by 'HOW_MANY_CHILDREN').

And, for the items of a given questions, there may be defined an external source of data (eg. 'city' question has its options retrieved live from corporate 'city' table).

The actual model provide versioning, but, for clarity's sake, I present a stripped down version of it.


MySQL - How to select rows until condition is met

Assuming:

CREATE TABLE T
(  x int not null primary key
,  y int not null );

INSERT T(x,y)
VALUES (1, 1)
  ,(2, 1)
  ,(3, 2)
  ,(4, 8)
  ,(5, 3)
  ,(6, 5)
  ,(7, 4);

You can determine the cumulative sum like below, and then filter the result in an outer select:

select x,y,acc
from (
    select x, y, (select sum(y)
                  from T T2
                  where T2.x <= T1.x) as acc
    from T T1
) as tmp
where acc < 10;

+---+---+------+
| x | y | acc  |
+---+---+------+
| 1 | 1 |    1 |
| 2 | 1 |    2 |
| 3 | 2 |    4 |
+---+---+------+

For DBMS that support window functions like sum(..) over ( ... ) much more elegant solutions exists.


T-SQL to show Azure SQL Managed Instance vs Azure SQL Database

To programmatically determine the engine edition, use SELECT
  SERVERPROPERTY('EngineEdition'). This query will return '5' for single
  databases/elastic pools and '8' for managed instances in Azure SQL
  Database.

https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-2017


Finding rows in a table with a foreign key pointing back at itself where the referenced row differs in a certain column

SELECT t1.*
FROM Organisation t1
JOIN Organisation t2 ON t1.id = t2.parent_id
                    AND t1.platform_id != t2.platform_id;


Database Mail in itself can only send emails, because it only 'talks' SMTP:

  A Database Mail account contains the information that SQL Server uses to send e-mail messages to an SMTP server. Each account contains information for one e-mail server.

(from the documentation)

But (depending on the configuration of your SMTP server), you can use a valid email address within your organization (a user, or a group like info@example.com) as the sender of the email. Then, the receiver can just reply as they would to a normal email.

If you need to make some automated actions based on a reply (such as scheduling another task in the database), you're looking at a (much) more complicated integration, way beyond the scope of Database Mail.


The answer will very much depend on the use you wish to make of the addresses. If you simply require a bunch of glyphs to print on a delivery docket then what you have looks good enough. The ADDRESS table will simply absorb the local variations in formatting, sequencing and line breaks. If you require to use this for, say, automated sorting and delivery route planning you will need a lot more detail than your five level hierarchy. If your purpose is to qualitative compare regions or subdivisions across countries then you must analyse that in detail and model at the level that suits that need.

Trying to chase down the One True Address model is a fool's errand. Leave that to each national postal organisation and concentrate on what adds unique value to your application.


How do I return all column values for the recently inserted row/rows?

Impossible with an AFTER trigger
The version of the row visible to the RETURNING clause is the same version that's visible to an AFTER trigger. The UPDATE in your trigger function creates a new row version. It's impossible to see the effects in the RETURNING clause, which is based on the prior row version. Demo:
db<>fiddle here
Effectively, the output of RETURNING is "dead on arrival", obsoleted immediately by the AFTER trigger. You would need a separate SELECT to see the new row version. (Can be in the same transaction.)
Impossible with a CTE
Your query is bound to return nothing:
WITH create_query AS (
   INSERT INTO test_registration (entity_id,code)
   VALUES ('a17e66c5-1049-4ba9-bed3-90bbc823e064'::uuid,'13')
   RETURNING *
   )
SELECT v.entity_id, v.row_id, b.entity_id, b.entry_name, b.row_id
FROM   create_query AS v, test_registration AS b
WHERE  v.row_id = b.row_id;

All sub-statements of a query with CTEs see the same snapshot of the database. Since the SELECT cannot yet see the row inserted in the CTE (and row_id is populated with uuid_generate_v4()) your implicit JOIN is bound to return no row.
See:

Why can't rows inserted in a CTE be updated in the same statement?

Proper trigger
I see nothing to warrant a STATEMENT-level, nor the need for an AFTER trigger. Use a much simpler (and faster) ROW-level trigger BEFORE. Then RETURNING * will just work:
CREATE OR REPLACE FUNCTION test_registration_entry_name()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.entry_name := NEW.code;
   RETURN NEW;
END
$func$;

CREATE TRIGGER test_insert_trigger
BEFORE INSERT ON test_registration
FOR EACH ROW
WHEN (pg_trigger_depth() = 0
      AND NEW.entry_name IS DISTINCT FROM NEW.code) -- optional
EXECUTE FUNCTION test_registration_entry_name();

db<>fiddle here
(The fiddle operates with integers because the additional module uuid-ossp providing uuid_generate_v4() is not installed on dbfiddle.uk. But it works all the same.)
I added another (optional) trigger condition: NEW.entry_name IS DISTINCT FROM NEW.code to avoid needless work if the trigger wouldn't change anything.
Related:

Assign to NEW by key in a Postgres trigger

Aside: more efficient INSERT
Do both inserts in a single query with a data-modifying CTE like this:
WITH ins_test AS (
   INSERT INTO test DEFAULT VALUES RETURNING entity_id
   )
INSERT INTO test_registration (entity_id, code)
SELECT entity_id, 'EB'
FROM   ins_test
RETURNING *;

Then you can immediately reuse the auto-generated entity_id without another round-trip to the server. See:

Insert data in 3 tables at a time using Postgres
Bulk insert in multiple tables
Conditional INSERT with a nested CTE?


Best situation to use READ UNCOMMITTED isolation level

I use READ_UNCOMMITTED (or NOLOCK) when querying production databases from SSMS but not routinely from application code. This practice (along with a MAXDOP 1 query hint) helps ensure casual queries for data analysis and troubleshooting don't impact the production workload, with the understanding the results might not be correct.

Sadly, I see READ_UNCOMMITTED/NOLOCK used widely in production code to avoid blocking at the expense of data integrity. The proper solution is a row-versioning isolation level (SNAPSHOT or READ_COMMITTED with the READ_COMMITTED_SNAPSHOT database option ON) and/or attention to query and index tuning.

I recently code reviewed a proc where the only change was to remove NOLOCK because it sometimes returned wrong results. Removing NOLOCK was a good thing but, knowing that missed or duplicated rows typically happens during allocation order scans of large tables, I suggested also refactoring to use a UNION ALL technique to promote efficient index use. The query now runs in a few milliseconds with correct results, the best of all worlds.


How to fully recreate database in MS SQL server 2016 from exported data

This is only a summary of some vague assumptions as a lot of details that should be added to the question were either discussed in a chat or have not yet been asked.

** Before you continue**

Create a database backup (WITH CHECKSUM) and store this database in a safe location. Even better: Seeing as your disk might be faulty, backup the database (WITH CHECKSUM) to an external drive and store in a safe location.

Possible reasons for data corruption on data growth

Your SSD disk has corrupt areas. DBCC CHECKDB... corrects the data (albeit with REPAIR_ALLOW_DATA_LOSS). Adding data places it in the corrupt areas of the SSD even though the SSD controller unit should avoid corrupt areas (once marked).

Solution: Check SSD S.M.A.R.T. parameters for errors and/or replace SSD.

Reference: How to See If Your Hard Drive Is Dying with S.M.A.R.T. (How-To Geek Site)

Your motherboard's disk controller (SATA interface) is having issues handling the large amount of data.

Solution: Replace disk controller and/or update disk controller driver.

The disk and/or disk controller is set to use a WRITE CACHE mechanism which is unable to keep up with the import of large amounts of data. The corruption is occurring on flushing.

Solution: Turn off (disk) cache mechanisms.

Reference: Disable Windows Disk Write Cache for Data Integrity and Better Performance (Long White Virtual Clouds)

General Reference: Data corruption (Wikipedia)


psql has a variable called HISTCONTROL. Quoting the manual:

  HISTCONTROL

  If this variable is set to ignorespace, lines which begin with a space are not entered into the history list. If set to a value of
  ignoredups, lines matching the previous history line are not entered.
  A value of ignoreboth combines the two options. If unset, or if set to
  any other value than those above, all lines read in interactive mode
  are saved on the history list.

I have this configured to automatically take effect on psql startup by adding the line

\set HISTCONTROL ignoredups

to my .psqlrc.


How to count repeated rows taking into account other rows in the middle

This is a gaps-and-islands problem. One (of the many) ways to solve it (this requires 2012+ versions):

WITH
  t AS
    ( SELECT a, b, x = CASE WHEN a = LAG(a) OVER (ORDER BY b)
                           THEN NULL ELSE 1
                       END
      FROM table_name
    )
SELECT a, b, c = COUNT(x) OVER (ORDER BY b)
FROM t
ORDER BY b ;

This should work in 2005 and above:

WITH
  t AS
    ( SELECT a, b, dx = ROW_NUMBER() OVER (ORDER BY b)
                        - ROW_NUMBER() OVER (PARTITION BY a ORDER BY b)
      FROM table_name
    ),
  tt AS
    ( SELECT a, b, mb = MIN(b) OVER (PARTITION BY a, dx)
      FROM t
    )
SELECT a, b, c = DENSE_RANK() OVER (ORDER BY mb)
FROM tt
ORDER BY b ;


PostgreSQL: Disk space not released after TRUNCATE

According to comments in the source, truncate creates a new, empty storage file, and deletes the old storage file at commit time. (Docs suggest "storage file" is just a file as far as the OS is concerned, but I might be misunderstanding the terminology.)

  Create a new empty storage file for
  the relation, and assign it as the
  relfilenode value. The old storage
  file is scheduled for deletion at
  commit.

Since it seems to be deleting a file, I can imagine some cases in which the underlying operating system might not immediately free that space. I imagine that in some cases the storage file might end up in the Recycling Bin under Windows, for example. But in my case, truncating a table under PostgreSQL 9.something immediately increased the freespace under Windows.

Truncation is also recorded in the WAL log. I don't know how much effect that might have.


What does "cleaning up" mean in MySQL InnoDB transaction status and how to prevent it?

To quote the man himself:

  Hi!

  Yes, you can get processes in the 'cleaning up' state if your
  table_cache is too small.

  The general answer is that you should increase your table cache if the
  status variable 'opened_tables' tend to increase a lot over time.

  Yours,
  Monty

Source: Grokbase.com


Converting Monthly Rolling SUM (YTD) back to Monthly numbers

select name,period
    ,amount-lag(amount,1,0) over (partition by name order by period)
from table1

with cte as
(
     select *
           ,row_number() over (partition by name order by period) as rn
      from table1
 )
 select t1.name,t1.period
       ,t1.amount-coalesce(t2.amount,0)
 from   cte as t1
        left join cte as t2
        on t2.rn = t1.rn-1


teradata database unable to increase database size in permanent space

Your query and the results don't match, but you're probably right.

This will return the actual available space (modify it accordingly for TBs or 1024**3):

SELECT DatabaseName,
   CAST(SUM(    MaxPerm                  / 1000**3) AS DEC(9,2)) AS MaxPermGB,
   CAST(SUM(CurrentPerm                  / 1000**3) AS DEC(9,2)) AS CurrentPermGB,
   CAST(MAX(CurrentPerm) * (HASHAMP()+1) / 1000**3  AS DEC(9,2)) AS SkewedPermGB, -- due to skewed tables
   MaxPermGB - SkewedPermGB AS AvailablePermGB -- this space can be assigned to other databases
FROM dbc.DiskSpaceV
WHERE MaxPerm > 0
GROUP BY 1
ORDER BY AvailablePermGB DESC

MODIFY DATABASE will only work if enough perm is available in the parent/immediate owner. If this database is not in the 2nd level of the hierarchy dbc is not the direct owner and then it's more complicated. Now you better use Tools -> Move Space, but this might fail if you don't have appropriate access rights.


Microsoft SQL Server SQL Injection Through .NET Even With Quote Escaping

Single-quote escaping can be circumvented if there is a numeric field input that is not checked for only numeric data (and, as always, if the field isn't length limited.) Here's the basic method:

Enter a valid number, followed by a semicolon to terminate the existing command. Then enter something like below, after the semicolon - this will execute the command SELECT * FROM users;

DECLARE @o varchar(max);SELECT @o= CHAR(83)+CHAR(69)+CHAR(76)+CHAR(69)+CHAR(67)+CHAR(84)+CHAR(32)+CHAR(42)+ CHAR(32)+CHAR(70)+CHAR(82)+CHAR(79)+CHAR(77)+CHAR(32)+CHAR(117)+CHAR(115)+ CHAR(101)+CHAR(114)+CHAR(115)+CHAR(59);EXEC(@o);

Nowadays, having code that would allow this through your input field would be pretty unlikely - a simple IsNumeric() check would blow this right out of the water, and a rational fieldsize limit on the numeric input leaves you trying to execute DECLAR or some such, which will of course blow up. But this is theoretically possible.


Sort values in MySQL using HAVING clause and IN function

I'm not sure why you're putting the EXT IN a HAVING clause, but this should work.

WHERE IFNULL(mxuser.EXTENSION, mxuser_1.EXTENSION)
  IN (207, 213, 235, 214, 226, 218, 224, 227, 216)
GROUP BY IFNULL(mxuser.EXTENSION, mxuser_1.EXTENSION)
ORDER BY ext=235 DESC, ext=207 DESC, ext=218 DESC, ext;


Security Concerns of SQL Server Express for Web e-Voting web application

Security is such a vast topic that it totally depends on your company's policies and the threat level you are trying to avoid.

You can use native asymmetric encryption in sql 2005 express as described in SQL Server 2005 Express Edition - Part 6 - Post Installation Configuration Tasks (Encryption) and SQL Server 2005 Security - Part 3 Encryption.

You can use master key encryption / cretificate / symmetric key. Read up : SQL SERVER – Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script

Also, you can secure sql server backups at file level or the entire disk as well if you are trying to stop a hacker that already has access to the database server by some third party softwares like BitLocker or open source T r u e C r y p t.

Brian Kelley has an interesting article on Implementing Encrypting File System (EFS) with SQL Server.

There is a white paper on SQL Server security best practices as well.


ODCI
Personally, I'd probably write my own aggregate function so that the SQL looks like
select
  SYS_CONNECT_BY_PATH(CHILD_ITEM_NUMBER, '>') ROUTE,
  my_mult_agg( ) over (order by level) ASSY_EXT_QTY
from ( .. ) bom

(There still might be a pure SQL way)
Example:
Modified from the example in the documentation found here
create or replace
type my_mult_agg_impl as object
(
  mult_buffer NUMBER, -- highest value seen so far
  static function ODCIAggregateInitialize(sctx IN OUT my_mult_agg_impl)
    return number,
  member function ODCIAggregateIterate(self IN OUT my_mult_agg_impl,
    value IN number) return number,
  member function ODCIAggregateDelete(self IN OUT my_mult_agg_impl,
    value IN number) return number,
  member function ODCIAggregateTerminate(self IN my_mult_agg_impl,
    returnValue OUT number, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT my_mult_agg_impl,
    ctx2 IN my_mult_agg_impl) return number
);
/

create or replace type body my_mult_agg_impl is
static function ODCIAggregateInitialize(sctx IN OUT my_mult_agg_impl)
return number is
begin
  sctx := my_mult_agg_impl(1);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT my_mult_agg_impl, value IN number) return number is
begin
  mult_buffer := mult_buffer * value;

  return ODCIConst.Success;
end;
member function ODCIAggregateDelete(self IN OUT my_mult_agg_impl, value IN number) return number is
begin
  mult_buffer := mult_buffer / value;

  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN my_mult_agg_impl,
    returnValue OUT number, flags IN number) return number is
begin
  returnValue := mult_buffer;
  return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT my_mult_agg_impl, ctx2 IN my_mult_agg_impl) return number is
begin
  self.mult_buffer := self.mult_buffer * ctx2.mult_buffer;

  return ODCIConst.Success;
end;
end;
/

CREATE or replace FUNCTION my_mult_agg (input NUMBER) RETURN NUMBER
AGGREGATE USING my_mult_agg_impl;
/

select level,
 my_mult_agg( level ) over (order by level) as MM
from dual
connect by level <= 10
order by level;

result:


MySQL cannot create foreign key, errors 1005, 150

I confused my databases and incorrectly thought I was working with InnoDB databases. Turns out one of them was MyISAM and then foreign keys don't work. Unfortunately, MySQL Workbench doesn't inform the user of this on Windows, but it does on the Macintosh version. (I guess it's good after all to have developers on your team with Macs.)

Hope this helps somebody with the same error message.


I would check what is setting the parameter value and make sure it is actually being set by debugging and setting a watch.  Also make sure that your parameter isn't being overwritten by a blank environment variable or something when it's deployed.


Mongo: mongodump and the config database error

You don't have the required privileges for the config database.

See https://docs.mongodb.com/manual/reference/built-in-roles/ :

  readWriteAnyDatabase

  Provides the same privileges as readWrite on all databases except local and config.

  userAdminAnyDatabase

  Provides the same access to user administration operations as userAdmin on all databases except local and config.

  dbAdminAnyDatabase

  Provides the same privileges as dbAdmin on all databases except local and config.

For mongodump, use the backup role.

  The admin database includes the
  following roles for backing up and restoring data:

  backup

  Provides minimal privileges needed for backing up data. This role provides sufficient privileges to use the MongoDB Cloud Manager backup
  agent, Ops Manager backup agent, or to use mongodump to back up an
  entire mongod instance.

Specifics about mongodump https://docs.mongodb.com/manual/reference/program/mongodump/ :

  Required Access

  To run mongodump against a MongoDB deployment that has access control
  enabled, you must have privileges that grant find action for each
  database to back up. The built-in backup role provides the required
  privileges to perform backup of any and all databases.


Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section

max_connect_errors=10  # from 1 Million to frustrate hackers/crackers at 10 attempts
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function
read_buffer_size=256K # from 128K to reduce handler_read_next RPS of 13,569
aria_pagecache_age_threshold=900  # from 300 to delay age out and reduce aria_pagecache_reads RPS of 1
connect_timeout=20  # from 10 to reduce aborted_connects RPhr of 60

There are more opportunities to improve your configuration.  Make contact, please.


Can I create a MySQL CSV table directly from an existing CSV file?

Here is something a MySQL Daredevil is more than willing to try.
I just did the following in MySQL 5.6.14 right on my Laptop
C:\Windows\system32>mysql -Dtest
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL://localhost/redwards/3306 test :: create table dt (a INT NOT NULL,b CHAR(10) NOT NULL) ENGINE=CSV;
Query OK, 0 rows affected (0.08 sec)

MySQL://localhost/redwards/3306 test :: insert into dt values (1,'rolando'),(2,'pamela'),(3,'dominique'),(4,'diamond');
Query OK, 4 rows affected (0.11 sec)
Records: 4  Duplicates: 0  Warnings: 0

MySQL://localhost/redwards/3306 information_schema :: flush tables;
Query OK, 0 rows affected (0.31 sec)

MySQL://localhost/redwards/3306 test :: exit
Bye

Now, going into the OS:
C:\Windows\system32>cd \MySQL_5.6.14\data\test

C:\MySQL_5.6.14\data\test>dir
 Volume in drive C has no label.
 Volume Serial Number is A06D-D0B3

 Directory of C:\MySQL_5.6.14\data\test

12/18/2013  04:18 PM    <DIR>          .
12/18/2013  04:18 PM    <DIR>          ..
10/24/2013  10:20 AM                65 db.opt
12/18/2013  04:20 PM                35 dt.CSM
12/18/2013  04:19 PM                49 dt.CSV
12/18/2013  04:18 PM             8,578 dt.frm
10/29/2013  06:06 PM                 0 exp1#P#part00.MYD
10/29/2013  06:06 PM             1,024 exp1#P#part00.MYI
10/29/2013  06:06 PM                 0 exp1#P#part01.MYD
10/29/2013  06:06 PM             1,024 exp1#P#part01.MYI
10/29/2013  06:06 PM                 0 exp1#P#part02.MYD
10/29/2013  06:06 PM             1,024 exp1#P#part02.MYI
10/29/2013  06:06 PM               336 exp1#P#part03.MYD
11/15/2013  05:44 PM             2,048 exp1#P#part03.MYI
10/29/2013  06:06 PM                 0 exp1#P#part99.MYD
10/29/2013  06:06 PM             1,024 exp1#P#part99.MYI
10/29/2013  06:06 PM             8,642 exp1.frm
10/29/2013  06:06 PM                60 exp1.par
10/24/2013  10:30 AM             8,682 menus.frm
10/24/2013  10:30 AM                68 menus.MYD
10/28/2013  03:00 PM             1,024 menus.MYI
10/24/2013  10:30 AM             8,584 pages.frm
10/24/2013  10:30 AM                80 pages.MYD
10/28/2013  03:00 PM             2,048 pages.MYI
              22 File(s)         44,395 bytes
               2 Dir(s)  1,869,894,590,464 bytes free

C:\MySQL_5.6.14\data\test>type dt.CSV
1,"rolando"
2,"pamela"
3,"dominique"
4,"diamond"

C:\MySQL_5.6.14\data\test>

As show in the above commands in mysql and in the Windows command line, the CSV file that mysql can handle has the following two characteristics:

fields delimited by commas
character fields enclosed in double quotes

Since you created the test table as CSV, here is what you should do:

Make sure the existing CSV file has the two characteristics for mysql to work with it
Go to the folder test in your datadir.
Rename test.CSV to test1.CSV
Copy your existing CSV file to test.CSV
Login to mysql and run FLUSH TABLES;

Afterwards, the table shold be fully accessiable.
CAVEAT (Windows)
If Windows has the CSV table locked, run these commands

Make sure the existing  CSV file has the two characteristics for mysql to work with it
Go to the folder test in your datadir.
Run net stop mysql
Rename test.CSV to test1.CSV
Copy your existing CSV file to test.CSV
Run net start mysql

Give it a Try !!!


I do not perform SUM with conditions in SQL Server

SELECT        cli_razon_soc,
SUM(CASE WHEN t .fac_saldo_tot > 500 THEN t .fac_saldo_tot ELSE 0 END) AS 'Mayor500'
FROM            cdc_vw_BuroCredito_Facturas95 AS t
GROUP BY cli_razon_soc, fac_saldo_tot
ORDER BY cli_razon_soc, 'Mayor500'

As the CASE operation is being applied to each row, checking the sum doesn't make sense logically. What we need to do for each row is check the value, if it's above 500 then sum it, else treat the value as 0.

The above code should accomplish this.


Optimize query with join and null predicate [SQL Server]

You have a two table query here with a join. One of the tables needs to be the outer table for the join and one of them needs to be the inner table. With appropriate indexes on both tables, as a general rule I would want the most selective table to be the outer table for a TOP 100 query. As far as I can tell, SQL Server picked the correct table for the outer table, AnalyticsSession. The problem appears to be that there isn't a selective enough index defined against that table.

Let's consider what would happen if the optimizer used the AnalyticsUser table for the outer table. That filtered index looks to be the best possible index for the query. However, the index isn't selective. In the comments you indicated that 97% of the rows in the table match the filtering that you have on [Email] and [UserId]. So SQL server might end up scanning a lot of rows from the index until it found the first 100 that satisfied the filter conditions for both tables.

Compare that to using the AnalyticsSession table as the outer table. The filter seems to be pretty selective (you had to process 113,000 rows just to get 100 matches), but the index used is not selective at all. A clustered index scan will just go in whatever order the data is logically or physically defined on disk. If you created an index similar to the following:

CREATE NONCLUSTERED INDEX [IX_AnalyticsSession__CreatedOn_Id] ON [dbo].
[AnalyticsSession]
(   CreatedOn, AnalyticsUserId)
INCLUDE (id, UserAgent, IP, Guid, CreatedOn, UpdatedOn, IsDeleted, AnalyticsUserId, SiteId)
WITH (online=on);

I suspect that would help query performance quite a bit. SQL Server would be able to do a scan on that index to immediately jump to the first possibly relevant row from AnalyticsSession. If 97% of the rows from AnalyticsUser match the filter condition and the filters between the two tables are independent, then you should be able to get the first 100 rows with a scan of 110 rows or less from the new index on AnalyticsSession.

If the filters are not independent then your query may scan more rows than expected. For example, suppose that users who registered near the beginning of the site are more likely to have an email or an id. With the index as defined above, SQL Server is likely to scan the oldest rows first by CreatedOn. As you described in your comment you may get better performance by getting the scan to go in descending order rather than ascending order. The best way to do this is to add an ORDER BY at the end of your query:

WHERE
(analyticsu1_.Email IS NULL
AND analyticsu1_.UserId IS NULL)
AND this_.CreatedOn < '2017-04-01'
ORDER BY this_.CreatedOn DESC

Otherwise you aren't guaranteed to get the scan in the order that you're expecting. Note that TOP 100 without ORDER BY is nondetermistic and may return different results from one execution to the next. Consider adding an ORDER BY to your query if that makes sense to do so.

If you're interested in learning more about how SQL Server optimizes queries with TOP in general I recommend Inside the Optimizer: Row Goals In Depth by Paul White.


MariaDB are bitwise operators defined on strings?

It appears that MariaDB (and MySQL for that matter) attempts to implicitly cast operands of bitwise operators to integers, and it is known MySQL behaviour when converting strings to integers to ignore the rest of the string if it encounters non-digits, e.g. cast('123abc' as integer) will yield integer 123, and cast('ff' as integer) will yield 0.
This is a feature of the bitwise operators and bit functions, not the BIT type itself, as stated in the MySQL manual (I assume MariaDB inherits this behaviour):

Currently, bit functions and operators require BIGINT (64-bit integer) arguments and return BIGINT values, so they have a maximum range of 64 bits. Arguments of other types are converted to BIGINT and truncation might occur.

It looks like this behaviour is going to change in MySQL version 8:

An extension for MySQL 8.0 changes this cast-to-BIGINT behavior: Bit functions and operators permit binary string type arguments (BINARY, VARBINARY, and the BLOB types), enabling them to take arguments and produce return values larger than 64 bits.

which will bring it in line with your expectations.


How can I merge data from an Excel file into an access database with multiple tables?

To update your tables as described requires multiple steps that can't be answered in detail for this single question.  I'll give what I hope is a useful outline, but you will need to search for more specific help for each step (in this forum, on Stack Overflow, or on the web).

I am using Access 2013, but these steps should be identical/similar for versions back to Access 2007.

Import or Link to the Excel file:

In Access, go to the External Data ribbon (i.e. toolbar tab).
Select the Import Access Database icon.
Follow the import wizard steps.

Linking to the Excel file creates a link "table" in Access.  On the surface it behaves and looks like another Access table, but it reads the data directly from Excel. Whether you link to the file or import the data depends on your preference and use.  If the Excel file data changes often AND the file location and format remain the same, then linking can avoid having to re-import into a new table, handling duplicates, etc.
However, if the Excel file is not formatted simply (i.e. basic columns with headers) or there are other complications, importing the data into a standard Access table can avoid some headaches.

Write multiple update queries that joins a table to the Excel data and updates the part number.

Because you do not speak "accesseze", perhaps you also don't know SQL.  In that case, you can use the Query wizards in Access to accomplish the updates.  However, it is much more convenient to share queries using SQL in these forums, hence the following example SQL.  The convenient thing about Access is that it allows switching between SQL View and the visual Design View
The following example will of course only update records with exact matches.  It is prudent to write data validation queries which look for missing part numbers, ill-formed part numbers, etc.  Such queries will ultimately require other types of joins between the tables, looking for Null values, etc.
Example SQL with made-up names since your question didn't include more details about the data:

The WHERE Component_Table.InternalPartNo Is Null clause is there to avoid inadvertently overwriting existing data.  Only remove this criteria if you are certain that you want to overwrite existing numbers, but refer back to my previous hint about data validation.

UPDATE Component_Table
INNER JOIN Excel_Data ON Component_Table.ManufacturerID = Excel_Data.ManufacturerID
SET Component_Table.InternalPartNo = Excel_Data.InternalPartNo
WHERE Component_Table.InternalPartNo Is Null


Recover an Innodb mysql database from an EBS on Ec2

InnoDB: Cannot continue operation.

The MySQL server is refusing to start, so it's not running, and you're not going to be able to connect with the mysql command line utility until it is... but you don't need it running to fix the permissions.

The permissions you need to fix are filesystem permissions, with some variant of this:

$ sudo chmod -R mysql:mysql /var/lib/mysql
$ sudo chmod -R mysql:mysql /var/log/mysql

(Don't copy/paste this unless you are clear on what it does.)

After that, you should be able to start the server if the files are intact and the paths were correct.

Also, I'm not sure where you found the steps you originally used, but I assume the purpose of this exercise is to get the server running again so you can make a backup, after which you'll stop it and unmount those "bind" mounts.


Not sure where there complexity is. Use a UNIQUE constraint on a composite key.

CREATE TABLE foo (
  a int,
  b int,
  UNIQUE(a,b)
);
INSERT INTO foo VALUES (1, null), (2, null);
INSERT INTO foo VALUES (null,1), (null,2);

But,

INSERT INTO foo VALUES (2,2), (2,2);
ERROR:  duplicate key value violates unique constraint "foo_a_b_key"
DETAIL:  Key (a, b)=(2, 2) already exists.

If you're not creating the table but modifying one already made, you can use this:

ALTER TABLE foo ADD CONSTRAINT foobar UNIQUE (a, b);


Update value by column/field in INSERT SQL

For an update, you are changing an existing column value in a number of existing rows, 'select'ed based on the "where" clause you specify:

update product_price
set price = price + 10
where product_id = 4442 ;

The query locates any rows with product_id 4442 and then, for each one, extracts the current value of price, adds 10 to it, and stores that result back into each record.

For an insert, you are creating a completely new record which, by definition, cannot contain any values (until after it's been created):

insert into product_price
values ( 2442, 1, price * 1.1 )

So; what value does price represent in this query?

Before this statement executes, there is no row with product_id 2442.
Therefore, there is no price value (in that non-existent row) for the query to retrieve and modify.


Sql Server - Build a json array of integer from an aggregate

You could approximate the desired output like this:

SELECT p.id
    , p.[name]
    , LuckNumbers = (
            SELECT ln.LuckyNumber
            FROM #LuckyNumbers ln
            WHERE ln.PersonId = p.Id
            FOR JSON PATH
        )
FROM #person p
FOR JSON PATH;

Essentially, creating a JSON array of int values for each person's lucky numbers.  I'm not sure how to remove the "LuckyNumber" tag from each value; this is my first time with JSON on SQL Server.

The output looks like:

[
    {
        "id":1
        ,"name":"Josian"
        ,"LuckNumbers":[
             {"LuckyNumber":581}
            ,{"LuckyNumber":777}
            ]
    }
    ,{
        "id":2
        ,"name":"Paul"
        ,"LuckNumbers":[
             {"LuckyNumber":123551}
            ,{"LuckyNumber":5}
            ,{"LuckyNumber":646464}
            ,{"LuckyNumber":1345}
            ,{"LuckyNumber":75}
            ,{"LuckyNumber":76}
            ]
    }
    ,{
        "id":3
        ,"name":"Seasonique"
        ,"LuckNumbers":[
            {"LuckyNumber":1}
            ]
    }
]


moving a Partition of Partitioned Table to other File Group, SQL Server

There are a couple of issues here.  First, the partition/filegroup mapping shown in your question does not match the DDL.  A RANGE RIGHT function boundary is inclusive of the partition on the right so the results should be as below to match the DDL.  Consider downloading the partition details custom report from codeplex that can run from an SSMS object explorer table context menu to show the boundaries and filegroups: http://ssmspartcustomrpt.codeplex.com/releases/view/30936 .

OnlineGeoPosition   1   FG_OlderData    less than                  2015-01-21 00:00:00.000
OnlineGeoPosition   2   FG_OlderData    greater than or equal to   2015-01-21 00:00:00.000
OnlineGeoPosition   3   FG_OlderData    greater than or equal to   2015-02-20 00:00:00.000
OnlineGeoPosition   4   FG_OlderData    greater than or equal to   2015-03-21 00:00:00.000
OnlineGeoPosition   5   FG_NewerData    greater than or equal to   2015-04-21 00:00:00.000
OnlineGeoPosition   6   FG_NewerData    greater than or equal to   2015-05-22 00:00:00.000
OnlineGeoPosition   7   FG_NewerData    greater than or equal to   2015-06-22 00:00:00.000
OnlineGeoPosition   8   FG_NewerData    greater than or equal to   2015-07-23 00:00:00.000

When you merge a RANGE RIGHT function, the removed partition is the one on the right.  Data from the removed partition are moved to the retained one on the left.  So merging a RANGE RIGHT sliding window partition always moves from newer to older partitions.  However it is best to MERGE and SPLIT empty partitions as you are doing to avoid expensive data movement during partition DDL, which is about 4 times that of normal DML. Conversely, a SPLIT of a RANGE RIGHT function creates a new partition to the right of the specified boundary, and on the NEXT used filegroup, which is the main problem you are having.

Below is a sample script to achieve a sliding window with the new partition/filegroup mappings below.  This script uses 2 staging tables, one for the latest partition on the old data filegroup and another for the oldest partition on the new data filegroup (the one to be moved).  This allows the boundary to be safely merged with both adjacent partitions empty.  You'll need either check constraints on the staging tables or another partition function/scheme to constrain the staging table data to match the target partition boundaries for the switch in.  Data are moved efficiently using a CREATE INDEX...DROP_EXISTING.

OnlineGeoPosition   1   FG_OlderData    less than   2015-02-20 00:00:00.000
OnlineGeoPosition   2   FG_OlderData    greater than or equal to   2015-02-20 00:00:00.000
OnlineGeoPosition   3   FG_OlderData    greater than or equal to   2015-03-21 00:00:00.000
OnlineGeoPosition   4   FG_OlderData    greater than or equal to   2015-04-21 00:00:00.000
OnlineGeoPosition   5   FG_NewerData    greater than or equal to   2015-05-22 00:00:00.000
OnlineGeoPosition   6   FG_NewerData    greater than or equal to   2015-06-22 00:00:00.000
OnlineGeoPosition   7   FG_NewerData    greater than or equal to   2015-07-23 00:00:00.000
OnlineGeoPosition   8   FG_NewerData    greater than or equal to   2015-08-22 00:00:00.000

Sample script:

--switch out newest partition on FG_OldData
ALTER TABLE dbo.OnlineGeoPosition
SWITCH PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-03-21')
TO dbo.OnlineGeoPosition_OlderTemp;

--switch out oldest partition on FG_NewData
ALTER TABLE dbo.OnlineGeoPosition
SWITCH PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-04-21')
TO dbo.OnlineGeoPosition_NewerToOlderTemp;

--move empty partition from new data to old data
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
    MERGE RANGE ('2015-04-21');

--create new empty partition on old data filegroup for moved data
ALTER PARTITION SCHEME ps_Positions_LastSixMonth_MontlyRange
          NEXT USED FG_OlderData;
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
    SPLIT RANGE ('2015-04-21');

--rebuild each index with drop existing to move data from new to old filegroup
CREATE UNIQUE CLUSTERED INDEX PK_OnlineGeoPosition_NewerToOlderTemp
ON dbo.OnlineGeoPosition_NewerToOlderTemp(PartitioningColumn)
WITH(DROP_EXISTING=ON)
ON FG_OlderData;

--switch staging tables data back in
--staging tables must have a check constraint or be partitoned similarly as the target partition
ALTER TABLE dbo.OnlineGeoPosition_OlderTemp
SWITCH TO dbo.OnlineGeoPosition PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-03-21')
ALTER TABLE dbo.OnlineGeoPosition_NewerToOlderTemp
SWITCH TO dbo.OnlineGeoPosition PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-04-21')

--remove oldest partition
TRUNCATE TABLE dbo.OnlineGeoPosition_OlderTemp;
ALTER TABLE dbo.OnlineGeoPosition
SWITCH PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-01-21')
TO dbo.OnlineGeoPosition_OlderTemp;
TRUNCATE TABLE dbo.OnlineGeoPosition_OlderTemp;
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
    MERGE RANGE ('2015-01-21');

--create new partition on new data filegroup for future data
ALTER PARTITION SCHEME ps_Positions_LastSixMonth_MontlyRange
          NEXT USED FG_NewerData;
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
    SPLIT RANGE ('2015-08-22');
GO


How to perform AVG, arithmetic operations in MySQL query

You can simply use the arithmetic operators in the first SELECT

SELECT
   AVG(value) as value_avg1,
   AVG(value2) as value_avg2,
   AVG(value) + (5.67E-8 * POWER(AVG(value2) + 273.14, 4)) as final_value
FROM kami_all_dat
WHERE usec >= 1401631200 AND usec <= 1401632400


Can I change the wal_level from logical to replica? If so are any impact on the replication process?

If you reduce wal_level to replica, and you have a logical replication slot (as is needed for logical replication), the server will refuse to restart.

This is because wal_level = logical is required by logical decoding and logical replication.

If you first drop the subscription and the publication, thereby removing logical replication, the replication slot will be gone, and you can reduce wal_level.

To switch from logical to physical replication, you have to re-create the standby server with pg_basebackup.