Fatal error 3624 : iKey GetKeyCount() I am receiving errors when I run the following query:

SELECT (
        SELECT 1
        FROM dbo.AccountEntry AE
        WHERE AE.Account = P.Account
        )
FROM dbo.Proposal P
WHERE P.id = @Id

When run via ADO.NET it gives:

  Warning: Fatal error 3624 occurred at Jun 20 2016 2:28AM. Note the
  error and time, and contact your system administrator. A severe error
  occurred on the current command. The results, if any, should be
  discarded.

When run in SSMS it gives

  Location:  statutil.cpp:4128
  Expression:    iKey < m_statBlob.GetHeader()->GetKeyCount()
  SPID:      59 Process ID:  1732
  Msg
  3624, Level 20, State 1, Line 1
  A system assertion check has failed.
  Check the SQL Server error log for details. Typically, an assertion
  failure is caused by a software bug or data corruption. To check for
  database corruption, consider running DBCC CHECKDB. If you agreed to
  send dumps to Microsoft during setup, a mini dump will be sent to
  Microsoft. An update might be available from Microsoft in the latest
  Service Pack or in a QFE from Technical Support.
  Msg 0, Level 20,
  State 0, Line 0
  A severe error occurred on the current command.  The
  results, if any, should be discarded.

Interestingly this code works when setting @Account to P.Account:

select 1 from dbo.AccountEntry AE where AE.Account = @Account

I have tried restarting the server.

I have tried dropping all indexes and statistics.

I have run DBCC CHECKDB and it returns no errors.

We don't believe anything has been changed about the server since Friday when it was running happily.

SQL Version: Microsoft SQL Server 2012 - 11.0.5343.0 (X64)   May  4 2015 19:11:32   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)

This issue is to do with corrupt/invalid statistics.

A specific case where it can occur is mentioned in this connect item

  The Metadata of disabled Non-Clustered Indexes becomes invalid, if you
  change the keys of the Clustered Index, and this caused the failure
  that you experienced.

With the workaround being to drop the disabled indexes.

If the situation above doesn't apply to you then try and find out the specific table with the problem hopefully dropping the statistics on this specific table or both tables if you can't pinpoint the problem one should do the job (and then recreate the stats if required).

If neither of the above work then probably time to open a case with Microsoft PSS.

Also you appear to be on SP2 + Security Bulletin MS15-058. There have been a lot of cumulative updates and another service pack since then. Possibly the issue you are experiencing is already fixed in one of these.


show list of master tables in postgresql I need to the SQL command to list all the master tables in the Postgresql database.

I have logged into a database called OpenBravo and when I run the command

\dt

It shows all the tables created in that database. I want to see the list of tables prefixed with pg_ which are master tables carrying the schematic details I believe


What happens if the mysql database's innodb log files are lost? What I did was

/etc/init.d/mysql stop

then removed the files: ib_logfile0, ib_logfile1

then modified my.cnf file, variable: innodb_log_file_size

and then:

/etc/init.d/mysql start

and allowed the files to be recreated

I later discovered that the global variable innodb_fast_shutdown is set to "1"

The question is, how much data was lost ?

Note: I still have the old files ib_logfile0, ib_logfile1, not deleted yet.

And the website relying on the database appears to be working.

First things first: kudos for not actually deleting the files -- moving files out of the way is always a better idea than just deleting them -- but don't try to put the old log files back.

It's possible that you might be able to analyze them but you can't put them back into service on this server, because the log sequence number (LSN) in the log files won't match the one stored in ibdata.

InnoDB:                          WARNING!
InnoDB: The log sequence number in ibdata files is higher
InnoDB: than the log sequence number in the ib_logfiles! Are you sure
InnoDB: you are using the right ib_logfiles to start up the database?

It would most likely then begin crash recovery and could potentially start "repairing" things that were not broken... so, I definitely would not try this.

  The question is, how much data was lost?

If any data was lost, it would have been recent changes (inserts/updates/deletes); however, quite contrary to what I've always thought, it may actually be safe to do what you did with innodb_fast_shutdown set to 1 (but not 2).

http://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html

Those instructions imply that as long as it's not set to '2' you'd be fine, and if it were set to '2,' they say to set it to '1' -- as opposed to '0' as I would have expected... so it looks like either you're good to go, or there's a fairly serious flaw in the documentation.


Lost entire MySQL Server. Unsure if "master-slave" or "master-master" - How to tell? Yup - my db02 server lost a disk, and the array itself is corrupt (RAID5, yet corrupt data.) I was able to get the OS back online, but the corruption is severe.  I have zero documentation about the mysql configuration, other than a copy of the /etc/my.cnf file from the failed host.  (one small joy in this.)
1) How would I determine if it was in a m-s or m-m configuration?
2) Since I am lucky enough to have the /etc/my.cnf file, is there a clean way of restoring from the master?
3) Anyone have any other ideas as to what I should look for, in order to make sure what else needs to be put back in place?  Backups are managed via scripting, from yet another system.

For M-M, both need to be masters and both need to be slaves.  Those need

log_slave_updates
log_bin

These should show something, even if not actively replicating at the moment:

SHOW SLAVE STATUS;
SHOW MASTER STATUS;

If you can't run things on the broken machine, then I suggest you do

Clone the good machine's disk to the bad
Change my.cnf to have a different server_id
Turn on the two flags above
CHANGE MASTER ... on both -- to establish being a Slave, and provide the pwd for the replication 'user'.

Now you will have Master-Master.  (There is not a lot of differences between M-S and M-M, so you may as well go for M-M.)


Having troubles creating a specific TRIGGER in MySQL So I am trying to implement a trigger in MySQL that is triggered once a new user is registered.
The relation of my tables are:
USER_ACCOUNTS has primary key USER_ID.
RACE_TRACKS has primary key RACETRACK_ID.
HIGHSCORES has as foreign_keys USER_ID and RACETRACK_ID.

The trigger I am trying make, must create records in HIGHSCORES table in a way that each NEW.USER_ID has a record for each of the RACE TRACKS (RACETRACK_ID).

Using phpMyAdmin database management tool I came to this point.

BEGIN
DECLARE trackID INT;
DECLARE done INT DEFAULT FALSE;
DECLARE trackCURSOR CURSOR FOR SELECT USER_ID FROM INSERTED WHERE USER_ID = NEW.USER_ID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

INSERT INTO HIGHSCORES SELECT trackID FROM RACE_TRACK;

OPEN trackCURSOR;
    REPEAT
    FETCH trackCURSOR INTO trackID;
    IF NOT done THEN
            BEGIN
            INSERT INTO HIGHSCORES (RACETRACK_ID, USER_ID)
            VALUES (trackID, NEW.USER_ID);
            END;
    END IF;
    UNTIL done END REPEAT;
CLOSE trackCURSOR;
END;

I am not getting any errors but when I am testing it if it works nothing happens. I am a bit confused to say the least because I do not have experience with triggers. If there is any way to enlighten me I would appreciate it.

Thank you in advance.

EDIT:
After some feedback more searching this is how my trigger looks now.

BEGIN
DECLARE v_RACETRACKID INT;
DECLARE FINISHED INTEGER DEFAULT FALSE;
DECLARE racetrackCURSOR CURSOR FOR SELECT RACETRACK_ID FROM RACETRACK;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = TRUE;

OPEN racetrackCURSOR;

get_racetrackID : LOOP
    FETCH racetrackCURSOR INTO v_RACETRACKID;
    IF FINISHED = TRUE THEN
          LEAVE get_racetrackID;
    END IF;

    INSERT INTO HIGHSCORES (RACETRACK_ID, BEST_LAP, BEST_RACETIME_1,
        BEST_RACETIME_2,USER_ID) VALUES (v_RACETRACKID,null,null,null
        ,NEW.USER_ID);
    END LOOP get_racetrackID;
    CLOSE

 END;

You need to put your code in a CREATE TRIGGER statement.

CREATE TRIGGER AddNewUserToHighscoresTable
    AFTER INSERT ON USER_ACCOUNTS
    BEGIN
         <insert code to run after inserts on the USER_ACCOUNTS table here...>
    END


What type of database server/service to use for an increased insert rate? Currently I am using MySQL and it processes 10,000 inserts per day, and soon it is going to be 200,000 inserts per day.

Now I am using a normal server which is okay with 10,000 inserts but processing 200,000 will require a large DB server. So my question is, what type of database server will I need to migrate to that could manage such a large insert rate?

What makes you think 200,000 inserts a day will be a problem? That is about 140/minute so if they are reasonably sized and spread evenly through the day just about any database will cope on the most modest hardware. If most of them are crammed into one hour than that would be approx 55 per second, so knowing how the pattern of your apps activity is likely to vary is vitally important when trying to predict how it will scale. The size of the inserts makes a difference too: a few hundred bytes per row, or LOB fields with several Mb of data each?

As it stands this question will get closed as "too broad" as it is essentially a "how long is a piece of string" one.

The only thing we can really suggest with this little information is "test it and see what happens".


mongodb bulk write all or nothing? I am learning about mongodb. If I create a bulk write is this transaction all or nothing? I have a scenario where my users can delete who they are following. For this to happen I need to delete from user A who they are following and from User B the follower. For consistency I need these to occur as a all or nothing because I wouldn't want only 1 of the 2 operations to succeed as this would cause bad data. Reading the docs:

https://docs.mongodb.org/manual/core/bulk-write-operations/

I could not find the answer.

MongoDB transactions are always atomic on single document. However, if it involves multiple documents transactions are not atomic.

Coming to Bulk operations, there is no transactions concept. For example, you are inserting 100 documents in a collection, if 51st insert fails, MongoDB will not insert the remaining documents in the list as it executes the bulk operations serially.


Viewing another session's settings It is possible in Oracle to examine the session-level settings for sessions other than your own (subject to privs obv). Is there an equivalent method in Postgres 9 or 10?

I don't think it's possible. PostreSQL session variables are meant for
mostly scripts to run in the same session. If you need persisting variables across user, or a place to store state -- PostgreSQL can do that too, just use a table.

From there you can limit access to the table through Pg access permissions or getter-setter functions.

Here is an example of such a scheme, https://www.depesz.com/2009/08/20/getting-session-variables-without-touching-postgresql-conf/


Can I use Try_Cast to give me a query result column with different datatypes? I have a column that is varchar(max).  It contains mostly valid XML.  But sometimes it has invalid xml (that I still need).

I am trying to make a view for this table that allows the value to display as XML (with the nice link feature that comes with SSMS) when the data is valid XML, but still shows the string when it is invalid XML.

Like this:

SELECT TRY_CAST([RunLog] AS xml) as [Log], -- works
       RunLog,                             -- Also Works

       -- This is the one I want, but it tries to parse all of them
       -- (even the ones that fail the cast)
       Case
           When Try_Cast(RunLog as xml) is NULL
                Then NettinRunLoggLog else CAST(RunLog AS xml)
       end as CombinedLog

FROM   LoggingTable

I think that this is a lost cause, because SQL Server will not allow a result to have more than one datatype.  But I thought I would ask the experts just to be sure.

All output of the case expression must be of the same datatype.

If maintaining the XML datatype for the valid XML data is worth a little manipulation of the invalid data, then one workaround could be to wrap the invalid data up like a declaration before casting it to XML:

cast('<?invalid ' + RunLog + ' ?>' as xml)

Also, you shouldn't need to use both try_cast and cast in the same expression.

coalesce(try_cast(RunLog as xml),cast('<?invalid ' + RunLog + ' ?>' as xml))


Correct way to restore dbs to new server We are migrating from sql server 2008 to 2017 and will do the following

install sql server 2017 on the new machine
make backups of all dbs
on old server

RESTORE DATABASE DbName FROM DISK = 'C:\temp\Live\dbname.bak'
WITH
   MOVE 'DbName' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DbName.mdf',
   MOVE 'DbName_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\DbName_log.ldf'

I would like to avoid the WITH Part if possible but that requires me to create a blank db first and set its version to 2008 before i can properly restore.

I guess what i am asking is, what is the best way to take a .bak file and restore it as db on a server that doesnt have any databases.

Prefer to do this without attach/detach

I have a large number of databases so I will be creating a script to do all of them at once.

I am not sure what your issue is here.

When you restore the database, the data (mdf) and log (ldf) files have be on disk someplace.  The WITH MOVE just tells SQL where to put them.
You can't have a SQL server without databases, every install will have 4 databases out of the box, master, msdb, model & tembdb, they are system databases, and they each have mdf & ldf files someplace, just like the user database you want to restore.
As  scsimon mentions in a comment, you probably do not want your SQL files on the C drive, best practice explained to me is for to have 4 drives for SQL,

Data Drive (mdf)
Log file drive (ldf)
Tempdb (mdf & ldf for tempdb)
Backups (bak & trn, which should be copied off at least nightly)


MySQL collate error occurs when sent by PHP app but does not occur in phpMyAdmin I have a query that uses:

t3.`value` LIKE "%someval%" COLLATE utf8_general_ci

I load the page in my browser and get this error printed to the screen:

COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

The query is also echoed to the page. And if I copy and paste the outputted query into a phpMyAdmin SQL window, it runs fine, producing the expected result.

I had a previous problem where PHP could not use LOAD DATA INFILE. I wonder if this is a similar type of problem. But I can't see how it would be.

This is running on an Ubuntu Server 10.04 MySQL Apache PHP stack.

Thank you google.

Apparently, I have been using PHP with MySQL without ever needing to worry about client collation.

I don't know if this will lead to other problems, but for the moment I've solved this problem by adding the following query to the main controller (so that the query is run for every script on the site).

SET NAMES "utf8" COLLATE "utf8_general_ci";

Now my query works through my PHP script. I suppose that phpMyAdmin might take the collation of whatever tables you are querying and set the client collation accordingly. I'm just guessing on that one.

Here's where I found the answer:
http://dev.mysql.com/doc/refman/4.1/en/charset-collation-charset.html


Minimizing SELECT latency in PostgreSQL I am evaluating Postgres 9.4 performance on a machine with Intel i7 quad-core 3.6 GHz CPU, 8 GB ram, and 7400 rpm HDD (no RAID) running Linux Mint. The DB schema has the following table:

          Table "public.sensor_readings"
  Column  |           Type           | Modifiers
----------+--------------------------+-----------
 time     | timestamp with time zone | not null
 value    | numeric                  |
 sensor_id| integer                  | not null

Indexes:
      "sensor_readings_pkey" PRIMARY KEY, btree (sensor_id, "time")

This table has 72 million rows and is 35 GB in size (PKEY index is 25 GB). sensor_id ranges from 0 to 5000.
I need to query sensor values for past two weeks:

SELECT
FROM sensor_readings
WHERE sensor_id IN (1,3,8,9,12)
    AND time BETWEEN CURRENT_TIMESTAMP - interval '14 day' AND CURRENT_TIMESTAMP ;

The problem is that the average query execution time is about 5 minutes even after a 2000-execution warm up. This is two orders of magnitude higher that what I want to achieve!

I did not change Postgres default parameters and did no optimization.

Can anyone suggest what can be wrong with my setup or schema? Are there any optimizations that I can do to minimize SELECT execution time? In particular, what is a proper ratio between table (index?) size and RAM?

P.S.

Sample EXPLAIN (ANALYZE, BUFFERS) output:

Bitmap Heap Scan on sensor_readings  (cost=3535.18..405031.07 rows=113904 width=0) (actual time=5190.213..60196.531 rows=103104 loops=1)
  Recheck Cond: ((sensor_id = ANY ('{1509,1504,1503,1500,1502}'::integer[])) AND ("time" >= (('now'::cstring)::date - '14 days'::interval)) AND ("time" <= ('now'::cstring)::date))
  Heap Blocks: exact=47786
  Buffers: shared hit=12 read=48491
  ->  Bitmap Index Scan on sensor_readings_pkey  (cost=0.00..3506.70 rows=113904 width=0) (actual time=5165.932..5165.932 rows=103750 loops=1)
        Index Cond: ((sensor_id = ANY ('{1509,1504,1503,1500,1502}'::integer[])) AND ("time" >= (('now'::cstring)::date - '14 days'::interval)) AND ("time" <= ('now'::cstring)::date))
        Buffers: shared hit=12 read=705

Planning time: 24.887 ms
Execution time: 60205.108 ms

The main problem is that you have 60 GB of data which you want to access quickly on a single slow HDD (I've never heard of 7400 rpm, is it 5400 or 7200?).

You can partition on the time, so the last two weeks of data are grouped together in tight set.

Or you could try clustering on the time instead of partitioning on it.  You could either build on index on the date column, cluster on that, then drop the index; or you could just make a new table by create table asdfkj as select * from sensor_readings order by time.

I would expect the table to already be pretty well clustered on time, but it doesn't seem to be.  How did this table get populated originally?  How does it get kept up to date now?

But all of these methods are pretty expensive (in your time) to avoid buying a better IO system.


History table design for supertype/subtype I'm designing an asset management database that tracks IT hardware. I decided to use a supertype/subtype design. I'm at a point where I want to track history of changes for devices. I wanted to use a separate history table, but I can't decide how to track history for changes made to subtype tables.

If I use separate history tables for each subtype table I can reconstruct records by joining them with the supertype history table, except in the case where subtype history tables change independently of the supertype history table. By independently, I mean there are x updates to data in the supertype table, creating x supertype history records, and y updates to a subtype table creating y subtype history records. If the changes are made on the same day, how would I reconstruct records?

Is this a good use of supertype/subtype, or should I denormalize the tables?
Otherwise, can anyone suggest any way to approach the history issue for this type of design?

Using MS SQL Server 2008.

Here is a very simplified ERD:

It sounds like what you want is an implementation of transaction-time temporal tables. It's harder than it sounds.

  Valid time, capturing the history of a changing reality, and
  transaction time, capturing the sequence of states of a changing
  table, are orthogonal, and can thus be separately utilized or applied
  in concert. A table supporting both is termed a "bitemporal table".

Reference: Developing Time-Oriented Database Applications in SQL, by Richard Snodgrass, p 20.

This book was the only book I could lay my hands on when I was working on this stuff some years ago. Now you can download a PDF of the book and the accompanying code from the author's web page.

Newer material might be more useful. There are links on his web page.


EMS SQL manager permission problem I have a strange problem with EMS SQL Manager.

I'm using MySQL 5.5 with linux servers. One of my developers need to edit store procedure, so I grant him:

GRANT CREATE ROUTINE, ALTER ROUTINE ON `testdb`.* TO 'testuser'@'192.168.13.11'

When he clicked edit store in EMS SQL Manager, what he got is:

SELECT command denied to user 'testuser'@'192.168.13.11' for table 'user'

I find out that EMS SQL Manager ask for select privilege in some tables, so I need to grant this user some extra permissions.

GRANT SELECT ON `mysql`.`user` TO 'testuser'@'192.168.13.11';
GRANT SELECT ON `mysql`.`db` TO 'testuser'@'192.168.13.11';
GRANT SELECT ON `mysql`.`tables_priv` TO 'testuser'@'192.168.13.11';
GRANT SELECT ON `mysql`.`columns_priv` TO 'testuser'@'192.168.13.11';
GRANT SELECT ON `mysql`.`procs_priv` TO 'testuser'@'192.168.13.11';

So my question is, WHY EMS SQL Manager need those privileges?
I can create, drop procedure in mysql command line client without problem.

Update

After some investigation, I still do not understand EMS SQL Manager.
This is the action EMS SQL Manager take when click the Compile button.

Executed  : 5/8/2013 3:10:40 PM
Operation : CONNECT
Result    : "OK."

Executed  : 5/8/2013 3:10:40 PM
Operation : show variables like 'character_set_results'
Result    : "OK."

Executed  : 5/8/2013 3:10:40 PM
Operation : SELECT * FROM mysql.user ORDER BY User, Host
Result    : "SELECT command denied to user 'ems'@'192.168.13.11' for table
'user'"

Executed  : 5/8/2013 3:10:40 PM
Operation : DISCONNECT
Result    : "OK."

Executed  : 5/8/2013 3:10:41 PM
Operation : select p.`db`, p.`name`, p.`type`, p.`specific_name`,
p.`language`, p.`sql_data_access`, p.`is_deterministic`, p.`security_type`,
p.`param_list`, p.`returns`, p.`body`, p.`definer`, p.`created`,
p.`modified`, p.`sql_mode`, p.`comment` from `mysql`.`proc` as p where
p.`type` = 'PROCEDURE' and p.`db`='reportingdb' and
p.`name`='100_rpt_campaign_ip'
Result    : "OK."

Executed  : 5/8/2013 3:10:41 PM
Operation : SHOW CREATE PROCEDURE `reportingdb`.`100_rpt_campaign_ip`
Result    : "OK."

So my question, What is the Compile button trying to do? Why does it want to select mysql.user? I think that editing a stored procedure is two steps of drop and re create it, why does it want to do more?

The best thing you can do to understand this behaviour better is to ask the software vendor. If you don't want to grant those permissions, lodging a bug report might be appropriate.

I'm going to hazard a guess that EMS SQL Manager is trying to be too clever, and check whether you have Alter_routine_priv or Create_routine_priv so that it can return a warning or error before it tries sending your ALTER ROUTINE to the database.

Most SQL Editors run lots of queries in the background to support "IntelliSense", speed up common queries, or provide background information, and a lot of the time they just don't make any sense (some of them are downright frightening).


How to properly replicate and archive at once I have a 300GB database that is growing around 5GB a day. It stores time-based data, and as such, I currently have around 60 days of data. My main database server has a 450 GB NVMe, so I want to get a secondary server that has expandable cheap storage for old data.
The main server only really needs the last 15 days of data. So anything older than 15 days of data I want to move off to the secondary database which has slower but cheap storage. I think standard backup or archiving isn't viable because I want to query the data at any given moment (maybe ~100 queries a day).
I want to kill two birds one stone here and I thought if I setup replication to the secondary server and then somehow indicate the cleanup stored procedure which delete records older than 15 days doesn't propagate deletes to the slave.
Is this possible and recommended?
If not, what is the best way to accomplish archived the data but to another server and allow querying. I suspect partitioning all of my tables but not all of my tables are time-based so I would need some way to move the time-based partitioned data over along with any FKs needed for it
PS: I won't be relying on the secondary server as a backup but rather my main replication.  I would then do offsite backups of the secondary server periodically

Let me explain multiple things.  Then you can figure out which combination fits best.

Technically the Primary and the Replica should have identical data.  However...
You can, via a temporary setting, say that specific statements are not to be replicated.  These could be anything -- INSERT, Partition commands, DROPs, etc.
It sounds like you want the data to be moved from fast media to slow media.  This would require some form of copy.  And that would probably block all operations (maybe even SELECTs) from the table while it is happening.
It may be possible to direct that certain PARTITIONs are in a different filesystem path.  If so, that secondary path could be the other drive.
"Transportable tablespaces" allows for an individual PARTITION to be disconnected from a table and turned into an independent table.  That could them be copied to another device, backed up, deleted, etc.  (Do not do it via filesystem operations.)
It is probably wise to switch to PARTITION BY RANGE(TO_DAYS(...)) with daily partitions.  This would facilitate such movement and eventually deleting after 60 days.
PARTITIONing applies to a table, so there is no need for doing it with all tables.  (Perhaps you are referring to "sharding", which involves having separate, independent, instances of MySQL with each one having part of the data.
If you have not already set up partitioning or replication, there will be a one-time cost (in downtime) to set up either.  After that, ongoing maintenance will be 'cheap'.
More on this use of partitioning:  http://mysql.rjweb.org/doc.php/partitionmaint


How do I search by attribute in DynamoDB? I inherited a site that uses DynamoDB for its database, which I know nothing about unfortunately, and I'm trying to change a user's email for them.  There appears to be a users table in DynamoDB, but I don't know the user's id, only their username and current email, so at this point, I am just trying to find the record before updating it.  When I attempt to scan for these values in the web interface, I get no results; it just keeps searching.  If I scan in the web interface for an email address which I know exists, I also get no results.
Using these placeholder values,
Username: example
Email: example@example.com

I have tried a few commands using AWS CLI, but I get various errors:
aws dynamodb get-item --table-name users --key '{"Username": {"S": "example"}}'

This yields this error:
Unknown options: example}}', {S:

I have also tried this:
aws dynamodb query --table-name users --key-condition-expression "Username = :v1" --expression-attribute-values "{ \":v1\" : { \"S\" : \"example\" } }"

This yields this error:
An error occurred (ValidationException) when calling the Query operation: Query condition missed key schema element: id

I have also tried printing out all users, but it appears to fail for anything but the smallest tables:
aws dynamodb scan --table-name users

This yields this error:
An error occurred (ProvisionedThroughputExceededException) when calling the Scan operation (reached max retries: 2): The level of configured provisioned throughput for the table was exceeded. Consider increasing your provisioning level with the UpdateTable API.

Not sure what to do at this point.  Any advice?

I contacted AWS support, and they said two things:

The read/write capacity of the table needed to be changed from "provisioned" to "on-demand".

Scan is definitely the command to use here, not query; query requires the id, scan does not.
aws dynamodb scan --table-name users --filter-expression "Username = :v1"
    --expression-attribute-values "{ \":v1\" : { \"S\" : \"example\" } }"


MySQL metadata function to get projected column type in query? Is it possible to get the field type from a MySQL query, in the same way you can get it from a table with the SHOW COLUMNS command? Such as from a derived table,

SELECT x -- presumedMetaFn(x) -- returns "int"
FROM (
  SELECT 1 AS x
  UNION SELECT 2
) AS t;

Given the above query, is there a function or something that I can use to get the type of x? PostgreSQL makes this available with the System Information Functions pg_typeof

SELECT x, pg_typeof(x)
FROM ( VALUES (1),(2) ) AS t(x);
 x | pg_typeof
---+-----------
 1 | integer
 2 | integer
(2 rows)

I am not looking for metadata on a table, but from the result of query. With psql on PostgreSQL 11+, this is also possible by running \gdesc after the query.

Within MySQL, you can get this information by creating a temporary table, then using DESCRIBE on that temporary table:

CREATE TEMPORARY TABLE `temp`
SELECT ...
FROM ...
LIMIT 0;

DESCRIBE `temp`;

We cannot just use DESCRIBE on the original table because the column we want to know the type of is a calculated column, not something being directly pulled from a table.  Likewise, we cannot use DESCRIBE directly on the query, because DESCRIBE can only be used on tables.  Creating a temporary table solves both of those problems.


possible to specify field enclosure in psql command line? In psql if I have a field separator of ",", but there are commas in some fields, is it possible to specify a field enclosure character? I have looked through the documentation but I can't find it.

example:

psql -h my_server -U my_user -d my_db -f sql_in/my_sql.sql -o sql_out/my_sql_out.csv -A -F','

-A will turn off the table formatting, -F specifies the field separator

If I have a row that looks like

id  types
1   zebra, lion, cat
2   horse

I want it to output to csv like:

1,"zebra, lion, cat"
2,"horse"

In v12, psql introduced the --csv option.  However, it still seems like the wrong tool for the job.  You should use COPY or \copy instead (probably still invoked through psql)


Multiply by value from related row with latest date I have two tables, one with staff time entries, another with staff rates starting from a particular date:

time_table

id | staff_id | entry_date | hours
----------------------------------
1  | 1        | 15-01-2019 | 1
2  | 1        | 15-02-2019 | 2
3  | 1        | 15-03-2019 | 3
4  | 2        | 15-01-2019 | 4
5  | 2        | 15-02-2019 | 5
6  | 2        | 15-03-2019 | 6

rates_table

id | staff_id | start_date | rate
----------------------------------
1  | 1        | 01-01-2019 | 1
2  | 1        | 01-02-2019 | 2
3  | 1        | 01-03-2019 | 3
4  | 2        | 01-01-2019 | 4
5  | 2        | 01-02-2019 | 5
6  | 2        | 01-03-2019 | 6

I would like to multiply the time entries by the rate for that staff member that is the most recent, occurring on or before that time entry.

I have this query but I have no idea how to select the most recent rate that occurs before the time entry:

select t.staff_id, t.entry_date, t.hours * r.rate as total_rate
from time_table t
left join rates_table r on r.staff_id = t.staff_id and r.start_date < t.entry_date;

https://rextester.com/DDK49143

I would like a result like the following:

staff_id | entry_date | total_rate
----------------------------------
1        | 15-01-2019 | 1
1        | 15-02-2019 | 4
1        | 15-03-2019 | 9
2        | 15-01-2019 | 16
2        | 15-02-2019 | 25
2        | 15-03-2019 | 36

How could I do this in Postgres?

A LATERAL subquery would do the job:

SELECT t.staff_id, t.entry_date, t.hours * r.rate AS total_rate
FROM   time_table t
LEFT   JOIN LATERAL (
   SELECT r.rate
   FROM   rates_table r
   WHERE  r.staff_id = t.staff_id
   AND    r.start_date <= t.entry_date -- "on or before that time entry"
   ORDER  BY r.start_date DESC NULLS LAST
   LIMIT  1
   ) r ON true;

db<>fiddle here

Details depend on more information. The LEFT JOIN keeps all rows from time_table in the result, even if no rate is found. (total_rate is NULL in that case.)

This is typically efficient for many rate entries per staff_id - if you have an index on rates_table(staff_id, start_date DESC NULLS LAST) or similar.

If you can get index-only scans out of it, a covering index would be better, yet:

CREATE INDEX ON rates_table (staff_id, start_date DESC NULLS LAST) INCLUDE (rate);

This form of the index requires Postgres 11 or later. See:

Can Postgres use an index-only scan for this query with joined tables?

Depending on table definition, indexes, data distribution etc. other query styles may be preferable. For querying the whole table and only few rows per staff_id, DISTINCT ON might be faster ...

Related:

Select first row in each GROUP BY group?
Optimize GROUP BY query to retrieve latest record per user
Slow left join lateral in subquery
What is the difference between LATERAL and a subquery in PostgreSQL?


Oracle: Very slow to purge table from recycle bin I ran a "PURGE TABLE" command on a table in the Recycle bin. It has now been running for 25 minutes and still going on. Is it normal for a purge to take that long?
Is it safe to cancel (I'm using SQL developer which has a cancel button)?

My database server was out of disk space, which caused the never-ending purging.


What exactly do database passwords protect? I would like to know what exactly I am password protecting on my server when I set a database password. I am unclear as to what people are referring to when saying to password protect a database. Are they referring to a single specific database or do they mean the complete mysql database structure located on the server. The reason I ask is because, when I set a password, it is only one password for all my databases that I'm managing in phpMyAdmin. So when I'm required to enter the password in my config file in codeigniter, it is always the same one for all my different databases that I'm using. Is the password that I'm required to enter in order to even get phpMyAdmin started the Database Password which people refer to, or can I (and should I) set different passwords for my seperate Databases?

The passwords you're talking about are passwords for MySQL users.  You don't really set a password for a database or table.  You can effectively implement different passwords for different DBs by creating multiple users with different passwords and only granting access to specific databases for each user.  When people say that you should set a password for the database, they probably mean that you shouldn't use users with no password, which is possible, but shouldn't be done due to the obvious security concerns.

Whether or not you "should" use multiple users depends on your situation.  If you have multiple databases that are used by independent applications, it is a good idea to create a user for each application to help isolate any risks from spreading between applications via the database.  If you have one database that is used by different applications, you can use different users to help track stuff or lock down permissions.  If you have on application, you're generally safe with using just one user.

The main reason to use different users is to lock down permissions and prevent any errors/attacks from causing too much damage.  If you're performing a task that only needs to read data, using a user that only has SELECT permissions can prevent accidental deleting or modifying of data; even though the risk of such an event is very low, it is still good to have something to prevent it.  Using different users is usually only helpful when all of the users would see significant use.  In your situation, you could use multiple users, but it would probably become more of a nuisance for you to keep track of when you would use which user and which user is currently connected.


How to find the max date stamp in a database? I want to find the most recent date in a database.

I could use sys.dm_db_index_usage_stats to find when it was last updated, but there is no value here if it has not been updated since the last server restart

I could use MAX()

use adventureworks
select Max(ModifiedDate)
from HumanResources.Department

But I would need to look at every table in the database, find each datetime (or date, smalldatetime, datetime2) field and run the query multiple times, then decide which was most recent.

I want something like

use <some_db>
select MAX(<FieldThatIsADate>)
from <anytable_in_db>

I have Googled around and am not finding a way to use the datatype in place of a field name. I have not attempted to find a way around naming the specific table.

How can I make this query as portable as realistic?

If I have to build and run a unique query for every table in each database, well that is what I have to do.

There is no way to query on a datatype much less across various tables like that, it would imply that the entire database is kept a second time in a set of tables that resemble the system tables. However, dynamic SQL using the INFORMATION_SCHEMA can be of assistance in your search, but it will not be portable to other types of SQL Servers. Most servers should have something similar available but the syntax will be radically different for each one.

You can do the following to find what you're looking for:

SELECT 'SELECT max(modifiedData) FROM ('+
        (SELECT 'SELECT MAX(['+C.COLUMN_NAME+']) as modifiedData FROM ['+C.TABLE_SCHEMA+'].['+C.TABLE_NAME+'] UNION ' AS [text()]
        FROM INFORMATION_SCHEMA.COLUMNS C
        WHERE C.DATA_TYPE IN ('date','datetime','smalldatetime','datetime2')
        FOR XML PATH('')) + ') AS AllDateTimes'

That dynamic SQL will output a query you can run to find your MAX date. Please note that you will need to trim a trailing UNION from the inner SELECT after you've copied the output to a query window. You can create the same query with the sys tables but you will have to join a few of them together and the query will be a bit more complex if you aren't familiar with how the sys tables are setup.

That all said, this will return the maximum date stored in your database regardless of context. Depending on how it was used there is no guarantee some value could not have been set in the 'future' for any number of reasons.


Sqlcmd: 'KReadOnly': Unknown Option I am using SQL Server 2014 SP2 with AG, running this command:

sqlcmd -S ABC-ISELL -E -d ABP_SFA_BMB -K ReadOnly

Where ABC-ISELL is the listener name, I am getting this error:

Sqlcmd: 'KReadOnly': Unknown Option. Enter '-?' for help.

sqlcmd -? shows there is no -K option, it`s just this:

[-k[1|2] remove[replace] control characters]

What could be wrong here?

One needs to use recent enough a version of sqlcmd. The parameter for application intent is -K. Note that sqlcmd's parameters are case sensitive, which is uncommon in Windows environment.

To check sqlcmd's version, run it with -? parameter. Like so,

C:\>sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 14.0.1000.169 NT
Copyright (C) 2017 Microsoft Corporation. All rights reserved.

usage: Sqlcmd            [-U login id]          [-P password]
...
[-k[1|2] remove[replace] control characters]
...
-K application intent

For updated version, check Microsoft's product documentation,
 there's a link to Download center.


Different types of result for coulmnName = nulll and coumnName IS NULL in PostgreSQL I have table Party with columns party_Id, party_type

When I use the below queries, I get two different types of result.

SELECT * FROM party WHERE party_type = null

Returns nothing

SELECT * FROM party WHERE party_type IS NULL

Returns all possible rows

Need a query which should accept both null and values.

As per SQL standard, comparisons (and boolean operations) use 3-Value logic (true, false, unknown), with unknown being represented by NULL. The following expression returns unknown (represented by NULL):

party_type = null

Whereas

party_type IS NULL

will return a pure boolean (true, false).

You probably want a "NULL-safe comparison predicate":

party_type IS NOT DISTINCT FROM null

See Comparison Predicates


Database Design - Removing ambiguity without adding loop I'm looking for a better approach to the problem (and my solution) described below. I've attached an image of my initial design

Let's say I have a cat, and I'd like to find its owners and what vets they took it to. I can easily find out what vets the cat has been to, if I choose a specific vet, I want to know what owners have brought the cat there. I run into problems here, because the list of owners that came to the vet may include owners who own cats other than the one I've found. So now my thought is to say I've got this cat, and I'm at this vet, I want to see all owners who have taken this cat to this vet.

So I add a relationship between the cat and it's owners (this cat is owned by many people, and each of them has multiple cats i.e. many-many). Now I can say that I know these people own this cat, and I know what vets the cat has been to. I can now select all owners who have taken the cat to a specific vet.

I am of course aware that loops are not inherently bad in a database design (as long as no redundancy exists). I'm not convinced that redundancy doesn't exist here, the many-to-many relationship makes me feel that this is the only approach, but I was hoping that someone might have a better approach to this problem.

If I understand your scenario properly, the following assertions are relevant:

A Cat has one-to-many Owners or, put another way, an Owner has one-to-many Cats.
A Cat is involved in one-to-many Visits to one-to-many Vets.
In each Visit to one specific Vet, one  Cat is taken by one-to-many Owners.

From this set of assertions I have derived (and uploaded to Dropbox) the following Cats and Vets Data Model, which depicts the way I would approach your specifications.
As you can see in such IDEF1X[1] data model, I consider important to add an entity that represents the specific event when a Cat (brought by one-to-many CatOwners) visited a particular Vet, and I called it Visit.
And yes, I also think that the CatOwner entity (that you have alredy included in your diagram) is very useful since, in addition to preventing a Person who is not an Owner of a particular Cat from taking such Cat to a Vet, it may also serve the purpose of relating a Cat and one-to-many of its Owners to a specific Visit to an individual Vet.
It is also necessary to add a many-to-many relationship to provide the advantage of associating one-to-many CatOwner ocurrences to one-to-many Visit instances (each Visit received by one particular Vet), and I propose the relationship called CatOwnerVisit to cover this requirement.
Note
1. IDEF1X is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the U.S. National Institute of Standards and Technology (NIST).


What is difference between is_unique column and is_unique_constraint columns in sys.indexes We have two columns is_unique and is_unique_constraint for unique constraints in sys.indexes and I want to clarify few concepts based on these two columns

Create statement for both types of indexes are same except fill factor property value or do I missing something?
If statement 1 is true then why we have two columns in sys.indexes for unique constraint?
When is_unique=1 is_unique_constraint =0 and then drop statement for index is
DROP INDEX [index name] ON [dbo].[TableName] WITH ( ONLINE = OFF )

And when is_unique=1 is_unique_constraint =1 and then drop statement for index is
ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [IndexName]

Why is that so?
Where we use unique index and where unique constraint, what is best practice?

Fact is that there is no practical difference between a unique constraint and a unique index.

Querying sys.objects, you will find unique constraint is listed as a constraint object and its related index can be found while querying sys.indexes, where it is marked is_unique = 1 as well as is_unique_constraint = 1. On other hand, for a unique indexes is_unique_constraint value will be zero (0).
While creating unique constraint you can use Fill Factor option as compare to unique index where you have more options like FILLFACTOR, PAD_INDEX, IGNORE_DUP_KEY, DROP_EXISTING, and STATISTICS_NORECOMPUTE.
Unique constraint is maintained through a unique index
Just like Default and Primary Key, a unique constraint can't be disabled using NOCHECK syntax
To delete a unique index with is_unique_constraint = 1, it is must to delete it through DROP CONSTRAINT syntax instead of DROP INDEX because such index is associated with a unique constraint (object), so one must drop CONSTRAINT and index will be dropped automatically.

As best practice, If uniqueness is required then always apply unique constraint, instead of creating only unique index. This way you can't drop a unique index accidentally.Also this way you can document all applied constraints easily.
http://technet.microsoft.com/en-us/library/aa224827(v=sql.80).aspx


Syntax error when trying to add columns to table in DBeaver (postgresql) I've tried this a few different ways but every time I get a syntax error when trying to add columns to a table.

I'm new to databases so it's probably something basic that I'm doing wrong, but I just don't see why this isn't working.

Any input would be appreciated.

P.S. at the end of the query line there is a ";"... You just can't see it in the screenshot.

So far I've tried changing "add columns" to "add column" and just "add" followed by the columns. I'm aware I can just use the GUI to copy columns in DBeaver but I'd rather get this SQL working.

According to Postgres docs the correct syntax is:

ALTER TABLE <your_table>
    ADD COLUMN <column name> <column type>,
    ADD COLUMN <column name> <column type>,
    ADD COLUMN <column name> <column type>;

Have a look at Postgres tutorials on PostgreSQL ADD COLUMN: Add One Or More Columns To a Table.

I've set up a minimal example that reproduces your error:

CREATE TABLE newtable (id int);

ALTER TABLE newtable
    ADD COLUMNS f1 float8, f2 int;

  ERROR:  syntax error at or near "float8"
  LINE 2:     ADD COLUMNS f1 float8, f2 int;                          ^

ALTER TABLE newtable
    ADD COLUMN f1 float8, f2 int, f3 text;

  ERROR:  syntax error at or near "f2"
  LINE 2:     ADD COLUMN f1 float8, f2 int, f3 text;

Using the correct syntax:

ALTER TABLE newtable
    ADD COLUMN f1 float8,
    ADD COLUMN f2 int,
    ADD COLUMN f3 text;
✓

db<>fiddle here


scenario to update or insert big amount of data I try to find the best scenario to update or insert 1 million contacts in my postgres database.

My actual scenario is:

read contacts 1000 per 1000 and try to insert multiple rows with one request
if my request fails, I go line per line to insert or update my 1000 contacts

It takes 7 minutes for 50K lines.

What is the best scenario to perform my update or insert?

I would do it like this:

Load all data into an empty staging table and use SQL to identify and correct problematic records before moving the data into your database.


How to insert an IP-address into an inet column in PostgreSQL? I would like to insert an IP-address into a column that has type inet. In what format can I insert the data? is it only binary or is there any way I can insert from text e.g. "192.168.1.082"? Are there any help-functions for this so I can test it from psql in the command prompt?

It seems pretty easy:

postgres=# create table inet_test (address inet);

CREATE TABLE

postgres=# insert into inet_test values ('192.168.2.1');

INSERT 0 1

postgres=# insert into inet_test values ('192.168.2.1/24');

INSERT 0 1

postgres=# select * from inet_test;

 address
----------------
 192.168.2.1
 192.168.2.1/24
(2 rows)


MySQL/MariaDB Read only except for a single table I Have a database with lots of tables and read_only enabled in the config, is it possible to set a single table to be writable?

I don't believe you can do read_only = 1 (except for table x);

So I would think your best option would be to set all your users to read_only:

GRANT SELECT ON *.* TO 'user'@'1.2.3.4';

Then the users that need to write to that table would be given permission to write to just that table. e.g.

GRANT INSERT, UPDATE ON 'dbname.tablename'@'1.2.3.4';


hstore type does not exist with hstore installed postgresql I recently had to copy over a databases structure from one database to another with the command

pg_dump -c -S database_name > pg_dump_date.sql

I then ran the dump file on the new database with

psql < pg_dump_date.sql

And my schemas were recreated, as well as my extensions were installed.

After checking the installed extensions I can see that hstore is installed, but when I try basic operators for hstore like

SELECT id FROM schema_name.table_name WHERE hstore_column->'hstore_key'::TEXT = 'hstore_value'

I get the error that

operator does not exist: schema_name.hstore -> text

I get a similar error if I do not try and cast the key

operator does not exist: schema_name.hstore -> unkown

My first instinct was to simply drop the hstore extension and re-install it, but doing so would also drop my numerous user functions, triggers, and other dependencies on the hstore type.

Short of doing a cascading drop of the hstore exentsion, is there a way that I can fix this error? I am using a postgresql 9.3 server.

For anyone that runs into this question. I ran into this problem today and found a solution.

Depending on where you have the extension installed, it may not be in your search_path.
In my case, i had it installed into the schema 'shared_extensions' on the same db.

using SHOW search_path displayed $user, public, which means that it was never looking in shared_extensions for the extension and it's operator

To fix it run
SET search_path TO "$user", public,
followed by the schema where the extension is installed.

Hope that helps!


Can I parse a text value from a specific column in a query (MySQL)? I was playing around with Sakila and I ran across the actor_info view and one of its columns is film_info which is a detail of all the movies one actor has worked in. For example a query would be:

select * from actor_info;
# actor_id | first_name | last_name | film_info
    1      | PENELOPE   |   GUINESS |  Animation: ANACONDA CONFESSIONS; Children: LANGUAGE COWBOY; Classics: COLOR PHILADELPHIA, WESTWARD SEABISCUIT; Comedy: VERTIGO NORTHWEST; Documentary: ACADEMY DINOSAUR; Family: KING EVOLUTION, SPLASH GUMP; Foreign: MULHOLLAND BEAST; Games: BULWORTH COMMANDMENTS, HUMAN GRAFFITI; Horror: ELEPHANT TROJAN, LADY STAGE, RULES HUMAN; Music: WIZARD COLDBLOODED; New: ANGELS LIFE, OKLAHOMA JUMANJI; Sci-Fi: CHEAPER CLYDE; Sports: GLEAMING JAWBREAKER

So I'd like to make a selection of actors who have worked in documentaries or animation but i don't know if this is possible. Sorry if the question is too basic. Thanks!

WITHOUT INDEXING

SELECT * FROM actor_info
WHERE film_info LIKE '%Animation%' OR film_info LIKE '%Documentary%';

or

SELECT * FROM actor_info
WHERE LOCATE('Animation',film_info)>0
OR LOCATE('Documentary',film_info)>0;

WITH INDEXING

Create a FULLTEXT index on film_info

ALTER TABLE actor_info ADD FULLTEXT (film_info);

Query using the FULLTEXT index

SELECT * FROM actor_info
WHERE MATCH (film_info) AGAINST ('+Animation*' IN BOOLEAN MODE)
UNION
SELECT * FROM actor_info
WHERE MATCH (film_info) AGAINST ('+Documentary*' IN BOOLEAN MODE);


MySQL Master - Master Replication I have established a master A and master B replication between two servers. Both are getting replicated, but it is limited to only one database.

My questions:

How to create a new database in Master A has to replicated in Master B or doing the same in vice versa?
Need to Replicate more than one database

Could you give us some info on the replication config in your config files.

For sure if you replicate one db at a time and you need to add one, you won't have a choice to change your config then to restart your instances.

If you can I'll suggest more a full replication. less trouble and if you need to add dbs, there is no problem there.


SQLPlus : default define settings My workstation is Ubuntu 14.04. I have Oracle Express 11g installed. Whenever i run the define command in SQLPlus, is see the define _editor = "ed". Is there a way to change that permanently to gedit instead of defining my preferred editor with every new session.

You should be able to do this using the either glogin.sql or login.sql. The former is run whenever anyone logs into SQL*Plus and the latter is specific to a user. Im not currently in a position to install it myself to try out but this link to the Oracle docs should help.
Configuring SQL*Plus


Database Design Techniques, Notations and Terminology I'm taking a Database course and I'm a bit confused with the different types of graphical representations terminology of a database design.

Are Data Model, Crows Foot Notation, Crows Foot Model, ERD & ERM all the same. Where do they differ?

My understanding is as follows:

Crows Foot Notation - Graphically illustrate the relationship (ex. zero or many, one or many, one and only one, zero or one)between two or more entities.
Crows Foot Model - Is this the same as Crow's Foot Notation?
Entity-relationship Diagram (ERD) - Portray each entitie's name, attributes and keys. Do I need to display the dataypes as well? Does this also consist of the Crows Foot notation?
ERM - Is this the same as ERD.
Data Model - I assume this is very general. Would this consist of a depiction of an entity and its name, and arrows pointing to the entities it relates to?

There are basically just two things here:

Your data model, which is the structure of your entities, attributes and relationships.
The graphical representation of your data model.

Your data model may be high level (entities/relationships only) or lower level (add in attributes with details of data types) and it may be logical or physical.  It all depends on what stage of your design process you're at.  The point of a data model is to record your database design, which is just an expression of your business requirements for persisting data.

Crows foot and ERD etc. are just graphical conventions for showing your data model pictorially.  The great thing about standards is that there's so many to choose from, and this is also true of graphical conventions.  Read this wiki article for a sampling.

Which conventions you choose may depend on what you need to communicate about your model, or it might just depend on what conventions make sense to you.

Don't get too wound up about all the different terminology.  It's all just your database design and how you choose to record and communicate it.


Now to normalize table when position in group uniquely identifies entity I need to model charts such as the one displayed below...

... and came up with the following:

For the sample chart I originally showed..

charts.name is "Custom transactions", charts.y_legend is "Amount", x_legend is "Customer Group"
series.name is "2007" for position 0 and "2006" for position 1, categories.name is "Foreign" for position 0, "Specialty Stores" for positions 1, etc.
chart_values.value will be the value for a given series and category such as $14,000,000 for series "2007" and category "Foreign".

As seen, the unique series can be identified solely on idSeries and position, (and similarly for categories, however, I will no longer be saying so), and thus series.idseries is redundant.  Does this violate the 1st, 2nd, 3rd, or Boyce and Codd Normal Form?

Regardless of normalization purity, I am also concerned (yet don't know why) with the implications of using composite key charts_idcharts/position as series primary key.  For my application, a backend configuration page will allow the user to add/remove categories and series, rename them, change the position order, etc.  To change a series name, the client can provide the chart ID and series position along with the new desired value.  To change position, the client could provide an array of the previous positions in a given order [2, 0, 1, 3].  Are there any negative implications of using position in a group in an entities primary key (other than needing to change the primary key upon changing position)?

Which happens more often?  Renaming/reordering versus fetching?

If you are fetching most of the time, then make that more efficient (PK order), while letting the other be more costly.

If you are renaming/reordering most of the time, then normalize to simplify that -- at the cost of ordering during fetching.

If the two tasks are the same (or even remotely similar in frequency), the do whichever is easier to code.


index mysql concatenated columns I have a table for author names with two fields, first_name & last_name, both varchar(55), both nullable (for crazy business logic reasons) although in reality last_name is unlikely to ever be null.

My where clause for the search screen contains:

WHERE CONCAT(a.first_name, " " , a.last_name) LIKE "%twain%"

so that "Twain" or "Mark Twain" can be searched on. The table has about 15,000 rows & is expected to gradually grow, but won't ever be more than double that, and not for years.

I understand that there are many other parts of my query that will affect this, but given just that information, how might I best index this?

If it would make a great difference in performance, making last_name not nullable is an option, but not first_name

TIA!

Two options I can think of. First, if you are on MyISAM or InnoDB 5.6+, you could store the concatenation in a separate field and use a FULLTEXT index on that field.

The other option is to index the first_name and last_name fields separately. Then change your query to:

WHERE a.first_name LIKE "twain%" OR a.last_name LIKE "twain%"

Removing the wildcard from the beginning will allow the indexes to be used.


How to find out which records are causing an exception in a select statement? I have a select query that I use as part of an insert statement,
but it raises the following exception:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Here is the definition of the table I want to insert to:

IF OBJECT_ID('[dbo].[tbl_crbHistory]') IS NOT NULL
DROP TABLE [dbo].[tbl_crbHistory]
GO
CREATE TABLE [dbo].[tbl_crbHistory] (
[Id]               INT              IDENTITY(1,1)   NOT NULL,
[ClientReference]  VARCHAR(50)                      NOT NULL,
[Status]           INT                              NOT NULL,
[DateAdded]        DATETIME                         NOT NULL
            CONSTRAINT [DF__tbl_crbHi__DateA__4F8DAFFA] DEFAULT (getdate()),
CONSTRAINT   [PK_tbl_crbHistory]  PRIMARY KEY CLUSTERED    ([Id] asc),
CONSTRAINT   [FK_tbl_crbHistory_ref_crbStatus]
            FOREIGN KEY ([Status]) REFERENCES [ref_crbStatus]([statusId]))

GO

CREATE NONCLUSTERED INDEX [idx_crbDateAdded]
   ON [dbo].[tbl_crbHistory] ([DateAdded] desc)
   INCLUDE ([Status], [ClientReference])
CREATE NONCLUSTERED INDEX [idx_clienteReferenceStatus]
   ON [dbo].[tbl_crbHistory] ([ClientReference] asc)
   INCLUDE ([Status], [DateAdded])
GO

EXEC sys.sp_addextendedproperty
          @name = N'MS_Description', @value = N'Stores history of CRB status change',
          @level0type = N'SCHEMA', @level0name = [dbo],
          @level1type = N'TABLE', @level1name = [tbl_crbHistory];
EXEC sys.sp_addextendedproperty
         @name = N'MS_Description', @value = N'Primary Key',
         @level0type = N'SCHEMA', @level0name = [dbo],
         @level1type = N'TABLE', @level1name = [tbl_crbHistory],
         @level2type = N'COLUMN', @level2name = [Id];
EXEC sys.sp_addextendedproperty
         @name = N'MS_Description',
         @value = N'unique id for each applicat per organisationId',
         @level0type = N'SCHEMA', @level0name = [dbo],
         @level1type = N'TABLE', @level1name = [tbl_crbHistory],
         @level2type = N'COLUMN', @level2name = [ClientReference];
EXEC sys.sp_addextendedproperty
         @name = N'MS_Description', @value = N'CRB Status',
         @level0type = N'SCHEMA', @level0name = [dbo],
         @level1type = N'TABLE', @level1name = [tbl_crbHistory],
         @level2type = N'COLUMN', @level2name = [Status];
EXEC sys.sp_addextendedproperty
         @name = N'MS_Description', @value = N'Time stamp',
         @level0type = N'SCHEMA', @level0name = [dbo],
         @level1type = N'TABLE', @level1name = [tbl_crbHistory],
         @level2type = N'COLUMN', @level2name = [DateAdded];

the complete statement, including the select and the insert:

INSERT INTO tbl_crbHistory (clientReference, status, dateAdded)
SELECT tc.pushedClientReference as ClientReference,
       tc.pushedStatusId as [Status],
       tc.createdOn as DateAdded
FROM tablebackups.dbo.tmp_colagbgdata tc
LEFT JOIN tbl_application_crb_initialData ci
ON tc.PushedClientReference = ci.ClientReference
WHERE ci.clientReference <> ''
AND tc.pushedStatusId  IN ( SELECT STATUSID FROM dbo.ref_crbStatus)

The column tc.createdOn is a datetime and it is breaking my code due to the error

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Is there any way(s) I can select the record(s) that are causing this?

If I could find these records in this or similar situations, I could deal with them and re-run my query at a later time.

Try this query:

SELECT tc.pushedClientReference as ClientReference, tc.pushedStatusId as [Status],
tc.createdOn
FROM tablebackups.dbo.tmp_colagbgdata tc
where TRY_CAST(tc.createdOn as datetime) is null
and tc.createdOn is not null

The TRY_CAST function returns null when a CAST operation is impossible, so the above query will return the rows where converting the createdOn column to datetime is causing an error.

If you have a particular preference there's also TRY_CONVERT.

Now that we know the SELECT query is feeding an INSERT, I'd suggest that tc.createdon in the SELECT list be explicitly converted using TRY_CAST or TRY_CONVERT.


Calculate Inventory Costing Based on First in, First Out (FIFO) I am trying to get the cost of inventory based on FIFO.

I saw a similar question but it does not look for cost of inventory and tables seem to be structured differently.

Using MySQL I have a table purchase_order_items, purchase_order_receipt_items and item_inventories the purchase_order_receipt_items table gets updated with every item that is received and for purchasing control, what was ordered and what was received.

The item_inventories table gets updated with a new line item for every inventory movement in or out and we calculate the current inventory using a simple SUM of all in and out quantity.

What I am missing is the cost of the current inventory. The cost of the same item varies and we don't have a cost added in the item_inventories table.

Table purchase_order_items:

| PO    | Date         | Quantity | Item | Cost  |
|-------|--------------|----------|------|-------|
| PO001 | 01-Jan-2019  | 1        | AO21 | 12.50 |
| PO002 | 02-Jan-2019  | 3        | AO21 | 10.99 |
| PO003 | 09-Jan-2019  | 2        | AO21 | 12.00 |

Table item_inventories

| ID    | Date         | adjustment_qty | Item |
|-------|--------------|----------------|------|
| 1O001 | 01-Jan-2019  | 1              | AO21 |
| 1O002 | 02-Jan-2019  | 3              | AO21 |
| 1O003 | 04-Jan-2019  | -1             | AO21 |
| 1O004 | 05-Jan-2019  | -1             | AO21 |
| 1O005 | 09-Jan-2019  | 2              | AO21 |

I currently calculate the item_inventories and get a total of 4 SELECT SUM(adjustment_qty) FROM item_inventories WHERE item='AO21' but that does not give me the cost of the inventory.

How do I join the purchase_order_items table to get the cost and qty in a SUM query of the item_inventories table?

I want to apologize up front.  I don't work with MySQL but maybe this SQL Server answer will help give you some guidance.

I think what you really want is a Remaining_Inventory column added onto each row of purchase_order_items (or a separate table).  Then each transaction can update the appropriate row in your FIFO methodology.  Then your inventory cost report is simply something like:

SELECT Item,
SUM(Cost_Of_Remaining_Inventory) AS Sum_Of_Inventory
FROM
(
    SELECT Item AS 'Item',
    (Cost * Remaining_Inventory) AS 'Cost_Of_Remaining_Inventory'
    FROM Purchase_Order_Items_Tracker
) Window_View
GROUP BY Item

You would then need to fill this table out with your initial data, and I think I have a CURSOR which will do what you need.  Again written for SQL Server.

First I recreated your tables with Table Variables just so i could test everything out:

DECLARE @Purchase_Order_Items TABLE
(
    PO VarChar(5),
    [Date] DATETIME,
    Quantity INT,
    Item VarChar(4),
    Cost DECIMAL(10,2)
)

DECLARE @Item_Inventories TABLE
(
    ID VarChar(5),
    [Date] DATETIME,
    Adjustment_Qty INT,
    Item VarChar(4)
)

INSERT INTO @Purchase_Order_Items (PO, [Date], Quantity, Item, Cost)
VALUES('PO001', '2019-01-01', 1, 'AO21', 12.50),
('PO002', '2019-01-02', 3, 'AO21', 10.99),
('PO003', '2019-01-09', 2, 'AO21', 12.00)

INSERT INTO @Item_Inventories (ID, [Date], Adjustment_Qty, Item)
VALUES ('1O001', '2019-01-01', 1, 'AO21'),
('1O002', '2019-01-02', 3, 'AO21'),
('1O003', '2019-01-04', -1, 'AO21'),
('1O004', '2019-01-05', -1, 'AO21'),
('1O005', '2019-01-09', 2, 'AO21')

Then I created a "Tracker" table for keeping track of the remaining inventory for a given PO as we work through the cursor.

DECLARE @Purchase_Order_Items_Tracker TABLE
(
    PO VarChar(5),
    [Date] DATETIME,
    Quantity INT,
    Item VarChar(4),
    Cost DECIMAL(10,2),
    Inventory_Remaining INT
)

Then running the below code to populate said table and then query off it should give you what you are looking for

DECLARE @Date DATETIME,
        @Adjustment_Qty INT,
        @Item VarChar(4),
        @Adjustment_Qty_Transaction INT,
        @Item_Inventories_PO_Transaction VarChar(5)

DECLARE Inventory_Cursor CURSOR FOR
--getting the absolute value of Adjustment_Qty so that we can only work with positive number
---The WHERE condition makes sure we are only working with records where the inventory was subtracted from, not added to
SELECT [Date], ABS(Adjustment_Qty) AS Adjustment_Qty, Item
FROM @Item_Inventories
WHERE Adjustment_Qty < 0 --That way we are only looking at removing of inventory
                         --Not logs where inventory was added

OPEN Inventory_Cursor
FETCH NEXT FROM Inventory_Cursor INTO @Date, @Adjustment_Qty, @Item

WHILE(@@FETCH_STATUS = 0)
BEGIN
    --Load in any new inventory
    INSERT INTO @Purchase_Order_Items_Tracker (PO, [Date], Quantity, Item, Cost, Inventory_Remaining)
    SELECT
    I.PO,
    I.[Date],
    I.Quantity,
    I.Item,
    I.Cost,
    I.Quantity
    FROM @Purchase_Order_Items I
        LEFT OUTER JOIN @Purchase_Order_Items_Tracker T
            ON I.PO = T.PO
    WHERE I.Item = @Item --Transaction is for our item in scope
    AND I.[Date] <= @Date --Purchase order recieved at or before this inventory change
    AND T.PO IS NULL --Record not already inserted into the tracker

    --Then Subtract from inventory
    WHILE(@Adjustment_Qty > 0)
    BEGIN
        SET @Adjustment_Qty_Transaction = NULL
        SET @Item_Inventories_PO_Transaction = NULL

        --You will need some kind of error handling for if there are no records found
        --which means at the time of trying to take out inventory
        --our records show that there was no inventory for that item
        SELECT TOP(1)
            @Adjustment_Qty_Transaction = Inventory_Remaining,
            @Item_Inventories_PO_Transaction = PO
        FROM @Purchase_Order_Items_Tracker
        WHERE Item = @Item
        AND Inventory_Remaining > 0
        AND [Date] <= @Date
        ORDER BY [Date] DESC

        IF(@Adjustment_Qty <= @Adjustment_Qty_Transaction)
        BEGIN
            SET @Adjustment_Qty_Transaction = @Adjustment_Qty_Transaction - @Adjustment_Qty
            SET @Adjustment_Qty = 0
        END
        ELSE
        BEGIN
            SET @Adjustment_Qty = @Adjustment_Qty - @Adjustment_Qty_Transaction
            SET @Adjustment_Qty_Transaction = 0
        END

        UPDATE @Purchase_Order_Items_Tracker
        SET Inventory_Remaining = @Adjustment_Qty_Transaction
        WHERE @Item_Inventories_PO_Transaction = PO
    END

    FETCH NEXT FROM Inventory_Cursor INTO @Date, @Adjustment_Qty, @Item
END
CLOSE Inventory_Cursor
DEALLOCATE Inventory_Cursor

--Then make sure that we have all other inventory additions added
--In case we got new inventory but have not had inventory taken out since it was added

INSERT INTO @Purchase_Order_Items_Tracker (PO, [Date], Quantity, Item, Cost, Inventory_Remaining)
    SELECT
    I.PO,
    I.[Date],
    I.Quantity,
    I.Item,
    I.Cost,
    I.Quantity
    FROM @Purchase_Order_Items I
        LEFT OUTER JOIN @Purchase_Order_Items_Tracker T
            ON I.PO = T.PO
    WHERE T.PO IS NULL --Record not already inserted into the tracker

SELECT *
    FROM @Purchase_Order_Items_Tracker

SELECT Item AS 'Item',
    [Date],
    (Cost * Inventory_Remaining) AS 'Cost_Of_Remaining_Inventory'
    FROM @Purchase_Order_Items_Tracker

SELECT Item,
SUM(Cost_Of_Remaining_Inventory) AS Sum_Of_Inventory
FROM
(
    SELECT Item AS 'Item',
    (Cost * Inventory_Remaining) AS 'Cost_Of_Remaining_Inventory'
    FROM @Purchase_Order_Items_Tracker
) Window_View
GROUP BY Item

The 3 final SELECT queries return the below 3 Tables:

+-------+-------------------------+----------+------+-------+---------------------+
|  PO   |          Date           | Quantity | Item | Cost  | Inventory_Remaining |
+-------+-------------------------+----------+------+-------+---------------------+
| PO001 | 2019-01-01 00:00:00.000 |        1 | AO21 | 12.50 |                   0 |
| PO002 | 2019-01-02 00:00:00.000 |        3 | AO21 | 10.99 |                   2 |
| PO003 | 2019-01-09 00:00:00.000 |        2 | AO21 | 12.00 |                   2 |
+-------+-------------------------+----------+------+-------+---------------------+

+------+-------------------------+-----------------------------+
| Item |          Date           | Cost_Of_Remaining_Inventory |
+------+-------------------------+-----------------------------+
| AO21 | 2019-01-01 00:00:00.000 |                        0.00 |
| AO21 | 2019-01-02 00:00:00.000 |                       21.98 |
| AO21 | 2019-01-09 00:00:00.000 |                       24.00 |
+------+-------------------------+-----------------------------+

+------+------------------+
| Item | Sum_Of_Inventory |
+------+------------------+
| AO21 |            45.98 |
+------+------------------+

Again all of this is based around SQL Server but it should at least give you a framework to build off of.  And I think in the real implementation you want a Remaining_Inventory Column (or a separate table) which keeps track of what is left on that PO


Can not insert accents in MySQL REPL I have been trying to run a MySQL database in a Docker container using the official image. I can run everything OK but I have a problem with inserting accents.

The thing is that I can not insert accents on the MySQL REPL (or CLI or shell or... I am not sure how to call it; I mean where you see the mysql> prompt). Nonetheless I can exit to the main Linux shell (prompt #) and insert accents normally.

I know that UTF8 is not reliable in MySQL so I tried to set utf8mb4. The character settings I do is:

mysql> set names utf8mb4;
mysql> set character_set_database=utf8mb4;
mysql> set character_set_server=utf8mb4;
mysql> set character_set_system=utf8mb4;

You can see what I have:

mysql> show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

Thanks.

I finally found a solution. The problem might be that I was setting utf8mb4 just for a database, not for the mysqld daemon.

Anyway I put here what I made. I first created a Dockerfile:

FROM mysql:latest

RUN apt-get update && apt-get install -y locales && rm -rf /var/lib/apt/lists/* \
  && localedef -i en_US -c -f UTF-8 -A /usr/share/locale/locale.alias en_US.UTF-8
ENV LANG en_US.utf8

COPY conf.d/ /etc/mysql/conf.d/

ENV MYSQL_ROOT_PASSWORD pass
ENV MYSQL_DATABASE gastos

As you can see, I copy a conf.d directory I just made. Inside this directory there is a character_set.cnf file:

[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci


How does a Page in SQL Server distinguishes between the data of different columns of a table? I know that row offset array at the end of page stores information about the starting index of any row. Does it also store information about column data? And where does it store? If not, how does it identify the different column data?

Column meta-data are stored in system catalog tables. These are held separately from the physical record.  The meta-data can be queried using the sys.columns catalog view. Records within a data page are a structure that includes a null bitmap, fixed-length fields, variable-length fields, and a column offset array entry for each variable length field present in the record. See Paul Randal's Anatomy of a Record for specifics of the internal data record structure. The physical record structure is significantly different when PAGE or ROW compression is used, for BLOBs (varchar(max) et al), and when a clustered columnstore index exists.

SQL Server uses the schema defined by the column meta-data to determine how to interpret the physical record structure and return the requested column values from the fields in the record. The physical record may or may not contain fields for each column, depending on whether the data type is fixed-length or variable, a NULL value, or columns added after initial table creation.

For example, if one adds a varchar NULL column to an existing table, that is a meta-data only operation that doesn't modify existing physical records. Similarly, adding a NOT NULL column with a default constraint is a meta-data only operation in Enterprise Edition because SQL Server uses meta-data to return the default value for existing rows even though the value is not physically present in records until later modified.


Vacuum vs Auto Vacuum I am trying to find answers for my below few queries which will help me in fine tuning my postgres DB. I did some googling but was not able to find answer.

1) Out of vacuum or Auto vacuum which is more resource intensive operation?
2) Is there a way to run Auto vacuum only when there is unusually heavy update activity?
3) How can I find out based on setting when my auto vacuum will start next time, based on threshold.

1) If you don't count your own time as a resource, then you should always be able to hand-craft a vacuum schedule which uses fewer total resources than autovacuum does.  If you do count your own time, this is almost surely not worthwhile.

2) Other than manually or algorithmically turning it on or off, no.  Nor would it make sense to do that.  A database that sees only a very steady predictable load with no unusual spikes still needs vacuuming.  However, if you do spend a lot of time coming up with a highly optimized hand-crafted vacuum schedule, then autovacuum will never have any vacuuming to do except under unusual activity not anticipated in your custom schedule; so in this sense what you want is automatically the case.

3) It isn't clear whether you mean when an autovacuum worker will start up in a given database,  or when any given table will start to be vacuumed.  The first is determined by autovacuum_naptime, unless autovacuum_max_workers becomes the limiting factor.  The second is determined by when the table crosses the threshold, with a granularity of autovacuum_naptime, except when autovacuum_max_workers becomes the limiting factor.  How accurately you can predict this depends on how steady the activity on that table is, and whether the vacuum tasks are generally overloaded or are mostly idle.


How can I determine why a concurrent Postgres index was marked as INVALID? I'm trying to create an index concurrently.
CREATE INDEX CONCURRENTLY blocks_expr_idx6 ON blocks USING btree ('blah') WHERE "type" = 'user root';

12 hours after I ran the query, if I run \d blocks Postgres reports that the index is INVALID:
"blocks_expr_idx6" btree ('blah') WHERE "type" = 'user root' INVALID

The documentation says:

If a problem arises while scanning the table, such as a deadlock or a uniqueness violation in a unique index, the CREATE INDEX command will fail but leave behind an "invalid" index. This index will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead. The psql \d command will report such an index as INVALID.
The recommended recovery method in such cases is to drop the index and try again to perform CREATE INDEX CONCURRENTLY. (Another possibility is to rebuild the index with REINDEX. However, since REINDEX does not support concurrent builds, this option is unlikely to seem attractive.)

How can I figure out why Postgres marked the index as INVALID?

One simple answer to this is to drop it, run it again, keep the terminal window around (or otherwise log the output) and see what error happens.


Get another function to return timestamp in PostgreSQL Can I have another function to return the last timestamp.
I want to compare with this query to use the best query.
My query:
CREATE OR REPLACE FUNCTION f_last_update1(nomTable text , OUT updated timestamp)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE 'SELECT max(updated) FROM ' || nomTable
   INTO updated;
END
$func$;

Your function is insecure and vulnerable to SQL injection:
SELECT f_last_update1('sometable; DROP TABLE sometable; SELECT current_timestamp');

Use
CREATE OR REPLACE FUNCTION f_last_update1(
   IN  nomSchema text,
   IN  nomTable text,
   OUT updated timestamp)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format(
              'SELECT max(updated) FROM %I.%I',
              nomSchema,
              nomTable
           )
      INTO updated;
END
$func$;


Reclaim space from dropped column when there isn't enough space for index rebuild I dropped a char(10) column from a table that has about 111 million rows (30gb data, 11gb index)

I then ran an index rebuild on the clustered index.  This ate up all the space on the drive containing the database.

I then ran dbcc cleantable and got all the space back, plus a few more gigs.

Is there a way to reclaim space from the dropped column when there isn't enough space for index rebuild?

Or did the cleantable operation actually reclaim the space?  (I know it reclaimed space, but I'm guessing it's possible it reclaimed space for a different reason, since cleantable isn't suggested as the way to reclaim space after dropping a column)

DBCC CLEANTABLE can only be used to reclaim space left from dropping a variable length column.  Thus it cannot be considered a "good" replacement for rebuilding the table/index.

Since you state that the column you dropped was a fixed length column, I can only surmise there must be some other reason for unused, but allocated space being present in the table; perhaps ghost records or perhaps you actually dropped some other columns as well that actually were variable length.


Using a Different Windows Account for Invoke-Sqlcmd I'm attempting to make a Powershell script that needs to interact with SQL Server, so part of the script will involve invoke-sqlcmd. However, the script can't be scheduled as a job in SQL Server and thus make use of the Agent to act as the Windows account used for authentication. Instead, it is going to be run using a different service under an arbitrary Windows account. The documentation for invoke-sqlcmd states that the -Username and -Password parameters are for SQL Server authentication, not Windows, and that it defaults to using the Windows account that is running the script for authentication if no SQL Server parameters are provided.

Is there any way to allow invoke-sqlcmd to use an arbitrary Windows account for authentication instead of the one it's being run under? I am aware that a possible solution would be to make accounts on the SQL Server instances that I want to run the script for and just use the SQL Server authentication option on the command, but I would ideally not want to have to do that. Some other research has also shown that a certificate could be an option to pursue, but I don't think that invoke-sqlcmd can take in certificates or otherwise utilize them like suggested. I could be wrong here though.

You could grant the account that is going to run the job the necessary permissions onto the SQL Server to run the query.  That would be the easiest.  If you are going to use Task Scheduler, it even gives a method for specifying the account to use.

The next best would be to use SQL Authentication.

Finally, you can use PowerShell to invoke a new thread to run under a different windows account, that thread then to use Invoke-sql.  However, the password then needs to be stored somewhere somehow (usually encrypted in a file).  This is not the best solution and not one that I would recommend using.


SQL Server Debugger Permissions without Granting sysadmin to Devs BOL documentation says sysadmin permission is required to run debugger.

I'm 90% certain there is no workaround to this requirement, but thought I would ask just in case someone found a way to grant Debugger permission without granting sysadmin permission.

What do people do when you have a team of developers needing to step through a complicated cursor loop with variables, etc to debug some aspect of that?

Most shops don't allow developers to have sysadmin permission even on development servers, and many wouldn't allow devs to keep a copy of enterprise data on their local machine with their own developer sql server edition e.g. due to PII and data security reasons.

Not sure why the debugger would be set up this way.

So, I'm curious how other people handle the requests for Debugger permission in a similar scenario.

What do you do in your environment?

You could add a declare @idebug int variable to your stored procedures and then code around the important bits when you require relevant information.

Your stored procedure would then look a bit like this:

CREATE PROCEDURE [dbo].[uspDoSomething]
...
@iiDebug int = 0
...
AS
...
BEGIN
    /* debugging configuration */
    declare @debug int

    /* debug settings
    1 = turn on debug information
    2 = turn on all possible outputs
    4 = turn on transaction handling

    e.g.: Adding an @iDebug paramter of 6 will turn on transaction handling
    and turn on all possible output information

    e.g.: Adding an @iDebug value of 1 will turn on debugging information
    */

    set @debug = @iiDebug
    ....
    if @debug & 1 = 1  print 'Checking variables...'

                    /* If general output has been turned on print output*/
                    if @debug & 2 = 2
                        BEGIN
                            PRINT 'Debug comment here' + convert(varchar(100), @iRetVal) + 'Debug comment here' + convert(varchar(20),getdate())
                        end

                    close <cursor_name>
                    deallocate <cursor_name>

                    RETURN(@iRetVal)
    ...
    END
    ...
END

This is just an example of how it can be done.

You would then call the sproc with:

execute uspDoSomething @iiDebug = 3

...which would the provide basic (bitwise 1) and detailed (bitwise 2) information, depending on where you inserted the relevant code.

I had issues once while running a stored procedure that wasn't producing the right results and I had to debug the individual statements, so I just entered the various debugging levels in the stored procedure and when required ran the sproc with the relevant @iiDebug values depending on the level of information I required.

Examples of input values:

@iiDebug = 1 -- > Basic "where am I in the sproc" information
@iiDebug = 2 -- > Print of @nvSQL values
@iiDebug = 4 -- > Run individual execution of statements in BEGIN and COMMIT transactions

Examples as code (input variable @iiDebug is stored in @debug in the sproc code):

set @debug = @iiDebug
...
...
if @debug & 4 = 4
BEGIN
    begin tran mojo
END
if @debug & 2 = 2 then print @nvSQL

exec @iRetVal = sp_executesql @nvSQL
if @iRetVal <> 0
BEGIN
    /* If transactions have been turned on then rollback if failed */
    if @debug & 4 = 4
    BEGIN
        rollback tran mojo
    END

/* If transactions have been turned on then commit on success */
if @debug & 4 = 4
BEGIN
    commit tran mojo
END

These are just quick examples of how you can introduce debugging without having access to the SQL Server Debugger or the required privileges.

  Caution:
  It can be a bit of a performance hog and is better removed from production.


Fastest way to copy data from MyISAM to InnoDB I want to copy all data from a MyISAM table with 16 millions rows to a InnoDB table, that will have a different (optimized) schema. See tables below to see the changes.

However, every approach I try leads to a slow result.

#1 INSERT

Using a simple INSERT with VALUES from a SELECT:

INSERT INTO new_table (field1,field2,...) SELECT * FROM old_table;

Really slow and it locks the original table, which I can't have locked for much longer.

#2 IMPORT FROM SQL FILE

Creating the SQL file takes minutes, but ok. Importing the file is really slow.

#3 LOAD DATA

I dump all the data to a comma separate file, which takes about 30 seconds (quite good), and then I load it with LOAD DATA INFILE. But still very slow.

Why not drop unique keys before loading the data?

Yes, it loads faster. However, when I try to add the unique keys I get this:

  Lock wait timeout exceeded; try restarting transaction

Let's split the into several files?

I tried that. Each file contained 100,000 rows.

File 1 = 2.76 sec
File 2 = 1 min 40 sec
File 3 = 4 min 16 sec

I gave up on file 3. I'd have to do it 157 more times and on each iteration the processing time would increase. Too much time...

What is 'slow'

When I say it is slow, I mean, with unique keys off, it takes about 10 seconds to load 500,000 rows. No bad. But then I can't add unique keys because of the timeout.

With unique keys previously set, it takes almost 1 minute to insert 100,000 rows. And the throughput decreases the more rows is inserted.

The tables

MyISAM - Original table

+----------------------+-----------------------+------+-----+---------+-------+
| Field                | Type                  | Null | Key | Default | Extra |
+----------------------+-----------------------+------+-----+---------+-------+
| player_id            | int(11)               | NO   | PRI | NULL    |       |
| date                 | date                  | NO   | PRI | NULL    |       |
| time                 | int(2)                | NO   | PRI | NULL    |       |
| minutes_online       | decimal(5,0)          | NO   |     | 0       |       |
| minutes_playing      | decimal(5,0)          | NO   |     | 0       |       |
| minutes_chatting     | decimal(5,0)          | NO   |     | 0       |       |
| minutes_away         | decimal(5,0)          | NO   |     | 0       |       |
+----------------------+-----------------------+------+-----+---------+-------+

InnoDB - New table

+----------------------+-----------------------+------+-----+---------+-------+
| Field                | Type                  | Null | Key | Default | Extra |
+----------------------+-----------------------+------+-----+---------+-------+
| player_id            | mediumint(8) unsigned | NO   | PRI | NULL    |       |
| date                 | date                  | NO   | PRI | NULL    |       |
| time                 | tinyint(2)            | NO   | PRI | NULL    |       |
| minutes_online       | tinyint(2)            | NO   |     | 0       |       |
| minutes_playing      | tinyint(2)            | NO   |     | 0       |       |
| minutes_chatting     | tinyint(2)            | NO   |     | 0       |       |
| minutes_away         | tinyint(2)            | NO   |     | 0       |       |
+----------------------+-----------------------+------+-----+---------+-------+

Am I stuck on this? Meaning, will I have to accept the fact that it is slow and be patient? Or is there any way or tweak to load the data with an acceptable performance?

Solution
I split into small CSV files, applied the tweaks that Shlomi Noach suggested and then loaded each file.

Not the perfect. But it loaded a bit faster. Dumping the original table to a CSV file took less than 1 minute, which avoided the table from being locked during the whole process. A script did the job to load each file, which would be very boring to do manually.

In the end I just had to get from the original table the rows inserted/updated in the last hours and update on the new table.

There are a few solutions. First, however, I'm not sure about the process in the first place. Is this a one time copy operation? A recurring copy operation? Do you want to migrate from MyISAM to InnoDB?

What is the main reason for your desire for a quick operation?

If you're looking for migration, then why don't you use an online table alter tool, such as oak-online-alter-table (disclaimer: I'm author of this tool) or pt-online-schema-change? Both will allow you to change your schema live and online with very little disturbance.

If you're looking to a copy+paste of your data, then I would suggest using chunking: copying the data in small packets. This way you don't get that huge lock and no funny timeouts. You can use either oak-chunk-update or pt-archiver for this. This may actually make the total runtime shorter because of reduces locking, but may also take longer. Also consider that it is not an atomic operation, and changes to original table while copying is made, may not get caught, so you may get an inconsistent copy.

Otherwise (or in addition) you can use all the usual tweaks, such as

SET GLOBAL innodb_flush_log_at_trx_commit := 2;

or set

[mysqld]
innodb_doublewrite = 0

or perhaps, depending on OS and disks,

[mysqld]
innodb_flush_method = O_DIRECT

Each of the above may reduce disk I/O access. First two will also make your server less crash safe. But if for limited time, this may be OK for you.


Difference in efficiency between NVL and IS NULL I have the below query (showing the last part of the query). In the first example I get a result (which is not exactly as desired) but the results arrive within 10min. The second version results in an error: snapshot too old: rollback segment number.

Basically I'm joining in a second table with one key, and add a second key if available, and a third key if available. If key #2 and #3 are null then the join should only happen on the first key.

My research has shown that the snapshot too old error message originates from the fact that the query takes too much resources. That's why I would like to understand in how far NVL is more efficient than IS NULL and what I can do to adjust my query.

quick but result is not as desired:

..…….
   left outer join tlea
          on (    dset_cob_dt = tlea_cob_dt
              and meas_data.tlsn_trd_id = tlea.tlsn_trd_id
              and nvl (meas_data.tlsn_vrsn_num, -1) =
                     nvl (tlea.tlsn_vrsn_num, -1)
              and meas_data.tlsn_leg_id = tlea.tlsn_leg_id);

works correctly but getting errors:

    ………
             LEFT OUTER JOIN tlea
       ON (dset_cob_dt = tlea_cob_dt
        AND  meas_data.tlsn_trd_id = tlea.tlsn_trd_id
        AND (meas_data.tlsn_leg_id = tlea.tlsn_leg_id OR tlea.tlsn_leg_id IS NULL)
        AND (meas_data.tlsn_vrsn_num = tlea.tlsn_vrsn_num OR tlea.tlsn_vrsn_num IS NULL)
         );

ORA-12801: error signaled in parallel query server P034
ORA-01555: snapshot too old: rollback segment number 240 with name "_SYSSMU240_2257379025$" too small

How can I make the second quefry more efficient so I can avoid the error? Is there a way to achieve the same with NVL?

UPDATE:
The query should always join on tlsn_trd_id and if available (not null) additionally on tlsn_leg_id and if available (not null) additional on tlsn_vrsn_num as well

To me, the logic is that the NVL value gives a specific answer to the query handler. Whereas, the IS NULL value causes an ambiguous join, thus resulting in a Cartesian product.

That is, every row on the left is trying to join with every single null on the right in the IS NULL. Conversely, in the NVL only those rows which are null on both sides join.


Why does my execution plan show a Clustered Index Scan is occurring when using an index hint that specifically uses a Nonclustered Index? The Backstory:
I have a table with the following structure:
CREATE TABLE WideTable1
(
    BoringColumn1,
    BoringColumn2,
    CellPhoneNumberColumn Phone(VARCHAR(20)), -- User-defined types, see below
    PagerPhoneNumberColumn Phone(VARCHAR(20)), -- User-defined types, see below
    IsActive YN(CHAR(1)), -- User-defined types, see below
    BoringColumn3,
    ...
    BoringColumn350
)

The original query consuming this table was doing a few SQL functions on the CellPhoneNumberColumn and PagerPhoneNumber and then later using the output of those functions as predicates in another query. NULLIF(LTRIM(ISNULL(CellPhoneNumberColumn, PagerPhoneNumberColumn))) was one example of this.
The columns are also all user-defined types, in this case CellPhoneNumberColumn and PagerPhoneNumberColumn are defined as UDT Phone(VARCHAR(20)), so their underlying data type is VARCHAR(20). And IsActive is defined as YN(CHAR(1)) so it's actually a CHAR(1).
Additionally to complicate things further, the collation of this table / database is Latin1_General_BIN.
Long story short, the original consuming query was running into Cardinality Estimate issues.
In an attempt to alleviate the issue, I created an index view on the above columns and SQL functions being applied to them with the following definition (*note I didn't  create this original logical, just trying to fix the performance of it):
CREATE VIEW PhoneNumbersNormalized WITH SCHEMABINDING AS

SELECT
    NULLIF(LTRIM(ISNULL(CAST(CellPhoneNumberColumn AS VARCHAR(20)), CAST(PagerPhoneNumberColumn AS VARCHAR(20)))) AS Cell,
    SUM(CASE WHEN CAST(IsActive AS CHAR(1)) = 'Y'THEN 1 ELSE 0 END) AS IsActive
FROM dbo.WideTable1
GROUP BY NULLIF(LTRIM(ISNULL(CAST(CellPhoneNumberColumn AS VARCHAR(20)), CAST(PagerPhoneNumberColumn AS VARCHAR(20))))

I also created the following indexes on the indexed view PhoneNumbersNormalized:
CREATE UNIQUE CLUSTERED INDEX IXV_PhoneNumbersNormalized_Cell ON dbo.PhoneNumbersNormalized(Cell)
CREATE NONCLUSTERED INDEX IXV_NC_PhoneNumbersNormalized_Cell_IsActive ON  dbo.PhoneNumbersNormalized(Cell, IsActive)

The Problem:
When I select from the indexed view PhoneNumbersNormalized with the Actual Execution Plan included, I noticed the execution plan specifcally mentions the original underlying table WideTable1 as where the data is coming from.
Furthermore if I select from indexed view PhoneNumbersNormalized with an indexed hint on the nonclustered index I created above IXV_NC_PhoneNumbersNormalized_PhoneNumber_IsActive the execution plan shows no mention of this nonclustered index being used, instead it says a clustered index scan is what it's doing instead (note I obfuscated the original table name, it's not actually called WideTable1 on my server):

Pasted Execution Plan: https://www.brentozar.com/pastetheplan/?id=HJytxh2UP
Why is the execution plan always showing the original underlying table instead of the indexed view, and always using the clustered index on the underlying table even when my select query on the indexed view uses an index hint to force the use of the non-clustered index?

Why is the execution plan always showing the original underlying table instead of the indexed view, and always using the clustered index on the underlying table even when my select query on the indexed view uses an index hint to force the use of the non-clustered index?

Your view definition is being expanded. You'll need the NOEXPAND hint.

Another Reason to Use NOEXPAND hints in Enterprise Edition.
Indexed Views and Statistics

Can I use the NOEXPAND hint on an non-indexed view?

From the docs: The query optimizer treats the view like a table with clustered index. NOEXPAND applies only to indexed views.

Does schemabinding a view materialize it?

Being schema bound is an object property that can apply to things other than views, like functions. You can't index a view that isn't schema bound, though.
For an example with functions, check out this Q&A:

Is there any benefit to SCHEMABINDING a function beyond Halloween Protection?


Informix trigger to delete records before insert I am trying to create a trigger to simply delete records from systables_growth table before an insert happens based on the where clause used on the date_added column for records older than a certain number of days (in this example, 15 days):

I am using the below:

CREATE TRIGGER systables_growth_clnup
INSERT ON systables_growth
DELETE FROM systables_growth
WHERE date_added < (CURRENT - 15 UNITS DAY)

I am getting the below syntax error when performing the above.

CREATE TRIGGER systables_growth_clnup
INSERT ON systables_growth
DELETE FROM systables_growth
#^
#  201: A syntax error has occurred.
#
WHERE date_added < (CURRENT - 15 UNITS DAY)

If you follow the syntax diagram(s) for CREATE TRIGGER, you find that the action clause needs either BEFORE, FOR EACH ROW or AFTER as a prefix before the action verbiage, and the triggered action segment shows you need parentheses around the triggered action(s).  Hence:

CREATE TRIGGER systables_growth_clnup
    INSERT ON systables_growth
    BEFORE (DELETE FROM systables_growth
            WHERE date_added < (CURRENT - 15 UNITS DAY)
           );