Idiomatic way to implement UPSERT in PostgreSQL I've read about different UPSERT implementations in PostgreSQL, but all of these solutions are relatively old or relatively exotic (using writeable CTE, for example).

And I'm just not a psql expert at all to find out immediately, whether these solutions are old because they are well recommended or they are (well, almost all of them are) just toy examples not appropriate to production use.

What is the most thread-safe way to implement UPSERT in PostgreSQL?

UPDATE (2015-08-20):

There is now an official implementation for handling upserts through the use of ON CONFLICT DO UPDATE (official documentation). At the time of this writing, this feature currently resides in PostgreSQL 9.5 Alpha 2, which is available for download here: Postgres source directories.

Here is an example, assuming item_id is your Primary Key:

INSERT INTO my_table
    (item_id, price)
VALUES
    (123456, 10.99)
ON
    CONFLICT (item_id)
DO UPDATE SET
    price = EXCLUDED.price

Original Post...

Here is an implementation I arrived upon when desiring to gain visibility into whether an insert or update occurred.

The definition of upsert_data is to consolidate the values into a single resource, rather than having to specify the price and item_id twice: Once for the update, again for the insert.

WITH upsert_data AS (
    SELECT
    '19.99'::numeric(10,2) AS price,
    'abcdefg'::character varying AS item_id
),
update_outcome AS (
    UPDATE pricing_tbl
    SET price = upsert_data.price
    FROM upsert_data
    WHERE pricing_tbl.item_id = upsert_data.item_id
    RETURNING 'update'::text AS action, item_id
),
insert_outcome AS (
    INSERT INTO
        pricing_tbl
    (price, item_id)
    SELECT
        upsert_data.price AS price,
        upsert_data.item_id AS item_id
    FROM upsert_data
    WHERE NOT EXISTS (SELECT item_id FROM update_outcome LIMIT 1)
    RETURNING 'insert'::text AS action, item_id
)
SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome

If you don't like the use of upsert_data, here is an alternate implementation:

WITH update_outcome AS (
    UPDATE pricing_tbl
    SET price = '19.99'
    WHERE pricing_tbl.item_id = 'abcdefg'
    RETURNING 'update'::text AS action, item_id
),
insert_outcome AS (
    INSERT INTO
        pricing_tbl
    (price, item_id)
    SELECT
        '19.99' AS price,
        'abcdefg' AS item_id
    WHERE NOT EXISTS (SELECT item_id FROM update_outcome LIMIT 1)
    RETURNING 'insert'::text AS action, item_id
)
SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome


Dynamic SQL is not allowed error for creating function in MySQL According to this question and answer of Angelo I create a MySQL function as below:

DELIMITER $$
CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT, reqdTable VARCHAR(50)) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
            DECLARE _id INT;
            DECLARE _parent INT;
            DECLARE _next INT;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
            SET _parent = @id;
            SET _id = -1;
            SET @table_name = reqdTable;
            IF @id IS NULL THEN
                RETURN NULL;
            END IF;
            LOOP

                SET @sql_text = concat('SELECT MIN(id) INTO @id FROM ', @table_name,' WHERE parent_id = _parent AND id > _id;');
                PREPARE stmt FROM @sql_text;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;

                IF @id IS NOT NULL OR _parent = @start_with THEN
                  SET @level = @level + 1;
                  RETURN @id;
                END IF;
                SET @level := @level - 1;

                SET @sql_text = concat('SELECT id, parent_id INTO _id, _parent FROM ', @table_name,' WHERE id = _parent;');
                PREPARE stmt FROM @sql_text;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;

            END LOOP;
END

But It has following Error:

 #1336 - Dynamic SQL is not allowed in stored function or trigger

What should I do?

You want to use a stored procedure instead of a stored function

You can rewrite it like this

DELIMITER $$
CREATE PROCEDURE hierarchy_connect_by_parent_eq_prior_id(IN value INT, IN reqdTable VARCHAR(50), OUT result INT)
BEGIN
    DECLARE _id INT;
    DECLARE _parent INT;
    DECLARE _next INT;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
    SET _parent = @id;
    SET _id = -1;
    SET @table_name = reqdTable;
    IF @id IS NULL THEN
        SET result =  NULL;
    END IF;
    LOOP

        SET @sql_text = concat('SELECT MIN(id) INTO @id FROM ', @table_name,' WHERE parent_id = _parent AND id > _id;');
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        IF @id IS NOT NULL OR _parent = @start_with THEN
          SET @level = @level + 1;
          SET result = @id;
        END IF;
        SET @level := @level - 1;

        SET @sql_text = concat('SELECT id, parent_id INTO _id, _parent FROM ', @table_name,' WHERE id = _parent;');
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;
END$$
DELIMITER ;

Then use it

CALL hierarchy_connect_by_parent_eq_prior_id(1, 'table1', @result);
SELECT @result;


How to delete all the tab and enter in a table? I have a big table with several \n (enter) and \t (tab) characters scattered in my data

It looks something like the following:

col1   col2   col3
data   \ndata data
\ndata data   \tdata

I need to delete all the \n and \t characters from all the columns.

Is there a simple way to do this ?

update:
when I record the table into tsv, it will be as

data
\ndata data
\ndata data \tdata

if I simply delte all \n and \t, the tsv still look the same except the \n

data
data data
data data data

If you want to get rid of a \n at the start of a column you can run something like this:

UPDATE your_table SET col1 = substring(col1, -1) WHERE col1 like '\n%';

You can do something similar for other columns or changing \n for \t.

If you want to delete all \t and \n, anywhere on the string, you can use:

UPDATE your_table SET col1 = replace(replace(col1, '\t', ''), '\n', '');


most effective architecture to apply mulitple functions I have a table that is the only source for a second table.  Currently the second table is created using a CTAS (create table as select) statement that pulls in all the columns from the first table and includes a dozen more columns that are functions that we have written.  These functions take a column or two from the source table and transform them into new data elements.

In some cases, the columns that are used as parameters for the functions are sparsely populated.  The functions are compiled natively and utilize the result_cache.  The first thing that happens in each function is a null check on the input parameters and a return of NULL.

I have a few questions:

I have full control over the architecture of the source table (as I create it in an earlier process).  Is there anything I can do with the source tables (index, partitions, etc.) that will help the CTAS run more efficiently?
Should I be using a CTAS at all or would an UPDATE be more efficicent (my gut here says no way, but I'd like some validation)
Is there something else I can do to the functions to make them more efficient?
Is there anything else I should be thinking about?

I have full control over the architecture of the source table (as I create it in an earlier process). Is there anything I can do with the source tables (index, partitions, etc.) that will help the CTAS run more efficiently?

Optimizing a CTAS is generally the same as optimizing the SELECT part of the CTAS. Run an explain on it to see what it's doing. The CREATE part is just sequentially writing the output of the SELECT so it wouldn't be the cause of slowness.

  Should I be using a CTAS at all or would an UPDATE be more efficicent (my gut here says no way, but I'd like some validation)

It depends on the size of the UPDATE. If the target table is indexed and you have few updates then the amount of work to do the UPDATE would be less. Specifics matter here as there's not general rule for which is faster. For an extreme example, if you have 100M rows and you're only changing one row via and indexed UPDATE, the UPDATE would clearly be faster.

In the end what counts is the total amount of I/O operations that will happen (both reads and writes). You can either analyze it by studying the EXPLAIN plans or test both approaches to get stats on which takes longer.

  Is there something else I can do to the functions to make them more efficient?

Well you could remove them entirely. Switching between SQL and PL/SQL is fairly quick and isn't really much of a bottle next if it's in the SELECT columns. If it's part of the WHERE clause and you're filtering a column on a the result of a PL/SQL function it can be very slow because they're usually not indexed (though Oracle does allow for indexes on deterministic functions).

If it makes logical sense, you can mark the function(s) as DETERMINISTIC. This tells Oracle that the result will always be the same for the same inputs (like a mathematical function, e.g. x^2). This means Oracle can cache the results between function calls. That may speed things up.

You can also try wrapping the function calls in a CASE/WHEN block to only call the function when it's not null. Again I don't think it makes that much difference as SQL -> PL/SQL context switches are pretty fast but it's something to try out:

CASE
    WHEN my_column IS NULL THEN NULL
    ELSE my_func(my_column)
END AS result_column

  Is there anything else I should be thinking about?

If the functions are querying lookup tables (ex: fn_country_name(country_cd IN VARCHAR2)) then you can consider replacing them with outer joins to the lookup tables directly. That should be faster than a function call as the optimizer can use a hash join (vs. a nested loop if it's in the function call).

Finally if things are really slow/confusing it's good to take a step back and see what the process is supposed to do end to end. You may need to go back a step in your processing chain to make things more efficient.


Tail-log backup options when restoring to a new database I want to restore a sql server database(sql server 2014) to a new database to see if I can recover deleted data.

I need to restore from about 2 months ago, so I will need to use the timeline and pick the correct trn point in time.

I'm restoring to a new database and don't want to mess with or interrupt the source database at all.

So should I leave the options under Tail-log backup (Take tail-log backup before restore and Leave source database in the restoring state) unchecked or checked?

Thanks!

I'm restoring to a new database and don't want to mess with or interrupt the source database at all.

  So should I leave the options under Tail-log backup (Take tail-log
  backup before restore and Leave source database in the restoring
  state) unchecked or checked?

Unchecked.  Just enter the name of the new database you want to restore into and pick the point on the timeline.


Create users in SQL Server 2012 I created a new login and a new user and I mapped a database in SQL Server 2012. In the login properties in Securables, we selected permissions "deny" for "View any database" for the user to see only the specified database.

The problem is that the user does not see any database.

If I select "Grant" to "View any database", user can see all databases. How can I make the user can see only the specified database?

Please do this after denying access to other database .. i think newely created user's default database is MASTER you need to give him newley created db as default database. you can do this by following steps

1) go to SECURITY >> LOGINS >> Select USER >> RIGHT CLICK on it >> Select PROPERTIES >> Select you newley created Database in DEFAULT DATABASE

Please see below screenshot for the reference


SQL Server: get meta-Info about LDF-File I am trying to get the timestamp of the first entry in the .LDF file of a live database.
Is there any way to do this?

I already get the first LSN in the LDF using

SELECT MIN([Current LSN])
FROM sys.fn_dblog(NULL,NULL)

This returns a string like 00000064:000000e9:0033. But how can I convert this into a timestamp? The function sys.fn_cdc_map_lsn_to_time throws an error that "cdc.lsn_time_mapping" is not defined. This is not surprising IMO, since CDC is not enabled on the server.

BTW: This is NOT a duplicate of my own question 83446 from last november: I don't have a TRN to parse, but only the .LDF.

This returns a String like '00000064:000000e9:0033'. But how can I convert this into a timestamp?

You can't.  An LSN is not a timestamp, nor does it store any portion of date and/or time in it's bits.

SQL Server does not log the datetime stamp for every logged operation.  It does keep track of these timestamps for transactions, but not for each and every single logged operation.

Note: You are trying to reference a Change Data Capture DMF with sys.fn_cdc_map_lsn_to_time.

I think you are taking the wrong approach to some sort of question that you are trying to answer.  Datetime stamps on transaction log records wouldn't really tell you much, even if it is possible.

Let's take a step back and let us know why you're trying to do this.


Using Galera Cluster(Percona XtraDB Cluster, MariaDB Cluster) for setting up multi master Before i gotta use one I want to know if it is working in windows OS cause I saw a lot using it in linux OS. Does using galera (Percona XtraDB Cluster, MariaDB Cluster) cluster works in windows?

it does not so far. I suggested it to Codership (the creator of Galera) to port it to Windows but somehow I did not have luck with my suggestion.

But we (fromdual.com) would be happy to help you setting it up and operating it on Linux?

Regards,
Oli


Query elemMatch Date I have a document structure like this:

{
"_id" : ObjectId("5359816b036493327d7cae95"),
"_class" : "com.myproject.MyObjectDTO",
"signals" : [
    {
        "signalType" : "ABC",
        "quantity" : NumberLong(1),
        "date" : ISODate("2001-12-13T23:00:00.000Z")
    },
    {
        "signalType" : "ABD",
        "quantity" : NumberLong(1),
        "date" : ISODate("2001-12-16T23:00:00.000Z")
    },
    {
        "signalType" : "ABF",
        "quantity" : NumberLong(1),
        "date" : ISODate("2001-12-17T23:00:00.000Z")
    },
    {
        "signalType" : "ABG",
        "quantity" : NumberLong(1),
        "date" : ISODate("2001-12-18T23:00:00.000Z")
    },
    ...
}

with around 160 000 entries and 3000 signals per entry.

I tried to find documents with a date greater than an specified date (I'm using $date because I query through Spring Data):

db.analysisDTO.find(
      "signals" : {
         "$elemMatch" : {
            "date" : { "$gte" : { "$date" : "2001-12-01T22:00:00.000Z"}})

The request takes more than 7 minutes and I get no result. Even if I change $gte to $lte, I don't get anything.

Is the request wrong?

The $date operator is a part of the MongoDB Extended JSON spec and that's what you get as default with mongoexport but you don't want to use it in the query criteria.

Try using the ISODate() constructor instead:

db.analysisDTO.find({"signals" : {
        "$elemMatch" : {
           "date" : {
             "$gte" : new ISODate("2001-12-01T22:00:00.000Z")}}}});

The reason for the 7 minute length is likely because the entire data set is being scanned into memory to check all fields.


PLPGSQL: Cannot use parameters inside a function query I'm trying to create a function that returns all events with the corresponding week number, and the day of week the event is occurring using crosstab and series generator.
I have tested that the actual query works inside the funcition if I use literal values, e.g. 2020 and 3 (March month number) in place of the variables.
Here is the function query I'm trying to use:
CREATE OR REPLACE FUNCTION get_month_events(
yr int,
mth int,
OUT week int,
OUT sun int, OUT mon int, OUT tue int, OUT wed int,
OUT thu int, OUT fri int, OUT sat int
)

RETURNS SETOF RECORD AS
$$
BEGIN
RETURN QUERY
SELECT * FROM crosstab('
SELECT
extract(week from starts) as week,
extract(dow from starts) as dow,
count(*)
FROM events
WHERE extract(month from starts) = mth
AND extract(year from starts) = yr
GROUP BY week, dow
ORDER BY week, dow',
'SELECT * FROM generate_series(0,6) AS dow'
)
AS (
week int,
sun int, mon int, tue int, wed int, thu int, fri int, sat int
) ORDER BY week;
END;
$$
LANGUAGE plpgsql;

When I try to call the function in a query, for example
SELECT * FROM get_month_events(2019, 8);

I'm getting this error:
ERROR:  column "mth" does not exist
LINE 7: WHERE extract(month from starts) = mth
                                           ^
QUERY:
SELECT
extract(week from starts) as week,
extract(dow from starts) as dow,
count(*)
FROM events
WHERE extract(month from starts) = mth
AND extract(year from starts) = yr
GROUP BY week, dow
ORDER BY week, dow
CONTEXT:  PL/pgSQL function get_month_events(integer,integer) line 3 at RETURN QUERY

Postgres does not recognize the parameter name inside the function query. How can I get it to reach the variable value?
It seems like it is just a stupid mistake I haven't spotted but so far I haven't been able to figure out why it does not let me access the variable.

Well in your function/procedure you are passing a string to the crosstab table function.
In the context of the string the value for mth can't be passed on as a variable in the function. You might have to concatenate the string like this:
CREATE OR REPLACE FUNCTION get_month_events(
yr int,
mth int,
OUT week int,
OUT sun int, OUT mon int, OUT tue int, OUT wed int,
OUT thu int, OUT fri int, OUT sat int
)

RETURNS SETOF RECORD AS
$$
BEGIN
RETURN QUERY
SELECT * FROM crosstab('
SELECT
extract(week from starts) as week,
extract(dow from starts) as dow,
count(*)
FROM events
WHERE extract(month from starts) = ' || mth || '
AND extract(year from starts) = ' || yr || '
GROUP BY week, dow
ORDER BY week, dow',
'SELECT * FROM generate_series(0,6) AS dow'
)
AS (
week int,
sun int, mon int, tue int, wed int, thu int, fri int, sat int
) ORDER BY week;
END;
$$
LANGUAGE plpgsql;

The relevant parts being:
...
WHERE extract(month from starts) = ' || mth || ' -- <<< HERE
AND extract(year from starts) = ' || yr || '     -- <<< AND HERE
GROUP BY week, dow
...

This way the value can be concatenated together with the string and executed in the context of the crosstab table function.
Working Solution
A working example can be found at this db<>fiddle
Create Table
create table events(
starts date,
eventtext varchar(20)
);

Insert Sample Data
insert into events(starts, eventtext)
values
('2020-03-01', 'test1'),
('2020-03-01', 'test2')

Create Function/Procedure
CREATE OR REPLACE FUNCTION get_month_events(
yr int,
mth int,
OUT week int,
OUT sun int, OUT mon int, OUT tue int, OUT wed int,
OUT thu int, OUT fri int, OUT sat int
)

RETURNS SETOF RECORD AS
$$
BEGIN
RETURN QUERY
SELECT * FROM crosstab('
SELECT
extract(week from starts) as week,
extract(dow from starts) as dow,
count(*)
FROM events
WHERE extract(month from starts) = ' || mth || '
AND extract(year from starts) = ' || yr || '
GROUP BY week, dow
ORDER BY week, dow',
'SELECT * FROM generate_series(0,6) AS dow'
)
AS (
week int,
sun int, mon int, tue int, wed int, thu int, fri int, sat int
) ORDER BY week;
END;
$$
LANGUAGE plpgsql;

Select Function/Procedure
select get_month_events(2020,03)

Output

get_month_events
----------------
(9,2,,,,,,)


How to create a new database in PostgreSQL 10 in Windows from batch file? I am creating a batch file to create a new database in PostgreSQL 10. I used the following code,

echo off
cd "C:\Program Files (x86)\PostgreSQL\10\bin"
psql "dbname=myDB host=localhost user=postgres password=postgres port=5432"
pause

But I get this error,

  psql: FATAL:  database "myDB" does not exist

I understand this is not the command to create a new database. I also tried the following code,

echo off
cd "C:\Program Files (x86)\PostgreSQL\10\bin"
psql "createdb myDB passfile %APPDATA%\postgresql\pgpass.conf"
pause

This is based on the information in this link. The "pgpass.conf" file has the follwoing text,

  localhost:5432:myDB:postgres:postgres

But it asks to enter password. I entered the default password "postgres". But this was the output,

  psql: FATAL:  password authentication failed for user "User1"

The objective here is to create new database from the batch file without asking for password. How to achieve this?

Create a bat file with the following content and execute it:

c:\path\to\postgresql\bin\psql.exe -f C:\path\to\db_create.sql  postgresql://user:password@localhost:port/postgres

This is what worked for me.


Where can I read about views for MySQL? I really don't know nothing about SQL.. I know how to insert and select data from  database using PHP but besides that, nothing.

I found this view And I am sure that 5 minutes for you to answer this question will give me a lot more knowledge.

I understand the fact the the view is created and given the name film_list and the AS defines what this view is going to retrieve.

Then we select from the Schema or Database film the field film_id and define it AS FID.

Q1. Why do we do that? What is the main purpose of this definition?

... The statement continues until GROUP_CONCAT.

Q2. What is the difference between CONCAT, that already combines the two fields to one element, and GROUP_CONCAT ?

.. Then the FROM syntax. Now we choose the schemas and tables to select from.
After reading on w3schools, I understand the INNER JOIN, FULL JOIN, LEFT JOIN and RIGHT JOIN statements, but not the JOINonly statement.

Q3. Which requirements does the JOIN statement have?

.. Okay now.. This one I haven't been able to find anything about.

Q4. What is the ON statement, and what is it good for?

The GROUP BY statement I understand.. The SQL simply groups everything together related to the film_id

CREATE VIEW film_list
AS
SELECT film.film_id AS FID,
film.title AS title,
film.description AS description,
category.name AS category,
film.rental_rate AS price,
film.length AS length,
film.rating AS rating,
GROUP_CONCAT(CONCAT(actor.first_name, _utf8' ', actor.last_name) SEPARATOR ', ') AS actors
FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id
LEFT JOIN film ON film_category.film_id = film.film_id
JOIN film_actor ON film.film_id = film_actor.film_id
JOIN actor ON film_actor.actor_id = actor.actor_id
GROUP BY film.film_id

Q5. What would the correct way to display returned data be?

I assume that the view would create some kind of table during the SELECT statement. Usually I would be able to simply echo a row in PHP. Is that  still possible, and if so - how would I have to do? Because usually I echo like this echo $row['field_name'] but how would I know which field to call? Is that why we defined the elements with FID, title, desciption etc.?

In my MySQL database I have 14 different tables each one containing a little bit information about a cooking recipe.

Q6. Would a view be ideal for me to use when having that amount of tables to search in?

I Really hope that some of you please would take your time to answer these questions. It would help me a lot. And then a final Question:

Q7. Which websites or books are the best to teach me how to work with SQL?

A1.  You don't have to do that, it's pretty arbitrary.  The column alias just renames the id column.

A2. CONCAT() and GROUP_CONCAT are very different -- CONCAT('a','b','c') is just a function that would evaluate to 'abc'  GROUP_CONCAT() is an aggregate function that works with GROUP BY;  for each group of rows that you "roll up" into a group, GROUP_CONCAT() will create a string based on the value for each row in that group.

A3. JOIN is just shorthand for INNER JOIN

A4. ON defines how the tables are joined.  Each set of possible combination rows are examined, and each combination where the ON clause evaluates to true, then you get a match.  Visual Join Explanation

A5. I can't find your actual question here.

A6. No, a view doesn't sound like the right approach.  If you're searching for the same thing in many tables, then all that stuff should probably be in one tables.  But that's a generalization, you haven't described nearly enough about your actual project to give a clear answer.

A7.  This question is answered other places.


CREATE DATABASE AS COPY does not work with Azure I'm new with T-SQL and MSSQL but need to copy Azure SQL database from one server to another.

As I googled here - it can be done with a CREATE DATABASE Database1_copy AS COPY OF server1.Database1; query, but in my Vusial Studio Code editor on the Ubuntu Linux with the vscode-mssql extention - I have an error:

  Msg 156, Level 15, State 1, Line 1 : Incorrect syntax near the keyword 'database'.

My full query looks next:

CREATE DATABASE Database1_copy AS COPY OF oldserver.database.windows.net.olddatabasenamehere;

Additional googling leads me to the same solutions (here is another example I found).

What I'm doing wrong here?

I understood that even the first link published on Azure's resource (azure.microsoft.com) - it does not necessarily is working solution for Azure's SQL.

P.S. Idea is to automate DEV environment rollout (with ARM templates) as a copy of the current Live environment, with creating databases as a copy of Live's databases.

From BOL :

  The AS COPY OF argument does not support the fully qualified unique domain names. In other words, if your server's fully qualified domain name is serverName.database.windows.net, use only serverName during database copy.

So your command should be

CREATE DATABASE Database1_copy AS COPY OF oldserver.olddatabasenamehere;


Improve performance by partitioning I'm using SQL Server 2012. I’m using CTE to create an orders table from the items table. The final count for the orders table is around 120 million. It is taking about 2 hours for the whole process.

Can partitioning the table help reduce the completion time?

If so, can I partition an empty table by order year and then insert into the orders table from the CTE. Or will I need to create a partition on the items table since the CTE is rolled up from the items table?

Also my estimated number of rows differ significantly from the actual number of rows.

Can partitioning the table help reduce the completion time?

No. Partitioning is not a performance feature, is used for other purposes. If the table has 120 million rows unpartitioned, it will also have 120 million rows after partitioning. Read How To Decide if You Should Use Table Partitioning.

If you want to improve performance you need to identify the bottleneck and address them appropriately. Is it a bad plan? Missing index? Slow IO? Slow CPU? Not enough RAM? Poor code? USe a methodology like Waits and Queues to identify the bottleneck.


Is Postgres ignoring my function cost annotation? Why does this:

create or replace function dummy() returns double precision as $$
SELECT random() $$
LANGUAGE SQL
COST 777;
explain select dummy();

return this:

Result  (cost=0.00..0.01 rows=1 width=0)

and not a cost of 777?

I believe the costs refer to different units of measurement. The cost when used in functions is the estimated execution cost in units of cpu_operator_cost, whereas the EXPLAIN plan costs are units of disk page fetches (seq_page_cost).

For function cost details, see http://www.postgresql.org/docs/current/static/sql-createfunction.html

For EXPLAIN plan costs, see http://www.postgresql.org/docs/current/static/using-explain.html


Riak - why can't I go to http://localhost:8091/stats in my browser? (Seven Databases in Seven Weeks) I'm trying to work through the book Seven Databases in Seven Weeks, and I'm in the process of setting up Riak. After installing Riak, starting a few servers, and putting them together in a cluster, the book says to verify the servers are healthy by checking their stats by going to the address http://localhost:8091/stats in a web browser. However, when I go to this address in my web browser, I'm unable to connect.

The book is somewhat outdated so this could be something riak used to support but doesn't anymore -- but I don't know whether that's the case. It also may be relevant that I'm doing this within a, Oracle Virtualbox VM I've installed on my Windows 7 work laptop, running Ubuntu.

It looks like that's not the port it runs on any more. You can have a look in the server's app.config (e.g. dev/dev1/etc/app.config), which is mentioned in the book in case you need to change the port.

On my instance it looks like it's running on port 10018, so I can see the stats on localhost:10018/stats (dev1), 10028 (dev2) and 10038 (dev3)


Should I normalize my string arrays, or just keep it a simple string? I am working on a project involving cards from the Magic: The Gathering game. To be of any use, I must load the basic information about each card into my database, but some of the fields may contain arrays of text, that is, they may contain multiple values .

For example, a card can be classified to be both "green" and "blue". Or it can have several different creature types specified, and so on.

My first thought was to simply store the values as a string, and then search using the LIKE keyword. In combination with the NOT keyword, it should be possible to search for any desired combination of words, and also exclude certain words.

However, LIKE is going to be (relatively) slow as it can't use an index and has to rely on text pattern matching, and probably has other drawbacks as well. With normalization I could improve speed by assigning an integer key to each keyword, and then create a table that connects each card with the relevant keys (see EBrowns answer for a clearer description).

But with normalized tables I have to deals with joins (severely increased code complexity, not only in the SQL part), transactions (to update all relevant tables or rollback everything), and possibly decreased performance due to joins (see To normalize or not).

AFAIK there are roughly 20000-25000 different magic cards in existence (and more being released every year). With one row for each card, is it worth it overall to normalize the data, or is the benefit not worth the costs? Why/Why not? And how do you decide this question in general?

EDIT: I am currently using a MySQL database, but I would also appreciate answers that apply to any of the other non-commercial/open source databases out there. Who knows - I might switch to a different database at some point.

Answers should not provide a technical solution only, but explain why it would/might be the right solution in terms of best/good enough performance for least amount of effort.

Well you could attack this in the manner you previously stated, or you could go another route.

Typically, in this situation, I would create three tables.

Table 1 - Cards: This would contain the basic card information.

Table 2 - Types: This would be one record for each possible type (i.e. one record for green, one for blue, one for creature A, one for creature B, and so on.)

Table 3 - CardTypes: This would have two columns, one Foreign Key to Cards and one Foreign Key to Types. The combination of the two would be the Primary Key.

The advantage to this approach is that you don't have to store a large amount of plain-text, as if a card has multiple properties they would each go one-and-one in Types, and then a relationship to each property would go in CardTypes. It also means if two cards are blue, then only one Type is store for blue, and two (small, mind you) entries are in CardTypes.

The downfall is the obvious overhead. Your programme must be aware of the three tables.

The advantages depend entirely on how often you are querying the tables. If you are often querying for a specific property, then the index on CardTypes would significantly improve performance, as the index would contain the necessary pointers to your cards that have that property.

In the end it all depends on how often/many queries you will be running. If you are running a significant amount you will likely be better off taking the programming overhead of creating two tables and then linking them with a third.


TOP(1) BY GROUP of very huge (100,000,000+) table Setup

I have a huge table of ~115,382,254 rows.
The table is relatively simple and logs application process operations.

CREATE TABLE [data].[OperationData](
    [SourceDeciveID] [bigint] NOT NULL,
    [FileSource] [nvarchar](256) NOT NULL,
    [Size] [bigint] NULL,
    [Begin] [datetime2](7) NULL,
    [End] [datetime2](7) NOT NULL,
    [Date]  AS (isnull(CONVERT([date],[End]),CONVERT([date],'19000101',(112)))) PERSISTED NOT NULL,
    [DataSetCount] [bigint] NULL,
    [Result] [int] NULL,
    [Error] [nvarchar](max) NULL,
    [Status] [int] NULL,
 CONSTRAINT [PK_OperationData] PRIMARY KEY CLUSTERED
(
    [SourceDeviceID] ASC,
    [FileSource] ASC,
    [End] ASC
))

CREATE TABLE [model].[SourceDevice](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_DataLogger] PRIMARY KEY CLUSTERED
(
    [ID] ASC
))

ALTER TABLE [data].[OperationData]  WITH CHECK ADD  CONSTRAINT [FK_OperationData_SourceDevice] FOREIGN KEY([SourceDeviceID])
REFERENCES [model].[SourceDevice] ([ID])

The table is clustered at around 500 clusters and on a daily base.

Also, the table is well indexed by PK, statistics are up-to-date and the INDEXer get defraged every night.

Index based SELECTs are lightning fast and we had no problem with it.

Problem

I need to know the last (TOP) row by [End] and partitioned by [SourceDeciveID]. To get the very last [OperationData] of every source device.

Question

I need to find a way to solve this in a good way and without bringing the DB to the limits.

Effort 1

The first try was obvious GROUP BY or SELECT OVER PARTITION BY query. The problem here is also obvious, every query has to scan over very partition order/find the top row. So the query is very slow and has a very high IO impact.

Example query 1

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY [SourceDeciveID] ORDER BY [End] DESC) AS rn
   FROM [data].[OperationData]
)
SELECT *
FROM cte
WHERE rn = 1

Example query 2

SELECT *
FROM [data].[OperationData] AS d
CROSS APPLY
(
   SELECT TOP 1 *
   FROM [data].[OperationData]
   WHERE [SourceDeciveID] = d.[SourceDeciveID]
   ORDER BY [End] DESC
) AS ds

FAILED!

Effort 2

I created a help table to always hold a reference to TOP row.

CREATE TABLE [data].[LastOperationData](
    [SourceDeciveID] [bigint] NOT NULL,
    [FileSource] [nvarchar](256) NOT NULL,
    [End] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_LastOperationData] PRIMARY KEY CLUSTERED
(
    [SourceDeciveID] ASC
)

ALTER TABLE [data].[LastOperationData]  WITH CHECK ADD  CONSTRAINT [FK_LastOperationData_OperationData] FOREIGN KEY([SourceDeciveID], [FileSource], [End])
REFERENCES [data].[OperationData] ([SourceDeciveID], [FileSource], [End])

To fill up the table a created a trigger to always add/update the source row if higher [End] column is inserted.

CREATE TRIGGER [data].[OperationData_Last]
   ON  [data].[OperationData]
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    MERGE [data].[LastOperationData] AS [target]
    USING (SELECT [SourceDeciveID], [FileSource], [End] FROM inserted) AS [source] ([SourceDeciveID], [FileSource], [End])
    ON ([target].[SourceDeciveID] = [FileSource].[SourceDeciveID])

    WHEN MATCHED AND [target].[End] < [source].[End] THEN
        UPDATE SET [target].[FileSource] = source.[FileSource], [target].[End] = source.[End]

    WHEN NOT MATCHED THEN
        INSERT ([SourceDeciveID], [FileSource], [End])
        VALUES (source.[SourceDeciveID], source.[FileSource], source.[End]);

END

The problem here is, that it also has a very huge IO impact and I don't know why.

As you can see here in the query plan it also executes a scan over the whole [OperationData] table.

It has a huge overall impact on my DB.

FAILED!

If you have a table of SourceID values, and an index on your main table on (SourceID, End) include (othercolumns), just use OUTER APPLY.

SELECT d.*
FROM dbo.Sources s
OUTER APPLY (SELECT TOP (1) *
    FROM data.OperationData d
    WHERE d.SourceID = s.SourceID
    ORDER BY d.[End] DESC) d;

If you know you’re only after your newest partition, you could include a filter on End, like AND d.[End] > DATEADD(day, -1, GETDATE())

Edit: Because your clustered index is on SourceID, Source, End), put Source into your Sources table too and join on that as well. Then you don’t need the new index.

SELECT d.*
FROM dbo.Sources s -- Small table
OUTER APPLY (SELECT TOP (1) *
    FROM data.OperationData d -- Big table quick seeks
    WHERE d.SourceID = s.SourceID
    AND d.Source = s.Source
    AND d.[End] > DATEADD(day, -1, GETDATE()) -- If you’re partitioning on [End], do this for partition elimination
    ORDER BY d.[End] DESC) d;


exec xp_cmdshell 'sc \\ start ' When I execute this on sql server on another machine (in same sub net) I am getting "Access is denied". But when I did it from command prompt (sc \ start ) it is working.

Please advice why I am getting access denied when I am doing it from sql server using exec xp_cmdshell cmd

You are doing a double hop impersonation that requires delegation.


Distributed AG on SQL Server 2017 Standard Edition? Can I configure Distributed AG with SQL Server 2017 Standard Edition?

No, you can't. That is one of the basic availability groups limitations.

Limitations

  Basic availability groups can not be part of a distributed
  availability group.


While using Cross Apply, can I force "empty" records to appear? I have a query that is working fantastically, except that I would like to force some records to appear. I am using SqlServer 2016, and this query will ultimately be used in a VIEW and used by SSRS 2016. If the desired behavior can be done in SSRS, or even changing to a PIVOT or some such, I am open to that option.

I will put some DDL down below to mimic the example images shown.

The data I am pulling from looks like the below:

And I am using a cross apply to make the totals appear vertically like so:

The problem is, as seen above, the records that have a state and SUI_State of 'IN', have a SUI_State of "OH". This leads to the output to not contain SUI records for "IN" because no records have an SUI_State1 of "IN"

Is there any way to force "IN Employee SUI", "IN Employer SUI", etc... to appear with values of 0?

There are many states where the State/SDI State and SUI State are different, so I cannot hard-code the logic in there.

And here is some dumbed down similar data with my current query. The values aren't important in this question, only forcing the SUI records to appear for the state, "IN'

    CREATE TABLE #EmployeeTaxes
(    Payroll int
    ,SDI_State char(2)
    ,SUI_State char(2)
    ,State char(2)
    ,SIT DECIMAl(19,2)
    ,Employee_SDI DECIMAL(19,2)
    ,Employer_SDI DECIMAL(19,2)
    ,Employee_SUI DECIMAL(19,2)
    ,Employer_SUI DECIMAL(19,2)
)

--Data in source table
INSERT INTO #EmployeeTaxes
VALUES   (4, 'IN', 'OH', 'IN', 50, 0, 0, 0, 100)
        ,(4, 'IN', 'OH', 'IN', 50, 0, 0, 0, 100)
        ,(4, 'IN', 'OH', 'IN', 50, 0, 0, 0, 100)
        ,(4, 'IN', 'OH', 'IN', 50, 0, 0, 0, 100)
        ,(4, 'IN', 'OH', 'IN', 50, 0, 0, 0, 100)
        ,(4, 'OH', 'OH', 'OH', 50, 0, 0, 0, 100)
        ,(4, 'OH', 'OH', 'OH', 50, 0, 0, 0, 100)
        ,(4, 'OH', 'OH', 'OH', 50, 0, 0, 0, 100)
        ,(4, 'OH', 'OH', 'OH', 50, 0, 0, 0, 100)

--My Current Query
SELECT Payroll
      ,v.Item                       AS [Witholding]
      ,SUM(v.TaxValue)              AS Tax
      ,v.OrderByNumber              AS [OrderByNumber]
FROM #EmployeeTaxes
CROSS APPLY (
    VALUES ([STATE]   + ' SIT', SIT, [STATE] + '110')
          ,(SDI_STATE + ' Employee SDI', EMPLOYEE_SDI,SDI_STATE + '111')
          ,(SDI_STATE + ' Employer SDI', EMPLOYER_SDI,SDI_STATE + '112')
          ,(SUI_STATE + ' Employee SUI', EMPLOYEE_SUI,SUI_STATE + '113')
          ,(SUI_STATE + ' Employer SUI', EMPLOYER_SUI,SUI_STATE + '114A')
          ,(SUI_STATE + ' Total', EMPLOYEE_SUI + EMPLOYER_SUI, SUI_State + '114B')
          ,(SDI_STATE + ' Total', EMPLOYEE_SDI + EMPLOYER_SDI , SDI_State + '114B')
          ,('---------------------------------',NULL,[STATE] + '121')

) v (Item, TaxValue, OrderByNumber)
GROUP BY Payroll, OrderByNumber, v.Item
ORDER BY PAYROLL, OrderByNumber
DROP TABLE #EmployeeTaxes

It's a little unclear what you're after, but one way to add in missing values into a GROUP BY query is to add all rows to your starting table with 0 or NULL for the aggregated columns. As @RDFozz pointed out, you want to use NULL if you are doing COUNT aggregates because otherwise the results will be inflated. However, 0 is a good choice if you only do SUM aggregates and don't want to bother with a COALESCE.

You can accomplish this with UNION ALL. One implementation is as follows:

SELECT Payroll
      ,v.Item                       AS [Witholding]
      ,SUM(v.TaxValue)              AS Tax
      ,v.OrderByNumber              AS [OrderByNumber]
FROM
(
    SELECT *
    FROM #EmployeeTaxes

    UNION ALL

    SELECT DISTINCT Payroll, [STATE], [STATE], [STATE], 0, 0, 0, 0, 0
    FROM #EmployeeTaxes
) AS #EmployeeTaxes
CROSS APPLY (
    VALUES ([STATE]   + ' SIT', SIT, [STATE] + '110')
          ,(SDI_STATE + ' Employee SDI', EMPLOYEE_SDI,SDI_STATE + '111')
          ,(SDI_STATE + ' Employer SDI', EMPLOYER_SDI,SDI_STATE + '112')
          ,(SUI_STATE + ' Employee SUI', EMPLOYEE_SUI,SUI_STATE + '113')
          ,(SUI_STATE + ' Employer SUI', EMPLOYER_SUI,SUI_STATE + '114A')
          ,(SUI_STATE + ' Total', EMPLOYEE_SUI + EMPLOYER_SUI, SUI_State + '114B')
          ,(SDI_STATE + ' Total', EMPLOYEE_SDI + EMPLOYER_SDI , SDI_State + '114B')
          ,('---------------------------------',NULL,[STATE] + '121')

) v (Item, TaxValue, OrderByNumber)
GROUP BY Payroll, OrderByNumber, v.Item
ORDER BY PAYROLL, OrderByNumber;

I think that this is closer to what you want? Top result set is from your query in the question and the bottom result set is mine:


PostgreSQL: set default privileges on schemas themselves? According to the PostgreSQL documentation on ALTER DEFAULT PRIVILEGES, default privileges can be set on tables, sequences, functions, types.

Is there any way to set default privileges on schemas themselves, so that the privileges specified in the GRANT clause are by default applied to any new schema in the database upon its creation?

As mentioned by @a_horse_with_no_name, this is now implemented in PostgreSQL v10

  ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in the future. (It does not affect privileges assigned to already-existing objects.) Currently, only the privileges for schemas, tables (including views and foreign tables), sequences, functions, and types (including domains) can be altered.

Example,

ALTER DEFAULT PRIVILEGES
  GRANT USAGE ON SCHEMAS
  TO role;


Recreating mat. view in Oracle failing due to dba_summaries entry, how to prevent? I have run into the issue described here several times in our current project and I would like to understand how it happens and how to prevent it in the future.

What is the meaning of DBA_SUMMARIES and why are entries created there for the MVs of a user?
Is there a way to remove the entries there as a standard user, so that I can recreate the MV without running into above problem?

The main issue is that SYSDBA privileges are required to remove the conflicting summary. Since we do not have a SYSDBA account on the target instance, we cannot recreate our MVs without having someone on the DB support team step in.

Any pointers to documentation and if somebody can explain what the logic behind DBA_SUMMARIES is and how I can prevent this issue from happening, that would be great.

The Oracle version on which we observed this is 11.2.0.1.0 (on 64bit Linux), we do not have a support contract and therefore no access to Oracle support.

Update / Solution

Thanks to Jack's answer below, I have been able to circumvent the issue, since it seems like we're really hitting the bug he mentioned. When I first drop all indexes on the MV, I do not get the error message and am able to recreate the MV as intended (it is not showing up in USER_OBJECTS anymore after having been dropped).

This is how I am doing it now (snipped for brevity):

SET serveroutput ON
SET echo ON

DECLARE
  CURSOR mv_indexes
  IS
    SELECT 'DROP INDEX '
      || index_name AS stmt
    FROM user_indexes
    WHERE table_name = 'MV_NAME'
    AND table_owner  = 'USER';
BEGIN
  FOR ix IN mv_indexes
  LOOP
    dbms_output.put_line('Executing: ' || ix.stmt);
    EXECUTE immediate ix.stmt;
  END LOOP;
END;
/

DROP materialized VIEW MV_NAME;

SELECT * FROM user_objects WHERE object_name = 'MV_NAME';

CREATE materialized VIEW MV_NAME ..

CREATE INDEX ix_someindex ON MV_NAME (..);
CREATE INDEX..

If drop materialized view is leaving an entry in dba_summaries, then you are hitting a bug - perhaps the one described here

As you do not have a support contract, you can't raise a TAR. However the first question Oracle Support would ask you is whether you are running the latest patchset, 11.2.0.3 - I suggest you consider this option first as the root problem is an Oracle bug. Note that "it is a full installation"

If that doesn't solve the problem, you'll need to try and work around it because as far as I know there is no alternative to sysdba for drop summary. The linked article suggested dropping indexes before dropping the MV which would perhaps be a good place to start.

  Any pointers to documentation and if somebody can explain what the logic behind DBA_SUMMARIES

dba_summaries and drop summary are undocumented.


Issue with SQL Server Aliases on a multi-instance installation I've a 64-bit installation of SQL 2008 R2 SP2 on Server 2008 R2, with five instances of SQL.

I can get the aliases set up to work fine on the server (i.e. if I remote onto the server, then it works without problems, but from the client machine, there is no joy).

Errors:

SQL Pipes Error 40, Error 53.

Or, if specify port 1433, TCP Error 0, 11004.

If I specify port 63769 (which is mentioned as a port that SQL is listening on), TCP Error 0, 11004.

If I connect using the servername\instancename I have no problems.

I've set up the alias in 32-bit and 64-bit on the server. I've left the ports as dynamic in the TCP settings, because if I define the port as being 1433 for each instance, none of them will start. This is weird, as it's an approach I've used successfully on other servers.

There is no internal firewall, but some ports may, or may not, be shut off...

Any ideas about what to try next?

Thanks!

If you want aliases to work from the client then you have to create them on the client. Install the SQL Server client (from memory the feature is called Client Tools) and use Configuration Manager to create the aliases (32-bit or 64-bit to match the client application).


PgAdmin 4 - How to connect to database using unix socket via ssh tunnel? Even though it might sound related to this question, it isn't, because I'm intreseted in the use of unix sockets instead of TCP/IP ones.
However, I am unable to connect to a remote PostgreSQL database using unix sockets and an ssh tunnel with pgAdmin4. pgAdmin always claims a refused connection by the server and asks for a password, which makes me wonder. When sshing into the server and using psql a connection can be made just flawlessly using the unix socket (/var/run/postgresql). Is there anything special that needs to be set to allow such a setup, or might this even be a bug in pgAdmin itself? I'd appreciate any ideas to solve this problem without having to resort on TCP/IP connections on the remote side.
I also post some screens from my current config:

That should work fine with the correct ssh command.
To forward from a local Unix socket to a remote one runs
ssh -L /tmp/.s.PGSQL.5555:/var/run/postgresql/.s.PGSQL.5432 -N laurenz@dbserver

Then you would use host /tmp and port 5555 to connect.
To forward a local TCP socket to a remote Unix socket, run:
ssh -L 5555:/var/run/postgresql/.s.PGSQL.5432 -N laurenz@dbserver

Then you would use host 127.0.0.1 and port 5555 to connect.


How to increase Postgres WAL size? PostgreSQL 10.7

I keep getting the message:

HINT:  Consider increasing the configuration parameter "max_wal_size".

So, I updated the following parameters and reloaded the configuration.

checkpoint_timeout = 30min
max_wal_size = 2GB
min_wal_size = 80MB

However, the WAL size stays at 16MB.  What did I miss?

Btw, I use repmgr for replication wal_level = replica.

max_wal_size controls the total size of all WAL segments, not the size of each segment.

So with a value of 2GB, you are saying that about 128 of those segments can be created before they get re-used.

The hint is about increasing the total number of files (hence the mentioning of max_wal_size), it's not about the size of each segment (file).

Up to Postgres 10, the size of each segment is fixed to 16MB

Starting with Postgres 11, you can change the size of each segment but only when you initialize a new database cluster (but again: that's not what that hint is about).


Cannot alter MYSQL Table (stuck in a invalid default value loop) Some how I have 2 columns with incorrect default values.
Not sure how it got created in the first place as I imported the db from another source.

Problem now is, I cannot make any changes to the table cause MYSQL will complain:

  MySQL said: Invalid default value for 'campaign_start_date'

if I tried adding a column.

But when I try changing the DEFAULT VALUE to CURRENT_TIMESTAMP for campaign_start_date, it complains campaign_end_date has an invalid default value.

So I'm stuck in a loop that doesn't allow me to make any changes to the table.

Is there anyway to fix this?

Try changing both columns defaults at the same time:

ALTER TABLE yourtable
    CHANGE campaign_start_date campaign_start_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHANGE campaign_end_date campaign_end_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;


Change MySQL default installation directory in OS X I install mysql in default installation directory /usr/local/mysql

Is it possible to change mysql installation directory to another path?

I am running OS X 10.10 & MySQL 5.6

1- Open mysqld_safe in NewMysqlDir/bin with a text editor & replase all /usr/local/mysql to NewMysqlDir & save.

2- Open mysqld_safe in NewMysqlDir/bin with Terminal


My application uses set schema and unqualified tables and I get confusing numbers from snapdyn_sql More of a finding, than a question, I'll add an answer in a minute.

The other day I stumbled upon an application that used several tables with the same name, but in different schemas. Example:

create table s1.parent (x int not null primary key);
create table s1.child (y int not null primary key, x int not null references s1.parent(x));

create table s2.parent (x int not null primary key);
create table s2.child (y int not null primary key, x int not null references s2.parent(x));

The application uses the pattern:

set schema s1;
select * from parent x join child y on ...

Despite that the question have been executed against several schemas, there is only one row in:

select num_executions, rows_read, stmt_text
from sysibmadm.snapdyn_sql
where stmt_text like '....%'

and the numbers appear to be cumulative for all schemas. Why is that, and how do I figure out metrics for the different schemas?

The more modern table function:

select EXECUTABLE_ID, STMT_TEXT, num_executions
from TABLE(MON_GET_PKG_CACHE_STMT('D', NULL, NULL, -2)) as T
where stmt_text like 'select *%'"

returns individual metrics for the different schemas.

It still takes some further examination to find out what executable_id that belongs to each schema, so I much prefer qualified table names in queries over the set schema pattern.

If tables are not qualified by schema, the schema can be detected by:

select EXECUTABLE_ID, VALUE as SCHEMA, STMT_TEXT, num_executions
from TABLE(MON_GET_PKG_CACHE_STMT('D', NULL, NULL, -2)) as T
CROSS JOIN TABLE(COMPILATION_ENV(T.COMP_ENV_DESC)) S
where stmt_text like 'select *%'
  and name = 'SCHEMA'

More details on COMPILATION_ENV can be found in the answer provided by Mark Barinstein


Remove Database from SQL Management Studio Database List I'm working with MS SQL Express 2008 and Visual Studio 2010.

If you are attached to a local MDF file how do you remove it from the Databases list without deleting the file? The reason is to purge the list of databases for old projects. The next time you run the project it will re-establish the connection so it makes sense to make things tidy and easy to find when you need it.

Trying to simply delete the the database of course deletes the files as well. I tried unchecking the Delete backup and restore history information for databases but that didn't make a difference. I also tried the ole' Shift-Delete to alter its functionality.

Odd thing is when I search I don't see simlar questions. I'd think that would be something others would want to do.

Right now I make a copy of the MDF and LDF files, delete the database from Management Studio and then copy the file back.

Is there a better way?

You could detach the database, that way the file isn't deleted and it won't display inside of SSMS.


MySQL Still purging binary logs and ignoring expire_logs_days We are running mysql v5.6.17 and I cannot find if there is something else that is causing the binary logs to be purged other than the following:

Adding expire_logs_days to the my.ini (and confirming it is set on the server via show global variables like 'expire%';)
Manually running PURGE BINARY LOGS

There is a nightly backup that backs up the main database, compresses it using 7zip and then FTPs it to another server. I found that the binary logs are purged every night to the binary log which was always created at 00:00 midnight. This is an issue as there is a replication machine running which uses GTIDs and it cannot find the binary logs on the master which are specified by the gtid_purged.

I created 2 events to troubleshoot this. The first event to turn on general logging into a table during this period where it was being purged, and a second to turn general logging off a few hours later. The backup starts at 00:05 and the purge is issued at around 00:07

It seems the PURGE MASTER LOGS TO command was being run by root[root] @ localhost [::1].

There is no one connecting to the server that would run this.
As far as I understand the --delete-master-logs in mysqldump command is similar to RESET MASTER, but is not the same as PURGE BINARY LOGS. We do not use --delete-master-logs however.

What could be causing the purge?

(Not an answer, but too big for a comment.)

Flipping over to a new binlog happens two ways:

passing max_binlog_size, or
FLUSH LOGS

But neither should cause the deletion of the previous binlog without PURGE or expires.

Sounds like FLUSH is happening, probably from the 'nightly backup'.

Please provide the details of your "nightly backup"; the answer is likely to be buried in there somewhere.


Simple count id in MySql table is taking to long I have to tables with 65.5 Million rows:
1)

CREATE TABLE RawData1 (
  cdasite varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  id int(20) NOT NULL DEFAULT '0',
  timedate datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  type int(11) NOT NULL DEFAULT '0',
  status int(11) NOT NULL DEFAULT '0',
  branch_id int(20) DEFAULT NULL,
  branch_idString varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (id,cdasite,timedate),
  KEY idx_timedate (timedate,cdasite)
) ENGINE=InnoDB;

2) Same table with partition (call it RawData2)

PARTITION BY RANGE ( TO_DAYS(timedate))
(PARTITION p20140101 VALUES LESS THAN (735599) ENGINE = InnoDB,
 PARTITION p20140401 VALUES LESS THAN (735689) ENGINE = InnoDB,
 .
 .
 PARTITION p20201001 VALUES LESS THAN (738064) ENGINE = InnoDB,
 PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

I'm using the same query:

SELECT count(id) FROM RawData1
where timedate BETWEEN DATE_FORMAT(date_sub(now(),INTERVAL 2 YEAR),'%Y-%m-01') AND now();

2 problems: 1. why the partitioned table runs longer then the regular table? 2. the regular table returns 36380217 in 17.094 Sec. is it normal, all R&D leaders think it is not fast enough, it need to return in ~2 Sec.

What do I need to check / do / change ? Is it realistic to scan 35732495 rows and retrieve 36380217 in less then 3-4 sec?

For 2, your COUNT(id) may be checking each value of id in the range to see if it is NULL (the column is marked NOT NULL but I don't know if mySQL's query planner will take notice of this) which is an extra lookup operation because id isn't included in the timedate index. Try use COUNT(*) instead and see if that helps any.

For 1, you are partitioning by a function rather than a direct column, so it isn't going to be able to perform simple partition filtering when querying base on just the column value. Even if you partition based on the column value directly you should n't expect simple partitioning to produce faster results unless you are spreading the data over multiple devices (or even then) - partitioning is more of a management feature than a performance one. You need to provide more detail before we can help more there: what is the full definition of the partitioned table and its indexes? How much slower are you finding it?

For both, you should give the output of EXPLAIN <query> so we can see the query plan that is produced.

(edit the extra information into your question, rather than as comments in response to this answer, otherwise some people would might be able to help may not see it)


Narrowing down the blocking session I am using different queries to find the blocking query. For example,
select
       ec.session_id,
       er.blocking_session_id,
       datediff(s, es.last_request_start_time, GETDATE()) as elapsed_time_sec,
       er.command,
       es.login_name,
       es.host_name,
       es.host_process_id,
       DB_NAME(er.database_id) as database_name,
       esql.text AS batch_text,
       ISNULL(CASE er.statement_end_offset + er.statement_start_offset
             WHEN 0 THEN esql.text
             ELSE SUBSTRING(esql.text,
                          (er.statement_start_offset/2)+1,
                          ((CASE er.statement_end_offset
                                       WHEN -1 THEN DATALENGTH(esql.text)
                                       ELSE er.statement_end_offset
                            END - er.statement_start_offset)/2) + 1)
       END, '')
       AS statement_text,
       eplan.query_plan,
       es.program_name,
       es.host_process_id,
       es.last_request_start_time,
       es.last_request_end_time,
       ec.num_reads as num_reads_connection,
       ec.num_writes as num_writes_connection,
       es.reads as reads_session,
       es.writes as writes_session,
       es.logical_reads,
       es.transaction_isolation_level,
       es.row_count,
       er.wait_type,
       er.wait_time
from
       sys.dm_exec_connections ec
       left join sys.dm_exec_sessions es on ec.session_id = es.session_id
       left join sys.dm_exec_requests er on ec.session_id = er.session_id
       --left join sys.dm_exec_sessions es_blocking on es.session_id = er.blocking_session_id
       outer apply sys.dm_exec_sql_text(er.sql_handle) esql
       outer apply sys.dm_exec_query_plan(er.plan_handle) eplan
where es.status <> 'sleeping'
order by ec.session_id

Here I am first checking rows with blocking_session_id is not null. Then from this blocking_session_id, finding it is also block by others and so on. So, let's say I have,
| -------------------- |
| Spid | Blokcing_Spid |
| -------------------- |
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | NULL |

Here 4 is the culprit. How can I quickly find the culprit spids?

Check sp_whoisactive by Adam Machanic - http://whoisactive.com/
It has @find_block_leaders parameter.


MYSQL: How to improve performance of inserting over 1M rows to a table with over 100M indexed rows I have this mysql table:

CREATE TABLE `codes` (
  `code` bigint(11) unsigned NOT NULL,
  `allocation` int(11) NOT NULL DEFAULT '0',
  `used` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`code`),
  KEY `allocation` (`allocation`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When it is fully up and running it will hold anywhere between 100 million to 300 million codes that have been randomly generated from a number between 1 and 10 Trillion.

To fill the table I have this stored procedure:

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_codes_v4`(
    IN bf_codes_to_generate BIGINT,
    IN bf_lower_limit BIGINT,
    IN bf_upper_limit BIGINT,
    IN bf_allocation_num INT
)
BEGIN

    SET @Codes = bf_codes_to_generate;
    SET @Lower = bf_lower_limit;
    SET @Upper = bf_upper_limit;
    SET @Allocation = bf_allocation_num;

    SET @qry_rand = 'SELECT ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) INTO @Random';
    PREPARE qry_rand_stmt FROM @qry_rand;

    SET @qry_insert = 'INSERT IGNORE INTO `codes` (`code`,`allocation`) VALUES ( @Random, @Allocation )';
    PREPARE qry_insert_stmt FROM @qry_insert;

    START TRANSACTION;

    WHILE @Codes > 0 DO

        EXECUTE qry_rand_stmt;
        EXECUTE qry_insert_stmt;

        SET @Codes = @Codes - ROW_COUNT();

    END WHILE;

    COMMIT;

    DEALLOCATE PREPARE qry_rand_stmt;
    DEALLOCATE PREPARE qry_insert_stmt;

END;;
DELIMITER ;

What this does is pick a random number between the given bounds and insert it into the table.

We currently use this stored procedure to insert anywhere between 500K to 5M rows at a time. While it works it starts to get very slow as more rows already exist in the table.

Once we get to 10M rows already in the table the generation process slows to about 1000 rows per second. As we ultimately plan to store 100M to 300M codes in this table the insertion process will take even longer at that point. Basically this table is not scaling well.

Is there anything that can be done to make this process scale better?

Here are some answers to questions I think you might ask

Q: Why an index on allocation column?
A: Each time a batch of rows are inserted we give it an allocation number. We need to be able to quickly get allow rows out that have a given allocation number.

Q: Why the use of a transaction?
A: Apparently this stops the index constantly been flushed to disk while inserting codes and in our testing speeded inserts up significantly. Also, while not implemented yet, we would like to be able to put a kill switch in place that can cancel a batch insert at any point in time.

Q: Why don't you split the table into multiple tables for example 1-1T goes into table one, 1T-2T goes to table two and so on?
A: We may have to look into doing this but I'd like to see if what we have now can be improved.

Q: Is there anything else we should know?
A: This table will be constantly been used as a lookup to check if codes exist and if they have been used and will be SELECT heavy. Any solution mustn't block this table from being read from and try and not slow its read performance too much.

If you have access to the server file system, I would suggest that you script (Perl, PHP, C++, etc.) the number generation into a flat file and perform a LOAD DATA INFILE operation.

Typically LOAD DATA INFILE performs faster than repeated INSERT statements for larger row sets and can also handle the IGNORE clause. Have a look at this answer regarding the bulk_insert_buffer_size variable, which is important when doing bulk inserts, should you elect to go with the LOAD DATA INFILE option.


MySQL Enums have non-intuitive behaviour, why? This very helpful article explains some oddities about how MySQL Enums behave: http://melp.nl/2009/05/mysql-enums-and-booleans/

There is no discussiong about why that behaviour exists. Furthermore, in googling the subject I find only contradictory information and speculation, by 'bloggers' and other blind leading the blind.

Can someone please describe why MySQL Enums suffer from the behaviour outlined in the post. Here is one example of Enum oddities presented in the post, but there are many more:

mysql> insert into t values('1'),('0');
mysql> select b,b=1,b=0 from t;
+---+-----+-----+
| b | b=1 | b=0 |
+---+-----+-----+
| 1 |   0 |   0 |
| 0 |   1 |   0 |
+---+-----+-----+
2 rows in set (0.00 sec)

The SELECT in your example should be consistent with the INSERT with the way it uses quotes. The following works:

select b,b='1',b='0' from t;

Here's proof.

MySQL's enum documentation explains that an enum is an index of strings - every entry has both a numeric index and a string value. The SELECT query in your question is using the numeric index, and the one I've provided is using the strings as you inserted them.

Also noted in the documentation are that the first value always has an index of 1, and that the index 0 always resolves to an empty string (''). An enum of ('0','1','3','Banana','2') would therefore look like this:

Index = 'Value'
    0 = ''
    1 = '0'
    2 = '1'
    3 = '3'
    4 = 'Banana'
    5 = '2'

In the above example - as well as your original example - select b,b=1,b=0 from t; will look up the indexes 0 and 1, which resolve to the values '' and 0 respectively.

You will almost never need to use the indexes when working with enum data - make sure you use the string values, and put quotes around them every time.

As a general rule, don't use enum types to store numbers. The risk of confusion is extremely high, and if you really want to restrict the values, there are other appropriate tools, such as lookup tables with foreign keys.


How to return a CTE as REFCURSOR from an Oracle stored procedure? I tried to modify an Oracle stored procedure to use a CTE instead of a simple Select Statement.

My procedure looked like this:

Create or replace Myproc ( MyRefCursor  IN OUT SYS_REFCURSOR)
as
begin
    Open MyRefCursor for
        Select * from ...something ...;

end;
/

I rewrote this query:

   Select * from ...something ...;

As this CTE:

with MyCTE As (
    ... ;
)
Select * from MyCTE;

Opening a cursor this way does not work:

Open MyRefCursor for
with MyCTE As (
    ... ;
)
Select * from MyCTE;

Nor does this:

with MyCTE As (
    ... ;
)
Open MyRefCursor for
Select * from MyCTE;

Since the CTE is part of the same SQL statement, it should not contain a semicolon.

So, there should be no semicolon on the second line of the third block or the third line of the fourth block.  The fifth block does not have a contiguous SQL statement.


mongodb could not find host matching read preference { mode: \"primary\" } for set? I am deploy a monodb sharding, I have deploy a replica set in three machines:

dev41:27017,dev42:27017,dev193:27017

and configsvr in three machine:

dev41:27019,dev42:27019,dev193:27019

and also a mongos in machine:

dev41:28000

at last I try to add my replica into mongos:

mongo dev41:28000/admin
sh.addShard('rs0/dev41,dev42,dev193')

then I got errors:

{
    "ok" : 0,
    "errmsg" : "could not find host matching read preference { mode: \"primary\" } for set rs0",
    "code" : 133
}

I faced the same issue then looked into Mongos log file and it was saying

can't authenticate to hostname as internal user, error: Authentication failed.

Please see here: internal user authentication and happens with SecKey file and auth- Enabled.
Resolution
I opened mongod.conf file and
security: authorization: enabled
keyFile: /var/run/mongodb/secKey.key

was missing so I added that and restart the mongod. Then I created shard again from Mongos and it is working now.


User Quota Default Oracle I am sure I should either know this or have been able to determine this, but I am investigating an issue with user allocated space quotas and am wondering how to determine the default (if there is one).

Our users are running into a problem with a database set up by another individual and apparently none of them were set up with specific quotas.  Is there some default quota that users are set to and if so, how can I determine what that is or change it.  At this point, I am setting them individually, but was not sure why they were never established originally.

Tablespace Quotas for a User

  ...

  By default, a user has no quota on any tablespace in the database.

  ...


Executing sys.dm_fts_parser without sysadmin server role A web application needs to execute sys.dm_fts_parser to discover which words SQL Server Fulltext Search used to match documents for the purpose of syntax highlighting.  That stored procedure can only be executed if the caller is in the sysadmin server role.

  Permissions

  Requires membership in the sysadmin fixed server role and access rights to the specified stoplist.

http://msdn.microsoft.com/en-us/library/cc280463%28v=sql.105%29.aspx

Since it would be most unwise to grant that server role to the web server's user, I attempted to create a stored procedure that runs as a separate user that is in that role.

create procedure usp_fts_parser
    @query nvarchar(max)
with execute as owner
as
select display_term from sys.dm_fts_parser(@query, 1033, 0, 0);
go

I then created a user login_sign_fts_parser, made it the owner of the stored procedure

  alter authorization on usp_fts_parser to login_sign_fts_parser

and attempt to allow the web server's DB user rights to impersonate that user:

  GRANT IMPERSONATE ON USER::[IIS APPPOOL\Strategic Window] TO login_sign_fts_parser

When I attempt to execute usp_fts_parser, I receive the error:

  Cannot execute as the database principal because the principal "login_sign_fts_parser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Why do I get this error?  How can I accomplish my goal of executing sys.dm_fts_parser without granting undue permissions to the web server's DB user?

The problem is with the difference between logins and users.  When you are granting the permissions you are working with a user.  Only a login can have server level permissions such as sysadmin.  I discussed this here if you are interested.

In the mean time I can tell you how to do what you need, however it's not the best idea in the world.

First create a seperate database, make SA the owner of that database, set the trustworthy property on for that database.  Then create your usp_fts_parser stored procedure with EXECUTE AS OWNER.  In this case OWNER is dbo.  Also in this case dbo is SA.  Because you made the database trustworthy it will allow users in it to access server level permissions.  All of this means that your stored procedure will be able to act as a sysadmin.  Next grant the login that you are using for the web application connect permissions to your new database.  Then grant that user execute permissions on the stored procedure.

This is very important.  ONLY grant the user execute permissions on the stored procedure.  You have created a big security hole by turning on trustworthy with a database owned by a sysadmin so you need to be very very very careful who you give permissions to that database and what you allow them to do.


Access denied for user 'root'@'localhost' using Password NO I have enabled SQL SLOW LOG Queries in my.cnf file but i need to restart mysql and when i try to run this command

sudo /etc/init.d/mysql restart

for restarting it throws this error.

Stopping MySQL database server: mysqld failed!
Starting MySQL database server: mysqld already running.
Warning: World-writable config file '/etc/mysql/debian.cnf' is ignored
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

[Image] :

It's due to incorrect permissions on /etc/mysql/debian.cnf file.

Do a long-listing of that file first to check current permissions:

sudo ls -l /etc/mysql/debian.cnf

If group or other fields has write permission enabled, run:

sudo chmod 644 /etc/mysql/debian.cnf

and then restart mysql:

sudo /etc/init.d/mysql restart


Is there a better way to exclude all "not actively created by a human" indexes than what I'm doing? My goal is to list the indexes which I, a human, have actually added into my database. I don't want to see ones that were created by PG when creating PKs and whatnot. I didn't even know it did that, but it sure seems to do. A lot. It's not obvious how to sort out all those, since there is no column such as "was_created_automatically_by_pg". I started with:
SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes;

That causes tons of "junk" records to be returned with the schemaname "pg_catalog". So, ignoring those:
SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname <> 'pg_catalog';

I still get a lot of indexes which I never created.
SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname <> 'pg_catalog' AND indexdef NOT LIKE 'CREATE UNIQUE INDEX %';

Now, all the resulting records seem to correspond to indexes which I remember having created, some with names such as "djhhcdhxchhhsxhsddf" and some with names that were auto-created, based on the columns it involves. Not sure how I made those since pgAdmin 4 refuses to let me create an index without manually naming it. (Which is quite annoying, might I add.)
However, what if I added a "UNIQE INDEX" myself? It feels like my own "final" query is "hiding" some records this way. It doesn't seem reliable.
How come they didn't have a column as I described above? Or is this actually pgAdmin 4 creating the indexes automatically? Is it actually possible to create a PRIMARY KEY without an internal index on it? I don't understand why anyone would ever want to create a PK which is not unique and indexed. That's the whole point of the field... to be unique and enable fast FKing from other tables.
It makes me anxious to see how many indexes there are which I never thought of. I assume that I shouldn't touch them, ever, but I always assumed that the PKs and whatnot had "internal" indexes which are never mentioned/displayed/returned.

This will return a list of indexes that are used to implement a constraint:
SELECT conindid::regclass
FROM pg_constraint
WHERE contype IN ('p', 'u', 'x');

Exclude these indexes from your query result, and you have thr list you desire.


How should I back-up an application database during a release? During an application release it is common that changes are made to the associated database schema and potentially the data it contains.  Should something go wrong with the application release, the changes made to the schema (and data) will need to be rolled back.

To throw a spanner in the works, there are multiple applications with their own schemas running on the same database instance.

What is the best way to create a back-up that would allow the application schema to be rolled back leaving all other schemas unaffected?

Use the expdp to dump the schema. When needed you can later import the schema back with impdp.


HTML column in a full text index? Using sql server management studio, I want to create a full text index where a certain field is treated as html, tags should be ignored and umlaut-codes should be taken into account. How can I do that?

From at least SQL Server 2008 R2 SQL Server comes with an understanding of HTML.

http://msdn.microsoft.com/en-us/library/ms142499.aspx says: "
SQL Server installs its own XML and HTML filters. In addition, any filters for Microsoft proprietary formats (.doc, .xdoc, .ppt and so on) that are already installed on the operating system are also loaded by  SQL Server."

You mention umlauts, but not what you want to do with them.  Umlauts and other characters that may seem unusual to English readers, can be managed by choosing the proper collation for the Index. Also the chosen Word Breaker (Neutral, English, French, etc) will affect your results.


Exclude rows with unique values in one column from grouped mysql query The following table represents interactions form users with different projects:

id         user_id    project_id
17373      6336       1001
17374      6336       1001
17375      6336       1001
529044     6336       1001
17305      6342       1003
17350      6318       1003
17388      6318       1003
811062     6342       1003
811063     6342       1003
1552793    6342       1003

I want to see which users are interacting with each other in the same projects. In other words, I would like to exclude

rows where a project_id is associated with only one user (row 1-4), because there are no interactions then.
and duplicate rows (last 4 rows), because I only want to know if there is an interaction or not (the intensity does not matter to me).

The query should result in:

id         user_id    project_id
17305      6342       1003
17350      6318       1003

Showing that user 6342 and 6318 are interacting in project 1003.

To achieve that I would start with grouping the results:

SELECT id, user_id, project_id
FROM t
GROUP BY user_id, project_id

Result:

id         author_id  project_id
17268      6336       1001
17350      6318       1003
17305      6342       1003

How can I now exclude the results with a unique project_id (row 1)? Adding HAVING COUNT(*) > 1
does not work.

This is the desired result:

id         author_id  project_id
17350      6318       1003
17305      6342       1003

You can use COUNT(DISTINCT xxx) in this way:

select distinct user_id, project_id
from   t
where  project_id in (select   project_id
                      from     t
                      group by project_id
                      having   count(distinct user_id) > 1);

user_id | project_id
------: | ---------:
   6342 |       1003
   6318 |       1003

db<>fiddle here


Solutions to migrate databases I'm working in redesign an application. This application has a MySQL database and has approximately 50 tables (there are no relations , there are not standars, etc). We want to migrate to Postgresql with some tables, some tables will not migrate, some will be residing totally and so more changes. My question is : Which techniques are recommend for this task ? Are there any tools for these job? and How can I do these in a efficient way?

Assuming your are able to code I'd write a conversion tool which reads from one database and writes into the other. That way you'll be able to do any conversion you like, do additional checks on the data and you could even have the program test the result. Just pick your favorite language. Any tool will still require you to define all the conversions one way or another, you'll still have to test etc. So the overall gain of a special to won't bee that big, and you'll have to learn it first. Assuming it's a one-off it probably simply isn't worth the effort to find a learn a new tool.

If you are planning to do these kind of conversions more often that might change though.


Can anyone help me in fixing the issue (terminating the instance due to error 474) and then Instance terminated by PMON I get these kinds of errors in my oracle logs many times leading to the abnormal shutdown of the oracle instance.

The alert log file contains the logs mentioned below:

Fri Feb 28 22:05:09 2020
Time drift detected. Please check VKTM trace file for more details.
Fri Feb 28 22:05:12 2020
System state dump requested by (instance=1, osid=26083 (PMON)), summary=[abnormal instance termination].
Fri Feb 28 22:05:12 2020
PMON (ospid: 26083): terminating the instance due to error 474
System State dumped to trace file /ora01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_26097.trc
Fri Feb 28 22:05:14 2020
ORA-1092 : opitsk aborting process
Fri Feb 28 22:05:14 2020
ORA-1092 : opitsk aborting process
Dumping diagnostic data in directory=[cdmp_20200228220512], requested by (instance=1, osid=26083 (PMON)), summary=[abnormal instance termination].
Instance terminated by PMON, pid = 26083
Sat Feb 29 08:40:40 2020
Starting ORACLE instance (normal)

I can't see your trc file to confirm, but it sounds an awful lot like this

https://support.oracle.com/knowledge/Oracle%20Database%20Products/1361872_1.html

Instance Terminated With Error ORA-00474: SMON Process Terminated With Error (Doc ID 1361872.1)
Last updated on JULY 23, 2019

If you login to the MOS system, you'll see the the cause is due to a corrupted INDEX. A diagnostic and remediation script is provided to verify and fix the problem.

Any time you get a message like this or a ORA-600 message, the first place to go is My Oracle Support.


Connect to a local PostgreSQL server using peer auth with pgAdmin Context

I have a PostgreSQL server running on a remote machine, but I'm connected locally through VPN.

Its pg_hba.conf file is accepting peer auth for local connections;

local   all             postgres                                peer

I don't like to change this.

If I follow what is written here: https://documentation.help/pgAdmin3/connect.html ;

  The host is the IP address of the machine to contact, or the fully qualified domain name. On Unix based systems, the address field may be left blank to use the default PostgreSQL Unix Domain Socket on the local machine, or be set to an alternate path containing a PostgreSQL socket. If a path is entered, it must begin with a “/”. The port number may also be specified.

I got this error on pgAdmin3:

I even tried not to leave the Host field blank but to write the name of the socket file in there: /var/run/postgresql -> the behaviour is the same.
And if I insert postgresql:// instead, I get;

An error has occurred:

07:55:28: Error: Error connecting to the server: could not translate host name
"postgresql://" to address: Name or service not known

And pgAdmin4 doesn't let me have the Host field blank:

Until there, I was logged in as the normal Ubuntu user.

So I decided to try while being postgres Ubuntu user, just like when I want to run a pg_* command using peer auth (e.g. $ sudo -u postgres pg_* -U postgres bla bla):

pgAdmin3:

$ sudo -u postgres pgAdmin3
Client is not authorized to connect to Server07:44:11: Erorr:
Unable to initialize GTK+, is DISPLAY set properly?

pgAdmin4:

$ sudo -u postgres pgAdmin4
QStandardPaths: XDG_RUNTINE_DIR not set, defaulting to '/tmp/runtime-postgres'
Client is not authorized to connect to Serverqt.qpa.screen: QXcbConnection:
Could not connect to display :1.0
Could not connect to any X display.

Next I decided to check this out on my local machine in order to by-pass these display related errors

pgAdmin3:

$ sudo -u postgres pgadmin3
No protocol specified
07:49:51: Error: Unable to initialize GTK+, is DISPLAY set properly?

I do not have pgAdmin4 on my local machine, but I guess I will encounter some same kind of error.

Question

How can I log to the local PostgreSQL server which is based on a peer authentication using pgAdmin3 or pgAdmin4?

Update:

These three new lines were added to the log file in /var/log/postgres/postgresql-12-main.log after the unsuccessful connection using pgAdmin3:

2020-04-16 16:34:55.548 CEST [17842] postgres@postgres LOG:  provided user name (postgres) and authenticated user name (ubuntumainusername) do not match
2020-04-16 16:34:55.548 CEST [17842] postgres@postgres FATAL:  Peer authentication failed for user "postgres"
2020-04-16 16:34:55.548 CEST [17842] postgres@postgres DETAIL:  Connection matched pg_hba.conf line 85: "local   all             postgres                                peer"

Make sure you have the OS user, which you have used to launch PGAdmin3 or PGAdmin4, is in the database.
For creating an OS user in the database, you can use following command:

sudo -u postgres psql -c "CREATE USER $(id -nu;" postgres

In HOST name address include the Postgres unix_socket_directories
You can use following command to get the unix_socket_directories setting

sudo -u postgres psql -c "SHOW unix_socket_directories;"


Triggering a shell script from MySQL without any 3rd party libraries Is it possible to call a bash script file in linux using a trigger in MySQL?
I've been searching for the answer online and I ran into this articles:

http://patternbuffer.wordpress.com/2012/09/14/triggering-shell-script-from-mysql/

http://crazytechthoughts.blogspot.com/2011/12/call-external-program-from-mysql.html?showComment=1416010000049#c2028082587789719436

But I was wondering if it's possible to do it without any 3rd party libraries like UDF for example. Any help is appreciated.

Nothing native unless using the MySQL Client where you can issue \! {shell command}.

e.g.

mysql> \! pwd
/home/moore


Unable to change engine to MyISAM in MySQL I have  installed MySQL 5.6.10 on Mac OS X 10.6.5. My issue is that MySQL is using InnoDB as its default engine.

I have checked following engines are supported in MySQL:

show engines\G

*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO

I want to change it to MyISAM, so I have changed the setting in my.cnf:

[mysqld]
default-storage-engine=MyISAM

After that I restarted MySQL from the system preferences. But if I check check the engines again and create a table, it is still being created with InnoDB, not MyISAM.

Can anyone tell me how would I change it to MyISAM?

Here is my my.cnf file content:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
default-storage-engine=MyISAM

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# sql_mode=STRICT_TRANS_TABLES

MySQL 5.6.10 is quite new to me as well, so bear with me on this answer

I installed MySQL 5.6.10 on a Linux VM

I added default-storage-engine=MyISAM to my.cnf and restarted MySQL

It went from this:

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

to

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

No problem.

If you placed

default-storage-engine=MyISAM

in my.cnf, restarted MySQL and still the default is InnoDB, then there is only one explanation: You placed default-storage-engine=MyISAM in the wrong part of my.cnf

I once caught a mistake like that in a ServerFault question : Set group_concat_max_len permanently (MySQL config)

Make sure the option is under the [mysqld] section of my.cnf

[mysqld]
default-storage-engine=MyISAM

SUGGESTION

I think MyISAM in MySQL 5.6.10 is run as a plugin. Check your error logs for any plugin errors.

Also, do this:

In one SSH session, tail -f /var/log/mysqld.log

In another SSH session, service mysql restart

Look for any plugin messages associated with MyISAM. For example, note that when I ran a shutdown I saw these messages:

2013-02-26 15:20:13 1725 [Note] InnoDB: Starting shutdown...
2013-02-26 15:20:15 1725 [Note] InnoDB: Shutdown completed; log sequence number 1600309
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'CSV'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'MEMORY'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'MRG_MYISAM'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'MyISAM'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'sha256_password'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'mysql_old_password'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'mysql_native_password'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'binlog'
2013-02-26 15:20:15 1725 [Note] /usr/sbin/mysqld: Shutdown complete

130226 15:20:15 mysqld_safe mysqld from pid file /var/lib/mysql/chtn-web2v.pid ended

Note that MyISAM is a plugin. If you do not see anything like

2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'MyISAM'

your MyISAM plugin may have an issue with the Mac OS X version of MySQL.


Is there a MySQL maximum database or table count? I am in the process of creating a massive archive of bitboards stored in MySQL.  In my schema,  there are four linked sets of bitboards which--for query sake--are represented in four different tables.  Each of the four sets will have ~100k rows and I expect there to be about 35k of these sets.

My question is about the performance ramifications and limitations of the SQL server itself. I have considered the following models:

35k databases, each with four tables of 100k rows each
1 database, four named tables for each set, e.g., db948-a, db948-b, db948-c, db948-d

Additional information:

These tables are in no way linked to eachother; they do not need to be joined at any point.
They do not need to be UPDATED at any point (one-time INSERT for each of the four tables) and only SELECTs from then on.
This will be stored on ReiserFS (or comparable FS with no file-count concerns)
I expect to use MyISAM (no transactional support needed)

So my main concern is about MySQL limitations on table and database counts--given the above information, is there any limitation I'll exceed that would disqualify one of the schemas?

In addition, are there any glaring performance issues that would arise from either the 35k number of distinct databases, or 140k tables in one database?

Thanks!

Let's look at many perspectives
PERSPECTIVE #1 : SELECTs against MyISAM tables
If you are doing only SELECTs, you need to run this query:
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will recommend the correct size for the MyISAM Key Cache. This maxs at 4GB for 32-bit. You can go higher on 64-bit machines. Yet, use common sense based on the amount of RAM the DB Server has. This should be a concern for you because only the pages from the .MYI are cached. Any thing from the .MYD must be read from disk over and over again.
If the amount of RAM recommended from this query far exceeds the amount of RAM installed by orders of magnitude, just set key_buffer_size to 4G and call it a day on this perspective.
PERSPECTIVE #2 : Leveraging Different Row Formats for MyISAM tables
Altering MyISAM tables to use a FIXED row format can increases overall performance for SELECTs. Why ?
Since CHAR fields require less string manipulation because of fixed field widths, index lookups against CHAR field are on average 20% faster than that of their VARCHAR counterparts. This is not any conjecture on my part.
The book MySQL Database Design and Tuning performed something marvelous on a MyISAM table to prove this. The example in the book did something like the following:
ALTER TABLE tblname ROW_FORMAT=FIXED;

This directive forces are VARCHARs to have the fixed amount of space allocate like that of a CHAR. I did this at my previous job back in 2007 and took a 300GB table and sped up index lookups by 20%.
Of course, by changing the ROW_FORMAT to FIXED, you will be double the MyISAM table's size in the worst case scenario. If you choose to do this, make sure you have sufficient diskspace.
PERSPECTIVE #3 : Limits on the Number of Open Files from mysqld's vantage point.
Whenever you startup mysqld, many variables are set based on /etc/my.cnf. The rare exception is the option open_files_limit. If my.cnf does not have this setting, mysqld will attempt to compute the best number for this option such that mysqld can still operate and leave whatever files handles available to the OS.
You can set this number higher strictly at your own risk.
PERSPECTIVE #4 : Disk Considerations
Since your storage engine of choice is MyISAM, you must have fast reading disks since data pages are never cached. Please use RAID10 sets. They are easier to do disk maintenances on than other setups. You can could also go with SSD drives. Whatever you choose, prepare for aging disks and periodic disk maintenances. Make sure your RAID cards have the latest firmware upgrades.
SUMMARY
All the information you supplied must be examined from these 4 perspectives. You must strike a good balance and may have to make concessions on your choices based on either budget, available hardware, amount of diskspace, and overall limitations of MyISAM that you have not anticipated.


Copy few rows from one database to another I have two PostgreSQL databases on two different machines and the network connection is slow.
I would like to copy few selected rows from the first instance to the second. Unfortunately, the full dump of single table is too big. A CLI solution would be fine.

Off the top of my head, here are a few ways to do it without resorting to 3rd-party tools.

If you are using PostgreSQL 9.3, you can use the extension postgres_fdw.

If you are using an older version of PostgreSQL have a look at dblink

If you want a command-line only solution, and you are using Linux (or Cygwin etc), you could try something like the following:

psql -d yourdb -qtAXc "COPY (select col1, col2, col3 from your_table where id between 123 and 456) TO STDOUT CSV HEADER" -U youruser -h localhost -p 5432 \
| psql -d yourremotedb -U youruser -h your_remote_host -qtAXc "COPY yourtable FROM STDIN CSV HEADER"