Crazy automatic float rounding in MySQL 5.7 My exact MySQL version is 5.7.29-0ubuntu0.18.04.1.
And I'm beginning to think that I'm getting crazy...

Just try these queries:

CREATE TABLE `float_bug` (
  `v` FLOAT NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO float_bug VALUES(92160596);

SELECT * FROM float_bug;

Returns 92160600.

Then I tried these:

SELECT * FROM float_bug WHERE v = 92160596;
SELECT * FROM float_bug WHERE v BETWEEN 92160596 AND 92160600;

Both return nothing!

However this

SELECT * FROM float_bug WHERE v = 92160592;

Returns 92160600.

I'm ready to throw my computer into the wall, but unfortunately that won't help because I have exactly the same behaviour on my remote server and I can't throw it into the wall physically.

Any ideas what is going on here?

This is not just a SQL thing: floating-point numbers should always be considered approximations in any language. See https://floating-point-gui.de/ amongst other references.

You can use the DOUBLE type instead which would help, as that can represent integers up to 53 bits in length accurately, but you would have problems later with bigger numbers.

Unless you absolutely must have floating-point numbers, these types should be avoided. Use integer (int, bigint, ...) or fixed-point types (decimal, numeric) instead. As you don't say what you are using these numbers to represent we can't make a more specific recommendation.


Debugging mode in MySQL Workbench When running queries in MySQL workbench, some of them are captured to the log file at either:

MySQL/Workbench/logs
MySQL/Workbench/sql_history

However, I'd like to see ALL the possible queries that MySQL workbench does. Is there a way to do this on the client-side, i.e., where MySQL workbench is running? For example, it would be nice to see how these graphs are being generated and the system data is being grabbed, etc.:

To see all queries sent to a server check the general/slow query log (it must be activated for that server, of course).
Alternatively, for these graphs check the source code of MySQL Workbench. Use the labels below the graphs to find a location in code. For example the "InnoDB Buffer Usage" graph is created in plugins/wb.admin/Frontend/wb_admin_monitor.py. Study the code to learn which variables are queried to get series of values.


MySQL Update/Insert trigger auto-increment over group by MySQL Update/Insert trigger auto-increment over group by

I have a form in which a user can click on a flag (=1 if yes or 0 if not) to create a receipt number (receiptno). I want this to be automated through the database - thus triggers came to mind.

I want to create an auto-increment over a group.

Schema:

 CREATE TABLE booking
    (BuchungId int, `category` int, `year` int, `flag` int, receiptno int)
;

INSERT INTO booking
    (BuchungId,`category`, `year`, `flag`, receiptno)
VALUES
    (1, 1, 2016, 1, NULL),
    (2, 1, 2017, 1, NULL),
    (3, 1, 2017, 0, NULL),
    (4, 2, 2017, 1, NULL),
    (5, 2, 2017, 1, NULL),
    (6, 1, 2017, 1, NULL),
    (7, 1, 2017, 0, NULL),
    (8, 1, 2017, 1, NULL)
;

I have created the body of a trigger:

    CREATE TRIGGER trigger1
        BEFORE UPDATE
        ON booking
        FOR EACH ROW
    BEGIN
      IF (new.Flag = 1) THEN

      SET New.receiptno = COALESCE(old.receiptno,0)+1;

      end IF;

end

Now I would like to update (or insert) the receiptno over a group by on category and year.

Thus eventually resulting in a table like:

(1, 1, 2016, 1, 1),
(2, 1, 2017, 1, 1),
(3, 1, 2017, 0, NULL),
(4, 2, 2017, 1, 1),
(5, 2, 2017, 1, 2),
(6, 1, 2017, 1, 2),
(7, 1, 2017, 0, NULL),
(8, 1, 2017, 1, 3)

http://sqlfiddle.com/#!9/05d7b

One option is not to have a distinct set of unique receipt numbers per category, per year. Instead, simply use the BuchungID as the receipt number (I'm assuming that value is unique in your table, and may very well be an actual autoincrement column in the actual database). This should guarantee a unique ID is already available, whether the user wants a receipt number or not.

However, I'll assume that you've got some business rule that prevents this simple solution from working, and you really need to generate distinct receipt numbers within each category/year pairing.

Simply looking at the maximum current receiptno and adding 1 doesn't guarantee uniqueness - it would be possible for two users to request a receipt number at the same time, both to check the current max for the category and year before either receiptno is updated, and thus to get the same receipt number.

My next thought was to use sequences, but there are two problems with that. First, it looks like MySQL doesn't have a sequence object. Second, since you would have to have a sequence for each category and year, you'd either have to use dynamic SQL to set which sequence to use (not allowed in triggers), or you'd have to hard-code all the sequence names into your query (complicating the query significantly, and forcing you to update it any time a new category or year is added).

However, we can get around that by using a sequence table. This is a table that has a row for each "sequence" you want, with column(s) that identify the sequences (category and year, in your case), and others that define how the sequence works (starting value, ending value, increment, etc.).

I found an article online that provides the code to set this all up, including some options I hadn't thought of.

To make things best fit your situation, I'd modify their code as follows:

Instead of naming the sequences, identify them by category and year.
When using the nextval function, have it automatically create a new sequence if there isn't one for the category and year you pass in.

(Note: the following code is untested, and it's just my tweaks of the code in the article)

CREATE TABLE `sequence_data` (
    `sequence_category` int NOT NULL,
    `sequence_year` int NOT NULL,
    `sequence_increment` int(11) unsigned NOT NULL DEFAULT 1,
    `sequence_min_value` int(11) unsigned NOT NULL DEFAULT 1,
    `sequence_max_value` bigint(20) unsigned NOT NULL DEFAULT 18446744073709551615,
    `sequence_cur_value` bigint(20) unsigned DEFAULT 1,
    `sequence_cycle` boolean NOT NULL DEFAULT FALSE,
    PRIMARY KEY (`seq_category`, `seq_year`)
)

DELIMITER $$

CREATE FUNCTION `nextval` (`seq_category` int, `seq_year` int)
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN
    DECLARE cur_val bigint(20);

    SELECT
        sequence_cur_value INTO cur_val
    FROM
        sequence_data
    WHERE
        sequence_category = seq_category
    AND
        sequence_year = seq_year
    ;

    IF cur_val IS NOT NULL THEN
        UPDATE
            sequence_data
        SET
            sequence_cur_value = IF (
                (sequence_cur_value + sequence_increment) > sequence_max_value,
                IF (
                    sequence_cycle = TRUE,
                    sequence_min_value,
                    NULL
                ),
                sequence_cur_value + sequence_increment
            )
        WHERE
            sequence_category = seq_category
        AND
            sequence_year = seq_year
        ;
    ELSE
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM sequence_data
                       WHERE sequence_cateogry = seq_category
                       AND   sequence_year = seq_year
                      )
        BEGIN
            INSERT INTO sequence_data (sequence_category, sequence_year, sequence_cur_val)
            VALUES (seq_category, seq_year, 2);
            SET cur_val = 1;
        END
    END
    END IF;

    RETURN cur_val;
END$$

In your trigger, you'd use this:

SET New.receiptno = nextval(`category`, `year`);

It's important to note that, as written, sequence_cur_val is actually the next value that the sequence will assign, not the last value it did assign. So, when you query sequence_data and see a current value of 71, then the highest assigned value for that category and year is 70. Also, if the current value goes over the maximum value, then it is set to NULL (or, if you change the default setting of sequence_cycle, to the minimum value for the sequence - in your case, you definitely don't want to do that). So, you should probably add a check to see if the function returns NULL, and error out appropriately; otherwise, no receipt number will be set.

Please read the article (and the comments below it) for more details on how this is all meant to work.

Also note that the function must be run inside a transaction, to ensure that you don't get duplicate sequence numbers. If it's only going to be run inside a trigger, then you may not need to explicitly start a transaction inside the function itself.

Actually, I'd be inclined to rearrange the nextval code a little, to make sure we've done our update or insert (which should lock down the value to our session) before I try to set the value. I believe that version would look like this:

DELIMITER $$

CREATE FUNCTION `nextval` (`seq_category` int, `seq_year` int)
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN
    DECLARE cur_val bigint(20);

    IF NOT EXISTS (SELECT 1 FROM sequence_data
                   WHERE sequence_cateogry = seq_category
                   AND   sequence_year = seq_year
                  )
        INSERT INTO sequence_data (sequence_category, sequence_year, sequence_cur_val)
        VALUES (seq_category, seq_year, 2);
    ELSE
        UPDATE
            sequence_data
        SET
            sequence_cur_value = IF (
                (sequence_cur_value + sequence_increment) > sequence_max_value,
                IF (
                    sequence_cycle = TRUE,
                    sequence_min_value,
                    NULL
                ),
                sequence_cur_value + sequence_increment
            )
        WHERE
            sequence_category = seq_category
        AND
            sequence_year = seq_year
        ;
    END IF;

    SELECT
        IF(sequence_cur_val IS NULL, NULL, sequence_cur_value - sequence_increment) INTO cur_val
    FROM
        sequence_data
    WHERE
        sequence_category = seq_category
    AND
        sequence_year = seq_year
    ;

    RETURN cur_val;
END$$

(Again, untested code).

Hope this provides help, or at least a direction to consider.


SQL Server Express on Windows 8 isn't locking MDF database files Historically, if I go to rename a database that is currently being used by SQL Server, I get an exception.

On this Windows 8 desktop I'm in.. my primary desktop.. I'm able to MOVE SQL Server database files to a different partition.. without getting any errors.. even if the files are currently 'in use'. I don't understand WHY I can do this, I want to get an error when I go to move a file and it should be listed as being attached in SQL Server master.sys.databases.

I am also able to rename a file that is currently in use. The punchline is that I was talking to my network admin today, and he actually attempts to rename a .MDF file, in order to determine if this file is in use.

I don't like this. What I really want to be able to do is to MOVE files (using a DOS command) from one disk partition to another, but if they're currently attached to the default SQL Server instance, then don't try to move the file.

I was counting on this to work as advertised. I shouldn't be able to rename or move a file that is currently being used as an attached database in SQL Server.

Also, I'm sorry.. but I do have an non-standard hard-drive config.

Disk 0
System Reserved 350mb
D: SSD

Disk 1
C: (Primary volume, Windows 8 Enterprise, etc)

I'm specifically using this SSD solely for SQL Server Express on this machine, I have a half dozen attached databases at any one time and I'm actually working towards creating a MILLION detached databases (and occassionally re-attaching some of them casually when I need them).

SQL Express creates database with AUTO_CLOSE ON by default. Auto close databases automatically close themself (and thus release the file handles) whenever there are no sessions using the database. This works 'as advertised', you just have to read the small print:

  True for all databases when using SQL Server 2000 Desktop Engine or
  SQL Server Express, and False for all other editions, regardless of
  operating system.

If you don't like the default behavior, simply turn auto-close off on the databases:

ALTER DATABASE [<dbname>] SET AUTO_CLOSE OFF;


apply cdc to existing data warehouse I'm building an education data warehouse. I have various dimensions that i would like to track changes to i.e. DimStudent, DimClass, DimCollege, DimExamPaper etc. etc. My solution currently drops keys, truncates tables and recreates keys before loading staging tables, which then go on to load Dimension and fact tables.

Therefore, my question is what are the minimum physical changes i need to put in place to --> truncate staging tables prior to load, track changes between staging tables, dim tables and fact tables, to incrementally load rows to dim tables and fact tables? Do i need to add a table/tables to track changes for EACH dimension or fact table? I have looked at examples but there seems to be a fair amount of work for just one table. I have 11 dimensions, 4 fact tables and 22 staging tables.

seeing as no one has helped me with this i'll answer myself to help anyone who is looking at the same thing.  It looks as if to enable CDC on an existing warehouse you need to do this on a table by table basis, recommending capturing all the ETL in package per table.  Also, it is recommended to enable CDC state per table to avoid conflicts.  Recommended approach would be --> enable CDC on source DB.  Transfer incremental loads to staging DB or schema inside DWH.  Incrementally load records to Dimensions and Facts. Every example i can find on net is for one table, which is fairly useless... be much appreciated if someone can find a CDC example for multiple tables...


DDL Trigger for ALTER any object Is there a way to make a DDL trigger which fires on ALTER of any object? So far the only way I see to do this is enumerate each object type (e.g. ALTER_TABLE, ALTER_PROCEDURE, etc) when what I'd really like is something like ALTER_ANY.

DDL trigger which restricts ALTER Objects. You can add additional Alter objects which you wish to restrict.

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER Tr_DDL_AlterObjects ON DATABASE
  WITH ENCRYPTION
  FOR ALTER_TRIGGER, ALTER_TABLE, ALTER_PROCEDURE, ALTER_SCHEMA,
    ALTER_CERTIFICATE, ALTER_FUNCTION, ALTER_APPLICATION_ROLE,
    ALTER_DATABASE, ALTER_INDEX, ALTER_ROLE, ALTER_VIEW,
    ALTER_XML_SCHEMA_COLLECTION
AS
BEGIN
    PRINT 'You do not have sufficient ALTER Privilege !';
    ROLLBACK;
END;

GO
ENABLE TRIGGER Tr_DDL_AlterObjects ON DATABASE;
GO


Overlap of group of scripts I have a group of scripts set to run in a certain order, but sometimes I find the group running long and overlapping into the next group run.   Is there a way to prevent that?

group.sh:
script1.sh &
wait
script2.sh &
wait
script3.sh

group.sh is still running (perhaps on script3.sh) when group.sh kicks off for the next run.

I'm assuming this is a Unix question, and you're not using OEM Cloud Control etc. I tend to do this by using a temporary file (with a known name) that indicates whether the script is still running or not. For example:

#!/bin/bash

if [ -f /tmp/groupisrunning ] ; then
  # do not execute if the process is already running. This is controlled by the
  # the existence of the above file

  echo "Already running"

else

  touch /tmp/groupisrunning

   # DO YOUR WORK BELOW THIS LINE, eg:
  script1.sh
  script2.sh
  script3.sh
  rm -f /tmp/groupisrunning
fi

There are other ways of doing this (ps parsing, using a service), but I find this simple & reliable.


Cannot Reset MySQL password I have been stuck on this for a long time, I tried to follow the instructions located here: https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html. When I run the commands I get this as my output:
2021-01-01T09:15:04.445377Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.22) starting as process 19920
2021-01-01T09:15:04.487135Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-01-01T09:15:04.901676Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-01-01T09:15:05.135403Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
2021-01-01T09:15:05.240496Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-01-01T09:15:05.242979Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-01-01T09:15:05.325105Z 0 [System] [MY-010931] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.0.22'  socket: ''  port: 3306  MySQL Community Server - GPL.
2021-01-01T09:15:45.594^C297Z 0 [System] [MY
-013105] [Server] C:C:\Program Files\MySQL\MySQL Server 8.0\bin>\Pro
gram Files\MySQL\MC:\Program Files\MySQL\MySQL Server 8.0\bin>ySQL Server 8.0\bin\mysqld.exe: Normal shutdown.
2021-01-01T09:15:46.281364Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.22)  MySQL Community Server - GPL.
mysqld --init-file=C:\Users\user\text.txt --console
2021-01-01T09:15:51.297226Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.22) starting as process 21652
2021-01-01T09:15:51.320019Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-01-01T09:15:51.784233Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-01-01T09:15:52.015722Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
2021-01-01T09:15:52.118175Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-01-01T09:15:52.126894Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-01-01T09:15:52.205977Z 0 [System] [MY-010931] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.0.22'  socket: ''  port: 3306  MySQL Community Server - GPL.

I am not sure what to do from here because I do not think it is on the tutorial. Any help solving this would be very appreciated. Thank you

Because no errors are shown (But this 2021-01-01T09:15:45.594^C297 does not look OK!), you should proceed with the next step as shown in the procedure.
Do a check if your password is changed.
If your password is not changed, please create a simple init-file (i.e. the same as is suggested in step 3), and try again.
If your password is changed, then do not forget to delete the init-file.
EDIT
:  I did a reset of my 'root' password following the procedure.
All output of this session is here, with some NOTE's in between:
First we notice that we do not know the password:
C:\Program Files\MySQL\MySQL Server 8.0>mysql -u root -p
Enter password: *******
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

C:\Program Files\MySQL\MySQL Server 8.0>

Then a mysql-init.txt file is created:
C:\Program Files\MySQL\MySQL Server 8.0>type mysql-init.txt
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Then we stop the MySQL server. My server is name MySQL80, but you can also stop it from the services window (more details here)
C:\Program Files\MySQL\MySQL Server 8.0>net stop MySQL80
The MySQL80 service is stopping.
The MySQL80 service was stopped successfully.

Password reset with:

Note the double \\ in the path names, and the " surrounding the complete path
My default settings for MySQL are located here "D:\MySQL Server 8.0\my.ini", this is almost sure different at your location

    C:\Program Files\MySQL\MySQL Server 8.0>bin\mysqld.exe --defaults-file="D:\\MySQL Server 8.0\\my.ini" --init-file="C:\\Program Files\\MySQL\\MySQL Server 8.0\\mysql-init.txt" --console
    2021-01-01T18:51:09.333120Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
    2021-01-01T18:51:09.333840Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.22) starting as process 21888
    2021-01-01T18:51:09.341869Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
    2021-01-01T18:51:09.908748Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
    2021-01-01T18:51:10.083573Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060
    2021-01-01T18:51:10.143779Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
    2021-01-01T18:51:10.144078Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
    2021-01-01T18:51:10.192745Z 0 [System] [MY-010931] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.0.22'  socket: ''  port: 3306  MySQL Community Server - GPL.

Now the server is started with output to console, to stop the server type CTRL+C:
2021-01-01T18:51:20.697066Z 0 [System] [MY-013105] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Normal shutdown.
^C
C:\Program Files\MySQL\MySQL Server 8.0>2021-01-01T18:51:21.447960Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.22)  MySQL Community Server - GPL.

We start the server again, the normal way (or via services):
C:\Program Files\MySQL\MySQL Server 8.0>net start mysql80
The MySQL80 service is starting.
The MySQL80 service was started successfully.

And we try to logon:
C:\Program Files\MySQL\MySQL Server 8.0>mysql -u root -pMyNewPass
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22 MySQL Community Server - GPL

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

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

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

mysql> alter user 'root'@'localhost' IDENTIFIED BY 'stackoverflow';

With this last command the password can be changed to 'stackoverflow'. I am sure any who read this can think of a better password.


database to use for Automated Teller Machine I have been working on an Automated Teller Machine project using Visual Studio and C# for my Bachelors Degree project.

Which database platform should I use for this?  I'm considering MySQL, but am open to other suggestions.

For projects using Visual Studio, I'd almost invariably recommend using SQL Server Express.  The product is free to use, as long as you can live with the limitations: Microsoft SQL Server Express supports 1 physical processor, 1 GB memory, and 10 GB relational storage.  I'd expect for an ATM project you won't be locally storing a lot of data, so that shouldn't be a concern.


Why does DBMS_UTILITY.COMPILE_SCHEMA break function-based indexes? After mass structure updates, some views/procedures became invalid.

So I just recompiled them with:

exec dbms_utility.compile_schema( USER )

But after that, all function-based indexes, which before were in a good state, became broken, and SQL statements against those tables failed. And that is the problem.

Example of such indexes:

CREATE INDEX DOCUMENTS_NMBR_UCN_FIDX ON DOCUMENTS(Str2Number(NUMBER_UCN));

CREATE INDEX DOCUMENTS_DATE_FIDX ON DOCUMENTS(Date2Number(REG_DATE));

Why did that happen?

Oracle is compiling all objects, so as far as Oracle is concerned all code in all objects is new code.

As a consequence, Oracle has no way of knowing that your function Str2Number still returns the same values that were used when the index was created.

Instead you should only recompile invalid objects,

exec dbms_utility.compile_schema(user, false);

will do this.


Example of query execution plan where the cardinality estimation makes a difference I am trying to create an example of a query execution plan which shows that if I have the cardinality estimation for some column of a table I can decide which query execution plan has the least cost.

So I have these two posts (1 and 2) which I can understand estimated cardinality as the number of distinct values on a column. And then I calculate the selectivity.

SELECT max(price) FROM tickets WHERE country = "CANADA";

If my table has 180 items and only 10 are CANADA. And there are only 4 different countries (CANADA, BRAZIL, USA, GERMANY). So....

the cardinality of column country é 4, because it is the number of distinct items.

The selectivity of the column country = CANADA is the number of items accessed divided by the items on the table. 10/180 = 0.0555.

But which are the different query execution plans that the optimizer could choose based on the cardinality?

If this is not a good example, could someone point one query that the cardinality would be a treasure to the optimizer in order to decide to chose one plan versus another?

Thanks,
Felipe

At 180 rows, you're not likely to see any difference whatsoever.

In order to see different plans get chosen at different predicate distributions, you'd need quite  bit more data, and likely competing data sources (indexes) to aid them.

Let's say you had 1.8mm rows in the table. If you've got

A clustered index on a column unrelated to price or country (Like Id or something)
A nonclustered index on price and country
A single column nonclustered index on country

You may start to see a difference in the method of aggregation, serial vs. parallel plans chosen, and index choice, depending on which country you search for and its selectivity.

For an example of a recent Q&A:

Large variances between two similar queries when doing WHERE EXISTS on related table

For a more complex reasons:

Why is my query suddenly slower than it was yesterday?


Is Network I/O included in the timings for "RPC complete" seen in SQL Profiler? When watching results in SQL Profiler, does the RPC Complete include time to send results to the client?  Or is it just the time the server spent gathering the records?

The reason I'm asking is I'm working on a program to move a large amount of data.  The databases are cloud hosted in Azure, and the code is (currently) running locally.  I can see the RPC call times in SQL Profiler, and the query to select the rows isn't cheap (massive table filtered by an indexed column then sorted by the table's primary key for paging results), and it isn't an insignificant amount of data (100k records per page, some situations I'm moving up to 60 million records per run).

I'm curious if I should expect it to run faster once the code is running inside Azure.  If the RPC complete timing includes the time spent for the data to be sent to the client, this should decrease some (I'm hoping dramatically).  If the RPC complete timing is just the time for the server to select the data, then I won't see much of an increase when it's running inside Azure.

Network I/O is included in the rpc_completed duration*, so I would expect you'll see improvement in the workload you've described.

I enabled TCP/IP on my local SQL Server 2016 instance, and then ran a series of queries through a .NET application that uses an ORM.  Here's a comparison of sp_statement_completed and rpc_completed Extended Events targets for the exact same queries in that test run:

Each set of two rows is the two events for that same query.

Notice that rpc_completed is significantly higher than sp_statement_completed each time, since the latter doesn't include the network time (it's just how long a statement in a batch took to complete on the server).

For laughs, I then turned off TCP/IP, so that the queries would go through the named pipes provider:

Notice that rpc_completed is still higher, but the difference is significantly less, since there's no TCP overhead going on - the "round trip" is all in-process.

*My testing was done with Extended Events, but I would suspect the same to hold true for Profiler

To be clear, network time can be included in sp_statement_completed as well in the case of "streaming plans" (thanks to David Browne for pointing this out).

Sometimes, especially simple queries with lots of results, the results aren't spooled for sending after the statement is complete. Instead the results are sent as the plan runs. This is sometimes called a "streaming plan", and it's what you'll get with a simple nested loop join or select * from a large table.


MySQL foreign key error I need to update rows in a child table,but I am getting following error while I run the code,

Company Table

CREATE TABLE `company`
(
 `Company_id` int(11) NOT NULL AUTO_INCREMENT,
 `Company_name` varchar(255) DEFAULT NULL,
 `BuildingNameNumber` varchar(255) NOT NULL,
 `Street_name` varchar(255) NOT NULL,
 `Area` varchar(255) NOT NULL,
 `Landmark` varchar(255) NOT NULL,
 `PIN_code` int(6) NOT NULL,
 `PhoneMobile_number` char(15) NOT NULL,
 `Website` varchar(255) NOT NULL,
 `Fax` varchar(255) NOT NULL,
 `Email` varchar(255) NOT NULL,
 `Upload_logo` varchar(255) DEFAULT NULL,
 `tin_number` varchar(50) NOT NULL,
 `CreatedOn` datetime DEFAULT NULL,
 `CreatedBy` varchar(255) DEFAULT NULL,
 `UpdatedOn` datetime DEFAULT NULL,
 `UpdatedBy` varchar(255) DEFAULT NULL,
 `is_active` bit(1) DEFAULT b'0',
 PRIMARY KEY (`Company_id`),
);

Service Table

CREATE TABLE `service` (
`service_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `serial_number` varchar(256) DEFAULT NULL,
  `service_name` varchar(256) DEFAULT NULL,
  `price` float(10,2) NOT NULL DEFAULT '0.00',
  `tax` float(5,2) NOT NULL DEFAULT '0.00',
  `is_active` bit(1) NOT NULL DEFAULT b'1',
  PRIMARY KEY (`service_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `service_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`Company_id`) ON DELETE CASCADE ON UPDATE CASCADE
);

When I trying to update the service table I am getting error as

**Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`etailershop`.`service`, CONSTRAINT `service_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`Company_id`) ON DELETE CASCADE ON UPDATE CASCADE)  0.031 sec**

I was trying to find the error, I do understand the foreign key constraint but I could not find what is wrong with this, similar structure and constraint tables are working fine.

You're getting this error because you're trying to update a row to service table that does not have a valid value for the company_id field based on the values currently stored in company table.

If you post some more code someone can help you diagnose the specific cause.


Oracle selective redo log shipping We are planing to setup an Oracle 12c RAC with a Logical standby in the DR site.

Since data consistency is extremely important, we our planning to setup redo log shipping to be in SYNC mode.

Our application which also runs on the Secondary site (redundant processes) requires the DB to be in write mode, hence the Logical standby option was chosen. Application on the Primary site DB with write to one tablespace (Say A). And on the secondary site it will write to a separate tablespace (Say B).

Our client's application writes to a tablespace (Say C) and needs it to be in sync across both sites.

The main idea behind doing the above is to stop large number of redo logs bring shipped to the secondary site via the WAN. Our application already has the ability to sync the inputs to the system. And since one input generates several DB inputs (and redo logs), we thought of writing to the databases independently will be the better option.

My Question is, can we configure the primary site to send selective redo logs to the logical standby. From what I know, all redo logs are shipped on-the-fly to the secondary site in SYNC mode and we can configure the logical standby to omit sqls for tablespace A.

Not possible, redo logs get transferred unprocessed. If you want to transfer changes related to just some specific objects or schemas, Streams (deprecated, but free) or GoldenGate (requires extra license) does that.

Another option for reducing network traffic is to configure redo transport compression (requires the Advanced Compression option), but this could still produce much more network traffic compared to above solutions depending on the ratio of the changes related to the replicated objects.


Creating a normalised Twitter database and foreign keys Hello I am creating a normalized Twitter DB for a project I am working on and wanted a bit of guidance on foreign keys.

I am curious about what the difference would be if for hashtags, symbols, user_mentions, urls and media, I made another field in each table called tweet_ID and the relationship changes to the primary key id in the tweet table to each tables tweet_id? The way I've modelled it means the tweets table has 5 extra fields for each tweet object.

Currently I have the schema designed as follows:

Are there glaring issues with the way I have implemented the foreign keys in the model this way?

Thank you!

So, for each tweet, you can have no more than one hashtag? That doesn't sound right to me.

For tweets and hashtags (and probably for the other four connected tables), I would create a "bridge" table, tweet_hashtag. This table would consist of:

tweet_id (foreign key to the tweets table)
hashtag_id (foreign key to the hashtagstable)
if appropriate, other columns that would apply only to the bridge table, if any (I can't think of any in this case, as I assume a tweet is created at  a single moment in time; if tweets could be edited, then a created_on date/time column could be appropriate)
(Note: the combination of tweet_id and hashtag_id should be the primary key for this table, as you presumably should only note a given hashtag appears in a given tweet once. You can create a surrogate key (probably just an auto-increment integer or bigint value) if you really want to, or if you do need to be able to have multiple entries of the same hashtag for a single tweet for some reason)

This allows a given hashtag to be associated with many tweets, and a given tweet to have multiple (or no) hashtags.

As noted above, the same probably applies to the other four fields associated with the tweets table (user_mentions, urls, media, symbols).


Database Reader Role with the ability to Create, View and execute stored procedure's and view's in sql server I will need to allow report writers  on the Subscription database to be able to view the table, create alter and delete stored procedure and views. Since it is a transactional replication they cannot alter or delete table structure. Is this possible if so how to achieve any ideas ?

Create an explicit schema or schemas that are separate from any other schemas and objects that are replicated from your publishers.

GRANT ALTER ON SCHEMA::YourNewSchema TO User_Who_Can_Create_Stored_Procs;
GRANT CREATE PROC TO User_Who_Can_Create_Stored_Procs;
GRANT ALTER PROC TO User_Who_Can_Create_Stored_Procs;
GRANT DROP PROC TO User_Who_Can_Create_Stored_Procs;


Read concern level of majority error while upgrading Sharded Cluster from 3.2 to 3.4 I have upgraded MongodDB sharded cluster having two replica sets from 3.2 to 3.4. Current storage engine is MMAPv1. After successfully upgrading all the secondary, primary, config server and mongos to 3.4, when I run config server using following command.

sudo mongod --configsvr

I keep getting following Error.

  SHARDING [shard registry reload] Periodic reload of shard registry failed :: caused by :: 115 could not get updated shard list from config server due to Current storage engine does not support majority readConcerns; will retry after 30s

And also I am unable to connect mongos with config server. When I try to connect it using following command

sudo mongos --configdb [ip-of-my-config-server]:27019

It gives me following error.

  BadValue: configdb supports only replica set connection string

I suppose mongos is unable to connect to config server due to the majority readConcerns error on config server.

MongoDB manual says "When reading from the replica set config servers, MongoDB 3.4 uses a Read Concern level of "majority"."

And to use a read concern level of "majority", WiredTiger must be used as storage engine.

So it seems I have to switch to WiredTiger storage engine to make it work. But when I was going to switch to WiredTiger storage engine of a secondary replica set member, according to manual

"This procedure completely removes a secondary replica set member’s data"

So I am stuck halfway. Situation is

Config server is giving error regarding majority readConcerns.
I have to switch to WiredTiger to get rid of it.
Switching to WiredTiger will remove data from secondary members.
Data will not be replicated back to secondary members during this switching to WiredTiger procedure because of config server error and eventually I will be ended up losing all the data (Please correct if I am wrong).

Questions are:

Can I make MongoDB 3.4 to use a Read Concern level of "local" when reading from the replica set config servers?
How can I switch to WiredTiger engine without losing data in my scenario?

Problem seems to be that mongodb 3.4 requires that config server is replica set (CSRS) and not old type single node config server. So you need to initialize your config server as replica set and then start it with --replSet  parameter.

Please check here


SQLPackage resets database recovery model I am using SQLPackage.exe with database publish profiles to deploy database changes to DEV and QC instances. I have databases in simple recovery mode. But when I deploy changes using SQLPackage, it reverts them to full recovery mode.

This is what I am using,

  "C:\Program Files (x86)\Microsoft SQL
  Server\110\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:"FILE
  PATH TO .DACPAC" /Profile:"PUBLISH PROFILE.XML"

If I use Visual Studio to deploy project changes using same publish project, same happens.

What am I missing here? As far as I can tell, there is no such flag in Profile. Is this expected behavior of SQLPackage?

You're right, as far as I know, that there's nothing in the sqlpackage publish options that would change the recovery model.  That property is set in the dacpac.  If you have access to the source code of the dacpac - the SQL Server Data Tools (SSDT, .sqlproj) project -  make sure that the "Recovery Model" setting is configured to "Simple" rather than "Full."

You can do that in Visual Studio by right-clicking on the project, going to the "Project Settings" tab, and clicking on "Database Settings."  This dialog should pop up:

If you don't have access to the source code, you can unzip the .dacpac file (it's just a compressed folder), and look in "model.xml" which should have the following for simple:

That line (under DataSchemaModel -> Model):

<Property Name="RecoveryMode" Value="1" />

...is omitted if the recovery model is Full.  It's set to "2" for bulk-logged.


Create nonclustered index on uniqueidentifier type CREATE TABLE [dbo].[session]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [user_id] [int] NOT NULL,
    [session_id] [uniqueidentifier] NOT NULL,
    [supervisor_id] [int] NULL,
    some columns ..

    CONSTRAINT [PK_session]
      PRIMARY KEY CLUSTERED ([id] ASC)
          WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
                ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tbl2]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [close_date] [datetime] NULL,
    [edit_date] [datetime] NULL,
    [session_id] [uniqueidentifier] NOT NULL,
    some columns ..

    CONSTRAINT [PK_tbl2]
    PRIMARY KEY CLUSTERED ([id] ASC)
       WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
             ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

I have those two tables which I can join on the session_id column. There are more than 20 000 rows in each table, and the join is too slow. Is it a good idea to create a nonclustered index on session_id columns which are of uniqueidentifier type? Or there is another way to do the join faster ?

Edit:

For example query is like :

SELECT some_columns...
FROM [dbo].[session] s
INNER JOIN [dbo].[tbl2] t2 on s.[session_id] = t2.[session_id]
WHERE t2.some_column = something

According to your sample query, you must have two separate NonClustered Indexes on Session_id column for both tables. Further it must contain columns that are being used by SELECT clause to avoid bookmark lookups.

Fill factor for indexes on uniqueidentifier: If only key column (like in your case) is uniqueidentifier then you can fill all the page (Fill Factor = 100), as there are no chances of value updates for this column.

But drawback for index creation on uniqueidentifier column is that GUID is a wide column (16 bytes to be specific) and contains a unique combination of 33 uppercase and numeric characters and are randomly generated by SQL Server hence huge chances of page split and index fragmentation. Using NEWSEQUENTIALID()  (SQL SERVER 2008 and above) to insert values instead of NEWID() can resolve this problem.


DB2 equivalent of Oracle DBMS_LOB.SUBSTR etc I have to migrate an Oracle stored procedure to DB2 LUW 10.5. It uses DBMS_LOB.SUBSTR() function on a BLOB parameter that receives an array of bytes from the client code.
What is its equivalent in DB2? SUBSTRB seems fitting, but I am not sure.

How about DBMS_LOB.SUBSTR, introduced in Db2 for LUW 9.7 as part of the Oracle compatibility feature?
If you are migrating from Oracle consider reading about that feature and how to use it, it might save you some effort.
Plain old SUBSTR will work as well.


Proxy authentication for PostgeSQL The following seems likely to be a fairly common situation, but I haven't seen any solutions.

I have a PostgreSQL database containing views that are specific to the $user, in order to restrict data depending upon some database-specific ACL logic. The database is typically accessed through JDBC, and in particular in JSP for a web interface. Users log in to the website with password authentication using LDAP.

What I would like is to make use of the views from within the JDBC connection to the database. But typically this mid-tier connection is created using a generic user, rather that specific to the person logged in to the website. I believe that in Oracle the "correct" approach is Proxy Authentication, but AFAIAA there is no equivalent for PostgreSQL. So what to do instead?

Some ideas come to mind:

Give all users' database accounts the same password, and then create user-specific JDBC connection using this generic password (this just feels so wrong...)
Store the user's login password somewhere in the website, to re-use when creating a database connection (again, this feels very wrong)

Any other (hopefully better) suggestions?

The usual solution is to authenticate the user within the web app, then issue a SET ROLE or SET SESSION AUTHORIZATION to "become" the user on a JDBC session that's already authenticated with the database using a fixed username.

In both cases the DISCARD ALL command that should be run by any connection pool when returning connections to the pool will automatically reset the authorization. Make sure your connection pool runs DISCARD ALL on returned connections.

These are a lot like the first of the three "proxy authentication" cases given in the linked Oracle doc.

PostgreSQL doesn't support the other two forms, where the client auth is passed through to the DB. It'd be relatively simple to support but nobody's wanted it enough to implement it or fund the work required for someone else to implement it.

SET ROLE

To use SET ROLE you must connect to the DB as a user that has membership of all the user roles, but the user can be otherwise unprivileged. The DB user should have the NOINHERIT option set with ALTER ROLE or at CREATE ROLE time. So you typically create webapp users as db roles that the webapp user is a member of, e.g.

CREATE ROLE newusername
  NOLOGIN
  ROLE mywebappuser;

(The ROLE mywebapp is equivant to running a subsequent GRANT newusername TO mywebappuser;).

SET SESSION AUTHORIZATION

To use SET SESSION AUTHORIZATION you must connect as a superuser, which is generally undesirable as it gives total control to the webapp user.

Unlike SET ROLE there's no requirement for group memberships.


Oracle DB union two select statements which return multiple rows I am trying to create a union inside a stored procedure which unions two select statements. One of the select statement returns two or more rows. Thus I get the error "exact fetch returns more than requested number of rows", which occurs of course because I have more than 1 row being retunrned from the second select statement.
This is my stored procedure:
DECLARE

timer_before timestamp;
timer_after timestamp;
timer_duration number;

referencelistid number(20);
referencename varchar(20);
startdate timestamp;
enddate timestamp;
creationuser varchar(20);
creationdate timestamp;
changeuser varchar(20);
changedate timestamp;
username varchar(20);
changetime timestamp;

BEGIN
timer_before :=systimestamp;
--DBMS_OUTPUT.put_line('Start at: ' || timer_before);

SELECT
    REFERENCE_LIST_ID,
    REFERENCE_NAME,
    START_DATE,
    END_DATE,
    CREATION_USER,
    CREATION_DATE,
    CHANGE_USER,
    CHANGE_DATE,
    USERNAME,
    CHANGE_TIME
into
    referencelistid,
    referencename,
    startdate,
    enddate,
    creationuser,
    creationdate,
    changeuser,
    changedate,
    username,
    changetime
from
    tbrd_reference_lists
where
    reference_list_id = 0000
UNION SELECT
    REFERENCE_LIST_ID,
    REFERENCE_NAME,
    START_ DATE,
    END_DATE,
    CREATION_USER,
    CREATION_DATE,
    CHANGE_USER,
    CHANGE_DATE,
    USERNAME,
    CHANGE_TIME
from
    tbrd_reference_lists_h
where
    reference_list_id = 0000;

timer_after :=systimestamp;
--DBMS_OUTPUT.put_line('End at: ' || timer_after);

select
  extract(second from  timer_after-timer_before) into timer_duration
from
  dual;

DBMS_OUTPUT.put_line('Delete Duration: (s): ' || timer_duration);
DBMS_OUTPUT.put_line('Delete Duration: (ms) ' || timer_duration*1000);

END;

I tried to search for a solution, and pipeline functions look like an option. Is there anything simpler that I can use to achieve this
Thanks
Pulkit

Code Review

Always define variables types with %TYPE where it makes sense.

This way, when the table changes, you don't have to go back and fix code.

Bulk Collect

When you are receiving multiple rows from a query, you need to BULK COLLECT INTO a collection.

Defining a Record, then a Table Type is one way to define a Collection Type.  If you were selecting all columns (eg SELECT *) then TYPE many_rows_t TABLE OF tablename%rowtype would be another way.

DECLARE
  timer_before      TIMESTAMP;
  timer_after       TIMESTAMP;
  timer_duration    NUMBER;

  TYPE one_row_t IS RECORD (
    referencelistid   tbrd_reference_lists.reference_list_id%TYPE -- number(20),
   ,referencename     tbrd_reference_lists.reference_name%TYPE -- varchar(20),
   ,startdate         tbrd_reference_lists.start_date%TYPE -- timestamp,
   ,enddate           tbrd_reference_lists.end_date%TYPE -- timestamp,
   ,creationuser      tbrd_reference_lists.creation_user%TYPE -- varchar(20),
   ,creationdate      tbrd_reference_lists.creation_date%TYPE -- timestamp,
   ,changeuser        tbrd_reference_lists.change_user%TYPE -- varchar(20),
   ,changedate        tbrd_reference_lists.change_date%TYPE -- timestamp,
   ,username          tbrd_reference_lists.username%TYPE -- varchar(20),
   ,changetime        tbrd_reference_lists.change_time%TYPE -- timestamp
  );
  TYPE many_rows_t IS
    TABLE OF one_row_t;

  some_buffer       many_rows_t;
BEGIN
  timer_before   := systimestamp;

--DBMS_OUTPUT.put_line('Start at: ' || timer_before);
  SELECT
    reference_list_id
   ,reference_name
   ,start_date
   ,end_date
   ,creation_user
   ,creation_date
   ,change_user
   ,change_date
   ,username
   ,change_time
  BULK COLLECT INTO
    some_buffer
  FROM tbrd_reference_lists
  WHERE
    reference_list_id   = 0000
  UNION
  SELECT
    reference_list_id
   ,reference_name
   ,start_date
   ,end_date
   ,creation_user
   ,creation_date
   ,change_user
   ,change_date
   ,username
   ,change_time
  FROM tbrd_reference_lists_h
  WHERE
    reference_list_id   = 0000;

  timer_after    := systimestamp;

--DBMS_OUTPUT.put_line('End at: ' || timer_after);
  SELECT
    EXTRACT( SECOND FROM timer_after - timer_before )
  INTO
    timer_duration
  FROM dual;

  dbms_output.put_line( 'Delete Duration: (s): ' || timer_duration );
  dbms_output.put_line( 'Delete Duration: (ms) ' || timer_duration * 1000 );
END;

Limiting memory consumption

The above example is OK if you are receiving a few rows.  But, if you are receiving a few Million rows, you may run into memory problems.

Instead, you'll want to LIMIT how many rows you fetch at one time.

To do this, you need to use a CURSOR and FETCH a limited number of rows at a time.
Please take the time to read this Oracle Magazine article and the Oracle Documentation.

DECLARE
  timer_before      TIMESTAMP;
  timer_after       TIMESTAMP;
  timer_duration    NUMBER;

  CURSOR C IS
    SELECT
      reference_list_id
     ,reference_name
     ,start_date
     ,end_date
     ,creation_user
     ,creation_date
     ,change_user
     ,change_date
     ,username
     ,change_time
    FROM tbrd_reference_lists
    WHERE
      reference_list_id   = 0000
    UNION
    SELECT
      reference_list_id
     ,reference_name
     ,start_date
     ,end_date
     ,creation_user
     ,creation_date
     ,change_user
     ,change_date
     ,username
     ,change_time
    FROM tbrd_reference_lists_h
    WHERE
      reference_list_id   = 0000;

  TYPE many_rows_t is table of C%rowtype;
  some_buffer       many_rows_t;
BEGIN
  timer_before   := systimestamp;

  OPEN C;

  loop
    FETCH C bulk collect into some_buffer limit 100;

    /* process data */

    exit when C%notfound;
  end loop;

  timer_after    := systimestamp;

--DBMS_OUTPUT.put_line('End at: ' || timer_after);
  SELECT
    EXTRACT( SECOND FROM timer_after - timer_before )
  INTO
    timer_duration
  FROM dual;

  dbms_output.put_line( 'Delete Duration: (s): ' || timer_duration );
  dbms_output.put_line( 'Delete Duration: (ms) ' || timer_duration * 1000 );
EXCEPTION
  -- Prevent cursor leakage
  when others then
    if C%isopen then
      close c;
    end if;

    raise;
END;


Redshift- How to use previous row's calculations in current row I am using redshift to determine projected inventory for next few week. I have receipts, current inventory and forecast.

ei= ei(previous row) + receipts - forecast

wk  bi  r   f   ei
1   100 20  80  40
2           50  0
3       30  15  15
4       40  14  41
5           10  31

create table ra_analytics.Test_1Test (wk int, bi int ,r int ,f int ,ei int);
insert into ra_analytics.Test_1Test values (1,  100 ,20 ,80 ,0);
insert into ra_analytics.Test_1Test values (2,  0   ,0  ,50 ,0);
insert into ra_analytics.Test_1Test values (3,  0   ,30 ,15 ,0);
insert into ra_analytics.Test_1Test values (4,  0   ,40 ,14 ,0);
insert into ra_analytics.Test_1Test values (5,  0   ,0  ,10 ,0);

select * from ra_analytics.test_1test order by wk

--Update first week of inventory
update ra_analytics.test_1test
set Ei= bi+r-f
from ra_analytics.test_1test where wk=1

--for next weeks inventory using lag function
select wk, bi, r, f, lag(ei,1) over(order by wk) +r -f as endinv
from ra_analytics.test_1test
order by wk

You need recursive common table expresion , feature not available in redshift , try another server for this or use a loop . Lag is not referring computed columns is not helpful in this case

On another sql server it should look like this :

with cter as (
select r,f , wk , bi+r-f as endinv from test_1test where wk = 1
union all
select t.r, t.f , t.wk, c.endinv + t.r -t.f as endinv from test_1test t inner join cter c on t.wk -1  = c.wk
)
select * from cter order by wk


Constrains relationship I have the following design. Each user has it's own contacts, it's own hosts and assign those to a domain which belongs only to him.

I want to insure that the host you assign to the domain belongs to the user that domain is assigned to.

Is it something that I have to insure in my application logic?

If I set domains.user_id foreign key to reference host.user_id and user_id.id does it eliminate my issue?

By the way can anyone suggest me a better design for my schema?

This is a rather common problem, when the design has a "diamond" shape. See similar questions:
Many to Many and Weak Entities

With MySQL, I'd use something like this:

(0) Note that I prefer user_id as name for the primary key of users and not id for all the tables. I find the SQL code totally confusing otherwise (plus you can use the JOIN ... USING (tablename_id) syntax).

(1) The extra UNIQUE constraints in tables hosts and contacts are needed for the foreign keys from the domains table.
(2) The two FOREIGN KEY constraints from the domains table are changed to use composite keys (include the user_id).

Table users

CREATE TABLE users
( user_id INT NOT NULL AUTO_INCREMENT
, username VARCHAR(45) NOT NULL
-- other columns
, PRIMARY KEY (user_id)
) ;

Table contacts

CREATE TABLE contacts
( contact_id INT NOT NULL AUTO_INCREMENT
, user_id INT NOT NULL
, info TEXT
, PRIMARY KEY (contact_id)
, UNIQUE INDEX (user_id, contact_id)       -- added, see comment 1 above
, INDEX (user_id)
, FOREIGN KEY (user_id)
    REFERENCES users (user_id)
) ;

Table hosts

CREATE TABLE hosts
( host_id INT NOT NULL AUTO_INCREMENT
, user_id INT NOT NULL
, name VARCHAR(45) NOT NULL
--
, PRIMARY KEY (host_id)
, UNIQUE INDEX (user_id, host_id)          -- added, see comment 1 above
, INDEX (user_id)
, FOREIGN KEY (user_id)
    REFERENCES users (user_id)
) ;

Table domains

CREATE TABLE domains
( domain_id INT NOT NULL AUTO_INCREMENT
, user_id INT NOT NULL
, contact_id INT NOT NULL
, host_id INT NULL                       -- nullable based on comments
, name VARCHAR(45) NOT NULL
-- other columns
, PRIMARY KEY (domain_id)
, FOREIGN KEY (user_id, contact_id)       -- composite FK, see comment 2
    REFERENCES contacts (user_id, contact_id)
, FOREIGN KEY (user_id, host_id)          -- composite FK, see comment 2
    REFERENCES hosts (user_id, host_id)
) ;


Architecture for low-latency RESTful server on Amazon EC2 with Node.js and a database This is a question for DBAs with a lot of practical experience with large scale deployments.

We are trying to build an app with very low latency. (We really want to create the best user experience, and latency matters in this case.)
The app is fundamentally a RESTful Node.js server that queries a database (OrientDB). Static content/files are hosted elsewhere. Objects are translate into UI largely on the client end.
We are planning on using Amazon EC2 to host the web server and database.
High availability is important as well.
Cost is not a major deciding factor at this time.

What architecture would you recommend?

Here are 2 options, maybe there's more:

Put the webserver (Node.js in this case) on the same EC2 instance as the database. We'd have multiple instances like this for availability and load balancing. In the past, I've used this to reduce latency in high volumes but those system didn't have to scale as dramatically. It was also before placement groups.
Or, have databases on different instances than the REST web server.

Background

The database size is about 50 GB and we need a better solution. Projections look like traffic will be 10x that amount (a 500 GB DB) within 8 months.

It is about 20% writes and 80% reads. There's about 1000 queries per second right now and the rate is increasing.

"Put the webserver (Node.js in this case) on the same EC2 instance as the database"

Never put your app server and DB server into one machine. Since you are using EC2 there are some white papers on Amazon named "Architecting on AWS" and there is a section called scalability. All your app servers should be state-less. It means it should be no data stored on any machine so you can easily bring a VM up (from your AMI) based on your auto-scaling group or get rid of it when the traffic goes down.

Database servers usually have their own way of scalability like Master-slave or replica-set. So they shouldn't be in the same group of your app servers (auto-scale or ELB).

Now the network latency, actually lately I was testing internal network speed check on AWS VPC and it wasn't that bad at all. Depends on your EC2 type the network has low-moderate-and high type of network but when it comes to internal it's pretty much fast. It will be inside DNS resolving time, HTTP request time, HTTP respond time, etc. You should be more worried about your query time on EC2 machines by choosing SSD volumes (Provisioning your EBS volumes maybe) and maybe RAID your EBS volumes to speed up read/writes. The bottleneck usually is the I/O when it comes to database in architecture not the network latency (unless you are using all the bandwidth so you need more secondaries so your app servers can read from them directly).

Hit me if you have a question,
Good luck


MySQL: How to avoid case sensitivity in SQL queries when migrate from Windows to Linux? I have migrated my Database from Windows to Linux server. In many places, query has been written without focusing their cases (uppercase / lowercase). So, these queries are not getting executed and generating errors. As a result, functionalities based on these queries are breaking. Below is the example query to run in Windows and Linux:

Select A.id,A.title from tablename a;

This query will work in Windows while never work in Linux.

Is there any way through which I can avoid these case sensitivity in MySQL
queries and no need to check and fix every query ?

In MySQL the commands themselves are not case sensitive, however depending on the OS, file structure variables might be case sensitive.

Check if your OS is case sensitive, and if this is the case, find out the correct case for the tables, and make sure your query is consistent in naming.
Don't forget though that you'll have to rewrite a lot of the queries to make sure they work in MySQL.

References:

MySQL docs: Identifier Case Sensitivity


launch database mirroring monitor grayed out in SQL server 2014 I have 2 servers configured with mirroring each other. Both the servers are principal & Mirror servers each other. From the 2nd Server launch database mirroring monitor is working fine but from 1st server launch database mirroring monitor is showing grayed out. Both server services are running with the same administrator account. The below screenshot is for 1st server. Can you please help me how to enable this launch database mirroring option in SQL server 2014 ?

Had the same problem - turns out if the first DB in the list is in restoring (mirroring) state that server will have this issue.  Solved it by adding an empty DB "aaa" as the first DB in the list.


How to insert a large amount of records to the database in a short time? I have to insert a bunch of records(500,000) to the database at once, using the fastest way. I've tried inserting 1,700 records at once but it took twenty minutes!! I am using SQL-SERVER, and C#.

Although the following table won't contribute to the technique for fast inserting, I decide to link it anyway so you get a better understanding about the number of fields I need to insert(duplicate it by around 500K).

There are several options.  Here are the main ones that come to mind.

SSIS - SQL Server Integration Services: This is SQL Servers ETL tool and you can move data from pretty much any source to pretty much any destination and it can be quite fast.

BCP - Bulk Copy Program: This is a command line tool that comes with SQL Server.  It's very good at moving text files into SQL Server or you can export into a native format from one SQL Server and import using that same format into another SQL Server.  BCP is also very fast but a bit more limited than SSIS.

INSERT INTO tablename SELECT fieldlist FROM othertable: This assumes that you are moving from one SQL table to another on the same server.  This is the fastest but at 500k rows you are looking at a fairly big transaction or having to break it up like you said.

OPENROWSET: This is a SQL Server function that will let you pull data into a table from various formats.  It's also supposed to be fairly quick but I haven't worked with it much.

General note: Your primary key is mbr_id.  If this is something that is stored in your source data (as opposed to being an identity column) your insert will go MUCH faster if the source data is in mbr_id order.


Get second highest value in a table id value
1   50
2   60
3   55

select max(value) from tablename;

Generally we know, we will get 60, but I need the next value 55.

How do I get the value 55 using SQL?

To get the second highest distinct value in the table you can use

SELECT MIN(value)
FROM   (SELECT DISTINCT TOP (2) value
        FROM   tablename
        ORDER  BY value DESC)T
/*If only one distinct value return nothing. */
HAVING MIN(value) <> MAX(value);


Oracle becomes too slow after system crash I am using Oracle 11.2 as a database for my application. For this purpose I should import my data (around 100m records) to this database. For this purpose I used SQL Developer.

Unfortunately at the middle of the import process, my system crashed and forced me to reboot the system. After system boot up everything related to Oracle is strange:

starting up Oracle takes around 10 min (before system crash takes less than 10 sec)
SQL Developer takes too much time to connect to database
and the importing process becomes 10000 times slower!

Would you please tell me what happen and how can I fix that?

P.S: After passing 24 hours from the crash time a performance becomes better but still much less than before crash.

Several things might be going on here and might be difficult to pin down but here's a few things.

After Oracle crashes, it must do some type of recovery (as in media or crash recovery) to bring the database back to a consistent state.  Depending on what you were doing (and whatever else was going on in the instance at the time of the crash) transactions may need to be kept or rolled-back and this is done before the instance will open so you need to wait until it completes.

This should, however, only happen the first time you start the database after the crash.  If it happens every time afterwards, there may be something else going on. If your job needs to roll back data, depending on the amount of data, that too, can be a lengthy process which might be running while you are re-trying your operation and may slow down other operations.

Also, since the database has crashed, you've lost any data cached in the db blocks buffers as well as anything in the shared pool, so any operations needing access to data may now have to go to disk and any shared SQL in the shared pool may need to be re-parsed, and so on and so forth.

Before re-trying your operation, check all your objects (as in tables, indexes, etc.). If you can, do a little testing - truncate the tables to be loaded (and remove any indexes, constraints, etc.) before you try to re-load, because you don't want to have to wait for rows to be deleted that you're just going to re-insert anyway or for indexes that might be corrupt and slowing down insert operations.

Lastly, instead of SQL Developer, you might want to try using SQL Loader or load in smaller increments in case there's some type of data causing the crash (SQL Loader will leave a "bad" file of data it could not load).

Also, what's your hardware setup? Are you running Oracle and SQL Developer on the same machine?  If so, since you're trying to load 100 million rows, try splitting the load.  If you are running SQL Developer from a PC, make sure you're not running out of PC resources - 100 million rows for Oracle is not a big deal, but it might for your PC.


Row Level security or Multiple Tables Im looking for documentation on best practices for the following scenario.

A hosted application contains some "global" data and some "Per-tenant" data.  A "Tenant" should have no access to another tenant's tables, and I'd like this to be enforced at DBMS level (in addition to being enforced to Application level).

So as an Example the application may offer to Video rental shops a management system.

The Shop Owners and staff and also information pertaining to people who rent movies are global.  Once a customer signed up they would not need to do so again to rent from another shop.

The Shops (tenants) have their own databases (list of customers, inventory, currently booked-out movies, etc).  So when a shop-owner logs in the application presents only his own data, which I don't have a problem with.

Should the system create a new set of tables for each Tenant/Shop?

Or Should the system use DBMS security / authorizations... in other words in the back end the inventory of Shop 1 is in the same table as that of Shop 2, but through DBMS security the views only allow selecting rows for that Shop for who the user is signed into the application.

I'm not sure if my question is clear enough?

My bias is to use a single table with appropriate row-level security.

There are potentially huge maintenance advantages to a single set of tables.  If you end up with n copies of each table, that means that you have to run n copies of each script every time you want to make a change.  Frequently, that means that you end up with at least a few very slightly different versions of the application running at a time because someone forgot to apply script 7 of 23 in a monthly build to one set of tables and someone else created an index on one set of tables to address one customer's issues without adding it to every customer which makes debugging much harder.

A single set of tables has significant scalability advantages.  Adding new customers just requires adding a new row to the customer table not deploying a new schema/ database with the new customer's copies of the tables.  Adding new customers also doesn't directly add ongoing work for the DBAs.  If you have separate copies of the tables, you need someone to deploy tables to create a new customer and every new customer means additional work for the DBA at least to run the scripts one more time every time there is a change.

A single set of tables may also offer performance advantages.  If you're using a separate set of tables, each customer would realistically need a separate connection pool in the middle tier.  It would defeat the purpose of having separate tables, after all, if your middle tier is connecting as a user that can see every tenant's data because then you'd be implementing row-level security in the middle tier and dealing with all the complexity of multiple sets of tables in the back end.  That makes it tough to scale across servers-- do you create a connection pool for every client on every server?  Do you send certain clients to certain servers? Do you not preallocate connections and incur the cost of waiting for connections to be established more frequently?

That being said, there are cases where separate tables might be preferred.  If your customers are frequently large institutions, for example, separate tables will make it much easier to do things like move a customer to a dedicated box (or at least a dedicated VM) if the customer wants to upgrade to dedicated hardware so that they don't risk performance being affected by other customers.  Those large institutions may want greater control over outages and upgrades so it may make sense to have separate tables to allow different customers to be upgraded at different times in order to work with that customer's schedule.  Those institutions may find it easier to tell an auditor that their data is physically separate from all other customers rather than explaining that the data is physically intermingled but security controls are in place to guarantee row-level security.  If you're going to have relatively few relatively large customers, the amount of maintenance overhead you introduce by having separate tables may not be particularly significant given the general day-to-day maintenance tasks that each client likely requires.  In that sort of environment, different clients often have sufficiently different configurations that the problems they encounter are relatively unique even when they're running exactly the same version of the software.


What does slow down an InnoDB database that is fixed by re-importing it? I recently noted a performance degradation on a MySQL 5.6 (InnoDB) database with several million records. This particular query was taking around 20 seconds on the first run (apparently data was loaded from disk) and 4 seconds on subsequent runs (apparently data was loaded from memory):

SELECT
COUNT(DISTINCT `t3`.`node_id`) AS '__value'
FROM `hd2_ont_class_url` AS `t3`
INNER JOIN `hd2_ont_link` AS `t4` ON ((`t4`.`passive`=`t3`.`node_id`) AND (`t4`.`linktype`='tagset'))
INNER JOIN `hd2_ont_rel` AS `t5` ON ((`t5`.`passive`=`t4`.`active`) AND (`t5`.`active`='28530') AND (`t5`.`linktype`='tagup'))
WHERE
(`t3`.`deleted` = 0) AND
(MATCH (`t3`.`title`) AGAINST ('+php*' IN BOOLEAN MODE));

Same query on the same database in another environment was taking only around 0.02 seconds, so I started investigating where the hold up was coming from. Tried different versions, configs — all the same. The EXPLAIN output was also identical everywhere.

In the end of it I just dumped and re-imported back the original database. And that was it! Now the query is taking no more than 0.05 seconds!

So here are the questions:

What caused the performance degradation? How to prevent it?
Is there some sort of maintenance procedure that would fix the issue like the re-import did?

Hint: the records in the database are rotated often — every day about 20% of them are deleted and new ones are created instead.

Performance degradation was most likely caused by your table getting more and more fragmented by inserting and deleting many rows every day.
Dumping your table, dropping it, and reloading after many deletes will compact the space on disk and reduce the size of the .ibd file, assuming that it uses its own tablespace.

You could accomplish the same thing with a null alter, like this:

ALTER TABLE sometable FORCE;

which will rebuild the table.


How to select the most recent entry based on a field in MySQL My table has multiple entries by individual customers, These customers have made multiple purchases over the years from any of several suppliers listed in my database. I need to run a query to select those whose most recent purchase was from supplier 'A'. If a customer purchased from supplier 'A' last month, but their most recent purchase was, say, yesterday from supplier 'B', then I don't want them included in the result. This may be a simple query, but I'm having trouble getting my head around a reliable method of running this query.

Basically, what I'm looking to generate is:

SELECT
`customer_email`,
`supplier`,
`purchase_date`
 from `customer_table`
WHERE
`supplier` = 'A' and

... "this purchase by "customer_email" is "customer_email's" most recent purchase, even though they have made multiple previous purchases from any of several 'suppliers'.

Any help in pointing me in the right direction as to completing this query would be greatly appreciated!

WITH CTE AS (
SELECT customer_email,
    supplier,
    purchase_date,
    ROW_NUMBER() OVER (PARTITION BY customer_email ORDER BY purchase_date DESC) AS rn
 from customer_table
 )
SELECT customer_email,
    supplier,
    purchase_date
FROM CTE
WHERE supplier = 'A' AND rn = 1

For MySQL versions older than 8 you can use this query:

SELECT customer_email,
    supplier,
    (SELECT MAX(purchase_date) FROM customer_table WHERE customer_email = t.customer_email) AS purchase_date
FROM (
    SELECT c.customer_email,
        (SELECT supplier FROM customer_table WHERE customer_email = c.customer_email ORDER BY purchase_date DESC LIMIT 1) AS supplier
    FROM (SELECT DISTINCT customer_email
            FROM customer_table) AS c
    ) AS t
WHERE supplier = 'A'

this query probably may be revised for better performance but for this I need to see your database structure.


What configurations do I need to fix the "unable to begin a distributed transaction" error when trying to run a remote procedure? This is the error message I'm receiving:

  Msg 7391, Level 16, State 2, Procedure spStoredProc, Line 62 [Batch Start Line 1]
  The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "MyLinkedServer" was unable to begin a distributed transaction.

As a test my stored procedure query is just SELECT 1 AS A and works locally on the server but doesn't work when I call it remotely on a linked server.

On the Linked Server properties have you tried setting "Enable Promotion of Distributed Transactions" to False?


Installation of SQL Server 2014 and SQL Server 2012 on the same system I have a system with a SQL Server 2012 Developer edition installed.

Can I install SQL Server 2014 (6 months trial) and SQL Server 2012 on the same system?

I do not want to overwrite the pre-existing SQL Server 2012

Yes you can! Your first SQL Sever (2012) will most likely be the default instance, any subsequent installation of SQL Server (any version) must be a named instance. As long as your machine has the resources, you can install multiple instances of SQL Server.


Should you update child states based on the parent? I have the following table structure

Event --* Session --* Membership

An event can be cancelled - reflected via a status column
A Session can be cancelled - reflected via a status column
A user can attend or leave an event via the membership row

My design questions

If an event is cancelled, should all of the session rows be marked as cancelled as well?

Likewise, if a session is cancelled should the membership row also be marked as cancelled?

What are the pros and cons to these methods?

It's opinion based, but I would err on the side of updating the bare minimum when a status changes.

If you cancel an event and only update the Event row indicating cancelled then you minimize the amount of data that would need to be updated should the event be reinstated.
Likewise, if you cancel a session, and only update the Session row indicating cancelled then you minimize the amount of data that would need to be updated _should the session be reinstated.
As Joe W points out in a comment:

  if you do update the sessions as well if there was sessions canceled
  for other reasons you would have a harder time getting the status
  correct if you un-cancel an event as you might not know exactly which
  sessions should still be canceled.


All SQL Server aliases linked to default instance instead of instance I assigned I have multiple SQL Server instances(different versions) on my computer.
I try to create aliases for each instance as

First I thought I had everything settled then I found no matter which alias name I connect, it will all be led to SQL2017 instance.

I wanna provide more information but don't know where to start with.
And idea what I might do wrong?

To correctly connect to SQL Server instance the network library has to know 2 things: IP address and port number. These two are necessary and sufficient.

The "name" of a named instance make no difference to the library, it was invented when named instances were introduced and it is used just to determine the port number.

When the port number is missing in the connection string, the network library asks for it SQL Server Browser (on UDP 1434) sending it the instance name. As the responce it receives port number.

When there is port number in the connection string, instance name is just ignored.

You can test it by yourself by changing instance name to bla-bla-bla:
if you create an alias or just type in SSMS connection dialog .\bla-bla-bla,1433
you'll be connected to the default instance.

Vice versa, if you omit instance name of the named instance but indicate the correct port where your  named instance is listening on, for example .,8852 where 8852 is the port of named instance you'll be connected to this named instance even if it's name was not specified.


Updating whole table once, criteria : column_name and each value I have a table with 606 rows. The first column is simply ID (starting at 1 to 606), the next column names are name1, other1, name2, other2, etc. until name100, other100.

I'm trying to create a query that would:

update each cell if column_name like "other"
if 0< cellValue < 50 then cellValue=cellValue*10
if 50<=cellValue<120 then cellValue=cellValue*5
if 120<=cellValue<230 then cellValue=cellValue*2

I don't care much about the efficiency because it is a one-shot query.

I worked on it to try to solve it. I got a complicated pseudo-code idea to explain better what I'm trying to do, but I guess it's not needed in SQL because SQL queries check every single value:

begin
for i in 1 to 100
  for each row
    if current column is named like "other"i then
      if 10000< value then value=value/40
      elseif 0< value<50 then value=value*10
      elseif 50<=value<120 then value=value*5
      elseif 120<=value<230 then value=value*2
      elseif 230<=value<500 then value=value*2
      endif
    endif
  end for
end for
end

I asked some people and they don't know either.

The result of:

SHOW CREATE TABLE example

CREATE TABLE `example`
( `id` int(11) NOT NULL,
  `name1` int(11) NOT NULL,
  `other1` int(11) NOT NULL,
  `name2` int(11) NOT NULL,
  `other2` int(11) NOT NULL,
  --
  -- 2 * 97 columns skipped
  --
 `name100` int(11) NOT NULL,
 `other100` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

I'm using:

MySQL Workbench Community (GPL) for Windows version 6.3.5 CE build 201 (64 bit)
It is a local instance of MySQL 5.7
Cairo Version: 1.10.2
OS: Microsoft Windows 8.1

This is a very simple query. You can use a CASE expression, one for each column that needs updating:

UPDATE table_name
SET other1 = CASE WHEN other1 <=  0 THEN other1    -- no change
                  WHEN other1 <  50 THEN other1 * 10
                  WHEN other1 < 120 THEN other1 *  5
                  WHEN other1 < 230 THEN other1 *  2
                  -- possibly more WHEN..THEN clauses
                  ELSE other1
             END,
    other2 = CASE WHEN other2 <=  0 THEN other2       -- no change
                  WHEN other2 <  50 THEN other2 * 10
                  WHEN other2 < 120 THEN other2 *  5
                  WHEN other2 < 230 THEN other2 *  2
                  ELSE other2
             END,
    --- repeat similarly for all columns that need updating
  ;


Approaches towards Marking Membership of Group? Given this somewhat SQL pseudo-code:

CREATE TABLE foo(
   id      NUMBER(5) PRIMARY KEY,
   marked  BOOL
);

I'll keep track of whether ID's have been "marked" or not.

Alternatively, I've thought to alter the table to consist of a list/set, more or less:

CREATE TABLE foo(
  id NUMBER(5) PRIMARY KEY
);

such that existence in the table indicates marked = Y, whereas, the opposite notes marked = N.

In other words, to determine whether, conceptually, an id is "marked," I would check whether it simply existed in the table.

Is there any advantage to choosing one over the other? Also, I don't have experience with the latter type of table structure. Is it common?

If you just need to know if an ID is marked or not, using your 2nd approach (not registering the ones not marked or marked) has a few more benefits than the first: less rows saved means more unused space, and a EXISTS or NOT EXISTS operation is faster than actually retrieving the row and comparing the BOOL marked against a Y/N.

There is a noticeable difference however:

Approach 1 (using a marked column) has 3 possible variations of
foo: foo exists and is marked, foo exists and it's not marked or foo doesn't exist.
Approach 2 (just using ID) has only 2 variations: either foo exists which we assume it's marked, or it doesn't which we can assume that it's either not marked or it doesn't exist. This brings a problem of discerning these last two scenarios.

On another hand, if you are going to need additional information for foo (like most systems) you will have to add more columns to it.

Imagine that now you need to know the date when it was marked or
unmarked. You can track this with another table, or just add a
ModifiedDate to foo, if on your first approach.
Imagine that now you have another state that's "partially marked" so
a BOOL's 2 possible values (other than NULL) is not enough. You will now need to change it to a VARCHAR, or an INT with a foreign key to another table that explains what that status is. If you had    implemented the 2nd approach, you will have to change a big part of your queries (removing the EXISTS / NOT EXISTS), rather than just a few literals or hard-coded numbers, which adds additional maintenance time.

Another thing to point out is the relationship of this foo to other entities. If another entity might relate to 1 foo (no more than 1) or not, then it would probably be part of that other entity's table, but on the other hand, if an instance of another entity can relate to foo in a 1 - N relationship, then the foreign key must be on foo, which you won't be able to add if you are using the 2nd approach, since an unmarked foo has no record.

From your question title I can guess that this foo represents the link between a User and a Group. If that were the case, the common approach would be using a User table with UserID as PK, a Group table with GroupID as PK and a many to many breakdown table UserByGroup with UserID, GroupID as composite PK, each being a foreign key to the related table. You could store the CreatedDate in this table if you need additional info, for example.


Dumping a database into a file from terminal freezes I've had this a couple of times and I can't find a way around it. I'm dumping a mysql database from the terminal using the following command:

mysql -uusername -p my_database > myfile.sql

When I run that and enter my password I wait and wait but nothing happens, the file myfile.sql is created but has the size of 0k. Am I missing something? Is there a better way to dump a database?

mysql is for interacting with the database.

mysqldump is what you want for this task.


Query to filter out data based on two logic This is a sample table:

  sample_id  |      timestamp       | p_id
============================================
    62054    |  2018-09-25 10:18:15 |  2652
    62054    |  2018-09-27 16:44:57 |  966
    62046    |  null                |  1809
    62046    |  2018-09-25 10:18:15 |  2097

We need to filter out unique sample_id column, but the logic is

IF the timestamp column is null, then return those null column data

    62046 | null | 1809

IF the timestamp column is not null, then return the latest timestamp column data

 62054 | 2018-09-27 16:44:57 | 966

So its great if anyone provide the sql query.

We need somethings like that,

WHERE
IF(
    NOT NULL = all row group by sample_id,
    row where cancelled_at is maximum,
    null column
)

So we're grouping by the combination of the sample_id and wheither timestamp IS NULL. The max is returned the group. MAX(NULL) is NULL so that case is covered.

SELECT sample_id, MAX(timestamp)
FROM table
GROUP BY sample_id, timestamp IS NULL


Is there a Maintenance Plan that checks my database for corruption? Is there any way to make a maintenance plan check all my databases on a single instance?
Is the maintenance plan Check Database Integrity Task enough ?

Good question. The integrity check in the maintenance plan is enough since it will run a dbcc checkdb.
From MSDN:
integrity check

The Check Database Integrity task encapsulates the DBCC CHECKDB statement.

Running this task will be resource intensive. You don't want to do this in prod. servers if you don't have a maintenance window.
The common way is the following:
1. Backup your databases
2. Restore your db's on a test environment
3. Run a dbcc checkdb on your databases.
This way you check your DB integrity and verify your backup.


error while converting sql to eloquent query I have converted an sql query to eloquent like below:

$roomtypes = DB::select(`rt.id`,`rt.type`,`rt.total_no_of_rooms`)
    ->addSelect(DB::raw(`SUM(r.no_of_rooms) as reserved rooms`))
    ->selectSub(`rt.total_no_of_rooms`, `rooms available`)
    ->selectSub(`-`, `rooms available`)
    ->selectSub(`SUM`, `rooms available`)
    ->from(`Roomtypes as rt`)
    ->join(`Reservations as r`, function($join) {
        $join->on(`r.roomtype_id`, `=`, `rt.id`)
            ->on(`$check_in`, `>=`, `r.check_in`)
            ->on(`$check_out`, `<=`, `r.check_out`);
        })
    ->groupBy(`rt.id`)
    ->groupBy(`rt.type`)
    ->groupBy(`rt.total_no_of_rooms`)
    ->get();

but I am getting an error

  "Argument 1 passed to Illuminate\Database\Connection::prepareBindings() must be of the type array, null given, called in "

Please, could anyone help ?

I have found the an alternate for this. I created a stored procedure in laravel using migrations

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateProcedure extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::unprepared('
            CREATE PROCEDURE fetch_all_users()
            BEGIN
                select * from users;
            END'
        );
    }

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    DB::unprepared('DROP PROCEDURE IF EXISTS fetch_all_users');
}

}

Then i called it in the code:

  DB::select('call fetch_all_users()')


How can I use function's parameter in it? I have a function like this:

DROP FUNCTION IF EXISTS user_repo //
CREATE FUNCTION user_repo(user_id INT) RETURNS VARCHAR(20)
BEGIN
    DECLARE vote_value mediumint;
    DECLARE score mediumint;

    SELECT coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)
      INTO vote_value, score
    FROM reputations
    WHERE owner_id = user_id
      AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK));

    RETURN CONCAT_WS(',', vote_value, score);
END;//

It works as well. Now I want to also pass another parameter to the function to determine the time range. I mean I want to pass a string instead of  WEEK. Here is my new function:

DROP FUNCTION IF EXISTS user_repo //
CREATE FUNCTION user_repo(user_id INT, range VARCHAR(10)) RETURNS VARCHAR(20)
BEGIN
    DECLARE vote_value mediumint;
    DECLARE score mediumint;

    SELECT coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)
      INTO vote_value, score
    FROM reputations
    WHERE owner_id = user_id
      AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 range));

    RETURN CONCAT_WS(',', vote_value, score);
END;//

But it throws this error:

  The following query has failed:

CREATE  DEFINER=`root`@`localhost` FUNCTION `user_repo`(`user_id` INT,
                `range` VARCHAR(10) CHARSET utf8)
       RETURNS VARCHAR(20) CHARSET utf8mb4
       NOT DETERMINISTIC
       CONTAINS SQL SQL
       SECURITY DEFINER
BEGIN DECLARE vote_value mediumint;
DECLARE score mediumint;
SELECT  coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)
    INTO  vote_value, score
    FROM  reputations
    WHERE  owner_id = user_id
      AND  date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 range));
RETURN CONCAT_WS(', ', vote_value, score);
END

  MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'range)); RETURN CONCAT_WS(',', vote_value, score); END' at line 9

Does anybody what's wrong?

Certain parts of queries cannot be substituted like that.  Such parts include table names and keywords.  "WEEK" fall into this category.  Instead...

-- Construct the query:
SET @sql = CONCAT("SELECT ... INTERVAL 1 ", range, "))");
-- perform it:
PREPARE _query FROM @sql;
EXECUTE _query;
-- clean up:
DEALLOCATE PREPARE _query;

(This will probably be more efficient than some IF..THEN..ELSE or CASE..WHEN construct.)


How to relate the middle table with One or Many relationship? I am currently developing a simple CRM system. These are my tables: users, roles, role_users, customers.

Rules:

A user can only be assign with a single role
A role can have a 1 or many users
A Customer is assigned to a user

Question:
Did I mark the correct relationship in the screenshot above?

If a user can have no more than a single role, and this will ever change, then put role_id directly on the user table, then the relationship between role table and user table is a single role to many users.


How can i return the max of a sum by each team? I have a lot of teams and i want to know the maximum of sum total of each team.

This's my query :

SELECT campaign_id,
       campaign_identifier,
       team,
       campaign_name,
       Month(time) AS month,
       Sum (total) AS Total
FROM   campaign
WHERE  Year(time) = Year(Now())
       AND Month(time) = 12
GROUP  BY campaign_identi,
          team

Thanks.

If am not wrong this is what you need

select team,max(Total)
from
(
SELECT team,
       Sum (total) AS Total
FROM   campaign
WHERE  Year(time) = Year(Now())
       AND Month(time) = 12
GROUP  BY campaign_identi,
          team
) A
Group by team


postgres automatic table list partitioning How to handle new partitions in a smart automatic manner in PostgreSQL ?

I want to create a PostgreSQL table List partition on a certain column.
For example:

CREATE TABLE orders
(
  id            int ,
  country_code  VARCHAR,
  order_total   int,
  CONSTRAINT orders_pk PRIMARY KEY (id)
) PARTITION BY LIST (country_code)

however I dont know all the possible counties in advance, on an oracle table I will enable auto partitioning:

ALTER TABLE orders SET PARTITIONING AUTOMATIC;

It seems like PostgreSQL lacks this feature, I have read that I can add a default partition to the table and then later on add another partition and the values from the default partition would be transferred automatically to the new partition. (EDIT: default partition block you from creating new partitions if you previously inserted values to it)

what would be the best strategy to handle this in an automatic manner ? Are there any plans to add this feature to PostgreSQL?

I haven't heard of any plans to add such a feature.

You can easily create a BEFORE INSERT trigger that creates partitions on demand, but of course that trigger has to run for each row inserted, which means a certain performance impact.

Using a default partition like you suggest is a solution with better performance, but it requires later explicitly moving the data to a different partition, which can be painful.

If you expect new countries to pop up all the time, maybe the trigger is the way to go. If it happens once in a blue moon, I might prefer the second way.


SQL Server: Find out unused queries I have a database with hundreds of queries created by many people over the years. Now I can investigate for each query if any SSRS report, application for the database or other queries are using each query manually, but as everyone can imagine this is penal labor...

So I found some articles in the web about unused tables or procedures.
So as list all queries with

SELECT * FROM sys.all_objects WHERE type = 'V' AND is_ms_shipped = 0

Can I somehow find out if this query is ever been called in a period of time?

E.g. since start which I can get via

SELECT sqlserver_start_time  AS LastSQLServiceRestart FROM sys.dm_os_sys_info

then I would take this date and list all views that aren't used in this time until now.

Community wiki answer:

In general, nothing exists built in that automagically keeps count like this. Some people tend to use indexes on a table as last access but that's sketchy at best.

The honestly best thing you can do is create an audit, trace, or extended event session and garner the object usage from that. This will also require extra work on things such as stored procedures where the procedure is executed and then may touch multiple or no tables, views, etc., based on logical flow.


Can't change MongoDB default DB path I know there are multiple questions on the same issue and I followed them but not sure what is happening here. First of all I am trying to change the default db path to a a separate mounted disk. And I changed the group and permissions and I updated the SELinux Contexts. But still I can see that the db is not starting saying permission denied in the log. Here is my log report

2019-03-03T18:53:25.514-0600 I CONTROL  [main] ***** SERVER RESTARTED *****
2019-03-03T18:53:25.517-0600 I CONTROL  [main] Automatically disabling TLS 1.0, to force-enable TLS 1.0 specify --sslDisabledProtocols 'none'
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten] MongoDB starting : pid=34998 port=27017 dbpath=/run/media/thomson/data2/mongo 64-bit host=localhost.localdomain
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten] db version v4.0.6
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten] git version: caa42a1f75a56c7643d0b68d3880444375ec42e3
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten] OpenSSL version: OpenSSL 1.0.1e-fips 11 Feb 2013
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten] allocator: tcmalloc
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten] modules: none
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten] build environment:
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten]     distmod: rhel70
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten]     distarch: x86_64
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten]     target_arch: x86_64
2019-03-03T18:53:25.528-0600 I CONTROL  [initandlisten] options: { config: "/etc/mongod.conf", net: { bindIp: "127.0.0.1", port: 27017 }, processManagement: { fork: true, pidFilePath: "/var/run/mongodb/mongod.pid", timeZoneInfo: "/usr/share/zoneinfo" }, storage: { dbPath: "/run/media/thomson/data2/mongo", journal: { enabled: true } }, systemLog: { destination: "file", logAppend: true, path: "/var/log/mongodb/mongod.log" } }
2019-03-03T18:53:25.529-0600 I STORAGE  [initandlisten] exception in initAndListen: Location28596: Unable to determine status of lock file in the data directory /run/media/thomson/data2/mongo: boost::filesystem::status: Permission denied: "/run/media/thomson/data2/mongo/mongod.lock", terminating
2019-03-03T18:53:25.529-0600 I NETWORK  [initandlisten] shutdown: going to close listening sockets...
2019-03-03T18:53:25.529-0600 I NETWORK  [initandlisten] removing socket file: /tmp/mongodb-27017.sock
2019-03-03T18:53:25.529-0600 I CONTROL  [initandlisten] now exiting
2019-03-03T18:53:25.529-0600 I CONTROL  [initandlisten] shutting down with code:100

But it works when I put the default path in mongo.conf file
Here is the default directory details (/var/lib/mongo)

[root@localhost lib]# stat mongo/
File: ‘mongo/’
Size: 4096          Blocks: 8          IO Block: 4096   directory
Device: fd00h/64768d    Inode: 70738359    Links: 4
Access: (0755/drwxr-xr-x)  Uid: (  979/  mongod)   Gid: (  973/  mongod)
Context: system_u:object_r:mongod_var_lib_t:s0
Access: 2019-03-03 18:23:09.971149485 -0600
Modify: 2019-03-03 17:49:21.178099641 -0600
Change: 2019-03-03 17:49:21.178099641 -0600
Birth: -

Here is the details of my new directory (/run/media/thomson/data2/mongo)

[root@localhost data2]# stat mongo/
File: ‘mongo/’
Size: 4096          Blocks: 8          IO Block: 4096   directory
Device: 820h/2080d  Inode: 4980737     Links: 2
Access: (0777/drwxrwxrwx)  Uid: (  979/  mongod)   Gid: (  973/  mongod)
Context: system_u:object_r:mongod_var_lib_t:s0
Access: 2019-03-03 18:52:09.764192229 -0600
Modify: 2019-03-03 18:32:17.212162930 -0600
Change: 2019-03-03 18:53:18.814193926 -0600
Birth: -

I am not sure what else need to be done. A help would be greatly appreciated. Thanks.

Run the following command to enable the user to write to /run/media/thomson/data2/mongo

sudo chown -R $USER:$USER /run/media/thomson/data2/mongo