Dynamically generate batched data for import I need to execute a test in which I have to simulate 20 years' historical data in PostgreSQL (and TimescaleDB) DB. My problem is that I have to generate .sql files and ingest them (using psql-client) in the targeted DB.

We made some calculations and the result is that our database will have 261 billion rows in our table for 20 years, so each year contains 13.05B data.

For each row, we have a timestamp (integer type) and I thought, to be more efficient, I write, in my .sql files transactions of 10.000 elements.
To make the generated .sql files small in disk space (I generate those files in python), I limited each file to 20M rows each.

So I thought that I could generate these files dynamically in a bash file and, when a file is generated, I run a psql command to ingest it into the DB, but the problem is that I don't know how to handle it: The ingest process takes much more time than .sql file generation, so in bash commands, I am afraid my bash script will wait the ingest process before starting to generate a new .sql file and execute the ingest process again.

So to summarize, I try to create an ingest process pseudo-batch based in which each generated .sql file that has been ingested successfully will be removed to avoid to take too much disk space.

How to avoid the fact it will wait the ingest process before starting an other .sql file generation and then start the ingest process?

This is for development purpose, these data I want to ingest are close to the one we would like to create in production mode. The aim, for now, is to execute read requests and compare those request in PostgreSQL and TimescaleDB.

The actual rate of ingest is going to be based on the number of spindles (physical hard disks) behind the RAID that holds your data files and log files.

A decent RAID setup should grant you the ability to achieve ~1 M rows/sec or higher.

Your algorithm is another major bottle neck.

Method 1

You can skip the create file step by making Python connect to the database directly.

I don't know if there is a Timescale DB driver for Python.

For speed:

Use BIND variables
Cache your Statement Handle
If the driver supports it, use BIND Arrays.

Make sure the "number of rows per BIND Array" and "number of groups between COMMIT" are variables in your program.  You'll want to play with these to find the sweet spot for your system.  That is: You will need to run benchmarks to find the best values for your setup.

The insert_vendor_list in this example uses the BIND Array technique.

Method 2

Sometimes, you want to review the data before it is generated.  In that case, you want to create a .csv file, not an .sql file full of INSERT statements.  Each of those INSERT statements will need to be hard parsed.

The RDBMS databases I have played with come with specialized applications that can ingest a CSV at (or near) the maximum rate (as defined by your RAID setup).

Notes on Speed Performance

Depending on what you are really testing, you should disable/remove all indexes and constraints on the target table prior to ingesting the data.  Then, create them afterwards.


SQL Server: modify data file size and shrink When I try to modify the Database File Size, it shows this error.

  Specified size is less than or equal to current size

I tried this query:

Alter database [dbname] modify file
(
 name=N'data-file-name', size=(newsize=90MB)
);

Now it works when I tried to shrink it, I mean change it from 100MB to 90MB, using this query that I saw on the internet:

DBCC ShrinkFile(data_file_name,size to)

But why?

Why can't I modify the file size to a smaller size and why shrinking it works?

What happens when I shrink a file and when I modify the file size?

When DB itself perform the shrink operation then it computes the value stored in the DB and try to store them in such a way that they takes less size, hence you see reduced size of DB. OTOH when you are saying that you want to modify size, then are you telling your DB in which order it should rearrange your data such that your data remain unchanged?


Database deployment using QSR (Quest Script Runner) is slow As part of the automatic deployment of a project, we deploy various Oracle scripts (packages, tables, data, etc...) using qsr - Quest Script Runner that is part of Toad. The script executes multiple sql files using the following syntax:

qsr -c "user/pass@server" -f "file.sql" -o "log.txt" -e

The problem is that we have around 100 sql files that we need to deploy on 8 environments. And each sql file execution takes approx. 1-2 seconds, even if it's a simple file or a simple check (if table exists, skip, else create it). And because of this sometimes the deployment can take 30 minutes or more.

I checked the only documentation I could find in the Help section of Toad and tried the -i parameter and also removing the -e parameter:

I see some improvement, mostly because of the -i parameter, but I am just worried what removing -e parameter really does. Are the scripts stacked then in QSR? When is QSR closed on the deployment agent (the running machine)?

Also, I am a bit puzzled that I cannot find any documentation online about this tool, so any help on how to speed this deployment up would be appreciate (I have already tried sqlplus as an alternative, but it seems our scripts are not 100% compatible with it - some of them are simply not executed).

For "Schema Updates", look at Edition Based Redefinition.  You might also want to check out 12.2's Application Container.


How to use COLUMN and TABLE options in a RAISE statement According to the docs, there are options to RAISE called TABLE and COLUMN. However , if I explicitly set them, they do not end up in the resulting error message. It seems only DETAIL and HINT are ever used in the error message. How can you get the other options to show up? E.g.:

RAISE EXCEPTION 'invalid_parameter' USING
    DETAIL = FORMAT('The specified user "%s" was not found.', _usr),
    HINT = 'Enter the username of an existing user.',
    COLUMN = 'username',
    TABLE = 'accounts';

Issues an error message:

... ERROR:  invalid_parameter
... DETAIL:  The specified user "aanders" was not found.
... HINT:  Enter the username of an existing user.
... CONTEXT:  PL/pgSQL function _api.find_user(text) line 24 at RAISE
... PL/pgSQL function _api.accounts_update_trigger() line 97 at assignment
... STATEMENT:
...     WITH pg_source AS (...

There is no "TABLE" or "COLUMN" shown.

UPDATE

The key term I needed was "VERBOSITY" - once I searched for that I found duplicate questions, e.g. https://stackoverflow.com/q/41959752/1571426
(not sure if a question on a different forum makes this "duplicate" or not.)

It depends on the client-side verbosity. When using psql, issuing

\set VERBOSITY verbose

before calling the statement would caused the error message to include:

TABLE NAME:  accounts
COLUMN NAME:  username

In recent versions of psql, it's also possible to issue \errverbose after the fact to get the last error message reformated in verbose form.


What other databases have a feature similar to Oracle's Flashback Query? I am interested to know what other databases have features similar to Oracle's Flashback Query. It would seem that any database that supports MVCC could have the feature, but which do?  SO has this question for SQL Server, but it seems more appropriate here.

PostgreSQL does not currently have it.  But you are right, with MVCC it should theoretically be possible.  In fact, the old university Postgres had an equivalent feature called time travel, but it was later removed because it caused too much overhead and the practical use was limited.  While it would probably be possible to resurrect it, since then there have been many optimizations to the MVCC setup that would make it much harder.


How to SELECT multi-columns from a column? I have a table with the structure

book_id int(11),
section_id int(11),
note_id int(11),
item_number int(11),
text varchar(255),
UNIQUE(book_id,section_id,note_id,item_number)

item_number can be 1 or 2 only.

How can I make a SELECT query to get text for item_number 1 and 2 together for each book_id,section_id,note_id

SELECT book_id, select_id, note_id, TEXT_number_1, TEXT_number_2

TEXT_number_1 = text (WHERE item_number=1)
TEXT_number_2 = text (WHERE item_number=2)

I considered two options:

GROUP BY, which can give me a CONCAT(TEXT_number_1,TEXT_number_2)
JOIN, which I am not sure what is the best query.

SELECT t0.book_id, t0.select_id, t0.note_id, t1.TEXT_number_1, t2.TEXT_number_2
FROM (SELECT DISTINCT book_id, select_id, note_id
      FROM `table`) t0
LEFT JOIN `table` t1 ON t0.book_id=t1.book_id
                     AND t1.item_number=1
                  /* AND t0.select_id=t1.select_id
                     AND t0.note_id=t1.note_id */
LEFT JOIN `table` t2 ON t0.book_id=t2.book_id
                     AND t2.item_number=2
                  /* AND t0.select_id=t2.select_id
                     AND t0.note_id=t2.note_id */


Do you need to include all foreing keys of a parent table in the child table? Suppose you have a table

my_table
-----------
id
name
foreign_id

And you are creating another table that will reference my_table. I think that this would be enough:

other_table
-----------
id
name
my_table_id

However, when creating a diagram on MySQL workbench, if I create a relationship, whether identifying or non-identifying, 1 to 1, or 1 to many, I get this table:

other_table
-----------
id
name
my_table_id
foreign_id

As you can see, both, the id of my_table, and the foreign_key of my_table get added to the new table. Is that really necessary? I would say that other_table.foreign_id is not necessary.

I first noticed this when I was working on schemabank.com (which seems to be gone now, such a shame). And now I noticed it too on MySQL workbench.

I think I follow but here's an example using more meaningful names:

Given these two tables:

CREATE TABLE Persons
(
 person_ID INTEGER NOT NULL UNIQUE
);

CREATE TABLE Employees
(
 employee_ID INTEGER NOT NULL UNIQUE,
 person_ID INTEGER NOT NULL UNIQUE,
 FOREIGN KEY (person_ID)
    REFERENCES Persons (person_ID),
);

I think you are saying you would add a referencing table like this:

CREATE TABLE HourlyPaidEmployees
(
 employee_ID INTEGER NOT NULL UNIQUE,
 FOREIGN KEY (employee_ID)
    REFERENCES Employees (employee_ID)
);

I would be inclined to agree because I am a human who can understand that given two candidate keys I will choose employee_ID because the table name HourlyPaidEmployees strongly suggests it pertains to employees.

However, it seems your software generates this:

CREATE TABLE HourlyPaidEmployees
(
 employee_ID INTEGER NOT NULL UNIQUE,
 FOREIGN KEY (employee_ID)
    REFERENCES Employees (employee_ID),
 person_ID INTEGER NOT NULL UNIQUE,
 FOREIGN KEY (person_ID)
    REFERENCES Employees (person_ID)
);

I would guess that the software has been designed so that given two candidate keys it should not attempt to 'guess' which one to use and therefore uses both and leaves it to the user to decide which one to remove.


The most efficient method to obtain the total entries into a mysql table I have a table that has a very high usage (insert/update/select) - one of the operations is
to obtain a count of the number of rows.
So far I've been using the classic select count(pk) from table but I'm thinking to read it from information_schema instead of doing the count.

select table_rows from tables where table_name = 'my_table'

Is this reliable enough ?

If there is a different method please let me know.

thanks!

If the table is MyISAM then you can

select table_rows from information_schema.tables
where table_schema='my_db' table_name = 'my_table';

or

select count(1) from my_db.my_table;

Either will work since the row count is physically stored in the .MYD file of the MyISAM table.

If the table is using InnoDB, the information_schema is absolutely no good for you because of the MVCC and transaction isolation it supports.

What is even worse about InnoDB is terms of counting is the fact that mysqld will guess at the cardinality of a table (row count) by doing BTREE dives in the indexes.

To learn more about this InnoDB Counting phenomenon, please read my Apr 23, 2012 post : Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...?


Does my ERD look well designed? I am making a database at no cost for a local dance school. I'm still very new this would like to be sure that I designed it appropriately.

From the front end, the parent will: fill out their personal info, create a student for each child and, an account number will be assigned to the parent. Each student will have an annual fee(s) attached to their name. Student will be enrolled in classes by the parent and, I plan to total the costs by adding together each unique enrolment for a student.

I think the diagram can explain the rest. Have I designed done this properly?

PRIMARY KEY (`STUDENT_ID`, `ANNUAL_FEES_ANNUAL_FEE_ID`, `PARENTS_PARENTS_ID`, `PARENTS_ACCOUNTS_ACCOUNT_ID`),

It seems like Students would have student_id as its PRIMARY KEY.

DECIMAL(4,2) -- do you want to limit things to $99.99?

This is what I mean about many-to-many relation table:

CREATE TABLE XtoY (
    # No surrogate id for this table
    x_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to one table
    y_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to the other table
    # Include other fields specific to the 'relation'
    PRIMARY KEY(x_id, y_id),            -- When starting with X
    INDEX      (y_id, x_id)             -- When starting with Y
) ENGINE=InnoDB;

What is a "Performance_Student"?

Parents table:

parent_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
family_id MEDIUMINT UNSIGNED NOT NULL,
type ENUM('mother', 'father', 'guardian', 'grandparent', 'other'),
`LAST_NAME` VARCHAR(35) NULL,
`FIRST_NAME` VARCHAR(35) NULL,
`EMAIL` VARCHAR(50) NULL
PRIMARY KEY(parent_id)

The Phones table would have parent_id and be many_to_1.


Calculating Median Value within a Group By I have a Stored Procedure which calculates the Mean duration of a request / response cycle within 10 minute intervals. This works well and suits my needs for plotting charts. What I would like to do next is calculate the Median value... I suspect I would need a subquery but can't figure out how to accomplish this.

SELECT dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0) AS [Time]
     ,AVG(CASE WHEN Duration is null OR Duration = 0
              THEN null ELSE Duration
          END) AS [Mean Response Time]
FROM [Application].[Exchange] WITH (NOLOCK)
WHERE [Started] >= '24 Oct 2012' AND [Started] < '25 Oct 2012'
GROUP BY dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0)
ORDER BY dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0)

Can anyone suggest a way I might accomplish this?

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- replace WITH (nolock)

SELECT [Time]
       ,AVG(NULLIF(Duration,0)) AS [Mean Response Time]
       ,AVG(CASE WHEN Rn in (Round(CN/2.0,0),Round((CN+1)/2.0,0))
                 THEN Duration END) [Median]
FROM
(
  SELECT [Time], Duration,
         Rn = Row_Number() over (partition by CASE WHEN NullIf(Duration,0) is null then 1 else 2 end,
                                         Time
                                 order by Duration),
         Cn = Count(1) over (partition by CASE WHEN NullIf(Duration,0) is null then 1 else 2 end,
                                         Time)
  FROM (
    SELECT dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0) AS [Time],
           Duration
    FROM [Application].[Exchange]
    WHERE [Started] >= '20121024' AND [Started] < '20121025'
  ) X
) Y
GROUP BY [Time]
ORDER BY [Time];


How to sort values starting with a letter followed up by a number? I have a series of values, possibilities are : A1, A2, B1, B2, A100, A101, B100, B200, 1, 2, 3, 4 and so on.

How can I sort it so that first the letter is sorted, and after that the number with the numbers from the previous list would be going like this:

A1
A2
A100
A101
B1
B2
B200
1
2
3
4

The list itself is a lot larger and not really predefined so preferably no solutions in which each value needs to get handled separately.

I'm using Microsoft SQL Server 2016 (SP1) (KB3182545).

You need to parse the string to separate the alpha + numeric part.

Assuming that it is always alpha first follow by numeric and not the other way round

the 2 cross apply split the alpha and numeric part from the string

select  *
from    yourtable t
        cross apply
        (
            select  alpha   = LEFT(your_col, PATINDEX('%[^0-9]%', your_col))
        ) AS a
        cross apply
        (
            select  num = CONVERT(INT, REPLACE(your_col, alpha, ''))
        ) AS n
order by case when alpha <> '' then 1 else 2 end,
         alpha,
         num


Passing path from batch file to mysql My batch file will contain some thing like this :

    set MUSER=root
    set MPASS=xprd123
    set MHOST=172.33.49.55
    set DB=DBName1
    set MBIN=C:\Program Files\MySQL\MySQL Server 5.5\bin
    set scriptpath=D:\STATS_COLLECTOR
    "%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "%MHOST%" "%DB%" -e "set @2:='%scriptpath%'; source DBQuery1.sql;"

Content of DBQuery1.sql will be :

SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS'
UNION
SELECT
MAX(IF(variable_name = 'INNODB_OS_LOG_WRITTEN', VARIABLE_VALUE, NULL)) AS INNODB_OS_LOG_WRITTEN,
MAX(IF(variable_name = 'QUESTIONS', VARIABLE_VALUE, NULL)) AS QUESTIONS
FROM information_schema.GLOBAL_STATUS
INTO OUTFILE '@2/stats.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
;

I am not sure this is the way to pass path to the SELECT query inside DBQuery1.sql.

This code is not working.

I looked at other posts on passing path to batch file, but whatever I tried, I am not able to solve this issue.

When I execute the batch file, I am not sure if the path is getting passed into the sql file and if yes, why @2 is not getting the path passed.

Could anybody kindly help on this.

MySQL's source command executes the file as a separate process.  By design, it executes in a different session than the one where you assigned the variable.  You should find that executing set @2:='%scriptpath%'; source DBQuery1.sql; from within a mysql.exe session also does not work, either, although I haven't tested this myself recently.

As a workaround, you can write your script to automatically generate the SQL script file each time the file is run:

set MUSER=root
set MPASS=xprd123
set MHOST=172.33.49.55
set DB=DBName1
set MBIN=C:\Program Files\MySQL\MySQL Server 5.5\bin
set scriptpath=D:\STATS_COLLECTOR\
set scriptfile=D:\STATS_COLLECTOR\DBQuery1.sql

echo SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' > %scriptfile%
echo UNION >> %scriptfile%
echo SELECT >> %scriptfile%
echo MAX(IF(variable_name = 'INNODB_OS_LOG_WRITTEN', VARIABLE_VALUE, NULL)) AS INNODB_OS_LOG_WRITTEN, >> %scriptfile%
echo MAX(IF(variable_name = 'QUESTIONS', VARIABLE_VALUE, NULL)) AS QUESTIONS >> %scriptfile%
echo FROM information_schema.GLOBAL_STATUS >> %scriptfile%
echo INTO OUTFILE '%scriptpath%/stats.csv' >> %scriptfile%
echo     FIELDS TERMINATED BY ',' >> %scriptfile%
echo     ENCLOSED BY '"' >> %scriptfile%
echo     LINES TERMINATED BY '\n' >> %scriptfile%
echo ; >> %scriptfile%

"%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "%MHOST%" "%DB%" < %scriptfile%

Another alternative is to write the query in a more versatile scripting language such as PowerShell, which would allow you to use the MySQL Connector/Net API to execute any arbitrary set of queries you wanted.


ERD for a web development company I am working on an ERD which represents the relationships between entities in a Web development company behavioral model.
There I have entities like:

Customer
Service ----------- (like: web development, web design, web consultation, ...)
Order-item ------- (like: web development, web design, web consultation, ...)
Order
Invoice
Project
Project-phase
Task --------------- (like: web development task3, web design task7)
Developer
Delivery ----------- (like: the developed web app, web consultation session, ...)

I have this diagram so far, though I know it still has major problems, and it is just for a start point:

Main concept

Customer makes order-items on services, combined to form an order which relates to an invoice.
Each project has multiple project-phases with each project-phase related to an order.
Each project-phase has multiple tasks, each of which is assigned to a developer.
Each project-phase has a delivery, which will be delivered upon payment of the related orders invoice.

Questions

Are has and contains completely the same? If not, how to choose between them for relationships?! what is the measure?!
Are there any rational problems with the Main concept section?
Any helpful ideas or suggestions to improve this model?

Any help with this, would be really appreciated.

Update 1

Refined version of the diagram

Update 2

Considering an analogy of a Fast food restaurant would be helpful:

Customer ---------- A hungry person
Service ------------- Making a burger, Home delivery, Mobile delivery, ...
Order-item --------- A burger, ...
Order ---------------- A home-delivered burger
Invoice -------------- The printed invoice
Project -------------- Serving the customer for a complete year (like a contract)
Project-phase ----- Serving the customer for a single day
Task ----------------- Preparing the kitchen, process the meat, ...
Developer ---------- The cookie
Delivery ------------- A burger in wrapped in wax papaer and a drink with the tray

Update 3

(1st comment from  Joel Brown)

  Project seems to be unrelated to Customer in your diagram. Is this what you intended?

I think Order is and Project should not be related, as I think it is something off-the-counter and customer has nothing to do with it. she/he is only after the delivery and is concerned about time and money, nothing else. Please tell if I am wrong.

  Are all Orders part of a Project or can you have orders that aren't in a project at all?

All Orders are part of a project, except those new ones which are waiting in the process queue.

  Similarly, you relate order item directly to customer, but also to order. Are orders split between customers?

Please think of Order-Item as these:

web design
web design consultancy
domain name consultancy
...

So, customer choose some of these items, collected as an Order which has an invoice and could be paid. Order-Items are directly related to offered Services. I don't understand what do you mean by Are orders split between customers?.

  If not you don't need a ternary relationship between customer, order item and service. Rather, an order item is an intersection between order and service. Order items belong to customers by inheritance from order (or even project?)

This is somehow true: Order items belong to customers by inheritance from order, but I think it is more rational to say it like this:

  Order-Items, inherited from Service, belong to Order which itself belongs to Customer.

You're oversimplifying the relations.  No two relations in your diagram are the same, since they don't relate the same domains.  <ORDER> has <ORDER-ITEM> is not the same as <PROJECT> has <PROJECT-PHASE>.  They might both be injective, but that's not sufficient to call them the same.

Try to give your relations more descriptive names.  While attributes (aka one-to-one binary relations) tend to be aggregated on a common determinant to create "entity tables", multivalued attributes and more complicated relations like the ternary relation between Customer, Service and Order-item usually become tables on their own.  Makes really doesn't describe the relation or resulting table properly.

I suggest you add cardinality indicators to your diagram and give your relations more descriptive names, using the predicate statement of each relation for guidance.

On second thought, those names are probably fine in a conceptual design as long as you read them together with the entity sets they relate.  When you get to physical design, 1-to-1 and 1-to-many relations will probably be implemented as attributes of one of the entity sets, so the relation names won't affect the physical design.  The many-to-many relations and non-binary relations will become their own tables, for table names at worst you can concatenate the entity names, e.g. CustomerServiceOrderItems.


Query returns results on pgAdmin running on remote client but not directly through psql on db server When I query my database using a role in pgAdmin on a remote client, I get the results of the query. However if I run the same query directly on the db server with the same role , I don't see the query output. I see the table are present with the "\dt" command but I am not able to view the results of the queries on these tables. Is there some configuration that is needed to enable querying the db from the same host ?

Note:
Made edits to the question based on the comments.

Found the resolution to my issue. To check query results you need to terminate every query in psql with a semicolon ;. If the semicolon is not used you don't see the query result when using the psql command line.

Quote from the manual

  Entering SQL Commands

  ...

  At the prompt, the user can type in SQL commands. Ordinarily, input lines are sent to the server when a command-terminating semicolon is reached. An end of line does not terminate a command. Thus commands can be spread over several lines for clarity.


shall I use multiple separete tables or one table with multiple partition on oracle I have an entity lets call it Response, and another entity called ResponseType, Response entity contains the ResponseType; so when I am building the physical model I confused with how to implement it; I have two option ; 1. I can create a Response table for each of ResponseType. 2. I can create a Global Response table with a ResponseType Column and create a partition for each ResponseType; Note that; Response Entity is same for each of ResponseType. Each ResponseType is equivalent.

What is your idea?

This depends on two things: the number of (expected) rows that you'll be dealing with, and the number of different fields between each ResponseType.

How many rows are you going to be dealing with?  And about how many of each ResponseType?  If you're talking about a solution that involves millions of rows, then partitioning the tables makes sense.  However, if you're only talking about hundreds or even thousands of rows then I wouldn't worry about it.

If the fields between each ResponseType aren't very different, the you probably won't need as many individual ResponseType tables.  However, if the data for each ResponseType is significantly different, then I can see the reasons behind creating (many?) multiple ResponseType tables, with one global Response table.  If it were me, I would try to create as few ResponseType tables as possible.


SSMS connection to Availability group listener doesn't failover I've set up an Availability Group on SQL Server (on Azure VMs) and created a Distributed Network Name as the listener for this. (SQL Server 2019 on Windows 2019)
If I connect to that listener in SMS then it works fine. If I manually failover and re-connect in SSMS it again works fine.  Running select @@SERVERNAME in both cases returns the primary server.
However, if I leave the original connection open, and later try select @@SERVERNAME then it still returns the server it connected to (which is now secondary). I can verify this by trying to INSERT a row and it tells me this server now needs a READ ONLY INTENT connection and I can't do an update.
Is it expected that you have to open a new connection after a (manual) failover event?

What you describe is normal behavior for SSMS, at least all the times I've done failover testing.
But Management Studio is a poor tool to test this type of thing.  Best practice for an application is to have retry logic built into it that will upon error dispose of the current connection and re-establish, which will connect to the now primary side.  And SSMS does not do that.
I've never seen or caught SSMS auto-magically re-connecting to the primary side once it's connection is established.


Simple way to list order of referential integrity in Postgres Is there a clean, simple, and preferably prepackaged (ready function?) way to list the order of referential integrity in a Postgres database, rather than trying to read the system tables to find that order manually, which is tedious and time consuming?

What I mean is, if the entity hierarchy is

  USER --> ACCOUNT --> TRANSACTION

meaning that a single user can have multiple accounts and that a single account can have multiple transactions, if I want to delete all the three tables without breaking any foreign keys, I would have to do it in the exact following order:

DELETE FROM TRANSACTION;
DELETE FROM ACCOUNT;
DELETE FROM USER;

Now this is a very simple and obvious example. But real life databases have a lot more tables and foreign keys. If I wanted to delete every table down to 0 rows, is there a quick way to obtain the exact order of referential integrity to do it in?

You can "dive" into the system tables and get all the tables that reference a specific table (and all those that reference them, etc). It will be a recursive query, not very difficult to write.

However, for the simple operation you want to to perform, there is a simple solution:
TRUNCATE with the magic option CASCADE:

truncate table a cascade ;

Tested at rextester.com


Automation approach to executing Shell scripts and performing checks on an Oracle DB I am in the process of producing an automation framework that will:

Check Oracle tables for entries, such as IDs and status numbers
Log onto a UNIX box via Putty and switch to a directory where the scripts are located
Execute Shell scripts
Perform further checks on Oracle tables depending on values
Repeat above actions (conditional)
Log/notify users of errors during the process above

Would I be able to achieve this purely using Bash/Shell and SQL, or would I need to possibly add another language into the mix?

I was thinking of building it in a modularized fashion.

Thanks

If it were me, I would put everything in the database that I can.  For instance, all SQL logic should go into packages/procedures, and I would control the execution of these routines via DBMS_SCHEDULER jobs if possible.  Notifications based off of results from these routines can be sent via UTL_MAIL.  Putting this in the database allows you to lock it down with proper security.

If you truly need to execute Shell Scripts (steps 1 and 2 above) outside of the database, there are various ways you can do this from a PL/SQL routine, but they get hard to manage if things don't go exactly as planned, so the shell script needs to be able to handle errors gracefully.  Just try to minimize security issues by properly locking down any external files being called, etc.  I'm not a fan of calling shell scripts from PL/SQL code, but ultimately your process and comfort level is going to dictate your approach.

There are other approaches too, especially if you have money to spend.  Another option would be to look into enterprise-level scheduler applications.  There are a fair number out there, and if this process is complicated enough or starts affecting other processes within your enterprise these can help make sense of the chaos.

At the end of the day, it'll come down to your comfort level with the technology(ies) and the amount of resources (e.g. time and money) you can spend that will determine your best approach.


How can I check the integrity of disabled foreign key relationships for each individual table? (SQL Server 2008) I'm porting a huge legacy database that many different legacy frontends depend on. When I'm finished, a new frontend will be running alongside all the old legacy frontends minus the frontend I replaced against the ported database.

Anyway, the legacy database doesn't have any foreign key relationships defined. My plan is to start putting them in, but leave them disabled to support the old frontends. However I want to use the disabled foreign key relationships in some kind of stored procedure to check the integrity of the database (I'll execute this as part of my integrity test suite every so often). Would that involve attempting to turn each one on, and then recording the error message, then turning it off again? Is there a special command that can do this all in one go?

Any scripts you have to help would be nice, I'm not good at T-SQL. Ideally I'd like the script to return a table consisting of the table name, a record's primary key value, and the  name of the foreign key for that record that failed.

Thanks!

These DBCC CHECKCONSTRAINTS commands can be run as follows:

DBCC CHECKCONSTRAINTS (TableName) - checks an individual table
DBCC CHECKCONSTRAINTS (ConstraintName) - checks an individual
constraint
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS - checks all constraints
in the database
DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS - returns all rows that
violate constraints
DBCC CHECKCONSTRAINTS WITH NO_INFOMSGS - suppress messages when query
runs
DBCC CHECKCONSTRAINTS check only enabled constraints

Keep in mind that SQL Server needs to read through all of your data to check for the constraints, so be careful not to run this across the board for your large or very busy databases. This should be something that is run off hours and also for very large databases you should run this either at a table level or a constraint level.


SQL Query to delete while database size is larger than 200GB I have a SQL table which is used to store a large number of logs.

I want to have a stored procedure or something along the lines of that. That runs every minute and deletes items from the end of the log table as long as the database size is above 200GB.

so something like:

While(databaseSize > 200GB)
{
Batch delete 100 entires From [dbo].[logs] OrderBy DateTime
}

How would I go about doing this?

[Edit]
SQL Azure limits my database size to 250GB. Hence why I need to limit and still keep a small buffer for other tables.

In a maintenance plan run something like this like hourly

While((select count(*) from From [dbo].[logs]) > 1000000)
begin
    delete top (10000) From [dbo].[logs] OrderBy DateTime
end


Creating SQL Servers on VMWare Can you recommend a good self service solution for creating SQL Databases? Our dev and test teams need to frequently create new databases. I would like to keep these databases as close to production configuration as possible but I would like them to be able to create this on their own without me being in the picture? We are a VMWare shop, so I prefer solutions that work with VMWare.

[Edit - Clarifying the question. Need to install a SQL server from scratch. Create a new VM, Install SQL and create a new DB]

Thanks,
Joe

Following your comment, I'm not entirely sure this question is a good fit for this site but:

VMWare vCloud Director for self-service VM creation.
If you're SQL2008R2+ you can SYSPREP a SQL installation. Alternatively, the standard install process shouldn't be a challenge for a SQL literate developer or sysadmin.
As for creating a database... anyone that can't do so probably shouldn't be coding against SQL Server.

If you're a Microsoft shop and your developers are MSDN licensed, you might want to expand the scope of this idea and take a look at Visual Studio 2010 Lab Management.

  Visual Studio Lab Management is an extension of Microsoft Test Manager
  that helps you to optimize the use of Microsoft Hyper-V technology to
  manage and use virtual machines in testing, building, and developing
  applications in Visual Studio 2010. Visual Studio Lab Management is
  integrated with System Center Virtual Machine Manager (SCVMM) to
  enable you manage multiple physical computers that host virtual
  machines and to manage the storage of virtual machines, virtual
  machine templates, and other configuration files in SCVMM library
  servers.


Mongodb Bulk find and remove is taking long time I'm just making my hands dirty(handson) with MongoDB. First I did bulk insert of 1 million documents.

// Bulk insert query
var bulk = db.bulktest.initializeUnorderedBulkOp();
for(var i=0;i<1000000;i++){bulk.insert({a:i});}
bulk.execute();

It took 10 seconds to insert all the document which is comparatively good. None of the documents were missed.

However, when it comes to bulk find and removal, the performance has drastically came down and is really bad as it is removing each document in the order of 10ms.

// find and removal bulk query
var bulk = db.bulktest.initializeUnorderedBulkOp();
for(var i=0;i<1000000;i++){bulk.find({a:i}).remove();}
bulk.execute();

Has anyone done tweaking around bulk removal ?

I've only one option to drop the complete collection and I don't want to do this.

This shouldn't have anything to do with bulk operations. The find operation must read through the collection, which takes time on 1million documents.

Adding an index on a should improve the speed dramatically.


Mysql hash partition In my system's database we have a table which stores 3 fields: id int, name(varchar) and description (mediumtext). The table is by far the biggest in our database and the queries on it are exclusively by id, which is indexed.

The problem is, we are unable to delete or archive any data and in addition, there is no way to partition the table in a way that will make sense. Currently the performance is good (we're talking about a 7.5 GB table) but I'm afraid we'll encounter some issues in the future.

A colleague suggested me to experiment with hash partitioning, which should divide the table to equally divided groups of data and keep the index level low.

Now my questions regarding this topic are:

Is hash partitioning a good solution to solve the issue I'm talking about, or the index is enough considering the queries are only by id
In case that I decided to go for hash partitioning, how many partitions should I go for? To remark, the table is currently 7.5 GB and expected to keep growing.

jkavalik is right.

I'll say it more strongly: PARTITION BY HASH is possibly useless in any situation for enhancing performance.

Your table is referenced only by the PRIMARY KEY(id), correct?  And it is InnoDB, correct?  If you have a million rows, the BTree that contains the PK and all the data is about 3 levels deep.  For a _trillion_rows, it will be about 6 levels.

The million-fold increase in data size (from 1M to 1T) might slow down a "point query" by a factor of 2.  That's all.


Optimizing query. FOR XML PATH or some alternatives? Is there any way to optimize the FOR XML PATH statement? Or maybe I should use another approach?

Current approach is not acceptable at all. It takes minutes. I know view is a really big union and it takes time to consume that, but maybe is another way...

Here is the query:

SELECT t.serialNumber as TVM
  ,[issuanceDate] as transactionDate
  ,ioy.tvmTransactionId as TVM_TRANS_ID
  ,STUFF((SELECT ', ' + stv.carrierSN
    FROM HERMES.wts.v_SaleTransactionView as stv
    WHERE stv.tvmTransactionId = ioy.tvmTransactionId
    and stv.serialNumber = 'M040'
    FOR XML PATH ('')), 1, 2, '') as [serial_numbers]
  FROM [hermes].[wts].[IOYLog] ioy
  left join Hermes.hermes.Terminals t on ioy.tp_terminalId = t.tp_terminalId
  left join hermes.hermes.POS p on t.tp_POSId = p.tp_POSId
  left join [Hermes].[wts].[IOUPaymentStatus] [is] on [is].[tp_paymentStatusId] = ioy.status
  WHERE [is].includeInReports = 1
  and (issuanceDate BETWEEN '2017/09/01' AND '2017/09/08') and (t.serialNumber = 'M040')
  ORDER BY transactionDate

Execution plan: https://www.brentozar.com/pastetheplan/?id=Sk7i98Wjb

Looking at this in Sentry One Plan Explorer, there are four major indexing issues, all associated with index scans hiding an expensive residual predicate:

The following four indexes will eliminate these scans and speed up this execution plan:

--- Currently scanning 7 million rows to return 11
CREATE INDEX give_me_a_name
ON [wts].[Prepaid_TransactionLog]
    ([tvmTransactionId])
INCLUDE
    (tp_terminalId, tp_POSId, status);

-- Currently scanning 21.5 million rows to return zero
CREATE INDEX give_me_a_name
ON [wts].[KBETicketTransactionLog]
    ([tvmTransactionId])
INCLUDE
    (carrierSN, tp_terminalId, tp_POSId, status);

-- Currently scanning 6.25 million rows to return 12
CREATE INDEX give_me_a_name
ON [wts].[Intercity_TicketTransactionLog]
    ([tvmTransactionId])
INCLUDE
    (formType, status, tp_terminalId, tp_POSId);

The fourth index is a little more complicated since the key involves a conversion to integer to match [wts].[IOYLog].[tvmTransactionId]. This means either altering the existing column, or providing a computed column as follows:

-- New computed column, should be instant to add (metadata only)
ALTER TABLE [mazovian].[Transaction]
ADD [SequenceAsInteger] AS CONVERT(integer, [Sequence])

-- Currently scanning 61.6 million rows to return 70
CREATE INDEX give_me_a_name
ON [mazovian].[Transaction]
    ([SequenceAsInteger])
INCLUDE
    (Id, Sequence, Terminal_id, Detail_id, Location_id);

Adding those four indexes should speed up the query significantly. As with all indexing changes, you should assess them on a test system before deciding on deployment.

There are a number of other small indexing improvements you could consider to avoid the Key Lookups in the plan:

add [status] to the existing index [IOYLog].[IX_IOYLog_tp_terminalId_tvmTransactionId_issuanceDate_owedAmount]
add carrierSN, tp_POSId, and status to index [TransportTransactionLog].[IX_TransportTransactionLog_tp_terminalId_tvmTransaction_KK]
add End_Status to index [Transaction_Distribution].[I_mazovian_Transaction_Distribution_Ticket_id]


What kind of hardware/software config should I look at for a MySQL database with 10,000 plus tables I am looking at rolling out a CMS system that will require the creation of around 10,000 tables within the primary MySQL database of the system.

The database will be the data store for several hundred small website front ends that might draw a modest load of around 150k unique viewers per month, but this might have to scale on short notice.

I'm looking for some advice around what kind of hardware should be used to be cost effective but also to have the ability to scale if the need arises.
I would also like some advice around the software configuration: i.e. should the MySQL setup be clustered or just straight forward MySQL with a high number of open files?

Any advice will be greatly appreciated!

welcome to the dba.StackExchange.com website, and it looks like you've got quite a pickle. So before I answer the question that you posed, I'll mention some details to (hopefully) get you off to a good start:

10,000 tables in a database are always wrong.

I take that back. If you had all of the Microsoft or IBM corporate databases on one server, 10k tables might be appropriate. However, the data aggregate that outlines is enormous. So we'll overlook the "it's possible to be right" and stick with the 99% of statistical non-outliers. For all intents and purposes if you suggest 10,000 tables to a dba, they will laugh at you.

I'm going to hazard a guess that your tables look like: (I don't mean the exact structure, I mean the concept of the structure)

website1_articles
website1_navigation
website1_overhead
website1_blogs
website2_articles
website2_navigation
website2_overhead
website2_blogs
.......
website375_articles
website375_navigation
website375_overhead
website375_blogs

which would (for this very simplistic example) yield me right at 1500 tables. But if you'll notice, the only thing that changes on each one is the website ID.

I could instead normalize to include a website ID on each record and reduce the entire mess to four tables.

Now, I know what you're thinking:

  "But that means I have to redo my entire database architecture, and it will ruin everything."

OR you're thinking:

  "But that will destroy the security, because then anyone could read anyone else's website information"

But really a) you've already killed the database by suggesting 10k tables, and b) this is in no way different than what you're suggesting.

Of course, it's entirely possible I'm wrong here, but anytime anyone suggests 10k tables, this is the course they're taking. And it's wrong.

So here's my bit of teaching for the day:

SQL is about sets. You should really consider that the power of SQL is in its ability to handle sets quickly and easily, and in the ability of the language to parse for the relevant matches to a set of query parameters. What this means for you is that the intent of SQL is to have one massive table of all blog posts (to pull a single example, that may or may not be appropriate) and to select the matching blog posts from the table by query.

Additionally, you ask about open files, but that's just it, by doing what I suggest above, you have at most four tables to worry about open files on. And it's "how the big boys generally solve this problem" if you're really curious.

But the fact that you're asking about open files and other micro-optimizations leads me to think that

The real point I'm trying to make here is this: Don't try to re-engineer the database according to what you think would make it optimized, because you're not a database engineer.

While this may seem to be a bit harsh, it's not. I assure you. If you were a database engineer, or worked on a database core-development team, you would already know the answers to the questions. Thus QED. But you have to understand that this is what it sounds to an experience DBA like what you're doing; this trying to optimize the database by theory rather than rational approach.

Now, having said all that, and having been a bit harsh, and having seemed to reject your request outright, I'ld like to ask for more details so I can really focus on answering the question you've asked, altho, if I can hazard a guess, you've gone and ignored everything I've had to say after the second paragraph.

So with that in mind, here's what would make this question a little more answerable:

What platform are you running this on? How many disks are there? What size and speed are they? What version of the OS? If Linux, what distro do you intend on running? Will this be in a VM? Will it be supported for updates in the future or do you need a prop-once and forget box? (meaning negligible long term support budgeting)

And if I may ask, what framework are you using for this database that it will have 10k tables? I ask for personal curiousity.


Mealplan: Allow ordering until 9am Imagine you have a meal service and everyday there is a different meal which customers can order.
Mon, Dec 12: Pasta
Tue, Dec 13: Pizza
Wed, Dec 14: Gnocci

Orders are possible until 9am.
This mean Monday on 8am customers may order Pasta for today, at 9:15am they are no longer allowed to order for today.
How to handle this is with PostgreSQL.
I see two ways:

Create a cron job which switches the column "orderable" to False.
Create a view which calculates the field "orderable" on the fly.

Is there a feasible third or forth way?
Which way is simple and robust?

As long as you have the dates in a date column, you can use a simple CASE to choose, which meal is selected.
The result if the query, depends when you look for the example, it will choose pasta before 9 in the morning a Minute later and you would get  gnocchi, you only have to enter the menus ahead of time
In an event you could delete the old menus, but that is not necessary, as you will not have millions of rows.

CREATe TABLe "OrDERS" ("orderdate" date, "food" varchar(40))

INSERT INTO "OrDERS" VALUES (CURRENT_DATE  ,'Pasta'), (CURRENT_DATE  + INTERVAL '1 day','Gnocchi')

SELECT to_char("orderdate",'Dy, Mon DD'), "food" FROM "OrDERS"
WHERE "orderdate" =
       CASE WHEN NOW() < now()::date + interval '9h' THEN now()::date
            ELSE now()::date + INTERVAL '1 day'
       END

to_char     | food
:---------- | :-----
Tue, Dec 22 | Gnocci

db<>fiddle here


pg_upgrade --link fails: `directory "/..." already in use as a tablespace` I'm attempting to upgrade from 9.6.2 to 10.5 using pg_upgrade

My upgrade command (from OpenBSD pkg-readme):

su _postgresql -c "cd /var/postgresql && \
    pg_upgrade --link -b /usr/local/bin/postgresql-9.6/ -B /usr/local/bin \
    -U postgres -d /var/postgresql/data-9.6/ -D /var/postgresql/data"

results in the following error

CREATE TABLESPACE "mydb" OWNER "mydb" LOCATION '/path/to/postgresql/mydb'; psql:pg_upgrade_dump_globals.sql:87: ERROR:  directory "/path/to/postgresql/mydb/PG_10_201707211" already in use as a tablespace

I'm using --link because the tablespace mentioned above is using 1.5TB of the available 1.8TB on its partition. So I don't think I'd have the disk space to do pg_upgrade without --link

I have disk space on other partitions if that helps.

EDIT

Before I encountered this error, I ran thepg_upgrade command without the --link flag, but then killed it when I realized there was insufficient disk space.  After that I re-ran initdb followed by the pg_upgrade command above.

Now I have 9.6 running again, but I have two directories in my tablespace location:

[root@host:~]$ls /path/to/postgresql/mydb/
PG_10_201707211   PG_9.6_201608131

Can I safely rename PG_10_201707211 to PG_9.6_201707211 and try the upgrade again?

"PG_10_201707211" must be a left over directory from your aborted attempt at running pg_upgrade without the --link flag.  You can delete it, or out of an abundance of caution move it to one of your other partitions.  Renaming it the way you describe wouldn't accomplish much and would be very confusing if you have more problems in the future which require more investigation.


Calculate period start and end date My table:

Date       Employee     Status
-----------------------------
20171106   001          At work
20171107   001          Sick leave
20171108   001          At work
20171109   001          At work
20171111   001          Sick leave (A gap here)
20171112   001          Sick leave
20171115   001          At work (Another gap)
20171116   001          At work

Desired result:

Employee      Status        StartDT                       EndDT
-------------------------------------------------------------------
001           At work       Some time in the history      20171106
001           Sick leave    20171107                      20171107
001           At work       20171108                      20171109
001           Sick leave    20171111                      20171112
001           At work       20171115                      20171116

The logic: We re-organize the source table by status, not by date. So a gap in date does not matter and should be ignored.

How to do this in Teradata 15?

Note: select min(Date), max(Date) group by employee, status; will not work, as there may be status change between two "At work"s.

The simplest solution normalizes a period:

SELECT NORMALIZE Employee, Status, PERIOD(date,date+1) AS pd
FROM mytable
ORDER BY Employee, pd

NORMALIZE is a very unknown syntax, it combines overlapping periods, you just need to create a one day period out of the date column. As this results in a period you get a slightly different output, the end date is +1 compared to the expected result.

To fix this you can split the period back into seperate columns:

SELECT Employee, Status, Begin(pd),
   Last(pd) -- last included date, i.e. expected EndDT
FROM
 (
   SELECT NORMALIZE Employee, Status, PERIOD(date,date+1) AS pd
   FROM myTable
 ) AS dt
ORDER BY 1,pd

Getting this Some time in the history is more complicated, would need additional calculations, you should check if you really need it.

Another more classical solution calculates groups of rows with  consecutive values:

SELECT Employee, Status, Min(date), Max(date)
FROM
 (
   SELECT Employee, Status, date,
   -- this is the tricky part, the differnce between a monotonous sequence (row_number)
   -- and another monotonous sequence with gaps (date)
   -- is constant when there's no gap
      date - Row_Number()
             Over (PARTITION BY Employee, Status
                   ORDER BY date) AS grp
   FROM vt
 ) AS dt
GROUP BY Employee, Status, grp
ORDER BY 3

Both solution will result in an extra row when you add

20171120   001          At work
...
001           At work       20171115                      20171116
001           At work       20171120                      20171120

If you want to combine with the previous row

001           At work       20171115                      20171120

it's also a bit more complicated...


Does an order by clause have a negative effect on performance? I couldn't find the answer by googling.

I only over heard a senior dev telling a junior dev that using an order by clause has a negative effect on performance and he shouldn't use it(Oracle).

In what circumstances can an order by clause effect negatively on performance?

Update

I found Bad performance of SQL query due to ORDER BY clause. Is there any other circumstances where an order by clause may negatively effect performance?

An ORDER BY clause will always add a sort operation if there is no usable index to retrieve the data in order.  For larger sets of data the data being sorted may be written to disk adding additional processing.  For smaller result sets where indexes are being used, there may be no requirement for a sort step.

If sorting is required, the database will have to retrieve all the sort key data and sort it before presenting results.  This can delay the availability of the first rows.  If you only want the first few rows of a large set, this can generate a lot of extra work for the database.

Than being said, I have rarely seen an ORDER BY clause a significant performance impact.  I recall one case where a developer attempted to improve performance by retrieving an unsorted result set and sorting it in the client, but it did not work well.

I avoid the ORDER BY clause unless there is a requirement for data to appear in a given order.  If there is a requirement to present data in a given order, I use the ORDER BY clause.  If there is a question about performance, I test the query against a production database before testing alternatives.


Oracle 10g weird behaviour We have a thick client application using an Oracle 10g database. We recently upgraded the application and in the process migrated the database tables from a latin to unicode character set.

After the upgrade some fields behaved 'read only' from the GUI screens. We found that we had some wrong values and updated them using an SQL command line tool and these fields suddenly became editable in the application.

So we updated the fields for all the rows and the problem disappeared completely.

The two queries we used were:

update listings_text set text = 'Y' where text = 'y';

and

update listings_text set text = text;

Now the second query should be a noop. But its not. Can anyone explain why this query might have an effect?

The "noop" would have fired any related triggers on the table, and it would have advanced the system change number.


How to implement superkey sub key method on cascading tables If I design a database with a securitymaster table that has all the common columns and then have a final column called securitytype which acts as a reference to the sub table, how is it possible to return the extended row (that joins the securitymaster with the sub table in a UDF or Stored Proc)?

In my example, I will abbreviate the securitymaster table as A, the subtables are B,C,...,Z. Each subtables has its own specific columns. Let's further imagine the A has the first column a1 which is the PK and the last column a10 that holds the value B,...,Z (I.e. a FK to the subtable name). The number of columns in the sub table differ and will normally be specific to each table, however some tables may share the same column names.

If I have a securityID, say @sID, then, I will need to join the table as defined in A.a10 where A.a1 = @sID. I will then need to present select * from F (let's imagine A.a10 was equal to F). I have tried with Execute, but people say its a bad method, also I don't know how to return the value or the combined row from an EXEC within a sp. UDF looks impossible as they can not be dynamic. An example would be really useful as I feel this should be a fundamental tenant of any DB?

Many thanks

Normally, you'd create a view for each subtype. Do the join between the "super" table and the "sub" table in the view.

If you make the views updatable, client code can just use the views.


How to export mysql database based on a where condition I need to export the data and structure of a table but the data must have a specific condition (WHERE status=0 and id>20).

How to export mysql database based on a where condition from phpMyAdmin or anything.

Using SQL from the mysql command-line:

SELECT * from YOURTABLE
WHERE status=0 and id>20
INTO OUTFILE 'yourtable.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

or using mysqldump with the --where= option:

mysqldump -u youruser -p yourdbname yourtablename --where="status=0 and id>20">yourtable.sql

Using phpMyAdmin you can execute the query in the GUI & click "export" under the resultset.


What is the format of EXTERNAL NAME argument? When a Java class cl with a method mth resides in a package pck, what should I put into the EXTERNAL NAME clause of CREATE PROCEDURE?
I tried
CREATE PROCEDURE mth (arg1 INTEGER) LANGUAGE JAVA EXTERNAL NAME 'myjar:pck/cl.mth'
CREATE PROCEDURE mth (arg1 INTEGER) LANGUAGE JAVA EXTERNAL NAME 'myjar:pck.cl.mth'
CREATE PROCEDURE mth (arg1 INTEGER) LANGUAGE JAVA EXTERNAL NAME 'myjar:cl.mth'

but either way, I get The user defined function or procedure "mth" was unable to map to a single Java method.. SQLCODE=-20204, SQLSTATE=46008, DRIVER=3.68.61
On Java side, the method is declared as follows:
public void send_mail( // with or w/o static, with or w/o synchronized
    final String P_TO,
    final String P_CC,
    final String P_BCC,
    final String P_FROM,
    final String P_SUBJECT,
    final String P_TEXT_MSG,
    final String P_HTML_MSG,
    final String P_ATTACH_NAME,
    final String P_ATTACH_MIME,
    final byte[] P_ATTACH_BLOB,
    final String P_SMTP_HOST,
    final int P_SMTP_PORT,
    final String P_USER_NAME,
    final String P_PASSWORD

On the DB2 side, the procedure is being attempted to create as follows:
CREATE PROCEDURE send_mail(
  IN P_TO          VARCHAR(4096),
  IN P_CC          VARCHAR(4096) DEFAULT NULL,
  IN P_BCC         VARCHAR(4096) DEFAULT NULL,
  IN P_FROM        VARCHAR(4096),
  IN P_SUBJECT     VARCHAR(32672),
  IN P_TEXT_MSG    VARCHAR(32672) DEFAULT NULL,
  IN P_HTML_MSG    VARCHAR(32672) DEFAULT NULL,
  IN P_ATTACH_NAME VARCHAR(255) DEFAULT NULL,
  IN P_ATTACH_MIME VARCHAR(100) DEFAULT NULL,
  IN P_ATTACH_BLOB BLOB DEFAULT NULL,
  IN P_SMTP_HOST   VARCHAR(255),
  IN P_SMTP_PORT   INTEGER DEFAULT 25,
  IN P_USER_NAME   VARCHAR(255) DEFAULT NULL,
  IN P_PASSWORD    VARCHAR(32672) DEFAULT NULL
)
LANGUAGE java
PARAMETER STYLE java
FENCED THREADSAFE
EXTERNAL NAME 'javadb2:db2.smtp.send_mail' -- with or w/o db2

I've checked and double-checked that every time I sqlj.db2_install_jar it appears under the function\username folder in the DB2 folder hierarchy.

The correct format is <JAR name>:<class name>.<method name>, <class name> is fully qualified, i.e. includes the package name. Since you are still getting this error, it's likely that your method signature does not comply with the convention you use. You don't specify the PARAMETER STYLE option, meaning DB2GENERAL is used by default, which is not recommended. Use PARAMETER STYLE JAVA and make sure the method signature is correct (public static void mth(int arg1) in your contrived example).
Note also that

For updating Java™ routines that are built into JAR files, you must issue a CALL SQLJ.REFRESH_CLASSES() statement to force the database manager to load the new classes. If you do not issue the CALL SQLJ.REFRESH_CLASSES() statement after you update Java routine classes, the database manager continues to use the previous versions of the classes. The database manager refreshes the classes when a COMMIT or ROLLBACK occurs. (source)


Single Clustered Index Concept Lets suppose we create a table called "SampleTable" and made "ID" column a primary key.  SQL Server by default creates a clustered index on the table. This physically sorts the data on the disk. Now suppose we create a view called "SampleView" with schema binding as select * from "SampleTable" and create a clustered index on the "SampleView". Now SQL Server will create a clustered index on the view and physically sort the data on the disk according to the clustered index.

If the data is already sorted on disk in SampleTable, how can it be sorted again in SampleView?

When you create the clustered index on SampleView, SQL Server has to materialize the data in the index.  Normally, a view does not have a separate copy of the data.  It is normally just a stored query.  An indexed view changes that.

Since the indexed view has a separate copy of the data, it can be sorted in whatever way you'd like.  Normally, of course, you wouldn't have an indexed view that simply replicates all the data in a table.  You'd normally only create an indexed view if you're going to materialize a subset of data based on various conditions or if you're going to materialize some aggregate data.


Unable to connect to SQL Server 2016 remotely I am trying to access SQL Server 2016 from outside of my network but each time I try I get this error message.

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

The ports on my managed firewall are open, and I have also turned off Windows Firewall too in the event that this was the blocker but I am still getting the issue.

I have checked the Configuration Manager and its listening on port 1433, however, when I look at netstat -an I dont see this port is being listened on so suspect this may be where the issue exists.

A question I have is if configuration manager says its listening on this port, why is it not showing in netstat?

Also, what else can I try in order to gain access.

Thanks

should i construct the server name as tcp:ipaddress\instance, 1433

Absolutely no.

All that the network library needs to connect to SQL Server instance is it's IP + port.

When you are using default instance, IP address is sufficient as the default port is known, it's 1433, so when you pass IP only, or PC name only, and your instance is listening on 1433 (even if it is named, but still configured to listen 1433), the connection will succeed unless the port is unreachable due to firewall.

When named instance is used, you should pass it's IP address + port or IP address + instance name, but not both port + instance name.

In case you pass in IP + port, the network library just uses them.

In case tou pass IP\instanceName, SQL Server Browser should run. Is's SQL Server Browser that listens on UDP 1434 (that should be open), takes InstanceName as the input and gives out instance's port.

When you pass in both InstanceName and a port, InstanceName is ignored. So not only it serves to nothing, it cheats you in that you may think there is an attempt to resolve InstanceName to the corresponding port, but it's no attempt at all: the port you are passing in is used.

In your particular case if 49689 port is used, all you need to connect to your instance is 192.168.1.182,49689 in SSMS + 49689 shoul be open.

If you are not sure what port is in use, just open SQL Server error log (you can do it using SSMS, notepad or any other text editor, or executing

exec xp_readerrorlog 0,1,N'Server is listening on'

Configuration Manager reflects the last configured value, but if the server was not restarted after configuring, the value for the port is not currently in use while SQL Server error log always gives you effective port values.


MySQL : UNION script help I have two tables which have roughly the same data but we could not mix them together due to the nature of the data input and presentation and to keep the table size small.

We run the followign script

SELECT * FROM (
SELECT dataString,dataInsertDateTime FROM tblMainData
WHERE deviceSerial='A111' AND mainDateTime BETWEEN '2012-06-20' AND '2012-07-07'
union
SELECT dataString,dataInsertDateTime FROM tblSecondData
WHERE deviceSerial='A111'  AND mainDateTime BETWEEN '2012-06-20' AND '2012-07-07')
as table1

The script runs quite slow
I want to mix the data from both tables
I want it ordered by mainDateTime

Thank you

Three(3) Suggestions
SUGGESTION #1
You have the following clause in the SQL
mainDateTime BETWEEN '2012-06-20' AND '2012-07-07'
If mainDateTime hold s a date and a time you should change the clause to this
mainDateTime BETWEEN '2012-06-20 00:00:00' AND '2012-07-07 23:59:59'
SUGGESTION #2
The whole WHERE clause gives away what index both tables need:
ALTER TABLE tblMainData
ADD INDEX deviceSerial_mainDateTime_ndx (deviceSerial,mainDateTime);
ALTER TABLE tblSecondData
ADD INDEX deviceSerial_mainDateTime_ndx (deviceSerial,mainDateTime);

That way, each SELECT will perform better index scans
SUGGESTION #3
If you want everything ordered by mainDateTIme, you should include it in the SELECT list:
SELECT * FROM (
SELECT dataString,dataInsertDateTime,mainDateTime FROM tblMainData
WHERE deviceSerial='A111' AND mainDateTime BETWEEN '2012-06-20' AND '2012-07-07'
union
SELECT dataString,dataInsertDateTime,mainDateTime FROM tblSecondData
WHERE deviceSerial='A111'  AND mainDateTime BETWEEN '2012-06-20' AND '2012-07-07')
as table1
ordder by mainDateTime;


MS SQL Linked Server Security failing using current security context I have 2 instances of SQL 2008 64 bit (ver 10.0.4000) running on a windows 2008 64 bit server db03 and db03\ins2. The ins2 has a linked server connecting to db03 that was setup by replication. I have code that needs to connect to db03 from ins2 to kick off a proc, so am trying to use same linked server connection. It is failing with error:

  Msg 18483, Level 14, State 1, Line 1  Could not connect to server db03
  because '' is not defined as a remote login at the server.

Here is what I have done:

Verified kerberos is setup correctly and validated I am connecting with kerberos. http://msdn.microsoft.com/en-us/library/ms189580%28v=SQL.105%29.aspx
Changed linked server property to allow data access (turned off b/c replication created it).
Ran this (I think redundant from my GUI change) EXEC sp_addlinkedsrvlogin 'DB03', 'true'
Changed to using sql authentication account to connect on linked server properties and tested that it works.
Both servers are using same domain service account.

What I would like to happen is to use the "Be made using the login's current security context."

EXEC master.dbo.sp_addlinkedserver @server = N'DB03', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB03',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'collation compatible', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'remote proc transaction promotion', @optvalue=N'true'

Thanks, Chuck.

You mention a SQL Agent Job. I usually make sure the SQL Server Agent is configured with a domain account as well, and I usually use the same account.


Migrate / Copy Huge data from Oracle to SQL Server I have huge tables in oracle database, appx 1 crore+ (10 million+) rows and want to migrate / copy those table and data in sql server.

Currently I am using Import functionality of SQL Server for this process. But it takes a day for this process and takes too much time.

Is there any better way? Any good outcome or step (SSIS, Any other functional step) to follow for this process?

Separate the two tasks.

One of the good ETL rules to follow is to get in and out of the source system as fast as possible. Dumping the Oracle data to flat files should be orders of magnitude faster than inserting direct, as should the subsequent import of those files to SQL Server.

Export from Oracle to flat file. There are several approaches detailed at How Can I Unload Data to a Flat File.
Import to SQL Server. bcp or BULK INSERT should yield the fastest raw loading speed. There is a useful list of bulk loading references in Optimising BCP performance for BLOB data.

I've found BULK INSERT and bcp to be less troublesome (i.e. I create and encounter fewer errors) if you forget CSV, avoid format files, and utilise a non-standard delimiter instead. I've got "~¬" in use on a bcp based ETL job at the moment and so far so good, the data dog I'm fetching from has yet to contain that string.


What types of methods can we call from a .NET assembly in SQL Server After we have registered a DLL in SQL Server (in Programmability -> Assemblies), can we call any type of method from that .NET assembly in SQL Server? Or just static ones?

SQLCLR is a very limited CLR host. Yes, you can only call static methods due to the App Domain being shared across all sessions (i.e. @@SPID), and hence any "shared" instances or variables would be very much not "thread safe". App Domains are created on a per-Database, per-"owner" (i.e. whatever User is listed in the AUTHORIZATION clause of CREATE ASSEMBLY, or dbo as the default if that clause is omitted), so there is a minimal amount of separation, but for any given method that is exposed via a CREATE {object} AS EXTERNAL NAME statement, any User in any Session executing that SQLCLR Stored Procedure, Function, Trigger, or User-Defined Type will be hitting the exact same App Domain. This is why you are also not allowed to store values in static class variables (unless they are marked as readonly) as they are shared across Sessions and one Session can overwrite the value that another Session just stored and hence you would get odd / erratic / unpredictable behavior.

Regarding SQLCLR being a restricted environment:

Please see the following MSDN page for a list of what is not allowed in SQLCLR: CLR Integration Programming Model Restrictions.
Please see the following MSDN page for a list of what few .NET Framework libraries are included in SQLCLR: Supported .NET Framework Libraries.
For more information on working with SQLCLR in general, please see the series I am writing on this topic on SQL Server Central (free registration is required to read their content, but it's worth it): Stairway to SQLCLR.


Does MariaDB support native JSON column data type? I’m not talking about dynamic columns, I’m asking about native column JSON data type. In simple words, can I run the following code on any MariaDB version?

CREATE TABLE example (names JSON);

As far as I know, it doesn’t but I’m still not sure since there’s a lot of topics talking about JSON support on MariaDB for ages, but none said it’s finally implemented.

-- Update --

Just found three issues still open on MariaDB Jira about JSON data type support, which means it's still not yet implemented, right?

https://jira.mariadb.org/browse/MDEV-9056
https://jira.mariadb.org/browse/MDEV-9144
https://jira.mariadb.org/browse/MDEV-6632

JSON support will come to MariaDB 10.2. See the official MariaDB blog post from 28.02.2017. There are some example SQL statements and validation.

  JSON is fast becoming the standard format for data interchange and for
  unstructured data, and MariaDB 10.2 adds a range on JSON supporting
  functions, even though a JSON datatype isn't implemented yet. There
  are some reasons why there isn't a JSON datatype, but one is that
  there are actually not that many advantages to that as JSON is a
  text-based format. This blog post aims to describe JSON and the use
  cases for it, as well as to describe the MariaDB 10.2 JSON functions
  and uses for these, as well as showing some other additions to MariaDB
  10.2 that are useful for JSON processing.


Can triggers be used to implement automated polymorphic association? (Apologies for any bad formatting or ignorance of common practices within this community; this is my first question here.)

I'm attempting to (re)design a MySQL database for a personal social networking site I'm developing. Quite often, I run into situations where there is an element (such as a "like" button) that requires a globally unique id despite being associated with a variety of different "parent" element types (blog post, comment, user page, etc). Previously, my design had been giving every like button a column for blog_id, comment_id, and user_id, and leaving the irrelevant columns NULL; if a like button is on a blog post with id 5, blog_id would be set to 5 while comment_id and user_id were NULL.

This implementation seemed redundant and unmaintainable, so I searched up alternative solutions.

The best solution I found seemed to be to define a new table, say like_buttons, with a primary key like_button_id that would be provided in a foreign key column within all other significant tables (blog_posts, comments, users). Then, whenever a like button's data must be accessed, one can search for the like_button_id column of the appropriate parent element, and go on from there.

However, when defining new comments and blog posts, I do not wish to have to add another line to my code separately defining a new like button just to pass the id to the significant element. Consequently, I stumbled upon MySQL's trigger functionality in searching for a solution.

To avoid further protracted exposition, my question lies in the acceptability of the following example code:

TRIGGER insert_user_like_button
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO like_buttons VALUES();
  SET NEW.like_button_id = LAST_INSERT_ID();
END

This trigger would be repeated for every other parent element table, such as blog_posts and comments.

Assuming that like_buttons (in this example case) only has one id NOT NULL PRIMARY KEY AUTO_INCREMENT column, is this the best method for preserving uniqueness in ids across multiple tables of information? Is there any more elegant, efficient, extendable, or appropriate solution?

I was going to write a long post telling you that there are basically 3 ways to implement polymorphic associations, but someone (@Bill Karwin) has already done it better in a more compact way: http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back/32

Your original solution is the first one presented on the slides, the one you propose is the 3rd one (but without using a base, parent table -more on this later), and maybe one can argue that is the one that seems the "cleanest". The actual preferred solution is not always the cleanest, but the best fit for the queries to be done. For example, the single table like_buttons may be much easier to query, but it can grow very tall. Having separate tables, like in solution #2 on the slides can be helpful as we are doing some kind of manual horizontal partitioning. On the other side, having separate tables may be less flexible, as it may force us to change the code if we add another entity part of the polymorphism. Also, there is a more direct relation in the direction like_button -> entity_to_be_liked.

Regarding specifically the trigger, I have nothing against the concept, but I am not sure if it is useful, as on creation time it is completely empty. Can't it be created on the first "like"? Referential integrity will already be secured if you create the extra generic table that I mentioned.


Re-initializing a transactional replication wihch was initially synced from backup I have a transactional replication which was initially synced from backup.   Now I need to add a new table which is really big so we have decided to backup and restore a fresh copy of the db to subscriber to re-intializing it.

My question is, in this scenario should I be dropping the subscription, backup restore and then re-add the subscription?  is that the correct way or is there any other way of going about it?

Thanks

So I had a similar situation.  I had a HUGE publication that took up to 36 hours to initialize with a snapshot.  When I had to move the subscriber to new hardware this just wasn't ideal.  So I used a backup to initialize the sub.  Of course I knew that we'd have to add articles eventually and I did what this article said to do in Option 1 (option 2 not available as there was no way I was going to be able to stop all activity on the subscriber short of a disaster).

Add Articles to Subscription initialized with backup

So I added a new publication to the same publisher database and then added the subscription to the subscriber.  Since the initial snapshot is very small (only 4 tables) it sych'd up very quickly.  Subsequent table adds to the publication are done on the new publication and snapshots generally are only of new tables, not entire publication.  Is working very well so far .


SQL Full Backup job is taking too long to complete I am facing a strange issue with daily FULL backup. We have a total of 23 databases. We have 1.21 TB big database in this SQL server 2012 Standard Edition. We are taking daily FULL database backup for all databases.

For the last 2 days the backup is taking too long. After more than 10:30 hours it had only completed 67 % of the backup. Daily Full backup job completion time is normally 4 to 5 hours.

And the backup job is also showing suspended state.
I found last_wait_type is ASYNC_IO_COMPLETION for this backup job.

We have sufficient disk space available.

Can anyone make any suggestions on this issue?

I found last_wait_type is ASYNC_IO_COMPLETION for this backup job. We
  have sufficient disk space available.

This blogpost from Paul Randal gives you most of the information regarding this wait stat

  ...The official definition of ASYNC_IO_COMPLETION is ‘Occurs when a task
  is waiting for I/Os to finish....

  ...Long-duration ASYNC_IO_COMPLETION waits commonly are from data backups....

A question similar to yours can be found on that same blog.
In his case a backup of a small database (1,5GB)  was taking over 15 minutes.

Q

  ...I’m facing a high level of ASYNC_IO_COMPLETION into one of my environments what is resulting in backups of small databases (around 1,5GB) to take more than 15 minutes. I have a feeling that it is related to low performance on the I/O
  subsystem. But I’m not 100% confidente to go ahead on this diagnostic
  to ask for analysis or changes on that.

A

  Yup – that’s what it is. Try doing a backup to nul, as that will give
  you time taken to read from the data files. The difference is the time
  being taken to write out the backup files – there’s your ammo.

This is the command to do that:

BACKUP DATABASE DatabaseName TO DISK = 'NUL'

And compare this with the  backup time you mentioned previously.

Keep in mind that this will still start a huge amount of I/O operations on your server.

  And the backup job is also showing suspended state

Suspended state simply means that it is waiting on resources, I/O based in your case.

Other improvements

Check what else is running on the instance, any maintenance plans such as index rebuilds, statistic updates, integrity checks?

You could start logging queries with sp_whoisactive and perhaps logging it in a table.

Striping your backups accross multiple files can help in reducing backup time. More on that here.
My go to is 4 files but you could test different settings.

Since you are on sql server 2012, if you are not using TDE you could leverage backup compression as well.

More information on making backups go faster can be found here


Sessions, Processes, Object Instances, Sequential and Parallel Processing Theory A stored procedure contains a view which is accessed multiple times with the confines of the SP.

If the SP is executed multiple times in one query window with different parameters, e.g.

EXEC spMySP 1, 2, 3
GO
EXEC spMySP 4, 5, 6
GO

Questions

If one assumes that the first call will complete execution before the second one starts and therefore the view within the SP, will it be bound by the connection, session and process?
If we were to place each SP execution in its own query window and execute the all, roughly at the same time - we assume they would execute asynchronously?
Can we assume that the view would within the SP be operating within a new instance (More a theoretical question)?

The SPs will be queued and run based on available resources. Depending on the isolation level used in defining the SP and how the transactions are defined, the SPs will wait accordingly. By design, the SP will not always wait for the first SP to finish, and then move onto the second one etc...

This design make is possible for multiple unrelated users to call the SP and work concurrently.


T-SQL INNER JOIN Optimisation Well, I really have read so many articles, used different techniques and done some random things to do this.

My problem was that a have big tables (over one million rows) and some small (with few hundreds rows) - we are talking about 8 inner joins involved.

What reduces the execution time from over 2 minutes to 30 seconds was very strange for me and I was not able to find why this happens.

When I select columns from the tables I cast them. What I did was to cast the column to the most small possible type.

For example:

nvarchar(4000) to nvarchar(50 or 25)
bigint to int
int to tinyint

The result was over 1 minute and 30 second better execution time. Why this happen?

For example if my variable is string with length 10 the nvarchar(4000) and the nvarchar(50) will cast it to nvarchar(10) (or something close to that). So, why when I reduce the outcome type from cast the things go better?

Something more - I make a lot of test to check which function is better - cast or convert (do test for string to string, string to number,  number to string) but was not able to define which works better. Sometime the convert gives me few seconds better execution time, but not enough to make a conclusion. Has anyone do something like this and succeeded in?

Thank for your time in advance.

The reason the performance is better is that the smaller data types have much less working set - see the execution plan (http://www.red-gate.com/our-company/about/book-store/assets/sql-server-execution-plans.pdf)

I expect you get the largest benefit from the nvarchar(4000) to nvarchar(50) - that's a reduction of 80x - and nvarchar(4000) can use up to 8K! of space.  For a key, that is not a good idea.

In addition, the fact that there are no foreign keys probably mean you don't have a very good indexing strategy either.  If you did have indexes (even for ridiculously large columns), you would probably find they could outperform the cast since it probably wouldn't spool as much.

In general, you don't want any operations on your keys in the join if at all possible, especially for large data sets.


Query exceeding 16384 bytes, skipping. Probably a bulk insert query On MariaDB 5.5, Ubuntu 14.04

What actually happens when I restore a 170mb mysql dump file and info "Query exceeding 16384 bytes, skipping. Probably a bulk insert query." shows on cmon.log?

Does it mean that I would not have all my backup data restored?

Below is output of max_all settings

> show variables like '%max_all%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 536870912  |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

It has been confirmed by severalnines that the message is just cmon does not analyze those queries because of their sizes.

http://support.severalnines.com/entries/80914809-cmon-Query-exceeding-16384-bytes-skipping-Probably-a-bulk-insert-query-?page=1#post_29095145


How to monitor load on database server Could anybody please suggest me a way to monitor the load on my database server?
What perfmon counters should I monitor on daily basis and confirm that load on the server is increasing day by day [or] by week by week [or] every month. Also, suggest me if we can achieve this using DMV's so that we can store this info in a table using a SQL agent job. Because as a sql guy I would be more happy working with TSQL queries.

Do all this helps me, to put for forward my argument to the management that this server can handle this much of load based on data and in case if the load is increased on the server , we might need more resources ( like cpu, better storage , more RAM etc ...) or move to a new server which has more power.

Also, please suggest any load testing tools so that I can test this on testing environment.

There are a couple of things here.  First performance monitoring is a subject that is very broad.  When I started down the rabbit hole of performance monitoring I found Brent Ozar and Paul Randal's blogs very helpful.

Specifically Brent's post on perfmon counters.  Its a little old but still a very good resource.  To go along with that is Paul's post on I/O latencies and Brent's How to Prove Your SQL Server Needs More Memory

You will notice that most of the information I have posted here is from only two sources but they are the first place I tend to look.  The second place I look to is Google.  On top of those there are third party monitoring solutions such as Red Gate SQL Monitor, Confio Ignite (now Solar Winds) and many others.

The way I learned how to do performance monitoring was by being in a situation where I thought something was going on and so I started to try and prove it.  Google was my best friend to find blogs and other information as well as reading this site and others as people asked questions.  Once I got enough information from perfmon and other sources to point out that there was something wrong but we needed better visibility into the server I was able to get a third party monitoring tool which really helped.  Turns out we did not really need more of something just tune several queries.


Filesystem copying inactive DBs on active MySQL server My question is related to this question and answer:
Copying datadir directory enough for backing up a database with InnoDB tables?

I want to use a filesystem copy on a whole DB without shutting down the whole server. The documentation references using FLUSH TABLES ... FOR EXPORT and then UNLOCK TABLES to be able to safely do this.

https://dev.mysql.com/doc/refman/5.7/en/flush.html#flush-tables-for-export-with-list

But what happens if I use those queries to target a whole DB like FLUSH TABLES example_db.* FOR EXPORT then filesystem copy the whole example_db folder then run UNLOCK TABLES example_db.*

Is that safe? Or just possibly safe because new tables in the DB could theoretically be created after the existing tables are locked which would then potentially not be flushed when the filesystem directory is copied? Is there something extra I have to do here to lock the whole database?

Whether it is safe or not -- do you want to be stalled until it finishes?  You may as well stop MySQL for the dump.

Even better would be to set up LVM, then

Stop mysqld
Do snapshot; this takes a minute or so, regardless of the size.
Restart mysqld


Information about Disk Storage MySQL I am trying to create an application for monitoring MySQL,in my application there's a part of  monitor DISK STORAGE, so after more research I have not found how to calculate :

used and free size of tablespace,
used and free of data files,
used and free of log files

Anyone know how to calculate their sizes or someone can give me articles or books can help me.
And Thanks.

Here is a query I use to get all the used space of MySQL for all storage engines:

SELECT
    IFNULL(ENGINE,'Total') "Storage Engine",
    LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
    SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
    LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
    SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
    LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
    SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
    SELECT ENGINE,DAT,NDX,TBL,
    IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM
    (SELECT *,
        FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
        FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
        FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
        FROM
        (SELECT
            ENGINE,
            SUM(data_length) DAT,
            SUM(index_length) NDX,
            SUM(data_length+index_length) TBL
        FROM
        (
           SELECT engine,data_length,index_length FROM
           information_schema.tables WHERE table_schema NOT IN
           ('information_schema','performance_schema','mysql')
           AND ENGINE IS NOT NULL
        ) AAA GROUP BY ENGINE WITH ROLLUP
) AAA ) AA) A,(SELECT ' BKBMBGBTB' units) B;

By Database

SELECT
    IFNULL(DB,'Total') "Database",
    LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
    SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
    LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
    SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
    LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
    SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
    SELECT DB,DAT,NDX,TBL,
    IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM
    (SELECT *,
        FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
        FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
        FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
    FROM
    (SELECT
        DB,
        SUM(data_length) DAT,
        SUM(index_length) NDX,
        SUM(data_length+index_length) TBL
    FROM
    (
       SELECT table_schema DB,data_length,index_length FROM
       information_schema.tables WHERE table_schema NOT IN
       ('information_schema','performance_schema','mysql')
       AND ENGINE IS NOT NULL
    ) AAA GROUP BY DB WITH ROLLUP
) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;

By Database/Storage Engine

SELECT
    IF(ISNULL(DB)+ISNULL(ENGINE)=2,'Database Total',
    CONCAT(DB,' ',IFNULL(ENGINE,'Total'))) "Reported Statistic",
    LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
    SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
    LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
    SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
    LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
    SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
    SELECT DB,ENGINE,DAT,NDX,TBL,
    IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM
    (SELECT *,
        FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
        FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
        FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
    FROM
    (SELECT
        DB,ENGINE,
        SUM(data_length) DAT,
        SUM(index_length) NDX,
        SUM(data_length+index_length) TBL
    FROM
    (
       SELECT table_schema DB,ENGINE,data_length,index_length FROM
       information_schema.tables WHERE table_schema NOT IN
       ('information_schema','performance_schema','mysql')
       AND ENGINE IS NOT NULL
    ) AAA GROUP BY DB,ENGINE WITH ROLLUP
) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;

CAVEAT ON InnoDB FRAGMENTATION

To find the fragmentation of an individual InnoDB table (.ibd) you cannot fully trust the INFORMATION_SCHEMA database. You have to piggyback off the OS.

Let's say the table is mydb.mtable and datadir is /var/lib/mysql

Get the Size of the InnoDB Table from OS and subtract it from what INFORMATION_SCHEMA.tables says

SQLSTMT="SELECT SUM(data_length+index_length) size FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE table_schema='mydb' AND table_name='mytable'"
FILESIZE_OS=`ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print $5}'`
FILESIZE_DB=`mysql -uroot -ppassword -ANe"${SQLSTMT}"`
(( FILESIZE_FRAG = FILESIZE_OS - FILESIZE_DB ))

CAVEAT ON MyISAM FRAGMENTATION

For the MyISAM table mydb.myatble for the .MYD

SELECT data_length INTO @datsize
FROM information_schema.tables
WHERE table_schema='mydb'
AND table_name='mytable';
SELECT table_rows*avg_row_length INTO @sizerows
FROM information_schema.tables
WHERE table_schema='mydb'
AND table_name='mytable';
SET @tblfrag = @datsize - @sizerows;
SELECT @datsize,@sizerows,@tblfrag;

CAVEAT ON Tablespace file ibdata1

Here is a look at the InnoDB Architecture

Please note that ibdata1 has 5 moving parts

Data Dictionary
Double Write Buffer (support data consistency; used for Crash Recovery)
Insert Buffer (Buffers Changes to Secondary Non-Unique Indexes)
Rollback Segments
Undo Space (where the most uncontrolled growth can happen)

Since you have innodb_file_per_table enabled, the data and index pages for all InnoDB have been separated from these internal parts. Thus, there is no need to monitor ibdata1 internally.

For the benefit of those who have innodb_file_per_table disabled, then everything InnoDB and its grandmother is inside ibdata1. This is how you monitor ibdata1:

Let's say your innodb_data_fie_path is this

[mysqld]
innodb_data_file_path=ibdata1:2000M;ibdata2:10M:autoextend

You would have to add the file sizes of the two files and subtract the sum of the data and index pages. Perhaps like this:

cd /var/lib/mysql
IBDATA_SIZE=0
for X in `ls -l ibdata* | awk '{print $5}'` ; do (( IBDATA_SIZE += X )) ; done
SQLSTMT="SELECT SUM(data_length+index_length)"
SQLSTMT="${SQLSTMT} FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE engine='InnoDB'"
INNODB_SIZE=`mysql -uroot -ppassword -ANe"${SQLSTMT}"`
(( IBDATA_FRAG = IBDATA_SIZE - INNODB_SIZE ))

See my additional suggestion on handling multiple ibdata files : How to solve "The table ... is full" with "innodb_file_per_table"?


Postgres hstore check if a key exists and does not match value I have a hstore field in an audit table that stores all the fields that have changed as a result of an operation.

On inserts, the key, updated_by in changed_fields is NULL and on system updates it is set to system. I would like to return all rows where either the key is not defined or the key is not system but I am lost as to how to do this.

So far I have tried

select changed_fields -> 'updated_by'
from audit.logged_actions
where (changed_fields -> 'updated_by' != 'system'
       or defined(changed_fields, 'updated_by') = false)
order by event_id desc

But that doesn't work and I am not quite sure why. Any ideas on what I am doing wrong?

The usage of defined() in the documentation:

  does hstore contain non-NULL value for key?

In addition to a NULL value for an existing key, it returns FALSE also when the key does not exist.

To keep it clean, you might want to use the exist() function instead:

SELECT exist('"bla" => 1234', 'bloo');
 exist
───────
 f

So your query would look like

SELECT changed_fields -> 'updated_by'
  FROM audit.logged_actions
 WHERE changed_fields -> 'updated_by' <> 'system'
       OR NOT exist(changed_fields, 'updated_by')
 ORDER by event_id desc;

Notes:

never test logical values using the equality operator (unless you can be really sure the value cannot be NULL).  You can directly use the value if it is guaranteed to not be NULL (like in my query), or use the IS operator.
In the current form, the query is checking a simple 'OR' - from your wording it is not quite clear what should happen when both conditions are true.


How to take grade letter from marks range from grade table? Grade Table is:

create table Grade
{
grade_letter char(1)
lowerlimit int(20)
upperlimit int(20)
};

insert into Grade (grade_letter, lowerlimit, upperlimit)
    values (
      ('A', 85, 100),
      ('B', 70,  84),
      ('C', 50,  69),
      ('F',  0,  49));

Suppose some another query returns marks.

Now, I am stuck with the query which takes the grade_letter based on the marks falling in the range given above. I want a query which is not hard-coded and calculates the grade based on the values given in the table only.

This will do the trick (assuming your marks are in the table Marks):

SELECT      B.grade_letter
FROM        Marks AS A
INNER JOIN  Grade AS B
    ON      A.Mark BETWEEN B.lowerlimit AND B.upperlimit


Is IFI compatible with tempdb growth? Is IFI (Instant File Initialization) compatible with tempdb growth? Recently I needed to increase my tempdb by 40 Gb and it took 15+ minutes to complete. I verified that the user had already been added to Perform Value Maintenance Task and also verified that other user databases took seconds to complete.

I didn't find any documentation or link that would have in-depth IFI details with regard to TempDB. I really appreciate your suggestions.

Yes,

Instant File Initialization works on TempDB Data files.  As with any other database, it doesn't work on the log file.  See Aaron's Blog Post to see where he shows IFI working with TempDB via setup.