How to prevent implicit casting from bytea to text during the current connection in postgres?

for example if there are non-ASCII bytes it cannot work smoothly

Why would you think so? bytea is coerced to either hex (default) or escape format when assigned to a text column. Non-ASCII characters are encoded automatically. Should work "smoothly" at all times - except that you don't want to allow it.

I could do CREATE CAST followed by DROP CAST but this seems dirty
to me as it still not contained within the connection.

True, but it is contained within the transaction if you create and drop the cast within: DDL commands are fully transactional in Postgres, so only your session (within that current transaction) will ever get to see the cast.

How do I make (implicit) casts from bytea to text throw an exception only within the current connection?

... I can add a preceding SQL statement before every statement, that's no problem.

Solution with custom cast
Currently (all versions incl. Postgres 13) the cast from bytea to text has no explicit entry in the system catalog pg_cast. It is provided by basic input/output functions of the respective types. This behavior can be overruled with an explicit entry, created with CREATE CAST.
You need to be the owner of the involved types, so this basically means you have to be superuser install it.
Create this casting function once per database:
CREATE FUNCTION public.text(bytea, int, bool)
  RETURNS text
  LANGUAGE plpgsql STABLE STRICT PARALLEL SAFE AS
$func$
BEGIN
   IF $3 THEN           -- true if the cast is an explicit cast, false otherwise.
     -- no infinite loop because we do the cast manually
     -- honors current setting for bytea_output, hence function not IMMUTABLE
      RETURN textin(byteaout($1));
   ELSE
      RAISE EXCEPTION 'Assignment cast from bytea to text forbidden by custom cast rules in this database!';
      RETURN textin(byteaout($1));  -- we should *never* get here!
   END IF;
END
$func$;

To allow creating / dropping the special cast to unprivileged roles, add wrapper functions. Do this as superuser (or as dedicated daemon role):
CREATE FUNCTION public.f_create_cast_bytea2text()
  RETURNS void
  LANGUAGE sql SECURITY DEFINER AS
'CREATE CAST (bytea AS text) WITH FUNCTION public.text(bytea, int, bool) AS ASSIGNMENT;';

CREATE FUNCTION public.f_drop_cast_bytea2text()
  RETURNS void
  LANGUAGE sql SECURITY DEFINER AS
'DROP CAST IF EXISTS (bytea AS text);';

Now you can do what you asked for:
BEGIN;
SELECT public.f_create_cast_bytea2text();  -- optionally activate your casting rule

INSERT INTO tbl(txt_col)
VALUES ('\000'::bytea::text, 'local bytea_output: hex');    -- explicit cast still works!

INSERT INTO tbl(txt_col)
VALUES ('\000'::bytea); -- but assignment cast forbidden! -> ERROR

SELECT public.f_drop_cast_bytea2text();  -- deactivate your casting rule
END;

db<>fiddle here -- second half does not execute due to missing privileges.
Extended test case
Test table:
CREATE TABLE test(id int, txt_col text, note text);
INSERT INTO test(id, txt_col, note) VALUES
  (-1, 'foo', 'plain text input')
, ( 0, '\000'::bytea, 'default bytea_output: ' || current_setting('bytea_output'));

No exception raised:
BEGIN;
SELECT public.f_create_cast_bytea2text();

SET LOCAL bytea_output = 'hex';
INSERT INTO test(id, txt_col, note)
VALUES (1, '\000'::bytea::text, 'local bytea_output: hex');    -- explicit cast still works

SET LOCAL bytea_output = 'escape';
INSERT INTO test(id, txt_col, note)
VALUES (2, '\000'::bytea::text, 'local bytea_output: escape'); -- explicit cast still works

SELECT public.f_drop_cast_bytea2text();
END;

Also no exception:
BEGIN;
SELECT public.f_drop_cast_bytea2text();

SELECT '\000'::bytea || text 'foo'; -- implicit cast still works

SELECT public.f_drop_cast_bytea2text();
END;

Exception raised:
BEGIN;
SELECT public.f_create_cast_bytea2text();

INSERT INTO test(id, txt_col, note)
VALUES (3, '\000'::bytea, 'must fail!'); -- assignment cast forbidden!

SELECT public.f_drop_cast_bytea2text();
END;

Client side problem?
Your comment seems to reveal a rabbit hole:

Input values must be prepared differently depending on whether they
are strings with encoding or binary strings. Even if we make use of
the currently active encoding. Assuming that binary strings will go
into bytea and strings with encoding will go into text.

The solution cannot work at all once you incorrectly assume data type text on the client side. The cast is only invoked if you hand in typed bytea values. I.e.: use a function or prepared statement with explicit data type or append an explicit cast to string literals handed to an INSERT command like demonstrated above: '\000'::bytea.
Once you pass untyped literals, Postgres has no way to know that it should really be bytea. And how can you (incorrectly) prepare bytea strings for text input and then still (correctly?) add an explicit cast to bytea?


Oracle remote OS authentication, security risks with domain users

Beware the OS authentication is done by client machine, not by the database server.

I think that within reasonable pessimism to Oracle tools you could expect that it boils down to this:  database server receives a TCP connection from whichever IP address that might pass the network path, and that connection just claims "I promise I've done OS authentication and, trust me, this connection is made on behalf of OPS$JohnSmithCEO."  Neither the database client or database server is given OS password for additional OS authentication! And you can't even trust that this connection came from a valid Oracle software. It can be man-in-the-middle in reality. It's worse than telnet.

Whatever "Windows-specific" checks are done by Oracle, they seem contrived and you can't really trust they are complete and secure.


After bringing the slave database cluster into standalone read-write mode (either by internal logic inside recovery_command or by restarting it without recovery.conf) it cannot be easily reverted to be a slave.

If you can, use file system level snapshots. This would be the easiest approach as you can revert the cluster to a known state without copying whole data directory.

More or less that would be the logic for testing slave:

stop_postgres()
make_pgdata_filesystem_snapshot()
remove_recovery_conf()
start_postgres()
run_testing()
stop_postgres()
revert_pgdata_fs_snapshot()
start_postgres()

If you don't have filesystem snapshots you will need a local copy of the whole cluster (rsync will be good to maintain this). Only disadvantage is disk space used, and more time needed.

rsync_pgdata_to_local_copy()
stop_postgres()
rsync_pgdata_to_local_copy()
remove_recovery_conf()
start_postgres()
run_testing()
stop_postgres()
rsync_pgdata_from_local_copy()
start_postgres()


How to make value in a row equal to now() in postgres

You can use now(), CURRENT_DATE and their friends wherever an expresison is accepted.  That means, you can define your table like

CREATE TABLE experiment (
    e_id text PRIMARY KEY,
    e_started date NOT NULL DEFAULT CURRENT_DATE,
    e_ended date
);

Here, when you insert an experiment, it is enough to insert its ID:

INSERT INTO experiment (e_id) VALUES ('this_experiment_about_AAAAA');

Still, the start date will be the day when this INSERT was issued.  The end date is left to NULL - this is the usual way of having undefined values.  You will find the running experiments like

SELECT e_id, e_started FROM experiments WHERE e_ended IS NULL;

When one finishes, you update the given row and set the end date as necessary (to CURRENT_DATE, for example).

Notes:

I used different column names than you.  I'd advise to use snake_case instead of CamelCase - the latter often leads to confusion.
another naming consideration is that the table defines the data type, no need to reflect this in the column name.
the last naming thing: I prefixed the column names with the table name acronym.  You don't have to do this, it's a matter of taste more than anything else.


Converting date string to SQL DATETIME - hopefully without a udf

This works:

DECLARE @mycol VARCHAR(30) = 'Tue Apr  1 12:20:40 2014'

SELECT CAST(SUBSTRING(@mycol, PATINDEX('% %', @mycol), 100) AS DATETIME)

The day name at the beginning is what prevents it from being able to convert directly.

Instead of declaring the variable, you can just substitute the column containing your dates for @mycol in the script above.


What is a good unique identifier for customer and payment details for this tourism company?

Your question is a business one, not a database one. The 'unique identifier', or your key, should be the key that the business uses to distinguish customers. Some may use a user-name, others may use Email, some may use SSN, some may use retina scan.
The database schema is a MODEL of the business reality, therefore, it should follow the keys that are used in the real world.

It won't help you much to assign a surrogate key, like IDENTITY or GUID, as you still won't be able to distinguish one customer from another, as you can't extract that property from the customer itself, and name is typically not enough. What would you do if a customer walks into the office and there are 4 other customers with the same name?

  Let's say you have 2 customers named John Doe. You insert them into
  the table and assign them surrogate primary keys with ID 34 for one,
  and 237 the other. A customer walks into the store. Says 'hi, I'm John
  Doe and I'm here to buy an item'. You type 'John Doe', and into the
  screen to pull up his account, and you see 2 options - John Doe 34,
  and John Doe 237. You look at the customer and ask "Are you John Doe
  34 or 237?". The customer thinks you're crazy and walks away... The ID
  doesn't identify the ENTITY, it is a relic of thought from 50 years
  ago when magnetic tapes had address pointers...

The question mentions that the data collected is "customer's first and last names, address, date of birth, current mobile number".
If that is all you have to distinguish customers in the 'real world', that is your key.
Perhaps it is just first and last name, perhaps first and last name + address, perhaps just mobile number...

As for payment, again, the question does not provide enough information for you to give an answer. What payment methods are available? Cash? Checks? Credit Cards? Wire Transfers only? Can the customer pay in multiple payments and multiple payment methods?
Again - whatever the BUSINESS uses to distinguish payments, is what your key will be.

HTH


Initializing values to a custom data type

I believe you are missing the datatype before the initialization of the type/object.  See the updated (but untested code below).

Oracle Documentation

declare
    rajesh CUSTOM_DATATYPE;
begin
    rajesh := CUSTOM_DATATYPE('xyz',123,'13-02-14','12','fdfd','add1','add2','add3','add4','packtype','noofpacktype','1234','dfs','dde');
      DN_Generated(rajesh);
end;


Does the creation of constraints affect the views that have been created earlier?

No matter what database you are using, you shouldn't find any problem with it, UNLESS you are using the VIEWS to UPDATE\DELETE records (I know is possible in SQL SERVER).

If you are doing that (update\delete on the view), you will get a constraint error once you try to violate the reference.


distinguish single-block vs multi-block reads in Oracle explain plan

TABLE ACCESS FULL and INDEX FAST FULL SCAN use multi-block I/O, other kind of access paths use single-block I/O.


Where to store versioned/historized records?

We use Change Data Capture. SQL Server essentially creates journal tables and then automatically reads from the transaction log and fills in the journal tables with information about DML operations on the original table.

To save space, we compress the journal tables. This means your journal tables are stored in the same database, but if you are having space issues, you could offload them to a different file/filegroup and store that on a different disk.

The journal tables are in a separate schema so you could exclude that entire schema from your short-term backups and only include them in your long-term backups.


Slow query / Indexes creation (PostgreSQL 9.2)

Perhaps this will help.

If you'll rely on the account_id from full_path often, then you'll benefit from a function and a functional index for it:

CREATE OR REPLACE FUNCTION gorfs.f_get_account_from_full_path(p_full_path text) RETURNS int AS $body$
SELECT (regexp_matches($1, '^/userfiles/account/([0-9]+)/[a-z]+/[0-9]+'))[1]::int
$body$ LANGUAGE SQL IMMUTABLE SECURITY DEFINER RETURNS NULL ON NULL INPUT;

CREATE INDEX ON gorfs.inode_segments (gorfs.f_get_account_from_full_path(full_path));

Ensure gorfs.inodes has an index (or key much better if applicable) on st_ino!

You run the function split_part several times for each row, this is likely taking a significant toll. I've replaced it with string_to_array, and then fetch the individual pieces as needed. I also didn't understand what you intended to obtain for the field field_name using reverse? The query below returns the last element for it.

Your query returns many million rows. Even if PostgreSQL processes the query reasonably quickly, your client application (especially if you use PgAdminIII) will struggle allocating enough memory and receive and format the results, and probably be what takes the most time. So you may want to create a temporary table with the results, and then query against the temporary table:

CREATE TEMP TABLE myresults AS
WITH
  accounts AS (
    SELECT id
    FROM public.ja_clients
    WHERE NOT (
               (last_sub_pay > EXTRACT('epoch' FROM now() - '4 Months'::INTERVAL) AND price_model > 0) OR
               regdate > EXTRACT('epoch' FROM now() - '3 Month'::INTERVAL) OR
               (price_model = 0 AND jobcredits > 0 AND last_login > EXTRACT('epoch' FROM now() - '4 Month'::INTERVAL))
              )
    ORDER BY 1 LIMIT 100 -- first 100 accounts for testing purposes; comment out this line once the query is proven performant enough
    )
SELECT r.parts[4]::INT AS account_id, r.parts[6]::INT AS note_id, r.parts[9] AS variation,
       st_size, segment_index, r.parts[array_upper(r.parts, 1)] AS file_name, st_ino, full_path, size_mb
FROM (
  SELECT string_to_array(full_path, '/') AS parts, st_size, segment_index, i.st_ino, full_path,
         (i.st_size / 1000000::FLOAT)::NUMERIC(5,2) || 'MB' AS size_mb
  FROM gorfs.inode_segments s
  JOIN gorfs.inodes i ON (i.st_ino = s.st_ino_target)
  WHERE gorfs.f_get_account_from_full_path(s.full_path) IN (SELECT * FROM accounts)
    AND i.checksum_md5 IS NOT NULL
    AND i.st_size > 0
  ) r;

SELECT *
FROM myresults
LIMIT 100;


I think the other answer missed the point of the question; which is that in SQL Server mirroring, a transaction that commits to both servers A and B are committed at the same time (in atomicity).  If the transaction fails on one server, it fails on both.  It doesn't seem like there is a similar feature on MySQL


Why - read but not update ops fail in case of lacking space in tempdb database?

(Converting my comment to an answer. I don't know if this is definitive, but it makes sense to me. If you can prove it wrong, let me know in the comments.)

The row versions aren't actually required to complete a write operation; they're only required by readers that come along and need those versions of the rows. If they need them.

If tempdb runs out of space during version generation, while that's not okay, it may end up working out if no readers need those versions. A read operation would only fail if it needs a version of a row that isn't in tempdb.

Furthermore, any write operation is considered more important than a SELECT, for obvious reasons (same idea as when the Deadlock Monitor figures out which process to kill if there's a deadlock), so it's preferable to fail a SELECT if possible.


Is there any way to concatenate a value in a column with different strings?

I have the generic expression you need:

SELECT CONCAT(@num,IF(MOD(@num,100) IN (11,12,13),'th',
IF(MOD(@num,10) IN (1,2,3),SUBSTR('stndrd',2*MOD(@num,10)-1,2),'th')));

What this does is the following

If the last digit of a number is 1 (other than last two digits being 11), st is appended
If the last digit of a number is 2 (other than last two digits being 12), nd is appended
If the last digit of a number is 3 (other than last two digits being 13), rd is appended
All other two-digit combinations have th appended

To test this out, run the following

SELECT CONCAT(num,IF(MOD(num,100) IN (11,12,13),'th',
IF(MOD(num,10) IN (1,2,3),SUBSTR('stndrd',2*MOD(num,10)-1,2),'th'))) n FROM
(SELECT th*1000+h*100+t*10+u num FROM
(SELECT 0 th UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) TH,
(SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
(SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U) A
ORDER BY num;

UPDATE 2017-04-13 14:29 EDT

You asked me

  Hey Rolando, I understand roughly how you have implemented this. But How would I work this around my code as so; select id, duration, @position := @position + 1 as position from (select @position := 0) x, (select id, duration from course order by duration asc) y;

SELECT id,CONCAT(position,IF(MOD(position,100) IN (11,12,13),'th',
IF(MOD(position,10) IN (1,2,3),SUBSTR('stndrd',2*MOD(position,10)-1,2),'th')))
FROM
(
    select id, duration, @position := @position + 1 as position
    from (select @position := 0) x,
    (select id, duration from course order by duration asc) y
) a;

Basically, I placed your query inside a subquery and applied the generic expression.


How to find the columns and tables used in a stored procedure (SQL Server)

Update: You can get the information from a non-deprecated dynamic management function rather then the older dynamic management view, as pointed out by dfundako in the comments below. This would be the recommended approach as the function is available in all currently supported SQL Server releases and likely to be so in at least the next few releases too.

To update my previous example:

CREATE PROCEDURE test_depends
AS BEGIN
    SELECT TOP 5 ou_id, prop_text FROM tr_n_ou WHERE prop_name = 'display_name'
    SELECT * FROM tr_n_org WHERE parent_display_name = 'Global'
    EXEC some_stored_procedure
END
GO

SELECT ISNULL(referenced_schema_name+'.','')+referenced_entity_name+ISNULL('.'+referenced_minor_name,'') AS Dependency
     , ISNULL(so.type_desc, sn.type_desc) AS DependencyType
     , CASE WHEN ISNULL(so.type_desc, sn.type_desc) IN ('USER_TABLE', 'VIEW')
            AND referenced_minor_name IS NOT NULL
            THEN 'Column in '
            ELSE ''
            END
     + ISNULL(so.type_desc, sn.type_desc) AS DependencySubType
FROM sys.dm_sql_referenced_entities('dbo.test_depends', 'OBJECT') sr
LEFT OUTER JOIN
     sys.objects so
     ON  so.object_id = sr.referenced_id
LEFT OUTER JOIN
     sys.objects sn
     ON  sn.name = sr.referenced_entity_name
     AND sr.referenced_id IS NULL

GO
DROP PROCEDURE test_depends

The caveats about ad-hoc SQL still apply.

Also if a procedure references something that is user schema dependent the referenced ID will be NULL in the DMF which is why I'm doing the extra lookup by name in this example. If the EXEC line specified a schema (i.e. EXEC some_schema.some_procedure) then the ID would have a value so the ID lookup would work, otherwise there could be several procedures with the same name in different schemas and the one called will depend on run-type details. Note also that the name lookup will return multiple rows for the same dependency in this case if there are multiple procedures of the same name.

________________________________________

Previous answer:

You can get this information, mostly, from sys.sql_dependencies but be aware that this is marked for deprecation so may not be available in future versions of SQL Server.

To test in one of your databases do something like:

CREATE PROCEDURE test_depends
AS BEGIN
    SELECT TOP 5 column1, column2 FROM some_table WHERE column3 = 'some value'
    SELECT * FROM another_table
    EXEC some_stored_procdure
END
GO

SELECT sd.*, so.name, sc.name
FROM   sys.sql_dependencies sd
LEFT OUTER JOIN
       sys.objects so
       ON  so.object_id = sd.referenced_major_id
LEFT OUTER JOIN
       sys.columns sc
       ON  sc.object_id = sd.referenced_major_id
       AND sc.column_id = sd.referenced_minor_id
WHERE sd.object_id = (SELECT object_id FROM sys.objects WHERE name = 'test_depends')

GO
DROP PROCEDURE test_depends

You will see that is correctly lists the individual columns for the first table the procedure references, all columns in the second table as that selects *, and the procedure it calls.

Note that this does not deference views so you need a recursive query to get down to the base tables if that is your requirement, and you'll need to dig further too if a procedure calls other procedures.

Note again that sys.sql_dependencies is flagged as deprecated. I ran the above on a 2008r2 instance, the documentation on MSDN seems to list it only as far as 2014 so it may not be present in 2016 (I don't have access to any instances above 2012 in my current location in order to test this) though Kris notes in comments below that the view is still present in 2016. The replacement (sys.sql_expression_dependencies) doesn't list individual columns, at least in my test on a 2008r2 instance (the is only one entry per table with the minor ID 0 is both cases).

Another important thing to consider is that this view can't contain any references made by ad-hoc SQL run via EXEC (<string>) if you have such evilness in your procedures.


Best practice around sys.dm_db_missing_index_group_stats in SQL Server

Kimberly Tripp did a few very good video's on Index Analysis that covered your question extremely well as part of the MCM Readiness videos.

Indexing Strategies Demo
Indexing Strategies
Index Analysis

Among other things she said DO NOT just create all of them.  Review each one and compare it to your existing indexes, review it's cost etc.  I highly recommend them and best of all they are free!


MySQL CREATE statement are really slow (Mac OS X / Macport install)

Based on the data for the performance of the two drives you mentioned:

http://www.harddrivebenchmark.net/hdd_lookup.php?cpu=FUJITSU+MHV2060BHPL

versus

http://www.harddrivebenchmark.net/hdd_lookup.php?cpu=FUJITSU+MHY2160BH

It looks like the MacBook should be much faster. However, this being a laptop versus a stationary computer, I'm willing to bet you use the MacBook a lot more than the MacMini. Therefore I'm going to suggest that it's likely drive contention in many ways, both in finding open areas on the disk contiguous to create the space as well as other programs on the computer running at the same time. I would really question if you're planning on running the database on this laptop and using the performance metrics from the test runs for anything besides baseline benchmarking. Because when you goto run this code on the server somewhere else it's going to have much better performance.

Until you can eliminate all other factors and declare the two boxes to be as identical as possible can we say why the macmini is faster than the macbook. This becomes an issue for SuperUser not dba.SE


Setup Master-Slave replication with DML changes in Slave

If you only use Streaming replication, it doesn't support the write operation on the slave node. Currently,  PostgreSQL  doesn't support  multiple master itself, If you want multiple master solution,  I suggest you use PostgreSQL-XC.


OK, I managed to solve this by creating a Synonym in DBApp_DEV:

CREATE SYNONYM GenTableName FOR DBGen_DEV.dbo.TableName

And in DBApp_QA, the Synonym would be:

CREATE SYNONYM GenTableName FOR DBGen_QA.dbo.TableName

This way, I can maintain the exact same query in my SP that exists in both DBApp_* databases:

select Col1 from GenTableName


How to see, if a foreign key is enforced?

Not sure what version of db2 you are using, but if it's LUW you can use SYSCAT.TABCONST (see column ENFORCED):

select enforced
from syscat.tabconst
where tabschema = ?
  and tabname = ?
  and constname = ?

FWIW, it is better to use the syscat views (vs sysibm), since they are more stable between versions.


How do I query based on percentages of a subset of values?

WITH  RequiredAmounts
        AS ( SELECT 'A' AS [TYPE] ,
                    200 AS Amount
             UNION ALL
             SELECT 'B' ,
                    200
           )
  SELECT  t.*
  FROM    RequiredAmounts
          CROSS APPLY ( SELECT TOP ( RequiredAmounts.Amount )
                                *
                        FROM    YourTable AS t
                        WHERE   t.[TYPE] = RequiredAmounts.[Type]
                      ) AS t

RequiredAmounts can select from a temporary table, table variable, a TVP. It can be anything. Just replace this sample CTE with whatever suits your needs.


How can we create a table from stored procedure?

I would do it with 2 steps:
I would create a table template allowing to create as many tables as you want based on that table. Advantage is you only need to modify the table template, you don't need to touch the SP.

Create a table template;

CREATE table tableName_template(
    ID INT NOT NULL,
    SNAME VARCHAR(100)  NOT NULL,
    SSTATE VARCHAR(100) NOT NULL,
    TIME_STAMP VARCHAR(45) NOT NULL,
    constraint FK_SENSOR
        foreign key (SNAME)
        references sensorInfo(SName)
        on delete set null
);

Use this code

DELIMITER $$
CREATE DEFINER=`xxx`@`localhost`  (do not use root !! Create dedicated account)
PROCEDURE `create_tb_one` (In tableName varchar(255))
BEGIN

    If not exists (select 1 FROM information_schema.tables
    WHERE table_schema=DATABASE()
    AND table_name=tableName )
    Then

        SET @sql = CONCAT('CREATE TABLE ',tableName,' LIKE  tableName_template');
        PREPARE s FROM @sql;
        EXECUTE s;
        DEALLOCATE PREPARE s;
    END IF;

END $$
DELIMITER ;


How to sort rows to appear alternating (e.g. A B A B ... )?

Try this:

CREATE TABLE SortTest (val char(1))

INSERT INTO SortTest VALUES
('A'),
('A'),
('A'),
('B'),
('B'),
('B')

SELECT val
FROM SortTest
ORDER BY row_number() OVER (PARTITION BY val ORDER BY val), val


Read Committed Snapshot, stale data reads?

Read committed should always get the latest revision of the row that was available at the time of executing the SELECT query.

Yes, you are correct. Since it's RCSI, it's at a statement level and not a transaction level which would be regular snapshot isolation.

  But is it guaranteed, when having two independent sessions?

Yes, it should be.

  Does the RCSI guarantee that R always see the most recent state of the table?

Yes, from when the statements starts it'll be the most recent and based off the version store if need be. This means if the read query started and the write was in the middle of the write, the older value would be seen by read statement.

What you're describing is normal and in line with small, quick, concurrent updates on one thread and reads on another. Most of the time RCSI alleviates the locking/blocking at the cost of potentially slightly stale reads. You could get to the bottom of the issue by checking on an individual statement level when each statement started, committed, etc., but if they are milliseconds or microseconds apart there really isn't anything you could do to change it outside of writing in thread synchronization for the application accessing the data - thus forcing the reads to not be stale.

Note that this assumes the writes are small, fast, and complete successfully. For example, a write where the application is hung and never commits would show the previous value when read via RCSI instead of staying blocked such as in read committed.


What you are asking is impossible, unless you brute-force the password hash for the user.

How do you expect a system to be secure if passwords are stored in plain text?


SQL Server database on an SSD - any advantage to a separate file for every table?

I was thinking of allowing each table to have its own file so that no
  matter if I'm joining on 2, 3, 4, 5 or more tables, each table will
  always be read using a separate thread and the structure of each file
  will be closely aligned with the table contents, which would hopefully
  minimise fragmentation and make it faster for SQL Server to add to the
  contents of any given table

What the heck are you talking about? Not sure where you got your information from, but you should certainly discard that source. Nothing from what you assume here is actually correct.

If you want to read a good discussion of SSD performance for SQL Server there are several blog series out there. As usually, Paul Randal's one is the top read:

Benchmarking: Introducing SSDs (Part 1b: not overloaded log file array)
Benchmarking: Introducing SSDs (Part 1: not overloaded log file array)
Benchmarking: Introducing SSDs (Part 2: sequential inserts)
Benchmarking: Introducing SSDs (Part 3: random inserts with wait stats details)
Benchmarking: Multiple data files on SSDs (plus the latest Fusion-io driver)

Brent also has a nice presentation on the topic: SQL on SSDs: Hot and Crazy Love and there are more out there.

Going through all these presentations you will quickly notice that they all focus on writes since this is where SSDs performance comes into picture. Your post wording is almost entirely about reads, which is a different topic. If reads are your pain point then you should be talking about RAM, not about SSDs, and about proper indexing and querying strategies.


why a convert from varchar do date fails on a different row everytime it runs?

The reason the error occurs after varying numbers of rows are processed is because the conversion isn't done in a deterministic order.  It depends on the execution plan.

And especially if the query is parallel (given the number of rows, I expect that it is), rows will be distributed differently across threads on each run and might hit the "Compute Scalar" in a different order each time.

Other reasons are that different indexes might be used (resulting in the rows being ordered differently), although that seems less likely given the static nature of the query.

The reason the formatting parameter doesn't work when you do this:

select convert(date,The_ugly_field,103) from Ugly_Table

...is because the 103 style you're trying to use is for converting a date or datetime to a character data type.  Not a varchar to a date (which is what that CONVERT statement does).  In this case, SQL Server is just displaying the date in the format that is the default for the instance.

You'll need to manually track down the "bad" data that can't be converted:

optional: add a MAXDOP 1 hint to the query to make the order of the data returned slightly more deterministic
look at that last rows that were successfully returned by the query
perform your SELECT without the convert and manually inspect the values in that area
Correct or remove the bad data if possible
Add a CASE statement to the SELECT to avoid trying to convert bad values, like this:

select  CASE WHEN LEN(The_ugly_field) < 8 THEN '01/01/1900' ELSE convert(varchar(10), cast(The_ugly_field as date),103)  END
From Ugly_Table
order by The_ugly_field

I would suggest TRY_CONVERT, but that is only available on SQL Server 2012 and higher.


If you can rebuild the x.sql, run mysqldump with suitable option such as --no-drop-tables, --no-creates (or whatever they are).  Then look at the dump file to make sure there are no surprises in it.

If you can't rebuild x.sql, then edit it to get rid of drops/creates/etc.

If there are no USE statements in x.sql, you can say mysql ... new_db < x.sql (after doing CREATE DATABASE new_sql).  Then play SQL games to copy the data from new_db.foo to old_db.foo.  (Provide more details if you need help in this area.)


Oracle : Is there a way to grant the ability to run a job to a role?

This sounds like the usual problem with stored procedures: when they are compiled by developer A in his schema, the compiler only considers the privileges DIRECTLY granted to A - as if the roles were all stripped off before compiling.  This is often addressed by writing your procedure as AUTHID CURRENT_USER, but if the procedure calls another procedure granted through a role, it can fail.

Generally it seems anything you want to compile into a PL/SQL object has to be directly granted to you.  I've written a number of scripts to turn role-granted privileges into direct GRANT statements to the procedure owner so that it would compile.

Note this does not apply to anonymous blocks - they compile and run just fine with the benefit of your current roles' privileges.

So I think the scheduler is another case where you have to code as if the roles weren't there.  It's a pain and I hate it, but that's how it is.


How to output string values in single quotes?

Rather than retrieving the value as text with #>>, use #> to retrieve it as json and cast it to text afterwards:

SELECT ('{"a":[1,2,3],"b":["4","5","6"]}'::json#>'{b,2}')::text;

 text
------
 "6"
(1 row)

There is also the json_typeof function that can tell you what JSON type the value has:

SELECT json_typeof('{"a":[1,2,3],"b":["4","5","6"]}'::json#>'{b,2}');

 json_typeof
-------------
 string
(1 row)


For what you're trying to accomplish, you'll want to normalize your data into two Invoice related tables. One for header information, e.g. Invoice_Id, Invoice_Date, Customer_Id, TotalCost; and one for the invoice line information, e.g. InvoiceLine_Id, Invoice_Id, Product_id, Quantity, Price.
The InvoiceLine table will have one row per product that was purchased on this invoice. It will have a unique InvoiceLine_Id per row (key field), and the InvoiceId field in it can be repeated across rows because it will be a many-to-one foreign key relationship to the InvoiceHeader table.


Query in procedure occasionally does not finish

I wouldn't bother with using Profiler at this point. You're likely just exasperating the issue, since streaming that back through a UI is not exactly light on resources. If you want to trace, set up a server-side trace and inspect the results afterward.

But first, let's start simpler. When the procedure is running in its "stuck" state, look at the DMV sys.dm_exec_requests. You want to see what it is waiting on (wait_type, last_wait_type) and whether it is being blocked by another session (blocking_session_id). You can also see if certain SET settings (quoted_identifier, arithabort, etc.) are different for the session than they are for the plan (which you can get in a roundabout way from sys.dm_exec_plan_attributes) and for the stored procedure itself - some of which you can get using OBJECTPROPERTY().

For example, let's compare just a few settings for the procedure, the session and the plan. First, while the query is running forever (and assuming you know the spid; I'll assume here it is 67), check these values from sys.dm_exec_requests and sys.dm_exec_sessions:

SELECT [status], wait_type, last_wait_type, plan_handle, blocking_session_id
  FROM sys.dm_exec_requests
  WHERE session_id = 67;

SELECT [quoted_identifier], [arithabort], [ansi_nulls], [date_format]
  FROM sys.dm_exec_sessions
  WHERE session_id = 67;

Now, this might immediately tell you where to look for the problem : you may have a wait_type that indicates there is an I/O problem, or you might see that the session is being blocked - in which case you can investigate what that session (blocking_session_id) is doing.

If neither of those pieces of information are useful, let's move on to the plan. A common issue with plans is that they can be different depending on various SET options, and this can result in quite different performance for two different users or an application and SSMS.

The plan handle is going to be a big 0x00... varbinary value in the results above. You can copy and paste that into this query:

SELECT
  [quoted_identifier] = MAX(CASE WHEN
      CASE WHEN attribute = 'set_options' THEN
        64 & CONVERT(INT, a.value) END = 64 THEN 1 ELSE 0 END),
  [arithabort] = MAX(CASE WHEN
      CASE WHEN attribute = 'set_options' THEN
        4096 & CONVERT(INT, a.value) END = 4096 THEN 1 ELSE 0 END),
  [ansi_nulls] = MAX(CASE WHEN
      CASE WHEN attribute = 'set_options' THEN
        32 & CONVERT(INT, a.value) END = 32 THEN 1 ELSE 0 END),
  [date_format] = MAX(CASE WHEN attribute = 'date_format' THEN
        CONVERT(INT, a.value) END)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS a

WHERE p.plan_handle = 0x... --<-- *** put the plan handle here ***

AND a.attribute IN ('set_options', 'date_format');

And you can compare those results with the session values you pulled above, and also some of these properties are stored with the procedure itself:

SELECT OBJECTPROPERTY(OBJECT_ID('dbo.usp_query_procedure'), 'IsQuotedIdentOn');
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.usp_query_procedure'), 'ExecIsQuotedIdentOn');

You can also repeat that query for any other plans you find for the same procedure, to see if there are in fact cases where the procedure is executed with different settings:

SELECT
  p.plan_handle,
  [quoted_identifier] = MAX(CASE WHEN
      CASE WHEN attribute = 'set_options' THEN
        64 & CONVERT(INT, a.value) END = 64 THEN 1 ELSE 0 END),
  [arithabort] = MAX(CASE WHEN
      CASE WHEN attribute = 'set_options' THEN
        4096 & CONVERT(INT, a.value) END = 4096 THEN 1 ELSE 0 END),
  [ansi_nulls] = MAX(CASE WHEN
      CASE WHEN attribute = 'set_options' THEN
        32 & CONVERT(INT, a.value) END = 32 THEN 1 ELSE 0 END),
  [date_format] = MAX(CASE WHEN attribute = 'date_format' THEN
        CONVERT(INT, a.value) END)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS a
INNER JOIN sys.dm_exec_procedure_stats AS s
ON p.plan_handle = s.plan_handle
AND s.[object_id] = OBJECT_ID('dbo.usp_query_procedure')
AND a.attribute IN ('set_options', 'date_format')
GROUP BY p.plan_handle;

If you see more than one result, then check every column. Any columns where one plan shows a 1 and the other plan shows a 0 would be suspicious to me.

In the meantime you can read a few great posts about this and similar issues:

http://www.sommarskog.se/query-plan-mysteries.html

http://www.simple-talk.com/sql/t-sql-programming/dmvs-for-query-plan-metadata/

http://www.benjaminnevarez.com/2011/09/parameter-sniffing-and-plan-reuse-affecting-set-options/

And yes, sp_WhoIsActive will probably give you some insight into what is going on as well.


MySQL 8 - user variables within expressions is deprecated [UDF calls with lot of parameters]

WITH
cte1 AS ( SELECT UDF1(0, a, b, c, d) AS Foo1,
                 UDF1(1, a, b, c, d) AS Foo2,
                 UDF1(2, a, b, c, d) AS Foo3,
                 UDF1(3, a, b, c, d) AS Foo4,
                 x, y, z
          FROM MyTable ),
cte2 AS ( SELECT Foo1, Foo2, Foo3, Foo4, x, y, z,
                 UDF2(Foo1, Foo2, Foo3, Foo4) AS Foo5,
                 UDF3(Foo1, Foo2, Foo3, Foo4) AS Foo6
          FROM cte1 ),
SELECT Foo1, Foo2, Foo3, Foo4, Foo5, Foo6,
       UDF4(Foo5, Foo6, x, y, z ) AS Foo8
FROM cte2;


Using top with ties with row_number() to get the row with the highest revision for the highest version for each filename and filetype.

select top 1 with ties
    filename
  , filetype
  , version
  , revision
from dmkr_asline.cmires_lbyi
order by row_number() over (
  partition by filename, filetype
  order by version desc, revision desc
  );

Alternative using a common table expression with row_number()

;with cte as (
  select
      filename
    , filetype
    , version
    , revision
    , rn = row_number() over (
        partition by filename, filetype
        order by version desc, revision desc
        )
  from dmkr_asline.cmires_lbyi
)
select
      filename
    , filetype
    , version
    , revision
from cte
where rn = 1
order by filename, filetype

cross apply() version:

select distinct
    t.filename
  , t.filetype
  , x.version
  , x.revision
from dmkr_asline.cmires_lbyi t
  cross apply (
    select top 1
        i.version
      , i.revision
    from dmkr_asline.cmires_lbyi as i
    where i.filename = t.filename
      and i.filetype = t.filetype
    order by i.version desc, i.revision desc
    ) as x

inner join version:

select
    t.filename
  , t.filetype
  , x.version
  , revision = max(t.revision)
from dmkr_asline.cmires_lbyi t
  inner join (
    select
        i.filename
      , i.filetype
      , version = max(i.version)
    from dmkr_asline.cmires_lbyi i
    group by
        i.filename
      , i.filetype
    ) as x
    on x.filename = t.filename
   and x.filetype = t.filetype
   and x.version  = t.version
group by
    t.filename
  , t.filetype
  , x.version


I finally figured that joining these tables would not bring a solution. I solved it doing this:

SELECT S.schaal, W.wnr, W.wnaam, W.salaris FROM Werknemer W, S_schaal S
WHERE W.salaris >= S.ondergrens && W.salaris <= S.bovengrens;


Two Inner join on the same field to get same data

Ah, you're right there.

It's easier if you use aliases. SQL Server needs some way to distinguish between the copies of the tables.

    select o.*, s1.Value, s2.Value
    from orderreceiptstep o
    inner join StringMapBase s1 on o.OrderReceiptStepStatus=s1.AttributeValue
    inner join StringMapBase s2 on o.OrderReceiptStepPrioriy=s2.AttributeValue
    where s1.LangID = 1065
    and s2.LangID = 1065

You might get better performance with a cte like this:

    with StringMaps as (select AttributeValue, Value from StringMapBase where LangID = 1065)

    select o.*, s1.Value, s2.Value
    from orderreceiptstep o
    inner join StringMaps s1 on inner join StringMapBase on o.OrderReceiptStepStatus=s1.AttributeValue
    inner join StringMaps s2 on o.OrderReceiptStepPrioriy=s2.AttributeValue


main manual doesn't help me a lot.

I hope you've read the manual, both entries:

ddl_partitioning (& the Limitations 5.10.2.3. Limitations)

create table

If yes, please mention specifically what you don't understand about it.

  partitioning natively... but what does it mean?

You don't have to create check constraints, trigger functions or triggers.

The SQL example above needs to be modified thusly:

e07=# CREATE TABLE public.audit_logs (
        log_type varchar(31) NOT NULL,
        id serial NOT NULL ,
        additional_data varchar(1024) NULL DEFAULT NULL::character varying,
        week int NOT NULL DEFAULT date_part('week', CURRENT_TIMESTAMP)
)
PARTITION BY list  (week) ;

--catch this error in the code:
--ERROR:  no partition of relation "audit_logs" found for row

  and how to create auto tables public.audit_logs_N, where N would be week number?

Depending on the requirements, create the partitions in advance or use a pgagent (or crontab) to create them one at a time in the prior week.

create table audit_logs_not_1 partition of audit_logs for values in (1);
...
create table audit_logs_not_53 partition of audit_logs for values in (53);

  Do i need to create a trigger? or what?

A trigger is only needed for Implementation Using Inheritance


What you are looking is simply not facilitated by MySQL as of writing this answer. Common Table Expressions (CTEs) are supported by several databases. @a_horse_with_no_name posted an answer naming those RDBMSs.

I have posted recursive Stored Procedures: Find highest level of a hierarchical field: with vs without CTEs. However, you emphasized not using any Stored Procedures. To do what you are asking does require Stored Procedures in MySQL.

Compromise Solution...

You will still need a Stored Procedure, but not using recursion. Using a simple loop, you can construct a self-join query and executed it dynamically. Given the depth of the tree you are looking for, here is such a Stored Procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `dianuj`.`GetTopParentGivenDepth` $$
CREATE PROCEDURE `dianuj`.`GetTopParentGivenDepth` (GivenDepth INT)
BEGIN

    DECLARE x1,x2 INT;

    SET x = 0;
    SET y = 1;
    SET @SQ = 'SELECT DISTINCT A0.id FROM prarent A0';
    WHILE y < GivenDepth DO
        SET @SQ = CONCAT(@SQ,' INNER JOIN prarent A',y,' ON A',x,'.id = A',y,'.parent_id');
        SET x = y;
        SET y = x + 1;
    END WHILE;
    SET @SQ = CONCAT(@SQ,' WHERE A0.parent_id = 0');
    SELECT @SQ;
    PREPARE stmt FROM @SQ;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END $$

DELIMITER ;

After loading your data into MySQL on my desktop, I ran the Stored Procedure with...

Depth 1

mysql> call GetTopParentGivenDepth(1);
+--------------------------------------------------------------+
| @SQLSTMT                                                     |
+--------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 WHERE A0.parent_id = 0 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Depth 2

mysql> call GetTopParentGivenDepth(2);
+------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                |
+------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id WHERE A0.parent_id = 0 |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+----+
| id |
+----+
|  3 |
|  2 |
+----+
2 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

Depth 3 .. 6

mysql> call GetTopParentGivenDepth(3);
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id INNER JOIN prarent A2 ON A1.id = A2.parent_id WHERE A0.parent_id = 0 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call GetTopParentGivenDepth(4);
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id INNER JOIN prarent A2 ON A1.id = A2.parent_id INNER JOIN prarent A3 ON A2.id = A3.parent_id WHERE A0.parent_id = 0 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call GetTopParentGivenDepth(5);
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id INNER JOIN prarent A2 ON A1.id = A2.parent_id INNER JOIN prarent A3 ON A2.id = A3.parent_id INNER JOIN prarent A4 ON A3.id = A4.parent_id WHERE A0.parent_id = 0 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> call GetTopParentGivenDepth(6);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id INNER JOIN prarent A2 ON A1.id = A2.parent_id INNER JOIN prarent A3 ON A2.id = A3.parent_id INNER JOIN prarent A4 ON A3.id = A4.parent_id INNER JOIN prarent A5 ON A4.id = A5.parent_id WHERE A0.parent_id = 0 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

Notice that Depth 6 has no return value. To find the maximum depth, you would have to iterate on it until no rows comes back.

CAVEAT

The worst case scenario would be to use the number of rows in the table as the depth. Such a tree would be nothing more than a degenerate linked list.
I cannot give you any guarantees on query performance, such on SQL construction and subsequent execution. Any kind of recursion would be the sole responsibility of the Query Parser.

Give it a Try !!!


Why MongoDB needs to disable majority read concern for PSA architecture?

Arbiter nodes don't "replicate" data...

The problem with your setup is that, that you can "lose" two of your three data-bearing nodes and your setup has still "majority" in votes. Meaning that replica set still keeps "serving" clients, but all data is only at the one node without replication.

Then comes that "majority RC", with that setup, even you lose one of those data-bearing nodes, the whole replica set stops accepting data (basically) because of missing ack messages. If three nodes of five are data-bearing nodes, that three IS majority of nodes and "majority RC" required that all those three nodes answer "ack, data is safely saved to the storage".


select group by with multiple AND conditions on the same columns

You can use INTERSECT which is available in both HSQLDB and SQL Server

SELECT SERVICE_SID
FROM SQLTable
WHERE attribute_sid = 5 AND Value='VAL1'
INTERSECT
SELECT SERVICE_SID
FROM SQLTable
WHERE attribute_sid = 4 AND Value='VAL8';

You can then join the result of this query with your table and group by SERVICE_SID


Error: Invalid object for global temporary table

There are 2 types of temporary tables in SQL Server. Local and Global.
Differenence between these 2 are:-

1.
Local temporary table starts with #,
Global temporary table starts with ##

2.
Local temporary table exists for a session only.
Global temporary table alwyas exists in tempdb.

3.
Because life of local temporary table is within a session so only the session can query on that.
Multiple session can query Global temporary table, but data is limited to a session.

Here in you post you are using table to insert value (Not global or local temporary table).

Try bellow query.

CREATE table ##qaw_temp
  (id      char(12),
   i_test    int)

print 'Before Insert'
  insert into ##qaw_temp
  values('0411',
    3077)

print 'After Insert'

Thanks


I'm performing a post-mortem on an Oracle database that's crashed its' host. Where should I begin?

The first thing to look at would be the database's alert.log file.  If the database was having problems at the times that were identified, you'll get error messages in the alert.log and, most likely, you'll get pointers to detailed trace files.

Are you licensed to use the AWR?  Is statspack installed?  If none of the database processes was crashing, it's possible that the server was unresponsive because the application was issuing runaway SQL and Oracle was crushing the server.  An AWR/ statspack report from the time in question will show whether Oracle was actually doing anything at the time or not.  If you have any Windows monitoring information from the time in question, that would also be useful.  If the Windows performance monitors show a pile of activity and Oracle shows none, for example, that would be very interesting.


Using character set en_us.364 in Informix database DB_LOCALE

For better or worse (and mostly worse), although some of the files necessary to support en_us.364 are present, the file needed in lc11/en_us is not present.  Thus, you can have de_de.364 or es_es.364 or fr_fr.364 or pl_pl.364 but you can't have en_us.364.

You have a few options at this point.

One is to try downloading the ILS — International Language Supplement.  This has many extra locales in it; it might have en_us.364 (but no promises).
Another is to try copying an existing locale (maybe gls/lc11/fr_fr/016c.lco or gls/lc11/de_de/016c.lco to gls/lc11/en_us/016c.lco. You'd probably want to edit the LANGUAGE and TERRITORY lines.  However, be aware that the French and German locales are different; it will matter which you choose.
Report a problem to IBM Informix Tech Support, requesting the file for gls/lc11/en_us/016c.lco.

I'm not sure which is best.  If the relevant file is in the ILS, then that's simple.  Copying a locale file is mostly straight-forward, but you need to consider what will happen if that's not available.


Query execution was interrupted, max_statement_time exceeded

I was experiencing the same issue.
Possible to set the global max_execution_time, but not the session max_execution_time:

SET global max_execution_time = 60000;
SELECT @@global.max_execution_time -- 60000

SET session max_execution_time=300000;
SELECT @@session.max_execution_time -- 15000

I used a hint as workaround:

select /*+ MAX_EXECUTION_TIME(300000) */ * from table
where ...

Source: https://stackoverflow.com/questions/415905/how-to-set-a-maximum-execution-time-for-a-mysql-query


Partial rollback in function in postgres 9.6

Something like this:

DECLARE
  _exception_message text;
  _exception_hint text;
BEGIN
    INSERT INTO table_a
    BEGIN
        INSERT INTO table_b
        RAISE EXCEPTION
        UPDATE table_b
        RETURN;
    EXCEPTION
      WHEN OTHERS THEN
            GET STACKED DIAGNOSTICS _exception_message = MESSAGE_TEXT, _exception_hint = PG_EXCEPTION_HINT;
            RETURN;
    END;
END

Reference: https://stackoverflow.com/questions/42638907/savepoint-in-postgresql-function


Filtered Stats and Incremental Stats on Partitioned table

Incremental stats and filtered stats serve completely different purposes. Incremental stats are not used by the query optimizer. They are a feature designed to make maintenance easier for large tables. They can only indirectly help with query performance because they can make it more practical to keep statistics up to date on large tables.

Filtered statistics are used by the query optimizer (if you write your queries carefully enough). They technically add to database maintenance but can significantly improve query performance in some scenarios.

In summary, you should not have to worry about filtered stats and incremental stats confusing the query optimizer. If you need specific help adding filtered stats to a table please post the table DDL and explain what you're trying to do along with what you've already tried.


Optimize SELECT statement for mysql database

FULLTEXT(name) and MATCH(name) AGAINST('+SAM0106' IN BOOLEAN MODE)

That will let you quickly whittle down the list of possible rows.  After that, the REGEXP (or whatever) can finish the filtering.

PRIMARY KEY(triggerid) is the other index that is useful for that SELECT.


MS SQL full datetime to only month and date

For best performance, use date/time arithmetic to truncate a datetime column to year and month before using it for grouping. There are at least two distinct approaches to that.

Approach 1

The first is a well-known date/time truncation method applicable to various units, not just months. It consists in finding the difference between your datetime value and another, fixed, datetime value in the same same units that you want to perform the truncation on and adding that difference to the fixed datetime value. Very often a 0 is used as an easily memorable fixed value (it represents the date of 1900-01-01 00:00):

DATEADD(unit, DATEDIFF(unit, 0, yourDateTimeValue), 0)

You want to do the truncation on months – so, in your case it would be

DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0)

The result will be the datetime value representing the midnight of the first of the same month as p._DATE. For 2016-11-16 14:32:58.547, for instance, you will get 2016-11-01 00:00:00.000.

Approach 2

Another method, primarily applicable to dates but easily adaptable for datetimes, is to take the date's day value (for instance, 16 for today), decrease it by one (15) and subtract that number of days from the date. As you can understand, that will also give you the first of the same month. In your case the expression would be:

 DATEADD(DAY, 1 - DAY(p._DATE), p._DATE)

That assumes p._DATE is the date type. If it is datetime, just cast the second occurrence to date:

 DATEADD(DAY, 1 - DAY(P._DATE), CAST(P._DATE AS date))

Usage

With both approaches the expression is supposed to be used as the grouping criterion. Using the first one as an example, your query could be rewritten like this:

SELECT
  AC_MONTH_ = DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0),
  PRE_DATE  = COUNT(CASE WHEN p.PC=266  THEN 1 END),
  PP_DATE   = COUNT(CASE WHEN p.PC=1325 THEN 1 END)
FROM
  dbo.true_all AS p
WHERE
  p._DATE > '01-Jun-2016'
GROUP BY
  DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0)
ORDER BY
  AC_MONTH_ DESC
;

Notes on formatting

Of course, if you want the date values in the output to look like Month-YYYY, you will need to format them accordingly. Conventionally, formatting should be done at the presentation level, not in the database, so that the application could take advantage of the client-side locale settings.

If you insist on doing it in Transact-SQL, though, you can certainly have some options. Just make sure you apply the formatting to the already transformed value (the expression) rather than the source column. Use of the DATENAME function is one of the options:

  AC_MONTH_ = DATENAME(MONTH, DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0))
            + '-'
            + DATENAME(YEAR,  DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0)),

In order to suppress repetition of the same long expression, you can use CROSS APPLY to calculate the month date once and reference it both in GROUP BY and in SELECT, like this:

SELECT
  AC_MONTH_ = DATENAME(MONTH, x.MONTH_DATE) + '-' + DATENAME(MONTH, x.MONTH_DATE),
  PRE_DATE  = COUNT(CASE WHEN p.PC=266  THEN 1 END),
  PP_DATE   = COUNT(CASE WHEN p.PC=1325 THEN 1 END)
FROM
  dbo.true_all AS p
  CROSS APPLY
  (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0)
  ) AS x (MONTH_DATE)
WHERE
  p._DATE > '01-Jun-2016'
GROUP BY
  x.MONTH_DATE
ORDER BY
  x.MONTH_DATE DESC
;

Note the ORDER BY x.MONTH_DATE DESC in this case. Now that the AC_MONTH_ column holds formatted values, it can no longer be used for consistent sorting. But the grouping criterion can, so the above query is using it in ORDER BY.


My first quesiton is, what is "very huge?"  Depending on the answer to that question, there may be other ways to optimize your migrations to reduce the time that they take.

Second question is, how often are you making schema changes on your live application?

If the online schema change tools don't work for you, you could try this from another angle.  Use master/master replication in active/passive mode to minimize your downtime.

The following assumes that you have db1 and db2 in an active/passive master/master configuration...

Perform the schema change on the db2 with binary logging disabled (SET SQL_LOG_BIN=0 in the session before you perform the ALTER TABLE)
Point your application at db2, thus making it the active server
Perform schema change on db1 with binary logging disabled
optionally, switch back to db1

For this to work, your schema migrations have to be backwards compatible, which is likely the case anyhow in most applications.  This isn't zero downtime, but it is close.  Realistically, most applications can tolerate a small amount of downtime periodically for maintenance.  True zero downtime is a very expensive luxury.

To potentially speed up your table alters, look into using Percona Server and the expand_fast_index_creation setting.

Also, another option for online schema changes is Facebook's OSC.


SQL Server Backup , Job with embeded SQL

In the sense, that script creates one backup and you are overwriting it each time it is executed....then you can most definitely just throw it into one step of a single job to run on your schedule. Just understand that you only have one full backup for each database, if you have requirement to go back in history to restore you cannot do it.

One tweak you can make to that script to get additional backups for each day is just throw in a date value.

A little bit more robust solution is offered by Ola's SQL Server Backup scripts that handles gracefully when databases are dropped and created. Just setting the parameter @database='USER_DATABASES' will handle this for you, along with the other required parameters of course.


Versioning: Is this technology used in DBMS other than spatial?

What you are describing seems really similar to MMVC- https://en.wikipedia.org/wiki/Multiversion_concurrency_control Where different clients can have a different "view" or "version" of the same database, and edits done withing a transaction cannot be seen by other sessions until they decide so by executing "COMMIT".

That concept is not exclusive to spatial databases (the wiki cites a 1981 paper as one of the first descriptions of it: https://en.wikipedia.org/wiki/Multiversion_concurrency_control#cite_note-3 ) but the concept has to be older than that. And while I see some special needs and implementations for extra dimensions, the essentials would be the same.

The implementation, of course, can vary widely from engine to engine. Git, even if not a database, it has to implement concurrency control in a particular way, and that will be completely different to how postgres does it. If then we go to distributed databases, we arrive to the wild west of keeping consistency thought heterogenous systems over the net. You want an implementation that is performant for the most useful cases, and all optimizations imply a trade off for operations considered not as important. One that is fast for small transaction sizes may be too expensive or unbearable for large edits. Also, different database engines interpret thing like consistency levels on its own way.

The good news is that with the rise of open-source database systems, you do not need to read large manuals anymore to understand what is happening internally- you can read the source code directly yourself and understand how those are written. For example, for Innodb:

https://www.percona.com/live/mysql-conference-2013/sessions/innodb-journey-core
https://github.com/mysql/mysql-server/blob/5.7/storage/innobase/row/row0undo.cc


In SSAS Parent-Child-Hierarchy how to use MembersWithDataCaption

My problem was I used a translation (german) and left the translation for MemberWithDataCaption empty. An other pitfall is a formatstring without a '*' does not work.

Answers:

Set MemberWithDataCaption of the Parent and any translations
In the formatstring the first star is replaced with the original
name, succeeding star will not be replaced.

Examples:

formatstring   original name   resulting caption
------------------------------------------------
constant       Charly          Charly
* (dm)         Charly          Charly (dm)
(*) *          Charly          (Charly) *

The conditions are:

Set MemberWithData=NonLeafDataVisible
Use a Formatstring containing a '*'
If you use translations, provide a translated formatstring


"peculiar reason"? Only that ntext is deprecated and damn difficult to work with?

It's a lossless conversion. If you are unsure, use the SSMS GUI designer to generate scripts for you.


Tablespace symlink error after changing data_directory location in PSQL 9.4

Postgres tablespaces are a symbolic link in the pg_tblspc directory. That link must appoint to a valid place for the tablespace files.

Please, make a link again, pointing to the write place.

You can see the tablespaces locations on psql client with the \db command or running the query bellow:

SELECT
  spcname AS "Name",
  pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
  pg_catalog.pg_tablespace_location(oid) AS "Location"
FROM pg_catalog.pg_tablespace
ORDER BY 1;

See the documentation for more details:

https://www.postgresql.org/docs/current/static/manage-ag-tablespaces.html


When a column is defined as IDENTITY you should not include it in the insert statement
SQL Server by it self will insert the value.

    INSERT INTO MYTABLE([CODE],[NAME],[DOB]) VALUES (123,'BOND','01-04-1971')

If you look at the insert statement ID column is missing but still will get a incremented value upon executing this statement.


SQL Server Partitioning - what to use for partition key?

The server spec questions should be directed to either Serverfault or DBA.SE.

For the partitioning question, I don't think you necessarily need to partition for this.

360m rows is a lot but it's not too unwieldy.

Do NOT under any circumstances try to partition based on the last digit of a field.  I'm not sure this would even work, but it's not SARGable which wouldn't be tenable.

If you only need to do a single row seek based on a numeric key, partitioning probably will not help.

If you do decide to pursue the partition route, bear in mind to be effective all your queries need to include your partition key(s) so the engine knows which partition to check.  Otherwise it will check them all and you actually hurt performance.


Import Windows MySQL dump on a Debian Linux server

The contents of the dump file you show appear to be UTF16 - this may be the result of the redirect operator on Windows (particularly if you're using PowerShell).

To avoid the Windows shell redirection operator interfering with the encoding/formatting of data emitted by the mysqldump program, use the --result-file=filename when dumping.

See the MySQL docs for this command line option at https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_result-file


Rebuilding a spatial index with minimum downtime

Concept of this solution is to prepare copy of the data with the same structure and swap with origial table using sp_rename. It is preety the same for other big changes in a table structure.
Let's create table and populate it with geometry data.
CREATE TABLE SpatialTable (id int IDENTITY(1,1) primary key, geometry_col geometry);
CREATE SPATIAL INDEX SIndx_SpatialTable_geometry_col1
   ON SpatialTable(geometry_col)
   WITH ( BOUNDING_BOX = ( 0, 0, 500, 200 ) );
GO

INSERT INTO SpatialTable (geometry_col)
SELECT TOP 1000000 geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0)
FROM sys.all_columns a,sys.all_columns b;
GO

Creating copy of the data and preparing appropriate indexes. Those operations don't harm original table and should be fast except spattial index creation.
SELECT * INTO SpatialTable2 FROM SpatialTable
GO

ALTER TABLE SpatialTable2 ADD CONSTRAINT PK_SpatialTable_Id PRIMARY KEY CLUSTERED (id);
GO

CREATE SPATIAL INDEX SIndx_SpatialTable_geometry_col1
   ON SpatialTable2(geometry_col)
   WITH ( BOUNDING_BOX = ( 0, 0, 500, 200 ) );
GO

Now let's simulate some insert on the base table.
INSERT INTO SpatialTable (geometry_col)
SELECT TOP 1000 geometry_col FROM SpatialTable

Below is the trick which should take only short time.
BEGIN TRANSACTION

    SET IDENTITY_INSERT dbo.SpatialTable2 ON

    INSERT INTO SpatialTable2 (id,geometry_col)
    SELECT id,geometry_col FROM SpatialTable sp WITH (TABLOCKX)
    WHERE NOT EXISTS (SELECT ID FROM SpatialTable2 sp2 where sp2.id=sp.id)

    SET IDENTITY_INSERT dbo.SpatialTable2 OFF

    EXEC sp_rename 'dbo.SpatialTable', 'SpatialTable_old';
    EXEC sp_rename 'dbo.SpatialTable2', 'SpatialTable';

COMMIT TRANSACTION

Now we have new table and new spatial index structures with the old data. Of course it is only proposition of steps and you should apply it to your requirements and data structure, especially UPDATE and DELETE operations if they occur in your case.
After this operation you can drop the old table to clean database or use it for any other purpose.


Turns out it was the foreach loop i was using to bind the parameters as seen in this issue on stackoverflow

for anyone who comes across a similar issue in the future the following will fail:

foreach($params AS $k => $v){
    $stmt->bindParam($k, $v);
}

as bindParam does not actually bind it when you call it, it just binds a link to $v so it will always be the last item it the loop. to overcome this, you need to pass the argument by reference as follows:

foreach($params AS $k => &$v){
    $stmt->bindParam($k, $v);
}

notice the & before $v in the foreach, cause it to work as expected.


Import data tier application in Managed instance

Try using SqlPackage instead:

sqlpackage.exe /Action:Import /tsn:MyServer /tdn:worldwide /tu:MyUserName /tp:MySecretPwd /sf:c:\sql\WideWorldImporters-Standard.bacpac

The command will import the bacpac file to Azure. The parameters used are the following:

/Action is used to indicate if we are going to import, export, publish, and extract data.

/tsn is used to define the Target Server Name.

/tdn is the name of the new database to be created.

/tu is the Target User. It is the Server Admin Login.

/tp is the Target Password. It is the password defined.

/sf is the source file.


The overall optimization process consists of two main steps:

Isolate long-running queries.
Identify the cause of long-running queries.

Step 1. Isolate Long-Running Queries

You can isolate long-running queries using SQL Profiler. For more information about how to identify the queries that take the longest to execute, see "Isolating a Slow-Running Query with SQL Profiler" in "How To: Use SQL Profiler" in the "How To" section of this guide.

Step 2. Identify the Cause of Long-Running Queries:

Several techniques can be used to identify the cause of long-running queries. The two most commonly used options are:

Using SET statements.
Using SQL Query Analyzer options.

Using SET Statements: Use such statements as SET SHOWPLAN_ALL, SET STATISTICS IO, SET STATISTICS TIME, and SET STATISTICS PROFILE. For more information about using these SET statements, see the SQL Server product documentation.

Using SQL Query Analyzer: SQL Query Analyzer displays query execution plans in text mode or graphical mode.

To use SQL Query Analyzer

Start SQL Query Analyzer, connect to the server, and select the database that you are working on.
Paste the query into the SQL Query Analyzer window.
If you are using SQL Profiler to trace queries, the query text can be copied from the trace window and used within SQL Query Analyzer.
On the Query menu, click Display Estimated Execution Plan. The estimated execution plan for the query is displayed. If the query window contains multiple queries, an execution plan is displayed for each query.
On the Query menu, click Show Execution Plan, and then run the query in the query window. Execution plans and query results now appear in separate panes of the window so you can view them together.
Place the mouse pointer over any icon displayed in the query execution plan. Details of the query step are displayed, including information about the execution and cost.

Before Optimize Query "write your query so you can read it... if you can't read it how can you possibly optimize it?" @Jacob H Thanks.

The complexity of the results can vary depending on the nature of the query. The following list identifies some common things to look for:

Red icons and warning messages: Look for icons that are color-coded red, and for warning messages. You might see a warning message such as "Warning: Statistics missing for the table." If the Physical operation in the query step details is in red, then it indicates that the query optimizer has chosen a less efficient query plan. The graphical execution plan suggests remedial action for improving performance. In the case of missing statistics you can right-click the icon, and click Manage Statistics to create the missing statistics.
Estimated cost: The estimated cost values indicate whether the query is I/O intensive or CPU intensive.
Table scan and clustered index scan icons: Look for table scan and clustered index scan icons, which indicate either that the table is small (not a problem), that the indexes are not properly designed, or, if you have indexes in place, that the optimizer has ignored the indexes. The Index Tuning Wizard can be used to identify the indexes needed. You need to ensure that you drop all hints before proceeding with the Index Tuning Wizard.
Queries with the highest cost: When a batch of queries is executed, a query plan is displayed for each query. The query cost is displayed for each query relative to the batch. Concentrating optimization effort on the highest relative cost query in a batch may yield the best improvements.
Some queries are inherently resource intensive — for example, queries that return large number of rows back to the caller, or queries that perform many calculations. In some cases, the only way to improve performance is to redesign the database or rewrite the query.


Multiple JOIN of 6 tables via 5 database instances

The issue is you're restricting on the instance field, which is resulting in the roles only appearing in one column or the other.

To get around this, remove the restrictions on db_instance and full outer join the roles from the second database to the roles in the first, like so:

SELECT W.GRANTED_ROLE "GRANTED_ROLE_DB1", V.GRANTED_ROLE "GRANTED_ROLE_DB2"
FROM SCHEMA.USR_ALL_USERS U
LEFT OUTER JOIN SYS.DBA_ROLE_PRIVS W
   ON (U.USERNAME = W.GRANTEE)
FULL OUTER JOIN SYS.DBA_ROLE_PRIVS@DB2_LINK V
   ON (U.USERNAME = V.GRANTEE AND V.GRANTED_ROLE = W.GRANTED_ROLE)
WHERE nvl(U.USERNAME, v.username) = 'USER'
ORDER BY nvl(W.GRANTED_ROLE, V.GRANTED_ROLE) ASC;

This SQLFiddle has a working example. Updated fiddle following comments from Andriy.


Doing something immediately with data in array field

I have to admit, I'm not sure what you're asking for. You've left out important items in your schema, like what are the "full records" you wish to pull?

If you have control over the schema, perhaps you would be better off altering it. It seems like your trying to store test scores, but store an array of children for each score received. Now it may be just me, but perhaps you might be better off splitting this into multiple tables.

stuff
  stuff-id
  content

children
  child-id
  child-fname
  child-lname
  child-seat
  child-wild
  gum-chewer-bit

scores
  child-id
  stuff-id
  score

In this setup you can construct a query that matches records on the stuff-id and uses a join to pull data from the other tables.

If you DON'T have access to the schema, it may still be possible using IN (). Without having a clearer understanding of the schema my suggestion is to use join queries while passing that array to IN. That being said, I'm not sure it could work that way as I'm not sure you can construct the join in this fashion (hence the multiple tables and not using an array field).

Now with a stored procedure, the answer is a definite "yes, you can".

You would loop through each stuff-id, split out the array values (or pass them directly/indirectly with IN), loop through the child records, and flatten it out into a single record per child. With stored procedures you have quite a bit of control over each record you "suspend" (old Firebird dude here) with 'Returns Sets'.

To sort by score, you would achieve this by sorting the output of your stored procedure.

Don't give up either. Postgresql is really amazing in what you can do, and I recommend camping in their online manuals.


Is this amount of disk IO expected with MySQL?

MySQL Disk USage
SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(
B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",CONCAT(LPAD(REPLACE(FORMAT(B.TSize/
POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,
SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

This will tell you exactly how much diskspace (in GB) MySQL is actually using for tables.
Please see my other post CPU usage on RDS instance monotonically increasing with no change to query volume on additional Storage Engine diskspace queries.
You also need to do this
cd /var/lib/mysql
df -h .
ls -l

Take note of ibdata1, ib_logfile0, ib_logfile1, and other files. You will also see how much disk is free for the disk holding /var/lib/mysql
Please run ls -l /var/log/mysqld.log to see if the log is too big.
MySQL Memory Usage
You have
innodb_buffer_pool_size=64M
query_cache_size=64M
key_buffer_size=64M

That's 192MB. Since you have a lot of other defaults, you only have about 100 or 131 max connections. I recommend downloading MySQL Administrator for Free and watching memory usage in other aspects.
Epilogue
Please look over my Amazon RDS posts for other ideas

Should I increase max_connections in AWS RDS t1-micro for MySQL?
When should I think about upgrading our RDS MySQL instance based on memory usage?


Remove duplicates in sequence based on keys

This works on consecutive Start_date for each partition (on Member):

SELECT `Member`, `Group`, `Status`, `Start_date`, `End Date`
FROM (
    SELECT @row := CASE WHEN @status=Status AND @member=Member AND @group = `Group`
        THEN @row + 1 ELSE 1 END as row
        , @member:=Member as Member
        , @group:=`Group` as `Group`
        , @status:=Status as Status
        , `Start_date`, `End Date`
    FROM data d
        , (SELECT @row := 0, @status := '', @member := 0, @group := 0) v
    ORDER BY Member, `Group`, Start_date
) as n
WHERE row = 1
ORDER BY Member, Start_date
;

Sample SQL Fiddle.

The behavior of this query is similar to the ROW_NUMBER()  available in Oracle (>= 10g), PostgreSQL (>= 8.4) and SQL Server (>= 2012).

Notes on Partitions:

This query and SQL Fiddle partition by Members and order Status by Members and Start_date.
Partition can be remove by removing @member=Member from CASE
Partition can be added or set by both Members and Group like this (SQL Fiddle):

Add a test on @group = 'group' to the CASE
Add @group := 'Group' as 'Group' in the inner SELECT [n]
Add @group := 0 in the variable SELECT [v]
Add Group to ORDER BY clauses

In term of performance, an index matching the order and columns used in the ORDER BY and CASE is very likely needed.

Output:

Member  | Group     | Status    | Start_date                | End Date
1       | 2         | Active    | April, 12 2011 03:19:00   | (null)
1       | 2         | Inactive  | April, 12 2011 05:19:00   | (null)
1       | 2         | Pending   | April, 16 2011 13:23:00   | (null)
1       | 2         | Active    | April, 16 2011 13:25:00   | (null)
1       | 2         | Pending   | April, 16 2011 13:27:00   | (null)
11      | 22        | Inactive  | April, 16 2011 13:27:00   | (null)


Upgrading MariaDB Galera cluster from 5.5 to 10.0 - rolling upgrade possible?

I have been asking the same question for several months and have also been unable to find a clear answer on the internet. Perhaps that means that I'm more concerned than I should be; perhaps I should not be doing database server maintenance...

So I was forced to do this yesterday, and everything worked well. This is how I went about it:

I upgraded the servers one at a time, using a round-robin technique. So these steps would be repeated for each server in the cluster, one at a time.

Fail the node out

Shut down MySQL. (service mysql stop on CentOS)

Uninstall Galera server

yum remove MariaDB-Galera-server on CentOS. For me an uninstall and reinstall is necessary because the package manager gave me fits on upgrade. Your mileage may vary.

Update the repository if necessary

You will need to install or configure your system to point to the repository with the later version of Galera. See the Galera repository configuration page

Install Galera server 10.0

yum install MariaDB-Galera-server on CentOS. After completing the installation, I also upgraded all other packages on the system to help ensure, the necessary client libraries and such were up to date.

Also, if your package manager renames central configuration files when software is uninstalled, ensure that your undo this after installing Galera again. Otherwise, for me, /etc/my.cnf.d/server.cnf will not have the correct information.

Startup the new version of Galera

This seemed to be the most dangerous step for me, but goes well. The server logs will likely include a few lines about columns in some internal tables. Don't worry

Upgrade the internal schemas

This is the tricky part. You want to make sure that the updates applied to the internal tables are not replicated to the other nodes in the cluster. There are two wsrep settings to help with this. One is called wsrep_desync, which is new to version 10. It does something I don't quite understand that will help prevent this node from slowing down the rest of the cluster while the upgrade is underway. The other is wsrep_on which is used to configure if the local node should replicate changes to the other nodes in the cluster.

Adjust the settings by logging in to the database locally as root.

set global wsrep_desync = on;

set global wsrep_on = off;

Now you can drop back the command line and safely upgrade the internal schemas.

mysql_upgrade --skip-write-binlog -u root -p

I use the skip-write-binlog option to further assist in keeping from replicating the updates.

(Optionally) rebuild the database

I did this because of failed past upgrades and such. It doesn't take too much longer and helps ensure that all your data is consistent and in the new formats.

mysqlcheck --optimize --all-databases -u root -p

Bring the node back into the cluster

Just reverse the wsrep_desync and wsrep_on settings and the node will resynchronize with the cluster. Log back into the MySQL command line client and set those global variables back.

set global wsrep_desync = off;

set global wsrep_on = on;


How to Store a Many-to-Many Relationship Between Fact Tables in a Data Warehouse

I just stumbled on your thread accidentally when searching for some stuff. I just read about this in the Kimball DWH ETL toolkit and I might share it with you. Your mapping table is what they refer to as a 'bridge table'. The extra element that they add is a 'group entity', which avoid a many-to-many join. You might want to check that out. See page 196 of that book.
I quote: 'To avoid a many-to-many join between the bridge table and the fact
table, one must create a group entity related to the multivalued dimension.
In the health care example, since the multivalued dimension is diagnosis,
the group entity is diagnosisgroup.'
Herewith the layout. Maybe it is of value to you. I thought it was quite interesting, although I haven't tried it out yet. I have used the similar concept like the bridge/mapping table within the context of object oriented software design though.


Optimising some SQL to get all rows corresponding to the minimum value of a column

The documentation provides a couple ways to get what you want, which will perform well.

SELECT TableA.col1, TableA.col2
FROM TableA
INNER JOIN (
    SELECT
    MIN(col3) AS col3
    FROM TableA
    WHERE col4 = 0
    AND col5 = 1
) lowest_col3_TableA
ON lowest_col3_TableA.col3 = TableA.col3
WHERE TableA.col4=0
AND TableA.col5=1

Or

SELECT TableA.col1, TableA.col2
FROM TableA
LEFT OUTER JOIN TableA lower_col3_TableA
ON lower_col3_TableA.col4 = 0
AND lower_col3_TableA.col5 = 1
AND lower_col3_TableA.col3 < TableA.col3
WHERE TableA.col4=0
AND TableA.col5=1
AND lower_col3_TableA.col3 IS NULL

For this second query, the last line of AND lower_col3_TableA.col3 IS NULL doesn't necessarily need to be col3, but DOES need to be a column that is defined with NOT NULL. Usually the leftmost part of the primary key is best.


Repeatable read: the transaction may not be serializable with respect to other transactions

This is related to the concept of Isolation Levels. See https://en.wikipedia.org/wiki/Isolation_(database_systems) for details.
Isolation levels are defined by the presence (or absence) of one or more of the following phenomena:

Dirty Reads

This means the ability to see uncommitted data: a change made by another transaction, but not committed yet. That is seldom seen in real life - very few database systems allow that. It is risky because it may return inconsistent data. For example a bank transfer: the origin account has been debited, but the destination account has not yet been credited. If your application reads the accounts in between the two updates, it looks like the money has disappeared!

Non-repeatable Reads

This is what the sentence you quote is talking about. It means that you can see changes that have been committed by other transactions. It implies that, should you repeat the same query in a transaction, you may get different results, because someone may have changed the data since you read it previously. It is a problem for any application that involves checking a stock before confirming an order: the application finds that there are 5 gizmos in stock, then places an order for 4 of them - but in the meantime someone else has ordered them.
This phenomenon can be prevented by placing read locks on everything you read in a transaction. That prevents anyone else from updating the data while you are reading it. It is a bit like if you looked at a pair of shoes in a shop window, and that simple fact makes the shoes unsalable to anyone else.

Phantom Reads

Assume you do place read locks on everything you read. That would still not guarantee that you get the same answer when you repeat a read: everything you read first is safe: nobody can update or delete any of it. However simple read locks do not prevent anyone from inserting a new row. This can lead to inconsistencies in reporting. Say you print out a sales report with the detailed sales. Then you want to print the total and you do that by doing another query to compute the total. That total may not match the details: that is because while you were reading the individual lines, someone has inserted a new sale and committed it.
Preventing phantoms is much harder: placing read locks on the rows read in order to prevent updates is not possible: there is nothing to lock since the new rows do not exist yet. Typical techniques are to place locks on the entire table - which is really bad for concurrency and effectively single-threads inserts. Better techniques involve placing locks on index nodes, since an index node represents a range or rows, effectively preventing any insert in a range of rows read by some application.
Those three phenomenons are used to describe the isolation levels:
Read uncommitted is the lowest isolation level: essentially no consistency at all (dirty reads are allowed).
Read committed prevents dirty reads only. Reads are not repeatable: repeating the same read can have different results
Repeatable reads prevents non-repeatable reads, but does not prevent phantom reads. A read will return the same rows as the previous execution - but it may return more rows: those rows did not exist yet on the first read.
Serializable prevents phantom reads. It is the highest level of consistency. It guarantees that all reads done in a transactions always deliver the exact same result. It is called serializable because the effect of the execution of a series of concurrent transactions is identical to a serial execution of those transactions in one specific order.


As of PostgreSQL 9.4: Different to the documentation of CREATE VIEW, the documentation of CREATE MATERIALIZED VIEW does NOT mention the REPLACE keyword. There seems to be no shortcut aside from dropping all dependent objects and rebuilding each one.

When you do so, I can only recommend two small things:

Use DROP MATERIALIZED VIEW blabla CASCADE to get a list of all dependent objects
Do the drop and recreation of all dependent object in one transaction.


Note that this download SQL Server® 2012 SP1 Data Mining is not a SQL Server database service. Its just a tool/add-on to work with Office and connect to a database.

You must install SQL Server to connect to the database.

Microsoft offers Express Edition to use locally with no licence need.


Does WITH SCHEMABINDING on a multi-statement TVF improve cardinality estimates?

In my tests, no, adding WITH SCHEMABINDING does not improve cardinality estimates. I created a simple table:

CREATE TABLE dbo.myobjects(id INT PRIMARY KEY);

INSERT dbo.myobjects SELECT [object_id] FROM sys.all_objects;

Then two functions:

CREATE FUNCTION dbo.noschemabinding(@UserID INT)
RETURNS @x TABLE (id INT)
AS
BEGIN
  INSERT @x SELECT id FROM dbo.myobjects;

  RETURN;
END
GO

CREATE FUNCTION dbo.withschemabinding(@UserID INT)
RETURNS @x TABLE (id INT)
WITH SCHEMABINDING
AS
BEGIN
  INSERT @x SELECT id FROM dbo.myobjects;

  RETURN;
END
GO

Comparing the actual plans, both show estimated rows = 1, actual rows = 2112 (this latter number may differ on your system depending on version/SP etc).

Comparing the speed:

SET NOCOUNT ON;
GO
SELECT SYSDATETIME();
GO
SELECT id INTO #x FROM dbo.noschemabinding(1);
DROP TABLE #x;
GO 1000
GO
SELECT SYSDATETIME();
GO
SELECT id INTO #x FROM dbo.withschemabinding(1);
DROP TABLE #x;
GO 1000
SELECT SYSDATETIME();

Results:

                    run 1               run 2
----------------    ------------------  ------------------
No schemabinding    14632 milliseconds  14079 milliseconds
Schemabinding       14251 milliseconds  13979 milliseconds

So, does it matter much? Nope.

SCHEMABINDING in this case is used for a more important goal: underlying schema stability. You will probably have much better optimization opportunities if you pursue converting your function to an inline TVF than to chase down obscure plan-affecting differences in a multi-statement TVF.


Is the Advanced Security Pack required for windows authenticaion?

Yes, you will need Oracle Advanced Security to be installed. See http://docs.oracle.com/cd/E25054_01/network.1111/e10746/asointro.htm#i1008433

However a license for Oracle Advanced Security is no longer needed with 11g Release 2, but you will apparently need a license for "Oracle Identity Management Directory Services Plus".

See PDF page 10 of https://docs.oracle.com/cd/E11882_01/license.112/e47877.pdf

  Enterprise User Security is an EE feature for directory-based
  management of database users. It does require a corresponding Oracle
  Identity Management Directory Services Plus to be licensed. Usage of
  Enterprise User Security with Oracle Database strong authentication
  (PKI, Kerberos) no longer requires Oracle Advanced Security to be
  licensed.


MySQL 5.6.17 running out of memory when opening table with many partitions

I found out that due to this bug, MySQL 5.6.17 uses 6kb memory per open subpartition. This would result in about 500 GB of memory usage for 100 connections each having 100 tables with 800 partitions each, with 8 subpartitions each.


How to find the table name from the pg_subscription_rel.srrelid (postgres logical replication)

You can join it to pg_class
SELECT s.subname AS subscription_name,
       c.relnamespace::regnamespace::text as table_schema,
       c.relname as table_name,
       rel.srsublsn,
       case rel.srsubstate
         when 'i' then 'initialized'
         when 'd' then 'copying'
         when 's' then 'synchronized'
         when 'r' then 'ready'
       end as state
FROM pg_catalog.pg_subscription s
  JOIN pg_catalog.pg_subscription_rel rel ON rel.srsubid = s.oid
  JOIN pg_catalog.pg_class c on c.oid = rel.srrelid


With TCP, you can only have one process listening on each IP/port pair.

This applies to SQL Server.  The first instance you start binds itself to listen on your_ip:1433, then when you start the second, the OS will not allocate the socket to the the new instance of SQL Server, because it has already allocated that listener socket to your first SQL Server instance.  If you look in your error logs, you will see a "socket already in use" error of some sort when you start up the second instance.

If you have multiple physical or logical NICs you should be able to bind the SQL Server instances to the same port on different IP addresses.


Migrating SQL Server 2008 R2 to new server

This will work

Some gotchas:

@@SERVERNAME will be wrong. Fix as per this MSDN sp_addserver example
SQL Agent jobs (in msdb) may be "owned" by the old server. You'll have to script and recreate. However, this doesn't apply to SQL Server 2012 from what I can see: it used to for SQL Server 2000 which is the last time I did it....

Note that using DNS to "hide" the physical server is a useful trick. See these

https://stackoverflow.com/a/1335228/27535
https://stackoverflow.com/a/5237769/27535


Character Strings related : string_units db cfg changed from SYSTEM to CODEUNITS32

ALTER TABLE <schema>.<tabname> ALTER COLUMN <colname> SET DATA TYPE VARCHAR(20 CODEUNITS32);

The alter column set data type with CODEUNITS32 clause did the job.
If the string_units database configuration parameter is set to CODEUNITS32,
any new table without specifying this clause in column definition still works.

We have to perform the above alter only to the columns of existing table which was defined when string_units was set to default SYSTEM.

Hope it helps to other users.


This is a very tough question to answer simply because you are going beyond InnoDB current limits.

Your question is not by any means unique. This has been addressed here before

By Me, Aug 14, 2012 : error 1118: row size too large. utf8 innodb
By Bill Karwin, Oct 06, 2011 : InnoDB create table error: "Row size too large"

I would also look into the character set you are currently using.

Bill Karwin said it best in his last paragraph

  I also have to comment that I've never seen a well-designed table exceed the row size limit. It's a strong "code smell" that you're violating the repeating groups condition of First Normal Form.

You are going to have to define a better design. No business reason can ever justify it. Why?

Back on July 20, 2011, I answered this question: Too many columns in MySQL

I personally eyewitnessed this

  In my earlier days as a developer, I worked at a company back in 1995 where DB2 was the main RDBMS. The company had a single table that had 270 columns, dozens of indexes, and had performance issues retrieving data. They contacted IBM and had consultants look over the architecture of their system, including this one monolithic table. The company was told "If you do not normalize this table in the next 2 years, DB2 will fail on queries doing Stage2 Processing (any queries requiring sorting on non-indexed columns)." This was told to a multi-trillion dollar company, to normalize a 270 column table. How much more so a 2000 column table.

A table with 300 TEXT columns is asking for same kind of trouble.

SUMMARY : Bill Karwin said it before and I agree: REDESIGN THE TABLE. That will circvumvent the row length issue for sure.


How to sync two databases (alternative to mysql replication)?

mysqldbcompare, part of mysql utils, can check differences in structure and data among two servers. However, if you are making updates to one of the servers at the same time, this may not be enough.

pt-table-checksum and pt-table-sync, from Percona Toolkit, can respectively check and synchronise differences in data between a master and a slave in real time. It doesn't matter if both servers have different data, but it requires having the same tables with the same definition.

So probably you can use a combination of both. Both MySQL utils and Percona Toolkit are open source, freely downloadable and well established utilities among MySQL DBAs.


This means you need to tune your autovacuum settings.

Auto vacuum is not the enemy

The default settings for autovacuum will trigger when the 20% rows are dead. This is not a good choice for a large OLTP system. So tune it on Number basis not the percentage.

Like, set auto vacuum when the dead tuple count reaches 10000.

autovacuum_vacuum_scale_factor = 0
autovacuum_vacuum_threshold = 10000
autovacuum_max_workers = 4

OR

you can set this threshold on a particular table.

ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0);
ALTER TABLE t SET (autovacuum_vacuum_threshold = 10000);

In some worst cases auto vaccum will not work. Read why?
The above parameters for Autovacuum for Ad-Hoc vacuum set something like,

vacuum_cost_delay = 20ms
vacuum_cost_limit = 200

vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20

maintenance_work_mem  is also a contributing for this