SQL Server splits A  B into A  B, yielding strange results if B is non-deterministic We have encountered an interesting issue with SQL Server. Consider the following repro example:

CREATE TABLE #test (s_guid uniqueidentifier PRIMARY KEY);
INSERT INTO #test (s_guid) VALUES ('7E28EFF8-A80A-45E4-BFE0-C13989D69618');

SELECT s_guid FROM #test
WHERE s_guid = '7E28EFF8-A80A-45E4-BFE0-C13989D69618'
  AND s_guid <> NEWID();

DROP TABLE #test;

fiddle

Please forget for a moment that the s_guid <> NEWID() condition seems entirely useless - this is just a minimal repro example. Since the probability of NEWID() matching some given constant value is extremely small, it should evaluate to TRUE every time.

But it doesn't. Running this query usually returns 1 row, but sometimes (quite frequently, more than 1 time out of 10) returns 0 rows. I have reproduced it with SQL Server 2008 on my system, and you can reproduce it on-line with the fiddle linked above (SQL Server 2014).

Looking at the execution plan reveals that the query analyzer apparently splits the condition into s_guid < NEWID() OR s_guid > NEWID():

...which completely explains why it fails sometimes (if the first generated ID is smaller and the second one larger than the given ID).

Is SQL Server allowed to evaluate A <> B as A < B OR A > B, even if one of the expressions is non-deterministic? If yes, where is it documented? Or did we find a bug?

Interestingly, AND NOT (s_guid = NEWID()) yields the same execution plan (and the same random result).

We found this issue when a developer wanted to optionally exclude a particular row and used:

s_guid <> ISNULL(@someParameter, NEWID())

as a "shortcut" for:

(@someParameter IS NULL OR s_guid <> @someParameter)

I am looking for documentation and/or confirmation of a bug. The code is not all that relevant so workarounds are not required.

Is SQL Server allowed to evaluate A <> B as A < B OR A > B, even if one of the expressions is non-deterministic?

This is a somewhat controversial point, and the answer is a qualified "yes".

The best discussion I am aware of was given in answer to Itzik Ben-Gan's Connect bug report Bug with NEWID and Table Expressions, which was closed as won't fix. Connect has since been retired, so the link there is to a web archive. Sadly, a lot of useful material was lost (or made harder to find) by the demise of Connect. Anyway, the most useful quotes from Jim Hogg of Microsoft there are:

  This hits to the very heart of the issue - is optimization allowed to change a program's semantics? Ie: if a program yields certain answers, but runs slowly, is it legitimate for a Query Optimizer make that program run faster, yet also change the results given?

  Before shouting "NO!" (my own personal inclination too :-), consider: the good news is that, in 99% of cases, the answers ARE the same. So Query Optimization is a clear win. The bad news is that, if the query contains side-effecting code, then different plans CAN indeed yield different results. And NEWID() is one such side-effecting (non-deterministic) 'function' that exposes the difference. [Actually, if you experiment, you can devise others - for example, short-circuit evaluation of AND clauses: make the second clause throw an arithmetic divide-by-zero - different optimizations may execute that second clause BEFORE the first clause] This reflects Craig's explanation, elsewhere in this thread, that SqlServer does not guarantee when scalar operators are executed.

  So, we have a choice: if we want to guarantee a certain behavior in the presence of non-deterministic (side-effecting) code - so that results of JOINs, for example, follow the semantics of a nested-loop execution - then we can use appropriate OPTIONs to force that behavior - as UC points out. But the resulting code will run slow - that's the cost of, in effect, hobbling the Query Optimizer.

  All that said, we are moving the Query Optimizer in the direction of "as expected" behavior for NEWID() - trading off performance for "results as expected".

One example of the changing of behaviour in this regard over time is NULLIF works incorrectly with non-deterministic functions such as RAND(). There are also other similar cases using e.g. COALESCE with a subquery that can produce unexpected results, and which are also being addressed gradually.

Jim continues:

  Closing the loop . . . I've discussed this question with the Dev team. And eventually we have decided not to change current behavior, for the following reasons:

  1) The optimizer does not guarantee timing or number of executions of scalar functions. This is a long-estabilished tenet. It's the fundamental 'leeway' tha allows the optimizer enough freedom to gain significant improvements in query-plan execution.

  2) This "once-per-row behavior" is not a new issue, although it's not widely discussed. We started to tweak its behavior back in the Yukon release. But it's quite hard to pin down precisely, in all cases, exactly what it means! For example, does it a apply to interim rows calculated 'on the way' to the final result? - in which case it clearly depends on the plan chosen. Or does it apply only to the rows that will eventually appear in the completed result? - there's a nasty recursion going on here, as I'm sure you'll agree!

  3) As I mentioned earlier, we default to "optimize performance" - which is good for 99% of cases. The 1% of cases where it might change results are fairly easy to spot - side-effecting 'functions' such as NEWID - and easy to 'fix' (trading perf, as a consequence). This default to "optimize performance" again, is long-established, and accepted. (Yes, it's not the stance chosen by compilers for conventional programming languages, but so be it).

  So, our recommendations are:

  a) Avoid reliance on non-guaranteed timing and number-of-executions semantics.
  b) Avoid using NEWID() deep in table expressions.
  c) Use OPTION to force a particular behavior (trading perf)

  Hope this explanation helps clarify our reasons for closing this bug as "won't fix".

  Interestingly, AND NOT (s_guid = NEWID()) yields the same execution plan

This is a consequence of normalization, which happens very early during query compilation. Both expressions compile to exactly the same normalized form, so the same execution plan is produced.


What is the purpose of a Row_GUID column? I've been digging around in the AdventureWorks2012 database and see Row_GUID used in several tables.

There are 2 parts to my question:

When should I include a Row_GUID column?

What are the uses and benefits of a Row_GUID column?

ROWGUIDCOL is primarily used for MERGE replication, and is also required for FILESTREAM, but can be used in any scenario where you want an immutable column separate from the primary key (e.g. in the case where a primary key value can change, but you still want to be able to tell which row was which before and after the change).

USE tempdb;
GO

CREATE TABLE dbo.example
(
  name sysname PRIMARY KEY,
  rowguid uniqueidentifier NOT NULL DEFAULT NEWID() ROWGUIDCOL
);

INSERT dbo.example(name) VALUES(N'bob'),(N'frank');

SELECT * FROM dbo.example;

UPDATE dbo.example SET name = N'pat' WHERE name = N'bob';
UPDATE dbo.example SET name = N'bob' WHERE name = N'frank';

SELECT * FROM dbo.example;

DROP TABLE dbo.example;

Now, if replication, or your application, or what have you is paying attention, it will notice that:

See here, here, and the "Snapshot Considerations" section here for more info.


Why does sp_columns / sys.spt_columns_odbc_view return incorrect results? While investigating a question on Stackoverflow, I discovered that sp_columns, which uses sys.spt_columns_odbc_view, is returning the wrong / unexpected DATA_TYPE & TYPE_NAME for NVARCHAR(MAX). It's returning NTEXT for NVARCHAR columns and I can't figure out why. Does anyone have any insight?

DEMO

CREATE TABLE [dbo].[deleteTable](
    [v] [nvarchar](max) NULL,
    [t] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

select
    c.name
    ,c.system_type_id
    ,ty.name
    ,ty.precision
    ,t.name
from
    sys.columns c
inner join
    sys.tables t on t.object_id = c.object_id
inner join
    sys.types ty on ty.system_type_id = c.system_type_id
where
t.name = 'deleteTable'

exec sp_columns @table_name = 'deleteTable'

I figured out why you get NTEXT but do not know why this is done. This note may be relevant:

  sp_datatype_info is equivalent to SQLGetTypeInfo in ODBC. The results
  returned are ordered by DATA_TYPE and then by how closely the data
  type maps to the corresponding ODBC SQL data type.

In the definition of sys.spt_columns_odbc_view there is a piece of code:

TYPE_NAME = CONVERT(sysname,
CASE
WHEN (
    t.system_type_id = 240
    OR
    t.user_type_id > 255
  )
  THEN -- CLR UDTs
  t.NAME
  ELSE d.type_name COLLATE database_default
END
), sys.types t ON (
  t.user_type_id = c.user_type_id
)
INNER JOIN sys.spt_datatype_info d ON (
  d.ss_dtype = c.system_type_id
  AND
  d.auto_increment = c.is_identity
)

In your case ELSE d.type_name COLLATE database_default this is being executed because the data type is -10.

sys.spt_datatype_info uses sp_datatype_info.

If you execute below code you will see where NTEXT is coming from.

EXEC sp_datatype_info -10;
GO


Add a total quantity column for groups of rows I have an Items table and an Inventory table.  An Item can belong to multiple Inventory records.  I'm trying to return a list of ALL Inventory records but with their quantity in a new column.  For instance:

Items

ItemID     ItemDescription
103        Headphones
115        Speakers
230        Wireless Adapter
275        20' Network Cable

Inventory

InventoryID        ItemID        WarrantyDate       Status
1                  103           12/22/2010         Available
2                  103           05/15/2012         Available
3                  103           02/24/2015
4                  275           01/01/2010
5                  275           01/01/2011

If I try to COUNT the ItemID and GROUP BY ItemID, like so:

SELECT ItemID, COUNT(ItemID) AS Quantity
FROM Inventory
GROUP BY ItemID

I get:

ItemID  Quantity
103     3
275     2

But what I really want is this:

InventoryID        ItemID        WarrantyDate       Status       Quantity
1                  103           12/22/2010         Available    3
2                  103           05/15/2012         Available    3
3                  103           02/24/2015                      3
4                  275           01/01/2010                      2
5                  275           01/01/2011                      2

Any suggestions/ideas are appreciated.

You can use the OVER clause on the COUNT function to get what you need:

CREATE TABLE #inventory(
   InventoryID  INTEGER  NOT NULL PRIMARY KEY
  ,ItemID       INTEGER  NOT NULL
  ,WarrantyDate DATE  NOT NULL
  ,Status       VARCHAR(9)
);
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (1,103,'12/22/2010','Available');
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (2,103,'05/15/2012','Available');
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (3,103,'02/24/2015',NULL);
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (4,275,'01/01/2010',NULL);
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (5,275,'01/01/2011',NULL);

SELECT *, COUNT(ItemID) OVER (PARTITION BY ItemID) AS Quantity
FROM #Inventory

Output:

+-------------+--------+--------------+-----------+----------+
| InventoryID | ItemID | WarrantyDate |  Status   | Quantity |
+-------------+--------+--------------+-----------+----------+
|           1 |    103 | 2010-12-22   | Available |        3 |
|           2 |    103 | 2012-05-15   | Available |        3 |
|           3 |    103 | 2015-02-24   | NULL      |        3 |
|           4 |    275 | 2010-01-01   | NULL      |        2 |
|           5 |    275 | 2011-01-01   | NULL      |        2 |
+-------------+--------+--------------+-----------+----------+


How to check mysql password change and other events logs Recently someone change my phpmyadmin password and drop database

I think it was sql injecting.

Now I want to find information about this like when, how and who change my password

Please tell me where I can find my mysql v 5.5 logs (Windows Server 2012)

Thanks

by default MySQL community version not support audit

You have only one build in way - enable general log for log all activity on MySQL server

alternatives for enterprise version - https://www.percona.com/doc/percona-server/5.5/management/audit_log_plugin.html

MariaDB audit plugin You can install and on Linux and on Windows server - https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/


TokuDB database size unknown in phpmyadmin In mysql server, I installed the TokuDB storage engine. I converted database engine InnoDB to TokuDB, but my table sizes show unknown in phpmyadmin.

Once the TokuDB storage engine was defined, it becomes responsible for updating the INFORMATION_SCHEMA database.

The first thing you should do it test TokuDB's INFORMATION_SCHEMA functionality.

First, run this query at a mysql client prompt:

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

This will group and summarize MySQL data by engine usage per database.

If TokuDB stats do appear, then upgrade the MySQL drivers for phpMyAdmin.
If nothing comes up for TokuDB, try getting the latest version of the TokuDB storage engine. If you already have the latest version of TokuDB, contact Tokutek.
If you already have the latest version of the TokuDB storage and the latest MySQL drivers for phpMyAdmin and still get unknown, file a bug report with phpMyAdmin.


Visual Studio database project and Enity Framework code-first At work we are developing our latest module in C# Entity framework code-first approach. I have been given the role of developing databases from the DBContexts from within the project/s.

I generated the tables/entities from the DBContext and created database projects and all is fine.

My question is: When a developer changes the DBContext entities or changes a property of an entity, how can I become aware of such changes and update the database projects?

Here is and article explaining a good way to do this will little extra effort. The article describes comparing the SQL Database Project with the EF generated LocalDB to persist schema changes.

Use EF to make schema/entity updates like you would normally in you development workflow. The use the EF database as your comparison DB to update your SQL Database project.

Going the other way means updating objects like stored procedures, logins, additional indexes, and other more administrative/fine tuning objects.

There is a caveat though... EF does not have the tooling to detect these changes automatically, so be disciplined in using the right tools for the right jobs. EF for models and SQL DB Project for... well, everything not controlled with EF code migrations :-)

SQL Database Project and EF


Grant permissions to run an SQL server job I have a job on my MSSQL server 2005, that I want to allow any database user to run.

I'm not worried about security, since the input to the job's actual work comes from a database table. Just running the job, without adding records to that table will do nothing.

I just can't find how to grant public permissions to the job.

Is there any way to do this? The only thing I can think of at this point is to have the job constantly running (or on a schedule), but since it only needs to do any actual work rarely (maybe once every few months) and I do want the work to be done as soon as it exists, this does not seem to be an optimal solution.

You could create a stored procedure that runs the job.  You can use with execute as owner to run the stored procedure as the database owner.  That way, the users themselves don't need permissons on sp_start_job.

create procedure dbo.DoYourJob
with execute as owner
as
exec sp_start_job @job_name = 'YourJob'

Grant execute rights on DoYourJob to allow people to start the job.

Of course, you can also put the contents of the job in a procedure, and grant rights to run that.  That would allow more interaction, like displaying a result text.


Remove first all null records from first part of time series until one value is not null and return the rest, in PostgreSQL I have a table that has something like this:

dt              col1 col2
2000-01-01      null null
2000-01-02      null null
2000-01-05      null null
2000-01-10      null 100
2000-01-11      1    2
2000-01-12      null 3
2000-01-14      null null
2000-01-20      1    1
2000-01-21      null null

Note it has an unpredictable number of records and in some cases I want to query the table and trim off the records that are earliest in the time series that are all null that occur prior to the first record that has at least one value. In this example I want to remove records 2000-01-01 through 2000-01-05. Note that I want to leave all records whether all values are null or not after the first one is found that has a non-null value starting from oldest date forward.

So my desired result would be:

dt              col1 col2
2000-01-10      null 100
2000-01-11      1    2
2000-01-12      null 3
2000-01-14      null null
2000-01-20      1    1
2000-01-21      null null

This doesn't work because it removes all records that have all null values:

select
    *
from
    tbl
where
    col1 is not null
    or col2 is not null;

Is there a way to solve this without changing the table at all (doing it all in a query)?

My alternative is to iterate through the records in a program that this query is needed. I'm thinking SQL might be better.

I'm using PostgreSQL 9.6.

select * from tbl
where dt>= (select min(dt) from tbl where col1 is not null or col2 is not null)


Adding COMPRESSION_DELAY to an existing COLUMNSTORE INDEX When I create an COLUMNSTORE index I can specify COMPRESSION_DELAY = x [minutes] in the CREATE-Statement. It delays the movement of the rows in an closed segment from the delta store to the compressed columnstore storage. Reason to do so: your import process is not just a simple INSERT but has some UPDATEs and maybe DELETEs too.

Is there a way to set this value afterwards without recreating the whole index (which would take very long on our big table)?

I tested already REBUILD and REORGANIZE, but they both do not understand the COMPRESSION_DELAY option in the WITH().

An existing columnstore index can be altered as such:

ALTER INDEX ColumnstoreIndexName ON SchemaName.ObjectName
SET (COMPRESSION_DELAY  = x Minutes);

As found in the documentation on ALTER INDEX

ALTER INDEX { index_name | ALL } ON <object>
{
...
    | SET ( <set_index_option> [ ,...n ] )

<set_index_option>::=
{
      ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF}
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | COMPRESSION_DELAY= {0 | delay [Minutes]}
}


Defining multiple fields in a select string with hard set length values (SQL 2000) Currently I have been tasked to modify a super old stored procedure that represents an email output in our SQL 2000 instance. With correcting a few syntax issues I wanted the original string format to be more presentable in the email. Because the fields "machine" and "Part #" have a variance of the fields data length thus the fields shift and the columns do not line up entirely. What I have been attempting is to set each field present in my SQL code to hard set values of say 15 characters that way the format of each field presented does not change/shift and looks straight to the end user. Now I have tried a few suggestions online and could not find a method to set each field. Any ideas?

 INSERT INTO ##prod_performance_txt_out2
               SELECT LEFT(machine + '             ', 9)
          + LEFT(part_no + '                              ', 30)
          + LEFT(CONVERT(varchar(7), ordered) + '       ', 7)
          + LEFT(CONVERT(varchar(7), picked)  + '       ', 7)
          + LEFT(CONVERT(varchar(7), allocated)  + '       ', 7)
          + LEFT(CONVERT(varchar(7), in_stock)  + '       ', 7)
          + LEFT(CONVERT(varchar(7), sh_bin_qty)  + '       ', 7)
          + LEFT(CONVERT(varchar(7), short)  + '       ', 7)
          + min_bin

               FROM #tempsummary2 WHERE resource = @resource AND short > 0

First - note that performing detailed formatting in SQL is often frowned upon, and "should be left to the calling application". Such recommendations, of course, ignore situations like this where there is no calling application.

I don't see an issue with the fundamental methodology seen in the query you provided. In a situation like this, it's wise to use application-specific knowledge. For variable-length values that you're willing to truncate, you need to figure out what length covers most of the data, and provides enough of that column to make out what it is. For values that must be provided as is (your numeric data), you need a length will cover the maximum possible value for the field. And, that may be different for different columns: a quantity column in a table to hold data on cars at a dealership can probably be smaller than a "total value" column in the same table.

If the hard-coded values you're using need to be used on a number of lines, you can put them into variables.

I would recommend making sure that you have "gutters" between your columns - 1-3 spaces between each column helps keep them distinct and easily readable. I'm pretty sure the "LD-MOLDINLD-" lines in your sample output are run together due to the lack of a gutter.

Finally, you might want to flip the padding on the numeric columns; if you change:

LEFT(CONVERT(varchar(7), ordered) + '       ', 7)

to:

RIGHT('       ' + CONVERT(varchar(7), ordered), 7)

then the numbers will be right-aligned, which is far more typical for numbers.

All that said, none of this works well if your carefully spaces output isn't displayed using a mono-space font.

Looking at "MACHINE", "SHIPPING", and "MOLDINLD", I suspect the output you've provided a screen shot of is not using a mono-spaced font. Note that "MACHINE" (7 letters) and "SHIPPING" (8 letters) seem like they line up on their right side (or are off by 1 pixel).

Not knowing your email program, I can't be sure of the best way to convince it to display this data that way. For HTML output, try enclosing the entire output block in
 tags (with appropriate closing
 tags at the end. As, in fact, is done on this very page at this time (right-click, select "View source", and scroll a ways to see).

You can confirm this is correct by copying the output you took a screenshot of, and pasting it into a text editor set to use a mono-space font.


Can I replicate slaves without a dump? I have setup a new slave in a set and I just want to allow it to naturally cach up to the master and start using the binary log once it has caught up.

However, I cannot seem to find anything on this, every method seems to require a pre-requisite dump of data. It would be a lot easier for me to just let the slave catch up naturally over a period of days.

Is there anyway I can do this or do I really have to take a full dump etc cetc?

It's pretty easy to create a slave either from the running master or slave.
Here Percona explains how to do it with XtraBackup http://www.percona.com/doc/percona-xtrabackup/2.1/howtos/setting_up_replication.html#adding-more-slaves-to-the-master

You can use this procedure with slight variation to add new slaves to a master. We will use Percona XtraBackup to clone an already configured slave. We will continue using the previous scenario for convenience but we will add TheNewSlave to the plot.

At TheSlave, do a full backup:

TheSlave$ innobackupex --user=yourDBuser --password=MaGiCiGaM /
      --slave-info /path/to/backupdir

By using the --slave-info Percona XtraBackup creates additional file called xtrabackup_slave_info.

Apply the logs:

TheSlave$ innobackupex --apply-log --use-memory=2G /path/to/backupdir/$TIMESTAMP/

Copy the directory from the TheSlave to TheNewSlave (NOTE: Make sure mysqld is shut down on TheNewSlave before you copy the contents the snapshot into its datadir.):

rsync -avprP -e ssh /path/to/backupdir/$TIMESTAMP TheNewSlave:/path/to/mysql/datadir
Add additional grant on the master:

TheMaster|mysql> GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'$newslaveip'
         IDENTIFIED BY '$slavepass';

Copy the configuration file from TheSlave:

TheNEWSlave$ scp user@TheSlave:/etc/mysql/my.cnf /etc/mysql/my.cnf

Make sure you change the server-id variable in /etc/mysql/my.cnf to 3 and disable the replication on start:

skip-slave-start
server-id=3

After setting server_id, start mysqld.

Fetch the master_log_file and master_log_pos from the file xtrabackup_slave_info, execute the statement for setting up the master and the log file for The NEW Slave:

TheNEWSlave|mysql> CHANGE MASTER TO
               MASTER_HOST='$masterip',
               MASTER_USER='repl',
               MASTER_PASSWORD='$slavepass',
               MASTER_LOG_FILE='TheMaster-bin.000001',
               MASTER_LOG_POS=481;

and start the slave:

TheSlave|mysql> START SLAVE;

If both IO and SQL threads are running when you check the TheNewSlave, server is replicating TheMaster.


RDS MYSQL instance rebooted and won't load INNODB table So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error in the logs.

140324 16:22:23 [ERROR] Cannot find or open table dbname/table_name from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

The problem is that no one noticed for three days that the table was missing and therefore my automated snapshots are useless (I only retain them that long) as all the snapshots have the same problem.

During my attempts to fix things I have had indications from the db that indicate that the .frm and .ibd files are all inplace. The show tables command lists the table (so frm is there), and I get the error below if I (on a copy) drop the table and try and recreate it.

140324 16:46:05 InnoDB: Error creating file './dbname/table_name.ibd'.
140324 16:46:05 InnoDB: Operating system error number 17 in a file operation.
InnoDB: Error number 17 means 'File exists'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: The file already exists though the corresponding table did not
InnoDB: exist in the InnoDB data dictionary. Have you moved InnoDB
InnoDB: .ibd files around without using the SQL commands
InnoDB: DISCARD TABLESPACE and IMPORT TABLESPACE, or did
InnoDB: mysqld crash in the middle of CREATE TABLE? You can
InnoDB: resolve the problem by removing the file './dbname/table_name.ibd'
InnoDB: under the 'datadir' of MySQL.

I suspect there was some issue with an index before the instance was restarted, or if it indeed crashed then perhaps that caused a corruption.

I managed to recreate the data (probably) from logs though it's only a guess, not a sure thing.

So I'm asking what I should do at this point. I'd love to find a solution that allows me to get that table back with the data it had, but if I have to delete it and recreate it that's also ok. Obviously I can't touch any mysql data files as it's an rds instance. Mysql version is 5.5.27.

You need to open the case with AWS Support team here. As only they have direct access to the server consoles and only they can help you to perform required operations as suggested in the output of attempt to recreate table.

Alternatively you can create the table with new name, as a possible workaround for the problem.


When and how to create users in a MongoDB sharded cluster I hope my question is clear enough.
I'm setting up a sharded cluster, 3 members, each running as a replica set. So, on each shard there are 3 mongod running as replica sets.

My question is what should be the time during the setup process to create users&roles and on what instance of mongod?
I know about the localhost exception on a mongo db deployment, but it's not clear from the documentation what would be the best way to do it in a sharded cluster.
I have the following users:

useradmin -> able to create new users only -> role userAdminAnyDatabase (to create new users)
manager -> use to manage the cluster -> role clusterAdmin
root -> root access/roles -> role root
appuser -> role readWriteAnyDatabase

So the options I see are:

On each replica set mongod, using localhost exception, before it's added to the sharded cluster, add all users from above?
At the end of setup, only once in the config replica set?
At the end, connecting to the sharded cluster?
Create initial users only with createUser role on each mongodbs and then create the others connecting at the end to the sharded cluster?

I did using 1st option, but I feel something is not right.

Tutorial Deploy Sharded Cluster with Keyfile Authentication provides a step-by-step setup for a sharded cluster with authentication, i.e. creating users.
The general procedure is this:

Deploy the entire sharded cluster completely without any user accounts.
When the cluster is complete connect to one mongos (using localhost exception) and create the user accounts. The accounts will be available in the entire cluster.
Connect locally to each shard (i.e. the PRIMARY node, using localhost exception) and create the "shard-local administrator" accounts.

Step 2 and 3 you can exchange, if you like.
What is a "shard-local administrator" account?
User accounts are (usually) defined in the admin database of the Config Servers. When a client connects to the cluster then it queries the config server to validate the credentials. Some operational tasks on the shard servers requires a direct (i.e. local) connection to the shard server, sometimes they are even disconnected from the cluster. A disconnected shard node cannot query the Config Server in order to validate any credentials.
For this reason you have to create the "shard-local administrator" accounts on each shard. With this account you can connect locally to the shard server and perform some maintenance operations even when it is not connected to the cluster. The accounts are created on the PRIMARY nodes and they are replicated to the SECONDARIES.
The "shard-local administrator" accounts are needed only for some operational task, your application does not require any of them.
The Config Server does not have such local accounts, because
local users are equal to cluster users. You can use the cluster user credential also to connect directly (i.e. locally) to a Config server.
The mongos does not have such local accounts either. The mongos does not store any data locally, thus operational tasks mentioned above does not exist and local accounts are not required nor possible.
When you add new shard replica sets then you also have to create the "shard-local administrator" accounts on them. However, it does not matter when you create it, because they are fully independent from any cluster user.


Write one or more triggers to manage the grade attribute of new Highschoolers If the inserted tuple has a value less than 9 or greater than 12, change the value to NULL. On the other hand, if the inserted tuple has a null value for grade, change it to 9. ```

CREATE TRIGGER gradeTrigger1
AFTER INSERT ON highschooler
WHEN new.grade < 9 OR new.grade > 12
BEGIN
    UPDATE highschooler SET grade=NULL WHERE id=new.id;
END

As i mentioned in the comment.

For your purpose you can use a BEFORE  INSERT trigger

DELIMITER $$
CREATE TRIGGER `gradeTrigger1`
BEFORE INSERT
ON highschooler  FOR EACH ROW
BEGIN
    CASE  WHEN  new.grade < 9 OR new.grade > 12  THEN
        SET new.grade = NULL;
    WHEN  new.grade IS NULL THEN
        SET new.grade = 9;
    ELSE
        SET new.grade = NEW.grade;
    END CASE;
END$$
DELIMITER ;


How does objectproperty() figure out if a function is deterministic in sql server? How does sql server determine if a function is deterministic or not?

Consider the following function, I think it's deterministic (as it's ultimately an integer operation), but sql server returns 0 when I call the objectproperty method on it.

Understanding how it works will help me understand why I'm wrong in my assessment of this function.

CREATE FUNCTION dbo.EndOfPreviousMonth ( @Date date )
RETURNS date
AS BEGIN
    RETURN DATEADD(day, 0 - DAY(@Date), @Date);
END;

SELECT dbo.EndOfPreviousMonth('2010-01-01'); --Usage example

--Returns 0, I expected it to return 1
SELECT OBJECTPROPERTY( OBJECT_ID( 'dbo.EndOfPreviousMonth' ), 'IsDeterministic' );

First it looks whether used functions are deterministic.
Then it looks whether your function is schema bound.
(Or in reverse order.)

So add with schemabinding to your function.


Change MariaDB 10.0.17 (MySQL) master to a slave I have a MySQL master/slave configuration

 --------        --------
| master |  ->  | slave1 |
 --------        --------

I want to kill my current master, transform slave1 to be the new master and have another slave pointing to slave1

 --------          ------------        --------
| master |        | new master |  ->  | slave2 |
 --------          ------------        --------
  KILLED

What is the best way to do this without database downtime?
I am using MariaDB 10.0.17

I would say your basic process is quite straight forward.

1) Ensure that slave1 has got log-bin and log-slave-updates enabled (this will require a restart if not)

2) Take a copy of Slave1 and set up Slave2 as a slave of Slave1. You now have
Master -> Slave1 -> Slave2

3) Once you are happy that Slave2 is performing as expected, set GLOBAL READ_ONLY=on on the master, and move all your writes to the Slave1.

4) Once this has completed, Shut down the master.

The only downtime you would have would be restarting Slave1 if required, and the time it takes to move your writes from the Master to the Slave1. This part is entirely dependent on your situation.


SQL Server 2008 R2 Express Connection I installed SQL Server 2008 R2 Express Edition.

I opened the command prompt and typed sqlcmd -S server1\SQLExpress -U SqlUserAccount -P SqlPassword to connect to SQL Server.

However, it occasionally shows error messages such as 'login failed for user', or 'could not open a connection to SQL server'.  When I install SQL Server 2008 R2 with Advanced Services, I can logon to SQL Server using SSMS successfully.

SQL Server Express greatly reduces the resources it uses if it is idle for a period of time.  This may cause connections to time-out if the machine is busy after SQL Server idles.
http://blogs.msdn.com/b/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx contains some details about this behavior, including:

When SQL Express is idle it aggressively trims back the working memory set by writing the cached data back to disk and releasing the memory. This frees up memory for other applications to use.
This behavior makes sense for a single user database engine - it supports higher performance when the application using the data is running, but once that application is shut down, memory is released to allow other applications to use it. I like to think of this as "being a good citizen" in the application community. There is always a catch when making this kind of trade-off though, and SQL Express is no different in this aspect. When SQL Express transitions from idle to active some of the memory that was released needs to be reclaimed; this results is a slight lag during "startup time" when you're first connecting to SQL Express. You can actually see this happening in the Widows Application Log, when you first activate SQL Express after some idle time, an entry is written to the log that reads like this:
Server resumed execution after being idle 16056 seconds: user activity awakened the server. This is an informational message only. No user action is required.

Also, SQL Server Express will "auto-close" any database that is no longer being used 300 milliseconds after activity ceases.


What is the Maximum Merge Articles in Merge Replication Possible Duplicate:
  Hard limitation for merge replication article count?

I am using merge replication in SQL 2012. I am looking here:

Maximum Capacity Specifications for SQL Server (2012)

Scroll to the bottom and under the 'Replication Objects' it says that the maximum number of articles in a merge publication is 256.

I wasn't aware of this apparent limitation, but in the mean time I have been running merge replication with 456 articles and everything seems to run fine.

I found these 3 links about it:

Too many table names in the query sql 2005 merge replication snapshot error
FIX: Merge Snapshot Agent Fails with Error 50007: "Too many table names in the query. The maximum allowable is 256."
Hard limitation for merge replication article count?

So to summarise, apparently in SQL 2000 there was a bug which caused this limit. It was fixed in a service pack, but was reintroduced in SQL 2005.

Does that limitation still exist or not? There doesn't seem to be any official information about SQL 2012.

I would rather management studio tell me 'you cannot add more then 256 articles' rather than having to guess whether this will be a problem for us or not.

The first link you provided is official documentation for SQL Server 2012.

http://msdn.microsoft.com/en-us/library/ms143432.aspx

The maximum number of articles supported in Merge Replication is 256.  This means that Microsoft will ensure up to 256 articles will work as expected.  I'm able to exceed 256 articles as well but I wouldn't do it in production.  You may want to consider splitting this up into multiple publications for production purposes.


TIMESTAMP OR DateUpdate I want to implement incremental ETL on some of my sql server tables, so I need a new column like UpdatedDate to track change dates of my records. I want to transfer only updated and inserted records, so a single column is enough. The question is should I use datetime column and populate it with triggers or it is better to user timestamp column? What are pros and cons?

In both cases I want to create nonclustered index on my columns to find rows from the last sync faster.

Timestamp don't carry any relationship with date and time. Absurdly enough, it is allowed to convert to datetime, but it won't be the date and time that you would hope for. I.e., no relationship with the date and time when the row was inserted/modified. As shown here:

CREATE TABLE #t(c1 timestamp)
INSERT INTO #t DEFAULT VALUES
SELECT c1, CAST(c1 AS datetime) FROM #t

0x00000000000007D5  1900-01-01 00:00:06.683

Since timestamp was such a stupid name for the type, MS decided that we should start calling it rowversion instead. Sound thinking. But the implementation sucked since the name rowversion is treated like an alias to timestamp at parsetime. I.e., specify rowversion in the CREATE TABLE, and when you script it, you'll see timestamp instead.

Wisely enough, MS do not allow this type to be converted to the new date and time types.

So, timestamp/rowversion is out of the question if you want to carry a meaningfule date and time with it. That leaves any of the datetime types, and make sure it is populated. Or use CDC or CT as suggested by Dan Guzman.


Point in time database restore I am trying to restore my database using point in time using .bak file. I selected the back from using the from device option. File is shown its type is full as i give the some back data and time. The .bak file shown disappear. what I am doing wrong here

For a point in time recovery, you need the series of transaction log backup files that are usually generated by regular, scheduled transation log backups in addition to the full database backup that you possess. Transaction log backups are often denoted by .bak or by .trn extensions. If you do not have those transaction log backup files, you can't do what you want. Roughly speaking, you would restore to a point in time by:
1. Restoring the full backup file using the NORECOVERY option.
2. Restore all of the relevant transaction log backups using the STOPAT option.
3. Lastly, bring the database out of recovery.


Problem locating rows with dynamic SQL I'm new at SQL in general, so I'm have a few problems with some of my stored procedures I'm designing. My procedure takes in 3 parameters. A location (or table name basically, a varchar), a room number (varchar), and a phone number (char) in the form of 'xxx-xxxx'. Here is the code for the procedure.

ALTER PROCEDURE [dbo].[AddPhoneNumberToRoom]
    @Location    varchar(25),
    @Room        varchar(10),
    @PhoneNumber char(8)

AS
BEGIN
SET NOCOUNT ON;

    DECLARE @String varchar(100);
    SELECT @String = ' UPDATE ' + @Location +
                     ' SET PhoneNumber = ' + @PhoneNumber +
                     ' WHERE Room = ' + @Room;
    EXEC(@String);
END

The error I receive is:

  The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Apartments_PhoneNumbers".  The conflict occurred in database "CMPhone", table "dbo.PhoneNumbers", column 'PhoneNumber'."

The phone number is in the PhoneNumber table, so when I do:

UPDATE Apartments SET PhoneNumber='123-4567' WHERE Room='2'

It works just fine. The PhoneNumbers table also looks like this, just has the one number in it.

PhoneNumber
-----------
123-4567

PRINT @String; will yield that it says PhoneNumber = 123-4567 (which treats it like an expression, 123 subtract 4567, which equals -4444), not PhoneNumber = '123-4567' like in the example you hard-coded. So you should be escaping the values you're appending with two single-quotes:

SELECT @String = ' UPDATE ' + @Location +
                 ' SET PhoneNumber = ''' + @PhoneNumber + '''
                   WHERE Room = ''' + @Room + ''';';

But better yet, to better protect yourself from SQL injection (see here, here, and here), you should have (a) check that @Location is a valid table, (b) QUOTENAME() it anyway, and (c) pass in the other two values as properly typed parameters rather than appending them to the string:

IF OBJECT_ID(N'dbo.' + @Location) IS NOT NULL
BEGIN
  DECLARE @sql NVARCHAR(MAX);
  SET @sql = N'UPDATE dbo.' + QUOTENAME(@Location)
           + N' SET PhoneNumber = @PhoneNumber
                WHERE Room = @Room;';
  EXEC sys.sp_executesql @sql,
      N'@PhoneNumber CHAR(8), @Room VARCHAR(10)',
      @PhoneNumber, @Room;
END

And even better still, you shouldn't have a separate table for each Location IMHO - Location should be a column. Then you wouldn't need dynamic SQL at all.


Query for data that is not there I have this setup

attendance table:
------------------------------------------
| member_id | attendance_date | event_id |
------------------------------------------

attendance indeces
---------------------------------------------
| p1 (member_id, attendance_date, event_id) |
| p2 (attendance_date, member_id, event_id) |
| member (member_id)                        |
| total (attendance_date, event_id)         |
---------------------------------------------

members table
-------------------------------------------------
| member_id | department_id | registration_date |
-------------------------------------------------

members indeces:
-----------------------------------------------
| PK (member_id)                              |
| registration (registration_date, member_id) |
| department (department_id, member_id)       |
-----------------------------------------------

So basically this is an attendance system. And I need to run reports based on the attendance table. I've figured out much of the queries needed except for the part where I need to know how many absences a member has for a given time. I pretty much know how to query for data that is there. But I do not know how will I query for data that is not there (absences).

I tried this:

SELECT
  attendance_date,
  COUNT(DISTINCT event_id)
FROM
  attendance
WHERE
  WEEKOFYEAR(attendance_date) BETWEEN 27 AND 31
GROUP BY
  WEEKOFYEAR(attendance_date)

which gives me the number of events a member should've been able to attend based on other's attendance record, but the problem is when there is an event with no attendees at all (unlikely) or when there is a newly registered member.

I tried this:

   EXPLAIN SELECT
     m.member_id AS id,
     COUNT(DISTINCT a.event_id)
   FROM
     members AS m
     LEFT JOIN
       attendance AS a
       ON
         m.registration_date < a.attendance_date AND
         WEEKOFYEAR(a.attendance_date) BETWEEN 27 AND 31
   WHERE
     m.department_id = 1

But EXPLAIN tells me that it will scan 4M rows so I know that it is not the way to go. I know that the query is wrong anyways since COUNT(DISTINCT) will give me multiple rows per member. Any thoughts? And any advice on how should I index the database for this query is also appreciated.

EDIT

To be more specific, this is what I want to do with the query:

Get all members of a department
Get the number of events that has a record in the attendance table between a given period but only those events that occurred later than the current member's registration date

When we subtract the total number of attendance records of the member, then we can actually get how many absences the member already has.

I know that part of this problem stems from a fundamental error in the structure of the application (events shouldn't be defined based on attendance records) but these events are recurring and are quite irregular in their occurrence patterns. So to minimize the need for maintenance, I just decided that I derive event information (or at least their count) be based on the attendance since it is very unlikely that an event will have 0 attendees.

Why are you determining a member's absence through another member's presence?

I guess there must be an events table in your schema. And I would expect it to have a column called something like event_date. It's the event_date column that you should use to determine which events a member could visit (members.registration_date < events.event_date).

After getting the list of members and their respective valid events, you can anti-join it to the attendance table to see which member missed which (or how many) events.

Here's an example query illustrating what I just said. It implements the anti-join using LEFT JOIN + WHERE IS NULL check:

SELECT
  m.member_id,
  e.event_id
FROM       members    m
INNER JOIN events     e ON m.registration_date < e.event_date
LEFT  JOIN attendance a ON m.member_id = a.member_id
                       AND e.event_id  = a.event_id
WHERE a.member_id IS NULL
  AND ... /* some other conditions to filter members and/or events, if necessary */

Or here's another example showing how you could count total events available to a member and those actually attended by him/her:

SELECT
  m.member_id,
  COUNT(e.event_id) AS total_events,
  COUNT(a.event_id) AS attended_events,
FROM       members    m
INNER JOIN events     e ON m.registration_date < e.event_date
LEFT  JOIN attendance a ON m.member_id = a.member_id
                       AND e.event_id  = a.event_id
WHERE /* some conditions as necessary */

Note that in the last example there's no IS NULL check. It's because that query needs to get all the events per member. Missed events are just not counted by the COUNT() function (because the corresponding a.event_id value contains NULL in those cases).

UPDATE

Since, as per your comment, the events are recurring (and thus have no fixed dates), I would suggest adding and using an event_calendar table, populated beforehand either manually or programmatically. Naturally, the events table in the above examples would be replaced with event_calendar.

Alternatively, if maintaining an event calendar is not an option, you could replace events in your queries with

(
  SELECT DISTINCT
    event_id,
    attendance_date AS event_date
    /* or, if they are not purely dates:
    CAST(attendance_date AS date) AS event_date
    */
  FROM attendance
) e

(basically, @ypercube's suggestion with dates thrown in).


databases/tables with nonconventional names Disclaimer: not an administrator

How does one deal with non-kosher names such as 'data?base' ?

I'm always one to ensure the cleanliness of my databases, but after finally installing phpmyadmin to aid in my mysql server (where before it was all manual). I seemed to have slipped up.

I logged in remotely through a limited access account and saw an option create a database. Thinking that I disabled create on that account, I went ahead and selected that option expecting and error. Nope, it actually worked, so I revoked access as well as double checked my permissions before I came to the quandry of removing the database.

The original database was named sig_db5 which phpmyadmin left as sig?db5 in the create database option (the default name in that context).

mysql> drop database 'sig?db5';

yeilds to

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''sig?db5'' at line 1

I can assure you that the database does indeed exist, but I'm in disbelief that if I can't remove it due to that character, how the heck did it get created.

+---------------------+
| Database            |
+---------------------+
| ******************  |
| ******************  |
| sig?db5             |
| ******************  |
| ******************  |
| ******************  |
| ******************  |
| ******************  |
| ******************  |
+---------------------+

(censured other names)

Disclaimer : not a user of phpmyadmin

You may have to check the following

see what character set phpmyadmin operates in
see what character set mysqld operates in
see what character set remote database servers operates in

Find out if you can manually change the default character set before connecting to the the desired DB server

If you connect to a remote DB using mysql client program and the characters for DB names look normal, then you are just dealing with character set issues with phpmyadmin.

WIth regard to the error message, do not surround database names with single quotes ('). Instead, surround them with back quotes (`)


#1215 - Cannot add foreign key constraint ERROR even though the columns are exactly the same Last night I ran SQL commands in phpMyAdmin very similar to the following to setup a players table and a games table linked by a foreign key.  It worked without errors, making google_id in my games table a foreign key.

CREATE TABLE players(
    google_id VARCHAR(36) PRIMARY KEY,
    display_name VARCHAR(255) NOT NULL,
    given_name VARCHAR(255) NOT NULL,
    family_name VARCHAR(255) NOT NULL,
    picture_url VARCHAR(2083) NOT NULL,
    leaderboard_initials VARCHAR(40),
    operating_system VARCHAR(255) NOT NULL,
    modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE games(
    game_id VARCHAR(255) PRIMARY KEY,
    google_id VARCHAR(36) NOT NULL,
    won INT(1) NOT NULL,
    score INT(10) NOT NULL,
    num_in_code INT(2) NOT NULL,
    num_colours INT(2) NOT NULL,
    num_guesses INT(2) NOT NULL,
    turn INT(2) NOT NULL,
    rank INT(10) NOT NULL,
    modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Then make the google_id a foreign key in games:

ALTER TABLE
    games ADD FOREIGN KEY(google_id) REFERENCES role(google_id);

Today the third command gave the following error:

Error
SQL query:

ALTER TABLE
games ADD FOREIGN KEY(google_id) REFERENCES role(google_id)
MySQL said:

#1215 - Cannot add foreign key constraint

AFAIK my google_id is exactly the same data type so this answer hasn't helped me

I've also read this answer but can't see anything wrong with my three queries.

Can anybody spot my mistake?

EDIT: Sorry people, this was a typo.  role should have been players

If games table is referring google_id from the parent table "players",then use below syntax (unless you are establishing Foreign key with "role" table as per your SQL statement):

ALTER TABLE
games ADD FOREIGN KEY(google_id) REFERENCES players(google_id);

Also, please check the DDL of role table(if exists)- datatype, width of column google_id and the mysql engine for both the tables.


Database relationship refresher All,

After several years of programming applications, I'm looking to refresh my understanding of databases. What I knew to begin with was fairly basic, and honestly, it's disappeared over time. I figured I could get an example and then come here to get a better understanding about the basics of relationships between tables.

Let's say I have the following three tables, describing train trips between three cities in the American Midwest.

The first is called TrainRide:

The second is called Customer:

The final table is called Journey:

I'm trying to figure out the primary keys, foreign keys, and relationships (one-to-many, many to many, etc.) of the tables. I've figured out that TrainRide's primary key is TrainNo. Customer's primary key is CustomerId. What I don't understand is the foreign key and primary keys of Journey, and I can't figure the _ to _ relationships between the tables.

I know this is more about the theory behind databases, but I figured this would be the place to ask.

Journey.CustomerIDis a foreign key to Customer.CustomerID. Journey.TrainNo is a foreign key to TrainRide.TrainNo.

The relationship here is many to many. A TrainRide can have many customers. A customer could ride many TrainRides. The table Journey is used to associate the two. You can see the same customer more than once in this table such as 834 or the same train more than once such as Amtrak 333.


Filtering by many-to-many table I'm having a hard time to understand how to filter out result of multidimensional expression.

This is my database schema.

And this is my data.

Manufacturer
╔════╦═══════════════════╗
║ Id ║       Name        ║
╠════╬═══════════════════╣
║  1 ║ Awesome Computers ║
║  2 ║ TailSpin Toys     ║
╚════╩═══════════════════╝

Item
╔════╦═════════╦════════════════╦═══════╗
║ Id ║  Name   ║ ManufacturerId ║ Stock ║
╠════╬═════════╬════════════════╬═══════╣
║  1 ║ PC      ║              1 ║    40 ║
║  2 ║ Server  ║              1 ║    10 ║
║  3 ║ STB     ║              2 ║    80 ║
║  4 ║ Console ║              2 ║    50 ║
╚════╩═════════╩════════════════╩═══════╝

Part
╔════╦══════════════════╦════════╦══════════╦═══════╗
║ Id ║       Name       ║ ItemId ║ StatusId ║ Stock ║
╠════╬══════════════════╬════════╬══════════╬═══════╣
║  1 ║ MBO              ║      1 ║        1 ║   100 ║
║  2 ║ Processor        ║      1 ║        1 ║   100 ║
║  3 ║ Server MBO       ║      2 ║        2 ║    20 ║
║  4 ║ Server processor ║      2 ║        2 ║    20 ║
║  5 ║ Main box         ║      3 ║        2 ║    40 ║
║  7 ║ Adapter          ║      3 ║        3 ║    30 ║
║  8 ║ Controller       ║      4 ║        2 ║    40 ║
║ 10 ║ Adapter          ║      4 ║        1 ║    60 ║
║ 11 ║ Memory card      ║      4 ║        2 ║    80 ║
╚════╩══════════════════╩════════╩══════════╩═══════╝

Status
╔════╦═════════════╗
║ Id ║    Name     ║
╠════╬═════════════╣
║  1 ║ No data     ║
║  2 ║ Available   ║
║  3 ║ Unavailable ║
╚════╩═════════════╝

I imported everything into the tabular model solution.
After this, I created two measures:

Table Item: ItemStock:=SUM([Stock])
Table Part: PartStock:=SUM([Stock])

Then I deployed the cube to the server.

By running the following MDX query...

SELECT
    NON EMPTY {
        [Part].[Name].CHILDREN
    } ON ROWS,
    {
        [Measures].[PartStock]
    } ON COLUMNS
FROM [Model]
WHERE (
    {
        [Status].[Id].&[1]
    }
)

...I get this resultset...

╔═══════════╦═══════════╗
║           ║ PartStock ║
╠═══════════╬═══════════╣
║ Adapter   ║        60 ║
║ MBO       ║       100 ║
║ Processor ║       100 ║
╚═══════════╩═══════════╝

...which is ok.

However, when running this MDX query...

SELECT
    NON EMPTY {
        [Item].[Name].CHILDREN
    } ON ROWS,
    {
        [Measures].[ItemStock]
    } ON COLUMNS
FROM [Model]
WHERE (
    {
        [Status].[Id].&[1]
    }
)

...I'm getting this resultset...

╔═════════╦═══════════╗
║         ║ ItemStock ║
╠═════════╬═══════════╣
║ Console ║        50 ║
║ PC      ║        40 ║
║ Server  ║        10 ║
║ STB     ║        80 ║
╚═════════╩═══════════╝

I was expecting that items in the ItemStock table would be filtered out by Part table as a many-to-many relationship. E.g. MBO, Processor and Adapter have references to items 1 and 4, so the result would be constrained to them, and the result should turn out like this:

╔═════════╦═══════════╗
║         ║ ItemStock ║
╠═════════╬═══════════╣
║ Console ║        50 ║
║ PC      ║        40 ║
╚═════════╩═══════════╝

What am I doing wrong?

Assuming we are talking about tabular models in SSAS 2012 or 2014 (since 2016 has a new capability around bi-directional crossfiltering) crossfiltering only flows over relationships in one direction. So filtering Status will crossfilter Part, but not Item or Manufacturer. Filtering Manufacturer will crossfilter Item and Part but not Status.

Any calculation you embed in a Tabular model must be written in DAX. So if you want to filter on Status and have it impact the ItemStock measure you need to code that:

ItemStock :=
IF(
  ISCROSSFILTERED('Status'[Id]),
  CALCULATE(
    SUM( 'Item'[Stock] ),
    SUMMARIZE(
        'Part',
        'Item'[Id]
    )
  ),
  SUM( 'Item'[Stock] )
)

More theory on many-to-many formulas in DAX:

Optimize Many-to-Many Calculation in DAX with SUMMARIZE and Cross Table Filtering by Marco Russo
Whitepaper: The Many-to-Many Revolution 2.0 by Marco Russo and Alberto Ferrari


Rolled back transactions during SQL Server Mirroring Failover I have two SQL Server 2014 instances setup with High Safety Mirroring for a production environment. I need to apply security patches to these servers on a regular basis which requires a reboot.

My typical process is applying patches to mirror and rebooting. Then triggering a failover to mirror and applying patches to the other server. Rebooting, then failing back.

My issue is, every failover has some uncommitted transactions rolled back and this is really not good for a production environment. I have no idea which transactions were rolled back and what kind of data loss I'm having. But I need to reboot these servers and apply patches. If I have zero data loss tolerance, what are my options?

In FULL SAFETY mode you should be good to do this without data loss if there are no transactions in flight. Are you waiting for the state to change from Synchronizing to Synchronized after that restart? My guess is application users are in there when you are doing the failover, though.

If the applications are still connecting and data is still being modified then there are transactions in flight and that is the price you have to pay. Best to get a window and stop access. Shut down the site or apps accessing SQL, etc.


How do I extend the character limit of a string in management studio The SQL below works to an extend however when I view the output in SSMS tables with a lot of columns appear to be getting cut of in that the string that is generated by the cursor only appears to go to a maximum limit.

    USE [HealthBI]
GO

/*** ENTER VIEW SCHEMA ***/
DECLARE @schema_name AS varchar(MAX) = 'trust'

/*** ENTER CDO TABLE TO REPLICATE ***/
DECLARE @table_name_value  AS varchar(MAX) = 'CDO[_]%'
DECLARE @table_name  AS varchar(MAX)
DECLARE @column_name AS varchar(MAX)
DECLARE @viewsql AS varchar(MAX)

DECLARE csr_view CURSOR FOR
SELECT TABLE_NAME ,
STUFF(( SELECT  ',' +QUOTENAME(TABLE_NAME)+'.'+QUOTENAME(COLUMN_NAME)
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = a.TABLE_NAME
ORDER BY COLUMN_NAME
FOR XML PATH(''),Type)
    .value('text()[1]','nvarchar(max)' ), 1, 1, '') AS value
FROM    INFORMATION_SCHEMA.COLUMNS a
where  TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME like @table_name_value
    and TABLE_NAME NOT LIKE 'CDO_CDS%'
GROUP BY a.TABLE_NAME

    OPEN csr_view
        FETCH NEXT FROM csr_View INTO @TABLE_NAME, @column_name
    SET @viewsql = ''
--  PRINT @TABLE_NAME PRINT @column_name

        WHILE @@fetch_status = 0
        BEGIN

        IF OBJECT_ID('['+ @schema_name + '].[' + @table_name + ']') IS NULL
        SET @viewsql = @viewsql + '
        CREATE '
        ELSE
        SET @viewsql = @viewsql + '
        ALTER '

        SET @viewsql = @viewsql + 'VIEW [' + @schema_name + '].[' + @table_name + ']
        AS SELECT ' + @column_name + '
        FROM [dbo].[' + @table_name + ']'

        IF @schema_name = 'trust' AND @table_name <> 'CDO_MPI'
        SET @viewsql = @viewsql + '
        INNER JOIN [CDO_MPI]
        ON [CDO_MPI].[UNIQUE_ID] = [' + @table_name + '].[CDO_MPI_UNIQUE_ID]
        AND [CDO_MPI].[IS_DUMMY_PATIENT] = ''N''   GO'

        IF @schema_name = 'trust' AND @table_name = 'CDO_MPI'
        SET @viewsql = @viewsql + '
        WHERE [CDO_MPI].[IS_DUMMY_PATIENT] = ''N'''
        PRINT (@viewsql)
--       EXEC (@viewsql)

        FETCH NEXT FROM csr_View INTO @TABLE_NAME, @column_name
        SET @viewsql = ''

        END
    CLOSE csr_view
DEALLOCATE csr_View

change  PRINT (@viewsql) to  SELECT (@viewsql)


Mysql select using regexp I have some user input i am receiving from some forms exactly in this format Names#Age#Gender#City

The problem is,some forms come with incomplete data which is acceptable during the filling of the form which leaves me with data of the form names#Age and the rest of the data is not there.

Since there are many records i want first to process the data with all the four fields with data and i want to use regular expressions to select all those complete records first.

I have tried this query

SELECT text_message from incoming_sms where text_message REGEXP '^([a-z]+$)*[a-z]+$';

but this does not match what i want.

It depends on how strict you want to be, and on what you can expect from the form.

If all that's needed is the presence of 3 #'s, it would suffice to use

SELECT text_message
FROM incoming_sms
WHERE text_message LIKE '%#%#%#%';

If you want to have at least one character in each field, you can use (as you suggested yourself):

SELECT text_message
FROM incoming_sms
WHERE text_message REGEXP '^([^#]+#){3}[^#]+$';

If you know that leading whitespace is trimmed from each field and you want to ensure you have at least one non-whitespace character, use:

SELECT text_message
FROM incoming_sms
WHERE text_message REGEXP '^([^#\s]+[^#]*#){3}[^#\s]+[^#]*$';


Handle location in a SQL database I am creating a cataloging system for rocks that come from all around the world. As part of this system, I need to handle the location of the where the rocks were found.

The problem is that the location needs to contain the country, a sub-item (e.g. State, Province), possibly another sub-item (e.g. County), then maybe a city, and then finally the mine/quarry/forest/etc that it was found in.

I don't think that I should allow the user to enter in the location every time in plain-text, as that runs the risk of multiple spellings for the same location. Also, because there are so many possibilities for the way location is structured (United states would be USA:CALIFORNIA:ORANGE:LOS ANGELES but Canada might be CANADA:BRITISH COLOMBIA:VANCOUVER) I don't know how to effectively store and query this information.

What is the best way to do this?

Are you aware of the "what3words" system??  https://en.wikipedia.org/wiki/What3words

Or, how to address mail from the U.S. to anywhere in the world  - a problem similar to yours - https://blink.ucsd.edu/facilities/services/mail/international/addressing/index.html

I've seen geographical data represented with latitude/longitude or similar coordinates, plus a code representing how specific or accurate the coordinates are.

You could use coordinates together with a textual address.  The text is searchable but the coordinates -are-specific.

I think the proper way at the moment to store arbitrary text with markups for field identities - name of country / county / town etc. - is XML.  A similar effort of your own would be just re-inventing XML.

How does the Smithsonian do it?  Or is that where you're doing this?


SQL Server Management Studio slow opening new windows After upgrading both SQL Server and Management Studio from 2008 to 2012, Management Studio 2012 is very slow when new query windows and dialog boxes are opened.

Even right clicking on tables is slow. There is typically at least a 5 second delay before I can start doing anything within a window. This happens every time, even if I open the same window twice in a row. Why does this happen and how can I fix it?

Other applications establish connections to the database very fast.

Things I have tried that did not help:

A hit on Google where I'd need to modify my hosts file
Resetting SSMS "user-defined settings"
Updating video drivers, turning off hardware acceleration, disabling DirectX
Disabling the Biometric Authentication Service (I don't have it installed).

My computer should be more than fast enough, and I also have 16GB RAM. My hardware should definitely not be a problem. It looks like SSMS is waiting for something - I can operate other programs smoothly while this is happening.

I don't have the opportunity to install the SQL Server 2012 CU1 update because there seems to be some risk of errors installing hotfixes and I can't risk that right now.

SQL Server Management Studio Startup

When Microsoft's SQL Server Management Studio (SSMS) starts it tries to connect the Certificate Revocation List (CRL) of Microsoft:

http://crl.microsoft.com/pki/crl/products/MicrosoftRootAuthority.crl

The underlying .NET components of SSMS are trying to contact the Certificate Revocation List and SSMS is unable to do so. This slows down the overall loading procedure. (15 seconds per certificate apparently)

  Ok so here is what is happening. SSMS has a high percentage of managed code, all of this code is signed when we ship it. At start up (if this setting is checked) the .Net Runtime tries to contact crl.microsoft.com to ensure that the cert is valid(there were some fake certs issued in Microsoft’s name a while back so this is a very valid concern). If there is no internet connection or there is a problem contacting the certificate revocation list server then this will delay SSMS startup.

Reference: FAQ, Why does SSMS take 45s to start up? (MSDN Blog)

  One issue that can cause this problem is that if the server does not have access to the internet, then the .NET framework can’t access the crl.microsoft.com website to verify that the digital signatures used to sign the binaries for managed applications are valid. Each certificate check has a 15 second timeout in the .NET runtime implementation. Depending on what features are installed, this can add up to a minute of startup time for Management Studio.

Reference: SQL Server Management Studio Startup Time (MSDN Blog)

Solutions

You can circumvent part of the issue, by downloading the certificate directly be entering the link into your browser and then importing the certificate to your certificate database

OR -

You can reconfigure your (company's) firewall to allow connections to Microsoft's CRL

OR -

You can reconfigure your personal antivirus/firewall to allow connections to the Microsoft CRL

OR -

You can configure your (company's) firewall to send a timeout faster to your client for requests accessing Microsoft's CRL.

OR -

You can configure IE to no longer "Check publisher's certificate revocation" in the advanced settings.

(See above mentioned blogs 1 and 2 for details)


Same postgres query in two different instances with the same data but with different times I have the same postgres query running in two different instances restored with the same dump file:

one instance in aws rds => https://explain.depesz.com/s/USMO ('PostgreSQL 11.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit')
one instance in compute engine vm in gcp => https://explain.depesz.com/s/LTUL ('PostgreSQL 11.10 (Debian 11.10-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit')

But the query in 1) (40s) is faster than 2) (400s) (execution time calculated in python)
What I tried:

ran without cache (restart the on prem gcp instance)
changed the where clause values
ran the query in different computers
analyze the basics of explain command (same plan and same indexes)

What are the main reasons for this?
My main hypothesis now is the network traffic. How can I test that?
Thanks in advance

both have the same postgres version and hardware configuration
I am not sure, but these times are the first run of each query and cache (maybe) is not involved
traceroute 1) = 13.864 ms / traceroute 2) = 32.469 ms

The difference is shared hits.
Shared hit means it read the blocks from ram (shared buffers).
Check for shared_buffers parameter on both servers.


Not able to connect to local MySql database "only" when connected to VPN on VM Setup:
MySql installed on my Mac using Xammp
VM installed using VMware Fusion running win XP.

I am able to connect to the mysql database through my VM normally.

As soon as I connect to a VPN it stops connecting to the Mysql.

Please let me know how can I make it work so that it connects to Mysql no matter if VPN is ON or OFF.

I'm fairly certain the MySQL not only has users listed by name and password, but also by "where they are coming from".  You may want to check and make sure the user is listed as being allowed to connect from "localhost".


How do I transfer logins from 2008R2 to 2014 with passwords? I've tried Tasks>Generate Scripts... and set 'Script Logins' in the Advanced Scripting Options, but I get this in the output:

/* For security reasons the login is created disabled and with a random password. */

Is there a simple way to script these logins with passwords or another way to transfer logins from a 2008R2 instance to a 2014 instance?

In order to transfer logins, you will need to use the sp_help_revlogin script and procedure.  This will allow you to transfer logins and retain the password from on instance to another.


Determine last usage date of a linked server We have a tonne of linked servers on our database server that we would like to clean up.

Is it possible to determine the last date a linked server was used? e.g. the last time a query was performed through it.

You'll have to setup SQL Profiler to monitor for queries which use the linked server.  There's no DMV that you can easily monitor for usage.


Master-Slave semi circle replication not working in MySQL I didn't know how to describe it so I am calling it semi circle. Here's what I am doing.

Server A
Server B = This is slave of Server A
Server C = This is slave of Server B

When I update something on Server A then it is reflected on Server B. But the same change is not reflected on Server C.

Only when I update something on Server B then change is reflected on Server C.

How do I make it so any changes done on Server A will come to Server B (which is already working) and then it goes to Server C?

EDIT
Upon investigation I found out that when I make some change on Server A then its log file position changes. But when those changes are reflected on Server B then Server B log file position doesn't change. It is because of this reason Server C doesn't know if there has been any change on Server B unless I explicitly change something (insert,update,delete) on Server B.

So is there a way to tell MySQL to increment log file position when Slave is receiving updates from Master?

I think the problem is the following:

Since B is a Slave of A, replication log position changes when something on A is executed and then records the changes to its binlogs. Server B picks up Server A binlog events and executes it.

Why won't Server B just pass it on to Server C ?

Server B need the following option enabled

[mysqld]
log_slave_updates

Whenever you want a server to be a Slave and a Master at the same time, you need log_slave_updates enabled. Here is what the MySQL Documentation says:

  Whether updates received by a slave server from a master server should be logged to the slave's own binary log. Binary logging must be enabled on the slave for this variable to have any effect.

In your case, Server B reads Server A's binlog events as recorded in Server B's relay logs, executes those events in Server B and nothing more.

When you enable log_slave_updates on Server B, then the following happens

Server B reads Server A's binlog events as recorded in Server B's relay logs
Server B executes those events in Server B
Server B records the event it executes in Server B's binlogs
Server C will receive Server B's binlogs and execute them

I mentioned using log_slave_updates a couple of times before:

Sep 09, 2014 : Is it mandatory to enable --log_slave_updates with MySQL Replication using GTID?
Nov 26, 2012 : Replicate MySQL 5.0 Master to 5.5 Slave?

Therefore, when you said

  It is because of this reason Server C doesn't know if there has been any change on Server B unless I explicitly change something (insert,update,delete) on Server B

The explicitly change something becomes implicit when you use log_slave_updates.


Not able to Connect to a named instance remotely I have a server with 2 SQL Server instances, I can connect to the default instance remotely, however I am not able to connect to the named instance.

When I try to connect remotely, I get this error, SQL Server is configured to allow remote connection and the name is correct

Try connecting to 311SQLI01,1430.  If that works, turn on the SQL Browser service.

SQL Browser needs to be turned on for port number translation ( it's off in your screenshot ) and you don't seem to be referencing the specific port in your connection attempts.


Duplicate keys error after switching to MySQL Galera I recently switched from a single MySQL installation to MySQL Galera, running on 3 nodes addressed via HAProxy. Ever since switching I do get randomly "duplicate key" errors reported which theoretically should not be possible as I do check if the dataset is already available.

e.g.:

MySQL Error executing stmt:

        INSERT INTO user_ips
            (user_id,ip)
        VALUES
            ('444079','41.205.7.198, 176.9.19.101')

 Error No:  1062
 Error:         Duplicate entry '444079-41.205.7.198, 1' for key 'user_id'

My code looks like this:

function save_user_ip($user_id){
    global $T62, $link;
    $ip = $_SERVER['REMOTE_ADDR'];
    // do we already know that user by this ip?
    $stmt="
        SELECT ID AS ip_id
        FROM $T62
        WHERE user_id = '$user_id' AND ip = '$ip'
    ";
    $row = db_get_row2($stmt);
    $ip_id = isset($row->ip_id) ? $row->ip_id : "";

    if (!$ip_id){ // ip unknown for that user, save it
        $stmt="
            INSERT INTO $T62
                (user_id,ip)
            VALUES
                ('$user_id','$ip')
        ";
        db_query($stmt);
        $ip_id = db_insertid($link);
    }
    return $ip_id;
}

Running this on a test page it works as intended. On the production server under load it however does not always work. I thought about INSERT IGNORE but would rather not run an insert on all actions as writes are slower and it also triggers a warning.

Running SHOW CREATE TABLE tablename creates following output:
1146 - Table 'tablename' doesn't exist

One possible solution that seems to work at least on development cluster:

// save ip. Will ignore if already known ip
$stmt="
    INSERT IGNORE INTO $T62
        (user_id,ip)
    VALUES
        ('$user_id','$ip')
";
#echo $stmt; exit;
db_query($stmt);
$ip_id = db_insertid($link);
// if ip already known, retrieve it
if (!$ip_id){
    $stmt="
        SELECT ID AS ip_id
        FROM $T62
        WHERE user_id = '$user_id' AND ip = '$ip'
    ";
    $row = db_get_row2($stmt);
    $ip_id = $row->ip_id;
}
return $ip_id;

Not sure how this improves the situation on prod or if it makes things even worse. There might be a more elegant solution.

Thank you in advance for any help on this.

User error.

Since when does an ip look like 41.205.7.198, 176.9.19.101?

444079-41.205.7.198, 1 implies that 41.205.7.198, 1 was what was used as the ip.  That's the first 15 characters, so I guess you have VARCHAR(15).  (Note: this is why the first comment was asking for SHOW CREATE TABLE.)

So, your funny IP got truncated, leading to a user-caused DUP KEY.


replication warning: transactional replication latency alert not working I have set up a predefined replication alert (as outlined here) in SQL Server 2008 and the alert for Replication Warning: Transactional replication latency does not appear to work.  This is triggered by error 14161.

I found a number of posts around the web that indicated this was a bug, but the posts were so old, I'm not sure that it is still the case.

Is this still a broken feature?  If so, can anyone suggest a work-around?

EDIT/ADDITIONAL INFO: I see there are a number of scripts that have been highlighted in similar questions.  To refine my question, I'd like to confirm this is a bug and I am looking for a work around that is rather out-of-the-box... that is, just another way to write the SQL Server Agent alert to get it to work...

As a work around (and to have more content in an alert), I wrote my own warning system that sends an email. http://www.sqlwebpedia.com/content/sql-replication-undelivered-command-count


To reduce recovery time (and save space): Is it possible to ignore some PostgreSQL 13 tables (or schemas) when backing up using Barman? We have some large staging/scratch tables in one of our PostgreSQL databases. The data in them can be automatically regenerated from other databases after recovery of the database, and aren't business critical. Is there a way to ignore those tables when backing up using Barman? They are all in one schema.
This is to reduce recovery time and save space.
Maybe we can use some sort of persistent temporary tables? Tablespaces?
We have the tables because it is convenient when we do ETL. But aren't used by any applications or clients.
Are there other alternatives to do something similar?

Some documentation I've been reading through:
https://www.pgbarman.org/index.html
http://docs.pgbarman.org/release/2.12/

Barman will create online file system backups, and such backups can only be done of the complete cluster.
To backup parts of a database, you must use a logical backup with pg_dump, but then you cannot perform point-in-time recovery.
One way to work around that is to have the tables you don't want to back up be part of a different cluster and access them with postgres_fdw, or store them as “flat files” and use file_fdw to access them.
Be warned, though, that there are performance penalties when querying such foreign tables.


SQL Server Audit file I have faced with some challenge regarding my audit. I need to save my audit events on a file, but file should be in a readable format ( like txt) which will allow to analyze it out of SSMS. XXXXX.sqlaudit format does not allow this. Maybe you know options that will help to export audit events` file to readable format ? Thanks in advance.

SQL Server Audit logs to either a binary file or Windows Application log or Windows Security log.  There are different options for reading the output depending on where you have logged to.  If you are logging to a binary file you could create a simple process that reads it with sys.fn_get_audit_file and outputs to a readable textfile, eg using SSIS, use an 'Execute SQL' task to run the following SQL and pipe the output to a text file:

SELECT *
FROM sys.fn_get_audit_file ( 'X:\SQLAudit\yourAuditName_*.sqlaudit', DEFAULT, DEFAULT );

You will need some way of tracking the files you have imported/exported so a simple table would help there.

A quick google shows someone else has done something similar with Powershell here.


BigInt or Varchar for a large number I won't ever sort / calculate on I need to store users' Facebook IDs in a column in my DB.

The ID always consists of only digits, and can be as large as a number in excess of 500 million. Considering that I won't ever need to do any arithmetic, sorting or use relational operators on the values, what would be a better choice: BigInt or varchar ?

The value will always be unique and never non-existent (null)

The only operation I do is check for the existence of an ID in the database, i.e. the = operator.

varchar adds overhead:

length of the string needs stored (extra 2 bytes IIRC in MySQL) per field and in the index
requires more processing for collation on comparison

Saying that, what does the Facebook API describe the type as? Use the "native" one of course


Calculating Open Days field and stopping with status change I had a question but wasn't sure where I needed to post or view if someone has done this before. I have a dilemma on my hands.

Background Info:
I have been trying to figure out what to do since I have a form that has a field where I use DateDiff to compare the current date to a field that has a measuring date. The difference between dates calculates the days open. I used DateDiff at first but then realize that I needed a way to stop the "Day's Open" when the status changed from a working (1) to a working (0).

Currently, I have a set of statuses (that have to be 0 or 1 for metric purposes and can only have a single 1 at any time) that are either working, dead or sold. Usually when the record is open, working is 1 and the open days field continues to increment. The other two fields are 0's if working is 1. If status changes, working turns to 0 and either dead or sold turn to 1.

My question:
If that was to change (either working turns to 0 and sold or dead turns to 1), how could I get the days open to stop counting days?

I'm sort of new still to access and vb. I am the one with the most experience that was willing to take it on so that they wouldn't be using Excel anymore for the monthly metrics sheets.

Is there a way you can help? This is where I am stuck and its very specific problem that I have not been able to see in any of the forums. Your forums have been really helpful with the other parts of my access tasks! I do appreciate it being available.

Thanks,
PPK

Counting days without explicitly storing the start date would require some kind of scheduled task to count them.  This is not trivial to implement and not recommended.

I would suggest storing the date when opened and then doing a date diff between the start date and the current date to work out the number of days (including the appropriate rounding).  I would suggest that it might be a good idea to also store the closing date.

Edit:

When the item is closed you have two options to work out/read the days opened in the future:

You can store both the start date and the close date and do a datediff between them when you need to know the days opened.
You can store the days open explicitly when you mark the item as closed.

I would recommend 1 as it is more concise.

Edit2:

To fully explain point 1.  Basically you would use the datediff function and compare the start date to the current date, while specifying days (d) as the required unit.

In an SQL select query this would look something like:

select
    datediff('d',table.StartDate,date()) as OpenDays,
    table.Status
from table

The date() function returns the current date.

Microsoft Access Help on DateDiff


What is the best way to add optional update in procedure in transaction? Currently I have a procedure that performs 4 operations in the Transaction:

Update record in table A
Update record in table B
Insert record into table C
Delete record from table D

The first operation is performed only when one of the parameters of procedure (@IsUpdateA) is equal to 1.
So from business perspective it looks If @IsUpdateA = 1, the update of table A has to be performed, while step 2, 3 and 4 are always performed.
The question is whether it is better (in terms of efficiency/execution plan) to split such procedure into two, i.e.

First procedure will always perform steps 1,2,3,4
Second procedure will always perform steps 2,3,4

And @IsUpdateA will be checked in application.
I can not use procedure in procedure (internal requirement).

The optimizer uses the sniffed parameter values regardless of your procedural logic. Imagine your proc code with only the DML statements and the parameters in it.
So there is a risk that when the plan is generated, the values to be used for the UPDATE are nonsense values when @IsUpdateA = 0. From a performance perspective, that is. Think of the WHERE clause and selectivity. I.e., the plan is created for that UPDATE (which isn't executed) is based on values that give you incorrect selectivity calculation and as a result a bad plan.
Or the other way around, of course.
I.e., having two procs can give you better estimates and in the end better plans.


MySQL remote connection: Public vs. private IP address I just went through the obligatory pain of getting MySQL to accept a remote login. Yes, I had to create a user of the form ''@'%', and I did so. Yes, I had to GRANT ALL ON . to my new '@%' user and then flush privileges, and I did so. Yes, I had to open up port 3306 on the server, and I did so. But, trying to access MySQL via HeidiSQL, I continued to have no luck at all.

Cutting to the chase, my struggle was with the bind address. I'm running Ubuntu Linux on an AWS server instance, so I have a public IP address and a private IP address. And of course there's always the MySQL default IP address of 127.0.0.1. That default wasn't allowing me any remote connections, though, so I figured I should try the public IP. But in that case, MySQL would not start. Finally I took a shot and used the private IP address as the bind address, and everything fell into place: I was able to stop and restart MySQL, and I was able to connect from HeidiSQL

But I am totally confused. HeidiSQL addresses this server via its public IP address. But MySQL refuses to bind to the public IP address! (As I said, when I tried, MySQL wouldn't even start.) MySQL has the private IP address as its bind address. What's going on here? Why, to get a remote connection, do I need HeidiSQL targeting port 3306 at the public IP address but MySQL bound to the private IP address?

The explanation here is a straightforward one: You can't bind a listening socket to an IP address that your machine's IP stack isn't aware of.

In AWS (whether EC2 Classic or EC2/VPC) as in many network environments these days, your external IP is not configured in your machine -- it's mapped through an external device (router, firewall) to your machine's private IP via NAT (network address translation).

You can confirm that your server itself has no idea about the external IP:

 $ ifconfig

The simplest solution, of course, is binding to 0.0.0.0 unless you have a specific reason not to.

http://en.m.wikipedia.org/wiki/Network_address_translation


How to select a column to compare two values in it with AND How to select a column to compare two values in it with AND

select distinct c.reg#, a.fname, a.lname, to_char(b.L#) as "L"
  from employee a, driver b, trip c
  where a.e#=b.e#
  and b.l#=c.l#
  and (c.reg#='PKR3344'AND c.reg#='WWF1155')
order by c.reg#;

error no rows selected
I want c.reg# for the two values inclusive

If I understand right you need select records that equals two values. You can look this query like exsample:

SELECT firm_property.firm_id, firm.title
FROM  firm_property INNER JOIN
   firm ON firm_property.firm_id = firm.id
WHERE  (firm_property.property_id IN ('PKR3344', 'WWF1155'))
GROUP BY firm_property.firm_id, firm.title
HAVING  (COUNT(*) = 2)

If you write your tables and treir data we can make the better answer query.


Why is "select join insert" so much faster than "loop insert"? MySQL docs state that the time required for inserts has these approximate proportions:

  Connecting: (3)
  Sending query to server: (2)
  Parsing query: (2)
  Inserting row: (1 × size of row)
  Inserting indexes: (1 × number of indexes)
  Closing: (1)

In other words, if we use stored procedures to do inserts into a table without indexes, the deciding factor for the time required would simply be:

  Inserting row: (1 × size of row)

So I test with a stored procedure that made 2 million inserts:

create table t(i int)engine innodb;
delimiter $
    create procedure f()begin
        while @a<=2000000 do
            insert t select null;
            set@a=@a+1;
        end while;
    end$
delimiter ;

set@a=1;
begin;call f;commit;drop procedure f;
select count(*)from t;drop table t;

The query call f; takes 29.44 seconds.

Rolando suggests (below) to use a prepared statement, so I updated the stored procedure to use prepare:

create table t(i int)engine innodb;
delimiter $
    create procedure f()begin
        while @a<=2000000 do
            execute e; # # # # # # # # # # # # # # changed insert statement
            set@a=@a+1;
        end while;
    end$
delimiter ;

set@a=1;
prepare e from'insert t select null';  # # # # # # added prepare statement
    begin;call f;commit;drop procedure f;
deallocate prepare e;  # # # # # # # # # # # # # # added optional deallocate prepare statement
select count(*)from t;drop table t;

However, the query call f; now still takes 30.27 seconds.

It seems like there's no way to beat raw "begin + loop insert + commit" (unless we resort to file loading using load data infile).

However, if we insert using select joins, it only takes 6.28 seconds:

create table t(i int)engine innodb;
insert t select null
from(
    select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
)`10`
join(
    select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
)`100`
join(
    select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
)`1000`
join(
    select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
)`10k`
join(
    select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
)`100k`
join(
    select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
)`1m`
join(
    select 1 union select 2
)`2m`;
select count(*)from t;drop table t;

I'm using MySQL (though I suspect this isn't a MySQL-specific issue).

Why is "select join insert" so much faster than "loop insert"?

What's the explanation for this oddity?

QUERY #1
Each time you do an INSERT, you are doing this under the hood
SET @sql = 'insert t select null';
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

Within the stored procedure, you fully parse, compile, execute and deallocate structures for the prepared SQL statement 2 million times.
QUERY #2
Running insert t select null from(, you fully parse, compile, execute and deallocate structures for the prepared SQL statement ONCE !!!
The execution of the Cartesian joins is really a self-contained atomic operation. Though it does perform iterations ( O(n2) running time ) to form the result table, it is not tethered by creating/destroying query parsing structures with each iteration as you are doing in the stored procedure (despite an O(n) running time). The execution plan of the single INSERT may be uglier to behold, but will surely run faster.
The inline queries are most likely memory tables. The resulting join could be a temporary MEMORY table or temporary MyISAM table. Either way, the formation of the result set is a single operation.
CONCLUSION
Inserting 2 million rows on a single INSERT is always faster that than doing an INSERT of a single row 2 million times. In principle, this would be more evident if you connected, inserted and disconnected. Connecting once beats connecting 2 million times (See my post How costly is opening and closing of a DB connection?)


database sharding on mysql I need to do database sharding(horizontal) based on MySQL. My database schema is as following:

I have tables A,B,C
A,B is Global(which need not to be sharding)
C have huge data so it need to be sharding to achieve write scalability.
I will have several sharding,each contains one copy of A,B, and a subset of C.

My questions are:

How could i keep A,B on all sharding consistent,i.e, replicate A,B on all sharding so that they should have exactly same data. Or just store the Global Tables on single DB and cache via memcached?
How to do high availability for each sharding,i.e, if one sharding  crashed due to unknown reasons how could i do fail over.Through MySQL backup or master-master AP mode replica?
How to do load balance for Global tables A,B on the multiple shardings?

Note that i will not use MySQL read replicas in this case.

It is possible for you to do this yourself rather than using commercial products like dbShards or ScaleBase but you will end up building the same thing and it isn't trivial.

To answer your question though, it makes sense to replicate A and B across all shards so you either need your application to duplicate the writes across all shards or you will need some replication technology to do this. I don't think you can easily configure MySQL replication to do this.

To load balance you will need your client to randomly choose a shard when running queries against these tables, just like you'll need your client to determine which shard to use when accessing table C.

For HA you will want to have master/slave setup for each shard so that you can failover in the case of losing a master shard.

Regards,

Andy Grove
Chief Architect
http://www.dbshards.com


Benefit to keeping a record in the database, rather than deleting it, for performance issues? So I have a client that I am building a Rails app for....I am using PostgreSQL.

He made this comment about preferring to hide records, rather than delete them, and given that this is the first time I have heard about this I figured I would ask you guys to hear your thoughts.

  I'd rather hide than delete because deletions in tables eventually lead to table index havoc that causes queries to take longer than expected (much worse than Inserts or Updates).  This won't be a problem in the beginning of the site (it gets exponentially worse over time), but seems like an easy issue to never encounter by just not deleting anything (yet) as part of the "everyday" web application functionality.  We can always handle deletions much later as part of a Data Optimization & Maintenance process and re-index tables in that process on some (yet to be determined) scheduled basis.

In all the Rails apps I have built, I have never had an issue with records being deleted and it affecting the index.

Am I missing something? Is this a problem that used to exist, but modern RDBMS products have fixed it?

In PostgreSQL, an UPDATE is implemented as marking the current row version as dead, followed by inserting a new version of the row with the new values.
The entire row is recreated no matter how many columns are updated.
Additionally, if it leads to an index change, a new row version is also created in each concerned index.
On the whole, it's pretty heavy (compared to overwriting bytes in a file).

By contrast, DELETE is implemented as marking the row version as dead, both in data and indexes. Since dead in this context means no longer visible to this transaction and the future transactions (unless it's rolled back), this is a bit similar to your hidden flag, except it's more efficient than an UPDATE since there's no new version of the row.

At some point, VACUUM will kick in, presumaby by way of autovacuum, and
mark the dead row as reusable space. Quoting the documentation:

  In PostgreSQL, an UPDATE or DELETE of a row does not immediately
  remove the old version of the row. This approach is necessary to gain
  the benefits of multiversion concurrency control (MVCC, see Chapter
  13): the row version must not be deleted while it is still potentially
  visible to other transactions. But eventually, an outdated or deleted
  row version is no longer of interest to any transaction. The space it
  occupies must then be reclaimed for reuse by new rows, to avoid
  unbounded growth of disk space requirements. This is done by running
  VACUUM.

The bloat caused by high-frequency or high-volume writes, both UPDATEs and DELETEs, is one of the things that a postgres DBA should indeed keep an eye on, but there's no apparent reason DELETE would cause more bloat. In fact, based on the implementation choices, it's probably the contrary.

In any case, periodic REINDEX are worthwhile. See Routine reindexing.


Are there any security implications with enabling "Data Access" on a sever? I'm trying to run an OPENQUERY statement within a local server but am getting the following error:

  Server 'MyLocalServer' is not configured for DATA ACCESS.

If I set MyLocalServer's Data Access option to true, what are the potential security implications?

Also can this affect anything with an AlawaysOn AG that MyLocalServer is a part of?

Enabling Data Access is not considered a finding in the CIS Microsoft SQL Server 2016 Benchmark but that doesn't mean it couldn't be a problem if it's poorly implemented.

If you are querying data on another server limit the privileges granted to the remote user.