What is the impact of temporarily setting ALLOW_PAGE_LOCKS ON The MSDN ALTER INDEX page says "An index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF."

I'm modifying our index reorg/rebuild process to be sensitive to indexes that have ALLOW_PAGE_LOCKS turned OFF.  When I detect the index has the option turned off, I perform the following steps:

BEGIN TRANSACTION
BEGIN TRY
    ALTER INDEX [MyIndex] ON [dbo].[MyTable] SET (ALLOW_PAGE_LOCKS = ON);
    ALTER INDEX [MyIndex] ON [dbo].[MyTable] REBUILD
        WITH (
            SORT_IN_TEMPDB = ON
            , MAXDOP = 1
            , PAD_INDEX = OFF
            , FILLFACTOR = 90
            , ALLOW_PAGE_LOCKS = OFF
            , ALLOW_ROW_LOCKS = ON
            , IGNORE_DUP_KEY = OFF
            , DATA_COMPRESSION = NONE
            );
    ALTER INDEX [MyIndex] ON [dbo].[MyTable] SET (ALLOW_PAGE_LOCKS = OFF);
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

Presuming the database is not actually "in-use" at the time this rebuild process starts, does this pattern present any issues?  Does setting PAGE LOCKS ON cause any side-effects?

Note - the options, such as FILLFACTOR = 90 are taken from the current definition of the index just prior to starting the reorg/rebuild process, and a decision is made whether to reorg or rebuild based on the number of pages in the index and the degree of fragmentation.  This question is NOT about these settings being "good" or "bad", it is ONLY about the impact of temporarily turning ALLOW_PAGE_LOCKS to `ON', to allow the index to be defragmented.

Also, I realize the ALTER INDEX ... REBUILD statement has ALLOW_PAGE_LOCKS = OFF, while the very next statement also turns it off... this is to allow the ALTER INDEX ... REORG statement, which does not have any options present, to run properly, while also resetting the ALLOW_PAGE_LOCKS setting off once it's completed.

I've wrapped the alter index statements in a transaction for only the indexes that have ALLOW_PAGE_LOCKS OFF so that if anything goes wrong during the rebuild process the index is not modified.

Also, note, we are not using Ola Hallengren's solution due to corporate policy.

EDIT:

From @Shanky's comment, I've confirmed that ALTER INDEX ... REBUILD does NOT require ALLOW_PAGE_LOCKS ON; this option is only required when reorganizing an index.  I've modified my code to do the transaction-wrapping shown above only when performing a reorganize where the index already has ALLOW_PAGE_LOCKS turned off.

Presuming the database is not actually "in-use" at the time this rebuild process starts

Assuming what you are presuming, I don't what side-effect it could have, outside of the obvious allowing for index REORG to happen :-).

Of course, I have no proof, but not sure what proof to look for that nothing bad will happen when nothing is happening in the DB outside of this REORG operation.

I am also not aware of there being any analogy here to Foreign Keys being untrusted, and hence not considered by the Query Optimizer, if created using the WITH NOCHECK option to skip validation (and not trusted until altering using WITH CHECK).

Regarding the notion of index property vs structure of the index pages: I would say that this could be answered by altering a large index to just turn this option ON and OFF. If it happens instantly, then it is not writing the change to all pages. And, it is also probably a moot point since even if there is a change, it will be handled via the ALTER INDEX [MyIndex] ON [dbo].[MyTable] SET (ALLOW_PAGE_LOCKS = OFF); and completed before the COMMIT. Still, I can't imagine this being anything other than a simple index property, since it can't change on a per-page basis. Of course, this can also be seen by using DBCC PAGE to view the contents of an index before and after you make this change.


How to handle update or insert situations From time to time my stored procedures looks like

create procedure handle_data
    @fk int,
    @value varchar(10)
as
begin
    if exists (select * from my_table where id = @fk)
    begin
        update my_table
        set value = @value
        where id = @fk
    end
    else
    begin
        insert into my_table (fk, value)
            select @fk, @value
    end
end

Probably there is fault in design of app which calls this stored procedure.
Should I avoid making apps which do same stored procedure and methods for inserting new data and also updating old ones?

Is there better way to achieve updating or inserting data in one approach?

I am using SQL Server 2005 / 2008.

A slightly more efficient way (which will do at worst one seek/scan instead of two against the existing data):

UPDATE dbo.whatever SET ... WHERE key = @key;
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.whatever ...
END

MERGE may be tempting, however there are a few reasons I shy away from it.

the syntax is daunting and hard to memorize
you don't get any more concurrency than the above approach unless you intentionally add specific locking hints
there are many unresolved bugs with MERGE and probably many more that have yet to be uncovered


MongoDB - Split brain with 3 DC I have mongoDB in 3 DC's.

DC1 - 3 Nodes  with priority 300
DC2 - 3 Nodes  with priority 200
DC3 - 3 Nodes  with priority 100

Questions :-

Say, N/W is broken b/w DC1 & DC2. But, still DC1 is able to communicate with DC3 and DC2 is also able to communicate with DC3. But DC1 will not be able to DC2.          Will it create 2 Primaries here, like 1 PRIMARY from DC1 and 1 PRIMARY from DC2?
What if there is no Priorities? Only DC3 will become PRIMARY?

Thanks

Will it create 2 Primaries here, like 1 PRIMARY from DC1 and 1 PRIMARY from DC2?

Assuming this deployment represents a single replica set, the expected outcome is either 1 primary or no primary.  In the event of network problems, a primary can only be sustained in the partition with a strict majority of voting members that are able to communicate with each other.

While replica sets can have up to 50 members in total, there is a maximum of 7 voting members so you cannot have 9 nodes with priorities.

If you have the maximum of 7 voting members distributed across your data centres, you will need at least 4 of those members healthy in order to elect (or sustain) a primary. If the current primary cannot see a majority of voting members, it will step down and become a secondary.

  DC1 - 3 Nodes with priority 300 DC2 - 3 Nodes with priority 200 DC3 - 3 Nodes with priority 100

If your intent is to prefer DC1 and DC2, that would likely mean having 3 voting members in DC1 and DC2 with a single voting member in DC3. With this configuration the primary would always be in DC1 if available, but preferentially in DC2 in the event DC1 is isolated. Majority votes could be achieved with all voting members in any two DCs available, or any degraded scenario with at least 4 voting members communicating in the replica set.

A primary would be unlikely in DC3, but possible transiently if DC1 is isolated and there is replication lag between DC1 and DC2 where the voting member in DC3 (with lowest priority) has a more current oplog than all eligible secondaries in DC2 (with medium priority). In this case, as soon as a higher priority member in DC2 has caught up with the lower priority primary in DC3, an election will be called so the higher priority member can be elected.

For more information, see:

Replica Sets Distributed Across Two or More Data Centres (MongoDB manual)
MongoDB Multi-Data Centre Deployments (White paper from MongoDB, Inc)


ERROR: ORA-01031: insufficient privileges After installing Oracle and opening Command Prompt, I typed in
sqlplus "/as sysdba"
and got an error message
ERROR:
ORA-01031: insufficient privileges
and then it asks me to enter the user-name.
I'm using my own personal computer and although the name given after C:\Users\ is not called "Administrator", it's my username when I used to log in to this computer and since I'm the only one using the computer I'm assuming it's the same as "Administrator" and when I opened account settings it says administrator under my account name.

Microsoft Windows [Version 10.0.17134.228]
(c) 2018 Microsoft Corporation. All rights reserved.

C:\Users\linus>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 20 10:56:53 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges

Enter user-name:

What might be the cause of this and what can I do to solve it? Thanks!

since I'm the only one using the computer I'm assuming it's the same as "Administrator"

That's not enough to be an Administrator.

Make sure that your user belongs to ORA_DBA OR ORA_<%ORACLE_SID%>_DBA OS group on Windows.

As well check the value set for SQLNET.AUTHENTICATION_SERVICES parameter on sqlnet.ora(%ORACLE_HOME%\network\admin) file. NTS should be included in the value.

SQLNET.AUTHENTICATION_SERVICES = (NTS)


Dictate edition during unattended install? I am creating an automated build of SQL Server installation which is run from a batch file. Is there any way to change the registry, or other method, of deciding which SQL Server edition (Either Enterprise or Standard) will be installed, or are the two versions still separate builds?

If they are separate builds, I guess I could get around it, by having a "master" batch file call an appropriate "child" batch file, but this would mean having a folder which contains both sets of binaries, which I rather avoid.

Per my answer here, the installation media contains every edition, and it's the licence key that dictates which licenced edition is available. The ISO you get from MSDN comes with a key for the edition specified on MSDN, but the key can be replaced with any valid key, as it's in a configuration file.

If you need to deploy an arbitrary licenced edition, you'll need to collect a set of licence keys for each edition you want to deploy, then use conditional logic in your scripts to use whichever one is appropriate.

The main piece you're missing, though, is to perform an edition upgrade. This will take the default install from Enterprise Evaluation to the edition defined by the licence key provided.

Here's the relevant part of the script I used in such a deployment, that uses the licence key that was provided with the installation media (presumably, you'll want to modify that part):

"C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe"
    /ACTION="Upgrade" /QS /CONFIGURATIONFILE="%installMediaPath%\x64\DefaultSetup.ini"
    /INSTALLMEDIAPATH="%installMediaPath%\x64\Setup" /INSTANCEID="MSSQLSERVER"
    /INSTANCENAME="MSSQLSERVER" /IACCEPTSQLSERVERLICENSETERMS

Note the presence of the user-defined variable %installMediaPath%, which I assume you'll have in your script. This script was written for 2008 R2, but I think it should work with 2012 (untested).


Select all before first break I have a table with three columns: id, sort, name in PostgreSQL 9.5 database.

I need to select all rows starting from sort=1 and before the first break (ordered by sort).

For example I have:

id | sort | name
1  |   1  | 'ss'
2  |   2  | 'ss'
3  |   3  | 'ss'
4  |   4  | 'ss'
5  |   7  | 'ss'
6  |   8  | 'ss'

I want to select rows with id=1,2,3,4.

As you can see, there is a break in the sort sequence (4-7). So I only need the rows before this break.

How can I do this?

This will work for the special case:

SELECT *
FROM your_table
WHERE sort < (SELECT MIN(t.sort)
              FROM your_table AS t
              LEFT JOIN your_table AS t2
                  ON t.sort = t2.sort+1
              WHERE t2.sort IS NULL
                AND t.sort > (SELECT MIN(sort) FROM your_table))


Does the @raise_snmp_trap do anything in @sp_add_alert? I have a request from a client to incorporate SNMP Traps as the method for alerting in SQL Server. The only way I have found to do this is by using [dbo].[sp_add_alert] found in the [msdb] database.

-- Add Message in sysmessages to be used for the alert
EXEC sp_addmessage
 @msgnum = 2624470,
 @severity = 1,
 @msgtext = 'A DEV GOT SYSADMIN, RUN!',
 @lang = 'us_english',
 @with_log = 'TRUE'

-- Create an Alert for a user-defined message in sysmessages
EXEC msdb.dbo.sp_add_alert
 @name = N'Alert_For_UserDefined_Message',
 @message_id = 2624470,
 @severity = 0,
 @enabled = 1,
 @include_event_description_in = 1,
 @raise_snmp_trap = 1,
 @job_id = N'00000000-0000-0000-0000-000000000000'

 -- Raise error to trigger the Alert
 RAISERROR (2624470,-1,-1)

However, when I actually raise the error, I only receive the actual Message located in sysmessages.

Now, I'm not too familiar with SNMP Traps, but I feel as though there are some settings that would need to be defined; whether that be at the time of creating the alert or as settings in the server configuration.  Am I missing something here?

I came across this forum thread and all I gathered from it was that after version 6.5 this feature stopped doing anything, but there is no documentation to support its lack of functionality.

msdb.dbo.sp_add_alert calls msdb.dbo.sp_add_alert_internal, which in turn writes values into the msdb.dbo.sysalerts table.

The sysalerts table insert looks like this:

INSERT INTO msdb.dbo.sysalerts
         (name,
          event_source,
          event_category_id,
          event_id,
          message_id,
          severity,
          enabled,
          delay_between_responses,
          last_occurrence_date,
          last_occurrence_time,
          last_response_date,
          last_response_time,
          notification_message,
          include_event_description,
          database_name,
          event_description_keyword,
          occurrence_count,
          count_reset_date,
          count_reset_time,
          job_id,
          has_notification,
          flags,
          performance_condition,
          category_id)
  VALUES (@name,
          @event_source,
          @event_category_id,
          @event_id,
          @message_id,
          @severity,
          @enabled,
          @delay_between_responses,
          @last_occurrence_date,
          @last_occurrence_time,
          @last_notification_date,
          @last_notification_time,
          @notification_message,
          @include_event_description_in,
          @database_name,
          @event_description_keyword,
          @occurrence_count,
          @count_reset_date,
          @count_reset_time,
          ISNULL(@job_id, CONVERT(UNIQUEIDENTIFIER, 0x00)),
          @has_notification,
          @raise_snmp_trap,
          @performance_condition,
          @category_id)

The @raise_snmp_trap variable is populated and inserted into the table in the flags column.  The documentation for dbo.sysalerts, at Microsoft Docs, says the flags column is "Reserved."

Since there appears to be no way to access SNMP properties, such as the "community name", "trap destination", etc through the user interface, I would guess SNMP is no longer supported.

In fact, according to Sean Gallardy - Microsoft (Premier Field Engineer):

  ...the definite answer is support was removed in 2000. Yes, at one point it existed but it no longer exists and even support when it existed was dubious at best. All extended stored procedures and associated DLLs were removed.


Is there any way to trace optimizer's work in DB2? Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query, is there anything in DB2 through which I can get the information like, which plans the optimizer considers, the cost of each plan?

I don't know of a way to trace all the plans the optimizer considers. Obviously the EXPLAIN statement gives you the plan the optimizer eventually selects (and the re-written statement), but that doesn't tell you anything about the ones it doesn't select.

One way to get a little more info might be to set CURRENT QUERY OPTIMIZATION to different values (the default is 5), run EXPLAIN on the statement, then set it back to the default (5) and run EXPLAIN again.

If you get different plans with the other optimization levels, this may provide a clue as to the paths DB2 is taking in optimizing your statement (of course, if you get the same plan, it doesn't tell you anything!). I would suspect that the simpler the query the more likely you are to find no difference (so conversely this technique would work better with more complex statements).


SQL Server index reorg While index rebuild will require at least same amount of space in the data file as current index index (as a copy of the index will be created)

My reading on SQL Server BOL related to reorg it only involves leaf level, and only needs a single 8KB Page,

Can anybody explains how reorg works (internals) in simple technical terms..?

This is a précis of the relevant section in the SQL Server 2008 internals book.

SQL Server first acquires an IX lock on the index (or index partition).

The reorganization then (assuming the LOB_COMPACTION option is not OFF) compacts any LOB pages belonging to the index by moving pages out of low density uniform extents (less than 75% / 6 pages full) into other uniform extents with free space.

Then it moves onto the leaf pages of the index.

The simplified description in the book is that it first  determines the first logical page and the first physical page. If different it takes X locks out on both pages and swaps them using one additional new page as a temporary storage area. Then it does the same for the second physical/logical pair and so on.

It is somewhat more complicated than that however. The book mentions that it also looks at a sliding window of eight logically consecutive pages and determines whether rows can be moved around between these pages to allow a single page to be emptied and removed (while taking into account the index fillfactor).

As re-organization does not allocate new pages to the index it cannot reapply the fill factor if the pages are fuller than required however. Additionally no defragmentation is carried out for pages in mixed extents.


MySQL Table not repairing Table info:

Database name: user_motiva
Table name: wp_options.frm  wp_options.MYD  wp_options.MYI  wp_options.TMD

when I do a mysqlcheck -r --all-databases it gets hung on that table even if you let it sit all day.

Is there anther way to fix/repair/recover that table?

Should I use myisamchk? I saw something like:

shell> myisamchk --recover City

My config on a 16GB ram box

 cat /etc/my.cnf
[mysqld]
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
skip-networking
max_connections = 500
max_user_connections = 20
key_buffer = 512M
myisam_sort_buffer_size = 64M
join_buffer_size = 64M
read_buffer_size = 12M
sort_buffer_size = 12M
read_rnd_buffer_size = 12M
table_cache = 2048
thread_cache_size = 16K
wait_timeout = 30
connect_timeout = 15
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
low_priority_updates=1
concurrent_insert=ALWAYS
log-error=/var/log/mysql/error.log
tmpdir=/home/mysqltmp
myisam_repair_threads=4
[mysqld_safe]
open_files_limit = 8192
log-error=/var/log/mysql/error.log

[mysqldump]
quick
max_allowed_packet = 512M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

and could this have happened because of a crashed table from doing killall -9 mysqld because it would not shutdown and restart?

EDIT:

root@server [/var/lib/mysql/user_motiva]# myisamchk -e *.MYI
Checking MyISAM file: wp_options.MYI
Data records:    1827   Deleted blocks:       3
myisamchk: warning: 3 clients are using or haven't closed the table properly
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check records and index references
MyISAM-table 'wp_options.MYI' is usable but should be fixed
root@server [/var/lib/mysql/user_motiva]# myisamchk --safe-recover wp_options.MYI
- recovering (with keycache) MyISAM-table 'wp_options.MYI'
Data records: 1827
myisamchk: error: Can't create new tempfile: 'wp_options.TMD'
MyISAM-table 'wp_options.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag
root@ns2 [/var/lib/mysql/user_motiva]# myisamchk -o -f wp_options.MYI
- recovering (with keycache) MyISAM-table 'wp_options.MYI'
Data records: 1827

Does this mean that it is now fixed? If so how do I move it back? (this was done on a different server)
Is there a way to maybe bring MySQL down on the main server and run a command to fix all the files?

could this have happened because of a crashed table from doing killall -9 mysqld because it would not shutdown and restart?

Absolutely.  If you kill -9 mysqld and you're using MyISAM tables, you're almost guaranteed to crash some tables unless the server is really, really, really not busy... although the definition of a "crashed" table isn't as catastrophic as it sounds in all cases... it only means, at the simplest level, that the table is not in the state that it should have been in had the server closed it properly.

  Is there anther way to fix/repair/recover that table?

Have you been through the steps covered in How to Repair MyISAM Tables?

Also, consider these things, which may give you some ideas that could get you on a faster path to recovery if they are not familiar concepts:

With MyISAM tables, everything is in the files.  You could literally take your wp_options.* files (assuming for the sake of discussion that they were not crashed), copy them into a database directory on another MySQL server, SHOW TABLES and you would find that your table is visible and available on that other server.  For future recovery, if you had a slave server, you could shut it down and copy this table's files directly from there back to the master and be back online.  (This is only true for MyISAM and ARCHIVE, not InnoDB, but by contrast, InnoDB tables are extremely immune from this sort of corruption due to a hard/forced shutdown).
The myisamchk utility is not technically a part of the actual MySQL Server itself -- it is a standalone utility built from many of the same underlying libraries, but can be used on a different computer, even without MySQL server installed.  The myisamchk utility has various capabilities and levels of aggression.
You could, therefore, stop MySQL, copy the table files to a different machine -- one with a nice speedy SSD, for example, could make a big difference in recovery time -- and repair the files with myisamchk, then (with the MySQL server stopped again) copy the table files back.  If you have other things running on your MySQL server, you could bring those other things back online while not bogging the server down with this repair.

By contrast, mysqlcheck is nothing but a simple program that connects to your MySQL server using the client libraries and...

  ...uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user.

...so while the two utilities use many of the same methods to repair the tables, they are also fundamentally very different.


cannot see logins subfolder Forgive me for my lack of dba experience but I can't find the answer anywhere.

I'm working remotely on a third party to a client that asked for a login, I created the login but there's problems with it.

When I decided to modify the login, I remote in and I can't see the logins subfolder under security, it's replaced with a users subfolder.

I'm using the same windows authenticated login I used on-site. So either there's some restrictions to remoting in, or the client's dba changed something.

Can someone help point me in the right direction?

If you're talking about Object Explorer within SQL Server Management Studio (SSMS) then it sounds like you've drilled in too far to the tree-view.

Server principals (Logins) can be found under:

Server Name -> Security -> Logins

You got to Users because you did the following:

Server Name -> Databases -> YourDatabaseName -> Security -> Users


When is a result returned deterministic and when not? if you execute a statement like this:

select first field1, field2 from exampleTable where field1 = '1';

then you get a warning like this:

  The result returned is non deterministic SQLCode=122

So far it's OK, because in this case you need an "order by" clause here.

But when you use the same statement inside of a BEGIN END block putting the values into some declared variables,

BEGIN
declare varField1 varchar;
declare varField2 varchar;

select first field1, field2 into varField1, varField2
from exampleTable
where field1 = '1';

END

the warning does not come up!
Why?

Is the "order by" clause not needed here? Is this statement suddenly deterministic?...

(using SQL Anywhere 12)

Rows are not returned in any specific order in SQL (in reality they are likely to be in the same order most of the time) so without an ORDER BY any of the rows could be returned first. So it is non deterministic because this first record can change each time you run the query.

Wrapping it in a block doesn't make the answer deterministic, it just hides the warning.


Why is sp_Blitz executing for more than 5 hours Screenshot
I am running sp_Blitz on my SQL Server 2014 with 93 databases. It has been running for more than 5 hours and still won't complete. I am able to run sp_BlitzWho,sp_BlitzIndex,sp_BlitzFirst and sp_Blitzbackup and they all take about 2-3 minutes to complete.

I wonder why is it (sp_Blitz) running too long and still not providing any results.

In your screenshot, you're running sp_WhoIsActive. You can click on the query text in blue:

And that'll tell you which query is running slow, and might give you some insight into why. You can also read the documentation, which mentions to try @CheckUserDatabaseObjects = 0 as a parameter to go faster.


Adding PRIMARY KEY and IDENTITY attributes to SQL Server 2008 tables An application is using an SQL Server 2008 database that has ID columns, but these columns are not primary keys and have no IDENTITY attribute.

What I need to do now is adding the PKs and IDENTITY attributes. However, old IDs must be preserved.

Also, I need to do this with SQL commands (no GUI).

Problems

I tried all the following solutions, with no success.

I can't directly INSERT/UPDATE an IDENTITY value
I can't disable IDENTITY constraint for more than one table simultaneously
I can't copy data with ALTER TABLE ... SWITCH TO (it doesn't work if origin table has not PK and target table has a PK)
I can't use sequences instead of IDENTITYs for primary keys, because 2008 doesn't support sequences
The following doesn't work either:

SET IDENTITY_INSERT mydb.dbo.AZIENDE_NEW ON;

INSERT INTO mydb.dbo.AZIENDE_NEW
    SELECT *
    FROM mydb.dbo.AZIENDE_OLD;

I get error

  Msg 8101: An explicit value for the identity column in table
  'mydb.dbo.AZIENDE_NEW' can only be specified when a column
  list is used and IDENTITY_INSERT is ON

This seems to me illogical. IDENTITY_INSERT should be ON for that table...

I'm open to other solutions, but I have no more ideas.

The easiest way to do this is to use the SSMS GUI, right click the designer and use "Generate Scripts". That generates you a table rebuild that preserves data and makes the schema changes for you.

If you care about performance you need to use the SWITCH technique. Make the schema of the two tables compatible with switch, then switch, then alter the schema back. That way you can add the IDENTITY property without (or with reduced) data movement.

  SWITCH from a table with no PK to a table with a PK

Yes, that doesn't work. Create a compatible table for use with switch. You can switch heaps just fine, but both sides need to be heaps.

You can write the rebuild script yourself.

Create a table that has the desired schema
IDENTITY_INSERT ON on that table
INSERT...SELECT the data over to that table
DBCC CHECKIDENT(RESEED) the temp table
Delete the original table and rename the temp table to have the original name


SQL Server : execute stored procedure from another stored procedure I have two stored procedures.

Proc1 executes a SQL statement and results a data table as a result set.
I need to use the datatable in a select SQL that resides in proc2 as a datasource. like,

SELECT Col1
FROM (Exec Proc1)

How can I do it? This syntax here shows error in SQL Server.

you could store your result in a Table Variable

DECLARE @TableVar TABLE (col1 varchar,col2 int,...)
insert into @TableVar
exec (Proc1)

select Col1 from @TableVar


Use a scalar hashing function in a computed column - non-deterministic? I have a scalar function

select [dbo].[fn_get_hash_fields] ('blah','dbo')

to which is passed a table and a schema
and which returns an expression representing a concatenation of all column values in each row.  For example, if the table had the 4 columns below, the function would return (CR's added for readability):

ISNULL(RTRIM([string1]),'')
+ISNULL(RTRIM(CONVERT(varchar(10),[number1])),'')
+ISNULL(RTRIM(CONVERT(varchar(10),[number2])),'')
+ISNULL(RTRIM(CONVERT(varchar(10),number3])),'')

Using this function, I then want to add a computed column that will store a 32 byte hash value representing all the values in each row:

alter table dbo.blah add [checksum] as
(substring(lower(convert(varchar(32), HASHBYTES('MD5',
[dbo].[fn_get_hash_fields] ('blah','dbo')
),1)),3,32))

The column is added, but the hash value is exactly the same for each row.

How do I convince SQLS that the function is non-deterministic and make the function evaluated for each row, computing different hash values?

I know that in the computed column I can simply use the expression returned by the function, rather than a call to the function, and that works.  But its not dynamic.  If I add a column to the table, it won't be represented in the hash until I drop the computed column and add an updated one that takes the new column into account.

Is there a way around this?
Is there a better approach to store a hash in each row built from all the columnar values in that row?

Here is the function:

CREATE function [dbo].[fn_get_hash_fields] (@p_table_name
VARCHAR(100),@p_schema_name VARCHAR(20))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @szSqlString as varchar(max)
SET @szSqlString = ''
      SELECT @szSqlString = @szSqlString +
            CASE DATA_TYPE
                    WHEN 'int'           THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '])),'''')'
                    WHEN 'tinyint'       THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '])),'''')'
                    WHEN 'smallint'      THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '])),'''')'
                    WHEN 'bigint'        THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '],112)),'''')'
                    WHEN 'datetime2'     THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '])),'''')'
                    WHEN 'datetime'      THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '],112)),'''')'
                    WHEN 'smalldatetime' THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '],112)),'''')'
                    WHEN 'date'          THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '],112)),'''')'
                    WHEN 'bit'           THEN 'ISNULL(RTRIM(CONVERT(varchar(1),[' + COLUMN_NAME + '])),'''')'
                    WHEN 'decimal'       THEN 'ISNULL(RTRIM(CONVERT(varchar('+ CONVERT(varchar(2),NUMERIC_PRECISION) +'),[' + COLUMN_NAME + '])),'''')'
                    WHEN 'numeric'       THEN 'ISNULL(RTRIM(CONVERT(varchar('+ CONVERT(varchar(2),NUMERIC_PRECISION) +'),[' + COLUMN_NAME + '])),'''')'
                    ELSE                      'ISNULL(RTRIM([' + COLUMN_NAME + ']),'''')'
                 END + '+'
            FROM   INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = @p_schema_name
            and TABLE_NAME = @p_table_name
            AND (COLUMN_NAME != 'HASHKEYID')
            AND [DATA_TYPE] NOT IN ( 'TEXT', 'IMAGE')

    RETURN LEFT(ISNULL(@szSqlString, ''),LEN(@szSqlString)-1)
END

How do I convince SQL Server that the function is nondeterministic and make the function evaluate for each row, computing different hash values?

SQL Server marks the function nondeterministic, because it accesses data in system tables:

SELECT
    UserDataAccess = OBJECTPROPERTYEX(FN.ObjectID, 'UserDataAccess'),
    SystemDataAccess = OBJECTPROPERTYEX(FN.ObjectID, 'SystemDataAccess'),
    IsDeterministic = OBJECTPROPERTYEX(FN.ObjectID, 'IsDeterministic')
FROM (VALUES(OBJECT_ID(N'[dbo].[fn_get_hash_fields]', 'FN'))) AS FN (ObjectID);

Nevertheless, the function actually does return the same value for each row in the table, because nothing in the function's query depends on anything in the source row. SQL Server does execute the function for each row (something to be avoided, in general for performance reasons), but each execution returns the same result.

The function returns a string, which would need to be executed (with SELECT and FROM <table_name> added in the right places) to return a result. Even then, it would return a value for every row of the table, because there is nothing in the constructed SQL to link the generated text to a particular row.

More to the point, it is not even possible to execute dynamic SQL in the context of a function (unsafe hacks aside).

  Is there a way around this? Is there a better approach to store a hash in each row built from all the columnar values in that row?

This isn't as straightforward as one might think at first glance.

For example, given:

CREATE TABLE dbo.Test
(
    c1 integer PRIMARY KEY,
    c2 varchar(10) NOT NULL,
    c3 datetime NOT NULL
);
GO
INSERT dbo.Test
    (c1, c2, c3)
VALUES
    (1, 'ABCDE', GETDATE()),
    (2, 'FGHIJ', GETUTCDATE());

The following query returns the broad sort of thing needed:

SELECT
    T.c1,
    T.c2,
    T.c3,
    Chk = BINARY_CHECKSUM(*) -- or CHECKSUM
FROM dbo.Test AS T;

Unfortunately, the Chk column cannot be added as a computed column in the table:

Msg 1789, Level 16, State 1, Procedure ComputeChecksum, Line 5
Cannot use CHECKSUM(*) in a computed column, constraint, or default definition.

It cannot be used directly in a function either, for a variety of reasons depending on exactly how you try it, but one entertaining result is the CHECKSUM(*) becomes recursive (referencing itself) and execution terminates when the depth limit of 32 is reached.

A trigger implementation of an all-columns checksum/hash is probably possible, but would need to contain dynamic SQL (or some XML trickery) because CHECKSUM(*) would include the checksum/hash column itself. Aside from DML triggers, a DDL trigger would also be needed to populate the values when a new column is added. This might be an interesting exercise, but it is not trivial.

If the restrictions are relaxed so the checksum/hash does not need to be in the same table, more options become available. For example, a view:

CREATE VIEW dbo.TestChk
AS
SELECT T.*, Chk = BINARY_CHECKSUM(*)
FROM dbo.Test AS T;

Though this looks like it would dynamically adapt to changes in the base table, in fact it does not. You will get wrong results if a column is added to the table, for example. When the table schema is changed, the view would have to be refreshed:

-- New column
ALTER TABLE dbo.Test
ADD c4 decimal(5,2) NOT NULL DEFAULT 0.0;

-- Refresh required
EXECUTE sys.sp_refreshsqlmodule
    @name = N'dbo.TestChk',
    @namespace = DEFAULT;

Note the view also cannot be materialized, because it cannot be schema-bound:

Msg 1054, Level 15, State 7, Procedure TestChk, Line 4
Syntax '*' is not allowed in schema-bound objects.

I haven't tried every possible implementation option (including a SQLCLR function), because even if it can be made to work as you want, it is very likely to be horribly inefficient, and not the 'best' solution to the problem you really need to solve.

We would need to know the reasons for wanting this column, as it is quite likely there is a better way to achieve the end result you need via a different implementation option, possibly a standard feature in the product. I would suggest asking a new question for that.


Parsing DATE while copying csv file into PostgreSQL table I have a long series of .csv files, which I want to import into a local database. I believe my query is correct, but there are some problems in parsing DATE and TIMESTAMP columns. PostgreSQL reads these columns expecting an ISO format "yyyy/mm/dd", but my data has it in another format: "dd/mm/yyyy".
I read online and on other Stack Overflow answers that one can SET the datestyle to be different, but it's not recommended.
Is there a way to specify the format of the columns to import? Also, I do not need to import all columns from the csv file: can I leave some out?
Details
First, I wrote the code to create the table (sorry if column names are in Italian, but it's not important):
CREATE TABLE IF NOT EXISTS bikes (
    bici INT,
    tipo_bici VARCHAR(20),
    cliente_anonimizzato INT,
    data_riferimento_prelievo DATE,
    data_prelievo TIMESTAMP,
    numero_stazione_prelievo INT,
    nome_stazione_prelievo TEXT,
    slot_prelievo SMALLINT,
    data_riferimento_restituzione DATE,
    data_restituzione TIMESTAMP,
    numero_stazione_restituzione INT,
    nome_stazione_restituzione TEXT,
    slot_restituzione SMALLINT,
    durata VARCHAR(10),
    distanza_totale REAL,
    co2_evitata REAL,
    calorie_consumate REAL,
    penalità CHAR(2)
);

Then I add the query to copy data into the table:
COPY bikes(
    bici,
    tipo_bici,
    cliente_anonimizzato,
    data_riferimento_prelievo,
    data_prelievo,
    numero_stazione_prelievo,
    nome_stazione_prelievo,
    slot_prelievo,
    data_riferimento_restituzione,
    data_restituzione,
    numero_stazione_restituzione,
    nome_stazione_restituzione,
    slot_restituzione,
    durata,
    distanza_totale,
    co2_evitata,
    calorie_consumate,
    penalità
)
FROM '/Users/luca/tesi/data/2019q3.csv'
DELIMITER ','
CSV HEADER;

The code seems fine, except the following error pops up:
ERROR:  date/time field value out of range: "31/07/2019"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY bikes, line 25296, column data_riferimento_restituzione: "31/07/2019"
SQL state: 22008

How can I specify in the CREATE TABLE portion of the code the format to parse? Also, I do not actually need all the cols of this csv, how do I leave these out? I tried to specify only those I need but I get an import error:
ERROR:  extra data after last expected column

Set datestyle to ISO, DMY, and your dates will be parsed as you want. There is nothing wrong with setting that parameter - do it with SET right before you COPY.
There is no way to skip columns from the CSV file. Add extra columns to the table and drop them later, that is cheap.


Cannot restore data using Postgres WAL files I'm a newbie at PostgreSQL.
I'm trying to setup a streaming replication (postgresql 9.1).
And I want to test backup & restore on local.
I also made backup files successfully (I think so) by using pg_basebackup
But cannot restore data.
Here is how I set my database up.

Postgres on server1 (master)
Configure file: /var/lib/pgsql/9.1/data/postgresql.conf
 # WRITE AHEAD LOG
 wal_level = hot_standby

 # - Archiving -
 archive_mode = on
 archive_command = 'cp %p /var/lib/pgsql/9.1/wal-archive/%f'

 # - Master Server -
 max_wal_senders = 5
 wal_keep_segments = 32

 # - Standby Servers -
 hot_standby = on

Start master and insert some data

Postgres on server2 (slave)
Configure file: /var/lib/pgsql/9.1/data/postgresql.conf (content is same as master)

Create recovery.conf file on slave
 // vi /var/lib/pgsql/9.1/data/recovery.conf
 standby_mode = 'on'
 primary_conninfo = 'host={master_hostname} port=5432 user=repl_user password={my_password}'

Start slave => and data is automatically synced with master

Create WAL backup
 // slave
 service postgresql-9.1 stop
 rm -rf /var/lib/pgsql/9.1/data/*
 su - postgres
 pg_basebackup -h {master_hostname} -p 5432 -U repl_user -D /var/lib/pgsql/9.1/data --xlog --checkpoint=spread --progress

WAL files is generated in /var/lib/pgsql/9.1/wal-archive on master after pg_basebackup is executed in slave
 // something like this
 000000070000000000000013
 000000070000000000000014
 000000070000000000000015
 000000070000000000000015.00000020.backup

Drop one table (master)
 DROP TABLE {table_name}

Restore that dropped table using WAL files (master)
 service postgresql-9.1 stop
 vi /var/lib/pgsql/9.1/data/recovery.conf

 // add this line to recovery.conf
 restore_command = 'cp /var/lib/pgsql/9.1/wal-archive/%f %p'

Start master again
 service postgresql-9.1 start

But that dropped table is not restored on master. I don't know why.
I also tried to add some other option, but everything is not worked.
recovery_target_time = '2020-10-29 08:20:00 UTC'
recovery_target_inclusive = false

Please help on how to restore data using WAL files.
If I accidentally drop a table on master, how can I restore it using WAL files? Which configuration should I setup on recovery.conf file? Is just write restore_command in recovery.conf file and start postgres again make server restore the lastest backup point data?

You need to read up on how backup and recovery works. WAL doesn't let you go backwards in time, only forwards.
When restoring you would normally restore your data files from your backup (eg one taken with pg_basebackup). That will get you back to the moment the backup was started. If you want to go further in time, you need to supply the WAL files for changes made since the backup. In your recovery_conf you can specify to restore to the time just before the table was dropped using recovert_target_time =  as you have done.
Normally such a restore would be done in a secondary environment. You would then export the dropped table(s) or modified data and import that into your production database (or run update/insert/delete statements to change the state of the database). That way you don't lose other modifications made since the table was dropped.


How to persist messages in Postgresql's LISTEN/NOTIFY? If I send notifications before starting to listen i.e.

notify test, 'test message';
listen test;

I loose that notifications. How can I persist them for later fetching?
Actually, I need this notifications for tables updates, so it is preferably not to start spezialized messaging system like RMQ etc.

You can't replay them. If no one is listening, they're gone. Start up your own communication daemon to catch them. Then you can do whatever you want.


What would cause the speed difference between servers? I have this query on my dev server:

select
    snStudyNumber as ProjectNumber
from CDB.cdb.Quotes q
    inner join CDB.cdb.LineItems l
        on q.PK_quID=l.FK_quID
    inner join CDB.cdb.StudyNumbers s
        on l.FK_snID=s.PK_snId
    left outer join ardb.projects p
        on s.snStudyNumber=p.prProjectNumber
    left outer join b
        on s.snStudyNumber=b.studynumber
where q.quDateWon>''
    and s.snPTWCompletionDate >''
    and p.PK_prID is null
    and l.liCreationDate>'12/31/06'
    and b.studynumber is null
union all

select CAST(ProjectNumber AS int)
from history.ProjectsToProcess a
    left outer join ardb.projects p
        on a.ProjectNumber=p.prProjectNumber
where p.PK_prID is null

order by ProjectNumber desc

This used to work ok on the dev server until yesterday.  Now it's taking about 1 1/2 minutes to run.  However, it works fine on the test server.

Here's the weird part.  If i modified the query on dev to take out one of the left outer joins (either one), it would run fine (but the wrong dataset).  If i added the join back it slowed down again.

Also, the data between the servers are within 90% similarity.  Structure and amount wise.

I also tried rebooting the dev server, but that didn't fix the issue.

So, what could cause this discrepancy?  Where should I be looking?

edit-
ok, so i missed something in the execution plans.  The test server (the one that works) is on the left, the dev server on the right.

Sounds like your statistics are out of date.  Run UPDATE STATISTICS on the tables on the server running slow and see if that fixes the problem.


How do I grant execute permission to an Oracle database user? I'm trying to call a Java function from Oracle database that does the following:

Process p = Runtime.getRuntime().exec("...");

When I try to run it, I get the following error:

ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
<<ALL FILES>> execute) has not been granted to SCOTT. The PL/SQL to grant this
is dbms_java.grant_permission( 'SCOTT', 'SYS:java.io.FilePermission', '<<ALL
FILES>>', 'execute' )

How do I grant this permission?

UPDATE: When I run the following in SQL*Plus (as sysdba):

dbms_java.grant_permission( 'SCOTT',
                            'SYS:java.io.FilePermission',
                            '<<ALL FILES>>',
                            'execute' );

I get the error:

SP2-0734: unknown command beginning "/dbms_java..." - rest of line ignored.
SQL> SP2-0734: unknown command beginning "'SYS:java...." - rest of line ignored.
SQL> SP2-0734: unknown command beginning "'<<ALL FIL..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.

The error message actually gives you the exact syntax

dbms_java.grant_permission( 'SCOTT',
                            'SYS:java.io.FilePermission',
                            '<<ALL FILES>>',
                            'execute' )

It's not obvious to me whether you modified the text of the error message to remove a directory from the error-- normally, the error will tell you a specific directory that you need to grant access to.

If your intention is to allow the Java stored procedure to execute arbitrary shell scripts (this would be very dangerous-- the commands would run as the Oracle operating system user so they would have the ability to bypass any security measures in the database), you should be able to do something like

begin
  dbms_java.grant_permission
      ('SCOTT',
       'java.io.FilePermission',
       '<<ALL FILES>>',
       'execute');
  dbms_java.grant_permission
      ('SCOTT',
       'java.lang.RuntimePermission',
       '*',
       'writeFileDescriptor' );
end;


Does Management Studio let me change the format of new script names? I use SQL Server Mangagement Studio to examine the definition of objects in my database.

For example, to examine the definition of the table dbo.MSreplication_options in the master database, I would right-click on the table in the Object Explorer and choose 'Script Table as... > CREATE to > New Query Editor Window...'.

As you can see from the screenshot of the current state of my Management Studio, a new tab is created for a new script called "SQLQuery9.sql", which contains the table definition.

By this time I have already examined a few other objects in this way, and have forgotten what most of the other scripts called "SQLQuery1.sql", "SQLQuery2.sql" and so on contain.

How can I configure management studio give the script a more descriptive name, perhaps containing the name of the object, like "dbo.MSreplication_options.sql", for example.

My Management Studio version is 10.0.5500.0.

You can't.

The SSMS tab is named after the open file, not the contents of the window/file.
So if you have not saved it, it has no name to assign


SQL Server agent Error I am in a new shop and the below error started to show up on 2/22 repeatedly until 3/7.

Event Type: Error
Event Source:   SQLSERVERAGENT
Event Category: Alert Engine
Event ID:   318
Description:
Unable to read local eventlog (reason: The event log file is corrupted).

Event Type: Information
Event Source:   SQLSERVERAGENT
Event Category: Alert Engine
Event ID:   311
Date:       2/22/2011
Time:       6:52:44 AM
User:       N/A
Computer:   XXXXXXX
Description:
Attempting to re-open the local eventlog...

Event Type: Warning
Event Source:   SQLSERVERAGENT
Event Category: Alert Engine
Event ID:   312
Description:
Successfully re-opened the local eventlog - NOTE: Some events may have been missed.

Then, the server shut down unexpectedly. The shutdown stopped the events. But, my concerns are: What was happening? Does anyone know what this means? More importantly, what kind of measures do we need to take to prevent this from happening?

Sounds like you've got some sort of corruption problem on the disk which the SQL Agent logs get written to.  I'd check the disk for corruption, as well as the C drive in case it's talking about the application log which the SQL Agent also writes to.


What's the values for the Freq_Interval column in MSDB.dbo.SysSchedules when Freq_Type is weekly and more than one day is selected in the schedule? I'm reading up on the SysSchedules table in the Microsoft API, and am trying to incorporate using this table in a query.

In the documentation, the Freq_Interval column seems to be defined out for when a single day is selected in the schedule and the Freq_Type is weekly.
But what are the values for every other combination of days when more than one day is selected?

For example, I have a job which is scheduled to occur weekly on the days Monday, Tuesday, Wednesday, Thursday, and Friday.
I see in the MSDB.dbo.SysSchedules table, it has a Freq_Interval of 62 (which isn't listed in the documentation.)

The documentation states

freq_interval is one or more of the following:
1 = Sunday
2 = Monday
4 = Tuesday
8 = Wednesday
16 = Thursday
32 = Friday
64 = Saturday

These values are all powers of 2.
When you select multiple days simply add the distinct codes.
2 + 4 + 8 + 16 + 32 = 62

or alternatively use the bitwise or operator to combine them.
2 | 4 | 8 | 16 | 32

(my recommendation would be to use | in code and + if adding them up in your head! - | is the "correct" thing to use and won't behave incorrectly if you have duplicate values in the list)
If you are trying to do the reverse operation you need bitwise and
DECLARE @Sunday TINYINT = 1,
        @Monday TINYINT = 2,
        @Tuesday TINYINT = 4,
        @Wednesday TINYINT = 8,
        @Thursday TINYINT = 16,
        @Friday TINYINT = 32,
        @Saturday TINYINT = 64;

DECLARE @TestValue TINYINT = 62;

SELECT CASE WHEN @TestValue & @Sunday = @Sunday THEN 'Y' ELSE 'N' END AS Sunday,
       CASE WHEN @TestValue & @Monday = @Monday THEN 'Y' ELSE 'N' END AS Monday,
       CASE WHEN @TestValue & @Tuesday = @Tuesday THEN 'Y' ELSE 'N' END AS Tuesday,
       CASE WHEN @TestValue & @Wednesday = @Wednesday THEN 'Y' ELSE 'N' END AS Wednesday,
       CASE WHEN @TestValue & @Thursday = @Thursday THEN 'Y' ELSE 'N' END AS Thursday,
       CASE WHEN @TestValue & @Friday = @Friday THEN 'Y' ELSE 'N' END AS Friday,
       CASE WHEN @TestValue & @Saturday = @Saturday THEN 'Y' ELSE 'N' END AS Saturday


Apply the select privilege to all users in mysql I have 30 plus MySQL users their permissions vary from one another.

I want to GRANT SELECT privilege to all of the 30 users. Is there any one command that I can use that can grant select to all 30 users in one shot!

Try this:

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| eroom            | localhost |
| musr             | localhost |
| musr             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql> select concat('GRANT SELECT ON *.* to "',user,'"@"',host,'";') into outfile '/tmp/select_script.sql' FROM mysql.user;
Query OK, 4 rows affected (0.00 sec)

mysql> source /tmp/select_script.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


Log Shipping Alternative In our current environment we have about 40 databases on one instance all being log shipped and configured as Standby/Read Only. About 90% of those are for reporting purposes and others for read only. The number of failures and maintenance plus user complain about not being able to access the databases when logs are being restored has forced us to rethink about this feature and we are strongly thinking of moving to Replication.

My main concern is can the Replication feature handle this many databases if we put our distributor on a separate instance? Shall we have one distributor or more than one? The network is handling the log shipping very well and I assume network bandwidth will not be an issue.

If replication is not a good option then what other option would be better that requires less maintenance or more importantly less failures.

We are on Amazon Cloud Infrastructure.

any input will be appreciated.

You could consider Availability Groups for your databases. Since you are using them for reporting/read-only already then you already have licenses for your secondary instances.

This will allow better access to your secondaries as it does not need to take the database offline for updates - your data will also be more up to date as it is a constant flow of transaction log updates rather than batched backups.


PostgreSQL: Extending database storage when disk is full I have a database that takes about 60GB in my disk storage. And it doesn't alow to insert into database anymore.
I have read the documentation about tablespaces link But It's like create space for a new table, whereas I want to continue insert into exist table. Could somebody help me out?

In addition, PostgreSQL database maximum is 64TB, so we have to connect many HDDs to achieve 64TB, then the solution could be apply to this situation (many connected HDDs or shared storage, I'm not sure how to say)

Thanks in advance!

PS: I'm working with postgreSQL 9.2 on ubuntu 12.04 LTS

PostgreSQL doesn't support spanning a single table over multiple tablespaces directly.

You can use table partitioning to create a "master" table, with child tables on different tablespaces. It's a bit tricky and it means you can't use foreign keys or enforce a proper unique constraint on the primary key, so it's not generally the preferred option.

A more practical option is usually to use a logical volume manager, RAID system, or similar to span multiple physical disks into a single volume, with a single file-system on it. Then PostgreSQL just uses it like anything else and doesn't need to care. On Linux, this might mean LVM, the md software RAID driver in raid0 mode, btrfs, or all sorts of other options. Most (all) of these require you to build a new filesystem then move the PostgreSQL database onto it, you can't convert a regular filesystem that's directly on top of a partition into a raid volume / logical volume / etc.

Note that neither of these options are safe if the additonal storage is removable. If you remove a disk containing a tablespace, or remove part of a logical volume or spanned raid, your entire database will become inacessible. If you lose the disk/volume or its data, the entire DB will be unrecoverable.


how to retrive data from InvoiceNo select  Month(IH.InvoiceDate) as Month,
    COUNT(ID.InvoiceNo) as Count,
    COUNT(ID.InvoiceNo) as DirectMobile,
    COUNT(ID.InvoiceNo) as Preselling,
    COUNT(ID.PerformaInvoiceNo) as DirectWeb
    from InvoiceDetail ID
INNER JOIN InvoiceHeader IH ON ID.InvoiceNo = IH.InvoiceNo
WHERE IH.IsBackend = 1
GROUP BY Month(IH.InvoiceDate)

I want to ask how to retrieve data from InvoiceNo into two columns and how to give the where clause to complete these conditions.

Count is full count of every type of invoices and i want to get the count of DirectMobile , DirectWeb and Preselling . For get the count of those 3 these are the coditions.

DirectMobile - IsBackend=0

DirectWeb - IsBackend=1 and PerformaInvoiceNo= '' or 0

Preselling - IsBackend = 1 and PerformaInvoiceNo = a varchar value.

Something like this (untested) might be what you're after:

SELECT Month(IH.InvoiceDate) as Month,
    COUNT(*) as Count,
    SUM(CASE WHEN IH.IsBackend = 0 THEN 1 ELSE 0 END) as DirectMobile,
    SUM(CASE WHEN IH.IsBackend = 1 AND PerformaInvoiceNo <> '' AND PerformaInvoiceNo <> '0' THEN 1 ELSE 0 END) as Preselling,
    SUM(CASE WHEN IH.IsBackend = 1 AND (ID.PerformaInvoiceNo = '' OR ID.PerformaInvoiceNo = '0') THEN 1 ELSE 0 END) as DirectWeb
FROM InvoiceDetail ID
INNER JOIN InvoiceHeader IH USING (InvoiceNo)
GROUP BY Month(IH.InvoiceDate);

This emulates the effect of the filter clause that is available in Postgres. See https://modern-sql.com/feature/filter It's optional in the SQL:2003 standard.


Extra rows or Quantity Column DB Design Me and a coworker are seeming to disagree on this topic.

The context is Product that have multiple components. Sometimes its multiple of the same component.

I think its better to have a record for each component where as my coworker thinks its better to have a quantity column. Personally I think this is what a one to many relationship in db design represents in a very real since, where he is saying its easier to work with (he is more of a c# guy where I am the dba). So, I can def see where we might be coming at this form different angles.

I guess since I am new to this, I figured I would ask the people who know better what their opinions are.

Which is better - extra rows per "product" or having a quantity column?

Your table structure is defined by the entity model it represents, not by opinions.

If components need to be represented individually, e.g. if they have unique serial numbers, different prices, or arrive as separate shipments, this would warrant separate records for each. If components are indistinguishable from each other (cf. nails, bricks, or gallons of paint) there is no reason nor method to track each one separately.


Update value with nested subqueries I am working in Oracle and trying to update a value in tableA to = 'Y' based on info from two other tables (B and C)

I have tried something like this which does work if I have only the first select statement but I need to exclude some records by using the second select statement.

update tableA set value1 = 'Y'
where tableB_ID in
(
    select ID from tableB
    where class in ('1','2')
)
and tableC_ID in
(
  select ID from tableC where name not like 'MPG%'
)

I find UPDATE statements, with complex WHERE clauses, are easier to understand, and maintain, when written as a MERGE statement.

Examples

MERGE INTO TableA a1
USING (
 SELECT TableA_ID, 'Y' value1
 FROM TableA a
 JOIN TableB b ON a.TableB_ID=b.TableB_ID
  JOIN TableC c ON a.TableC_ID=TableC_ID
WHERE
    b.class in ( '1', '2' )
  AND c.name NOT LIKE 'MPG%'
) b1
ON (a1.TableA_ID=b1.TableA_ID)
WHEN MATCHED THEN UPDATE
SET a1.value=b1.value
;


Try_Convert Datetime showing '1/1/1900', want to show space How do I get try_convert datetime to display NULL when the input is space?

I am getting two different results here. My intention is to show 'space' number query as Null also, just like first query.

select try_convert(datetime,'abcd')   ---> Null

select try_convert(datetime,'')    ---> '1/1/1900'

You can use NULLIF() to force a NULL when the value is an empty space.

SELECT TRY_CONVERT(datetime, NULLIF('', ''));

Assuming this is actually coming from a column or parameter/variable:

SELECT TRY_CONVERT(datetime, NULLIF(@param, ''));

SELECT TRY_CONVERT(datetime, NULLIF(column, '')) FROM ...;


Sharding AWS MySQL data I am working on a SaaS application using AWS MySQL.
The application is for multiple organizations. We use the same database and tables to store multiple org data. We store org_id in most of the tables and use this in select, update and delete queries as where org_id = ? .
DB Model

Organization
org_id

User
user_id
org_id

client
client_id
org_id

client_contact
client_id
phone_number
phone_type

project
project_id
org_id

user_project
project_id
user_id

The data is growing rapidly and we need to shard the data. We have customers from all over the world.
If I use a different sharding id for different table, then a join might happen across the nodes. For example, for user, sharding can be on shard_id and for client, sharding can be on client_id and for project, sharding can be on project_id. Here, the problem would be joining project and user. The user_id and project_id might be on different nodes and join will happen across nodes.
What is the best approach here? I am thinking of sharding based on org_id as I store org_id in most of the tables.
I see two problems here:

A few child tables don't store org_id as the parent table is storing. Do I need to store the org_id in all the tables?
Some orgs might have more data and load which might lead to a hot spot and more storage on a particular node. Is it possible scale a particular node alone with AWS RDS?

Please suggest the best approach.
Note Each org might have up to 500,000 records in any table with around 10 columns. We could reach more than 5,000 organizations in 6 months. 500,000 multiplied by 5,000 orgs will reach 2.5 billion records.

(Not yet an answer, but some more steps toward understanding the task enough to provide an answer.)
2.5 billion rows will fit on a single machine.  (Perhaps 400GB, including ancillary tables.)  Have you determined that the activity will exceed a single server's capabilities?

Which table is the biggest?  (Please list the 3 largest and their projected sizes.  Also, please provide the full SHOW CREATE TABLE for each of them -- We need to consider shrinking their datatypes.)
Which column(s) in that table seem reasonable for sharding?
If you shard on that column, what other tables will also be split up?
Ignoring for the moment, provide the 6 most common SELECTs with JOINs and multi-table Updates/Deletes.
Which tables will be needed for JOINs?  Are they small enough to simply maintain copies of them in each shard?  How complex will that maintenance be?


Understanding MongoDB's contra-indications for read preferences I'm very new to MongoDB and am reading the manual to familiarize myself with it. Would appreciate some help in understanding some points related to read preferences.

  In general, do not use secondary and secondaryPreferred to provide
  extra capacity for reads, because:

  All members of a replica have roughly equivalent write traffic; as a
  result, secondaries will service reads at roughly the same rate as
  the primary.
  Distributing read operations to secondaries can compromise
  availability if any members of the set become unavailable because
  the remaining members of the set will need to be able to handle all
  application requests.

I don't agree with point no. 1, and I don't understand point no. 2. If I have three members in my replica set, then it seems like common sense that read traffic can be made one-third by distributing it evenly, provided I don't care much about data freshness. As for point no. 2, isn't that the failover situation that MongoDB is fundamentally good at solving? I mean, if the secondary dies, does the client lose all capacity to read? As I see it, there should be a new secondary should be automatically elected to replace it (and if there are none, the load should transfer to the primary).

Please help explain!

1) All members of a replica have roughly equivalent write traffic; as a result, secondaries will service reads at roughly the same rate as the primary.

By definition, all data-bearing members of a replica set maintain the same data set so the write traffic is roughly similar for secondaries - but not necessarily identical if compared to a primary. Depending on your workload and server configuration, it's not uncommon to see secondaries with different I/O activity than the primary.

Replica sets are designed to support redundancy and high availability as a key feature, with reads directed to the primary by default (aka a read preference of primary). Servicing secondary reads is a lower design priority than ensuring successful replication and minimizing replication lag.

One interesting caveat is that writes do not have to be applied identically on the primary and the secondaries as long as reads from a secondary always reflect a state that previously existed on the primary. In particular, writes on secondaries are applied in batches using multithreaded replication which improves the write concurrency of secondaries with the expected side effect of adding latency to secondary reads under load.

If you read from secondaries you will have to deal with the possibility of eventual consistency (if there is replication lag) or intermittent latency (if the secondary is actively applying a lot of replicated writes). Replication lag can also vary depending on which secondary you are connecting to, so results may not be as your application code expects if subsequent page requests fetch data from secondaries with significantly different lag.

I would amend this contra-indication warning to suggest that "secondaries will likely service reads at a similar or lesser rate than the primary".

  2) Distributing read operations to secondaries can compromise availability if any members of the set become unavailable because the remaining members of the set will need to be able to handle all application requests.

This counter-indication is referring to capacity planning and possible consequences of failover.

  If I have three members in my replica set, then it seems like common sense that read traffic can be made one-third by distributing it evenly, provided I don't care much about data freshness.

The specific warnings you highlighted are for read preferences of secondary (only read from a secondary) and secondaryPreferred (read from a secondary if available). Both of these read preferences exclude reading from a primary.

With either of these two read preferences and a healthy three member replica set, your reads could be split between the two secondaries. However, if one secondary is unavailable the surviving secondary will need to service 100% of the secondary read load rather than 50%, which could overwhelm your deployment without careful planning. This is exactly the read preference you've asked for, but perhaps not the outcome you expected.

A better alternative would be to use a read preference of nearest, which may read from the primary or from secondaries.

  As for point no. 2, isn't that the failover situation that MongoDB is fundamentally good at solving?

Replication handles recovery from failover in a properly configured deployment, but capacity and deployment planning are separate exercises. The defaults are generally chosen to be reasonable, so if you want to change them it is worth understanding possible implications.

  I mean, if the secondary dies, does the client lose all capacity to read? As I see it, there should be a new secondary should be automatically elected to replace it (and if there are none, the load should transfer to the primary).

A three member replica set has a fault tolerance that allows any one node to be unavailable while still maintaining a primary. If you have two nodes unavailable, the surviving node will become a secondary. With any read preference aside from primary you could still service reads in the sole survivor scenario.

For more discussion on secondary reads, see: Can I use more replica nodes to scale?.


Plaintiff and defendant have the same attributes I am working on my final year project, "civil case management system" related to civil law.

I have a problem to store attributes for both plaintiff and defendant. They have the same attributes. I am not sure what is the best solution.

As you are managing cases, you may want to ensure you don't double book people.  A person could be a Plaintiff on one case and Defendant on another.  I would have a persons table (legal persons like corporations can be problematic), and use a join table to associate persons with cases.  The join table would have a flag to indicate the role in the case.

This model could be extended to cover judges, lawyers, clerks etc.


How to get top 1 row for each of the state in USA I have a list of states in table 1 (Statename, StateAbbreviation)
I have different city based records having StateAbbreviations in table 2

I want top 1 record per state from table 2

My result should contain one record for each of the states in table 1

You can use the ROW_NUMBER and PARTITION BY StateAbbreviation to obtain the first city per state:

WITH d
AS
(
    SELECT StateAbbreviation, CityName
         , ROW_NUMBER() OVER
          (
           PARTITION BY StateAbbreviation
               ORDER BY CityName
          ) AS Ordinal
      FROM dbo.Cities
)
SELECT d.StateAbbreviation, StateName, CityName
  FROM d
  JOIN dbo.States AS s
    ON s.StateAbbreviation = d.StateAbbreviation
 WHERE Ordinal = 1;

You can change the ORDER BY clause to whatever suits your requirement but the key here is to make sure that you partition by the StateAbbreviation to get the row number generated per state.


select columns with different conditions This question focus to the COUNT result

1st query will show the COUNT with date condition.

2nd query will show the COUNT without date condition.

Now I need a query which make the COUNT result in 1st query without date condition which is :
COUNT(c.CustomerID) > ONLY with condition b.BookingCustomerID=c.CustomerID
(without date condition)

c.CustomerTelephone, b.* > with condition WHERE b.BookingCustomerID=c.CustomerID
AND BookingDate LIKE '%2019-05-04%'

Thanks.

SELECT COUNT(c.CustomerID), c.CustomerTelephone, b.*
FROM Booking b, Customer c
WHERE b.BookingCustomerID=c.CustomerID
AND BookingDate LIKE '%2019-05-04%'
GROUP BY c.CustomerID

SELECT COUNT(c.CustomerID), c.CustomerTelephone, b.*
FROM Booking b, Customer c
WHERE b.BookingCustomerID = c.CustomerID
GROUP BY c.CustomerID

When doing a GROUP BY, it is improper to list anything other than the group-by columns and aggregates in the SELECT part.  In your case, the values of b.* could be random.

Are you trying to compute both counts at the same time?  If BookingDate is inc, then it is easy.  If it is in the other table, then it gets messier.  _WhenJOINing`, please make it clear which columns come from which tables._

SELECT COUNT(c.CustomerID) AS count_all,
       SUM(( SELECT BookingDate FROM Booking
                     WHERE BookingCustomerID = c.CustomerID
                       AND BookingDate LIKE '2019-05-04%' )) AS count_day
    FROM Customer AS c
    GROUP BY c.CustomerId

To get the rest them,

SELECT x.count_all, x.count_day
       c.CustomerTelephone,
       b.*
    FROM ( ... the query above ... ) AS x
    JOIN Customer AS c USING(CustomerID)
    JOIN Booking AS b ON b.BookingCustomerID = x.CustomerID
    FROM Customer AS c
    GROUP BY c.CustomerId


Acceptable to use Antelope and Barracuda simultaneously? I've been doing some reading about the Antelope and Barracuda file formats for InnoDB.  I recall at least one source mentioning that the two should not be used simultaneously.

I can't think of a good reason (while using innodb_file_per_table) that I couldn't use Antelope on one table and Barracuda on another.  However, maybe I'm missing something.

Are there any good reasons to avoid using Antelope and Barracuda simultaneously?

(Note: The reason I'm planning on using both simultaneously is that I don't want to mess with our existing (quite large) tables.  Perhaps this isn't a big deal?  Is migrating from Antelope to Barracuda trivial, even for large tables?)

I wrote a post 3 years ago that discusses this very subject : innodb_file_format Barracuda

In terms of storage, using Antelope and Barracuda is possible. However, in my post, I discuss a residual effect on the InnoDB Buffer Pool. You need a larger Buffer Pool because Barracuda compressed pages in the Buffer Pool have to be decompressed to the read in the Buffer Pool.

You should dig deeper through Percona's Performance Blog site for any additional insights.


Redirect pg_dump output to stdout How to redirect pg_dump -Fd output to gzip? It requires that a directory would be specified.
PGPASSWORD=$db_pass pg_dump mydb -U db_user -h localhost --verbose 2>/var/log/postgresql/pgdump.log -Fd | gzip > /mnt/bulk-data/db_backup/mydb_bak.gz

You cannot do that. Use one of the other formats; ideally the custom format, which is automatically compressed.


Where to put my.ini file for 5.6.21 MySQL Community Server on Windows I have copied the C:\Program Files\MySQL\MySQL Server 5.6\my-default.ini to C:\Program Files\MySQL\MySQL Server 5.6\my.ini and changed some settings including setting table_definition_cache=400.  When I log into the database and look at the table_definition_cache value I get the following:

mysql> SHOW GLOBAL VARIABLES LIKE '%table_definition_cache%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| table_definition_cache | 1400  |
+------------------------+-------+
1 row in set (0.05 sec)

Where do I need to put my my.ini file such that the database loads it on startup?

The folder C:\Program Files\MySQL\MySQL Server 5.6 is the correct location for my.ini.
There are two possible reasons 1400 shows up as table_definition_cache.
REASON #1
You did not restart mysql.
Login to Windows DOS command line as Administrator and run
C:\> net stop mysql
C:\> net start mysql

Value should be in place afterwards.
REASON #2
The default value for table_definition_cache is -1 (autosized). That makes mysqld figure out a fair value for table_definition_cache, given your OS configuration.
CAVEAT
Make sure you place
table_definition_cache = 400

under the [mysqld] group header before you restart mysql
GIVE IT A TRY !!!
I dug a little further and learned why 1400 comes up for table_definition_cache
According to the Documentation for table_definition_cache

The default value is based on the following formula, capped to a limit of 2000:
400 + (table_open_cache / 2)

The default value for table_open_cache is 2000
Applying the formula
400 + (table_open_cache / 2) =
400 + (2000 / 2) =
400 + 1000 =
1400

One of two things is happening

table_definition_cache is still not being picked up from my.ini
table_definition_cache is being picked up from my.ini, but is being rejected due to needed resources


Will a clustered index speed up this query? (Inner join with a where clause) I'm inserting data into a new table in a new database, based on a select to an existing table in an other database. The existing table has some 1500 million rows, and I am inner joining a table with about 10% of that. Both existing tables, are indexed on C5, while the the new table is indexed on C2, which is an unixtime column (I am going to do aggregates over time with the new table). This is taking horrendously long time. Its been running for over 3 hours, and the new table is just over 750 mb big, I expect it to be about 50GB when its filled up. This is also about 50% of the original table.

Is it because I'm indexing the new unixtime column?

Or could it be that I've not indexed the C8 column, that I use in my inner join? If I clustered C8 with C5, I'd probably get a faster inner join?

Here is the query:

INSERT INTO NewTable (C1, C2, C3, C4, C5, C6, C7)
SELECT OldTable1.C1, OldTable1.C2, OldTable1.C3, OldTable1.C4, OldTable1.C5, OldTable1.C6, OldTable1.C7
FROM OldTable1
INNER JOIN OldTable2.C5 ON OldTable2.C5 = OldTable1.C5
WHERE OldTable2.C8 >= 70 AND OldTable2.C8<= 89;

You are totally trashing the index on NewTable.C2
Just drop / disable the index and then build / rebuild it after the insert is complete

And you need to break this up as you are putting a massive load on the transaction log

If you want to optimize the select then just run the select alone

INSERT INTO NewTable (C1, C2, C3, C4, C5, C6, C7)
SELECT OldTable1.C1, OldTable1.C2, OldTable1.C3, OldTable1.C4
     , OldTable1.C5, OldTable1.C6, OldTable1.C7
FROM OldTable1
JOIN OldTable2.C5
  ON OldTable2.C5 = OldTable1.C5
 AND OldTable2.C8 >= 70
 AND OldTable2.C8 <= 89;


Ensure only one of the several records for a given item has a column with true value I have a table called Computer with columns:

id
NIC_name
is_primary

My primary key is (id, NIC_name) and is_primary is a bit field for a boolean value.

I would like to ensure that for all computers with same id there is always zero or one records whose is_primary is set to true. How do I accomplish this?

You can use a filtered index to enforce uniqueness on id and is_primary when is_primary is 1.

An example here:

create table Computer(
    id char(20) not null
  , NIC_name varchar(100) not null
  , is_primary bit not null
  , constraint pk_computer primary key (
    id asc,
    NIC_name asc
    )
);

create unique index ix_one_primary on Computer(id, is_primary) where (is_primary=1);

-- these three are fine
insert Computer(id, NIC_name, is_primary)
values('computer1','foo',0);

insert Computer(id, NIC_name, is_primary)
values('computer1','bar',1);

insert Computer(id, NIC_name, is_primary)
values('computer1','foobar',0);

-- this one is not allowed
insert Computer(id, NIC_name, is_primary)
values('computer1','oops',1);


missing indexes on distributor database Looking for missing indexes on my distributor databases, I surprisingly find too many.
the tables that need to be indexed are generally 3:

MSrepl_commands
MSlogreader_history
MSdistribution_history

Here is the picture of the missing indexes

Now concentrating on TABLE MSdistribution_history
It all seems to boil down to these 3 indexes:
USE distribution_BOCSS;
CREATE NONCLUSTERED INDEX
IDX_agent_id_time_xact_seqnorunstatus_INC_delivery_latency
ON [distribution_BOCSS].[dbo].[MSdistribution_history]
 ([agent_id], [time], [xact_seqno], [runstatus])
 INCLUDE (delivery_latency,start_time,timestamp) WITH (ONLINE = ON)

USE distribution_BOCSS;
CREATE NONCLUSTERED INDEX IDX_runstatus_INC_agent_id_delivery_latency
ON [distribution_BOCSS].[dbo].[MSdistribution_history]
([runstatus])
INCLUDE (agent_id,delivery_latency,time,xact_seqno,timestamp)
WITH (ONLINE = ON)

    USE distribution_BOCSS;
    CREATE NONCLUSTERED INDEX IDX_agent_idrunstatus_INC_time_xact_seqno_delivery_latency
    ON [distribution_BOCSS].[dbo].[MSdistribution_history]
([agent_id], [runstatus])
    INCLUDE (time,xact_seqno,delivery_latency,timestamp,start_time)
     WITH (ONLINE = ON)

However, as I would imagine, the amount of updates on the current existing clustered index is very high (not sure since when it has been adding up to these stats, possibly since the last reboot)
as you can see on the picture below:

The writes are too high, it is a production system, I would like to have more support regarding this. Would I really benefit by creating these indexes?
Is there any documentation that supports creating indexes on the distributor databases of transactional replication?
Why Microsoft has not supplied these databases with the proper indexes on them?

Replication normally works and I have not encountered any issues where I have to create an additional index on the distribution database. (In my company we use replication a lot for doing data movement from one region to another)

If you are not having problems with Replication, then avoid creating those indexes. Index creation is not FREE, since SQL Server has to maintain any DDL changes that means you will have to do a proper maintenance for those indexes.

There is tuning that you can do. For e.g. if immediate sync is set to TRUE for your Publication then that will cause the transactions to be held in MSRepl_commands rather than deleting them when distributed. So setting immediate sync to FALSE will help the distribution clean up job do clean up much faster.

  Is there any documentation that supports creating indexes on the distributor databases of transactional replication?

Microsoft does not support making any changes to the system tables (replication, logshipping) except you are told by CSS. This does not mean that you cannot create those indexes, it just means that if you open a case with Microsoft they wont support unless you remove the indexes that you have created by yourself.

Check this connect as won't fix - Add index on [distribution].[dbo].[MSrepl_commands]

So my recommendation would be, baseline your server instance. Get data for a longer duration and if you are facing problems then address them by creating appropriate indexes. Test, test and test all your changes first and always be on the supported side when you work on a vendor software. You can file a connect item and if Microsoft addresses it, then it will be included as hotfix or in future versions.


Is my MySQL instance irretrievably dead? I'm trying to install a JIRA instance on a MySQL 5.1.73 instance running on CentOS 6.5 for our developers.

The install gets to the point where I need to create the database, and then FLUSH PRIVILEGES;

This is what I get:

ERROR 1033 (HY000): Incorrect information in file: './mysql/tables_priv.frm'

So I hit google, and try what's suggested by those who know.

(root@root.server) [mysql] 18:19:47 (4) $ REPAIR TABLE tables_priv EXTENDED USE_FRM ;
+-------------------+--------+----------+---------------------------------------------    -------------+
| Table             | Op     | Msg_type | Msg_text                                                     |
+-------------------+--------+----------+---------------------------------------------    -------------+
| mysql.tables_priv | repair | Error    | Incorrect information in file:     './mysql/tables_priv.frm' |
| mysql.tables_priv | repair | error    | Corrupt                                                      |
+-------------------+--------+----------+---------------------------------------------    -------------+
2 rows in set (0.00 sec)

After trying some other stuff, I decide to cut my losses, and reinstall the database..

mysqldump --events --lock-all-tables --all-databases -u root -p*********** > alldatabases.23.07.2014.sql

Oops...

mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)

All right, so I'm stuck, now.  The 'nuclear option' would be to remove the database files, uninstall the database and reinstall it, but we have Groupware (Horde) running on that server, and that would be .. not cool.

Any help gratefully received.

Looks like you installed MySQL 5.1 over a 5.0 (or older) installation, without running mysql_upgrade to create new system tables and add columns to existing system tables. –  Bill Karwin 25 mins ago

It is safe to run mysql_upgrade without disturbing your other databases.

Read http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html for more information


Combine multiples table to a new one I apologize, I am not sure how to word this question. It probably has been answered somewhere, but I couldn't find it if so.

I have three tables that I want to combine in one using a common id column, with empty rows here and there.

table1

 id | col1
----|------
  1 | 23.4
  3 | 13.4
  4 |  7.6

table2

 id | col2
----|------
  1 | 12.2
  2 | 17.1

table3

 id | col3
----|------
  1 | 22.2
  2 | 14.1
  5 | 10.8

I want to create a fourth table table4:

 id | col1 | col2 | col3
----|------|------|------
  1 | 23.4 | 12.2 | 22.2
  2 | NULL | 17.2 | 14.1
  3 | 13.4 | NULL | NULL
  4 | 7.6  | NULL | NULL
  5 | NULL | NULL | 10.8

I'm stumped as to how to approach this problem honestly.

I think you are looking for a FULL OUTER JOIN:

SELECT COALESCE(a.id,b.id,c.id), COALESCE(a.col1,'NULL'), COALESCE(b.col2,'NULL'), COALESCE(c.col3,'NULL')
  FROM table1 a
  FULL OUTER JOIN table2 b ON a.id=b.id
  FULL OUTER JOIN table3 c ON b.id=c.id;

or, if you want blanks instead of strings:

SELECT COALESCE(a.id,b.id,c.id), a.col1, b.col2, c.col3
  FROM table1 a
  FULL OUTER JOIN table2 b ON a.id=b.id
  FULL OUTER JOIN table3 c ON b.id=c.id;


SQL Minimum Row count I have a stored procedure that allows users to pass in parameters.

SELECT *
FROM Table1
WHERE columnA =@paramA, ColumnB=@paramB....

I need to hide the results if there are less than 10 rows, but return them if there are more than 10. Is there a clean way to do this? Any help would be great.

You can use count(*) over() to make sure your result set has that many rows.

You didn't say what to do if the rows = 10, only > or <, so you may need to change the operand to >= based on what you want.

DB FIDDLE

select *
into mytable
from (select 'X' as c1) x
cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))as y(Y)

select * from mytable

--change the 10 to 11 to see it not return
select * from
(
select *, COUNT(*) OVER() CT
from mytable
where c1 = 'X'
) sub
where CT > 10


SQL Partition Function without actual values I would like to partition a column by fiscalyear. How Do I create a partition function without harding coding values?  This is what I currently have below.

create table dbo.CustomerAccount
(
    CustomerAccountId bigint primary key identity(1,1),
    CustomerId int not null,
    FiscalYear int,
    AccountBalance numeric(15,2)
)

How Do I create a partition function without harding coding values?

CREATE PARTITION FUNCTION DateRangePF (datetime)
AS RANGE RIGHT FOR VALUES ('2015', '2016','2017')

You cannot create a dynamic VALUES clause in a CREATE PARTITION FUNCTION statement.  An alternative is to use the ALTER PARTITION FUNCTION statement to add a new range to the function.

I've encapsulated the required statements into a stored procedure:

IF OBJECT_ID(N'dbo.partition_function_add_date_range', N'P') IS NOT NULL
DROP PROCEDURE dbo.partition_function_add_date_range;
GO
CREATE PROCEDURE dbo.partition_function_add_date_range
(
      @function_name sysname
    , @range_date datetime
    , @scheme_name sysname
    , @next_filegroup sysname
)
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (
        SELECT 1
        FROM sys.partition_functions pf
        INNER JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
        WHERE pf.name = @function_name
            AND prv.value = @range_date
        )
    BEGIN
        DECLARE @cmd nvarchar(max);
        SET @cmd = N'ALTER PARTITION SCHEME ' + QUOTENAME(@scheme_name) + N' NEXT USED ' + QUOTENAME(@next_filegroup) + N';';
        PRINT @cmd;
        EXEC sys.sp_executesql @cmd;
        SET @cmd = N'ALTER PARTITION FUNCTION ' + QUOTENAME(@function_name) + N'() SPLIT RANGE (''' + CONVERT(nvarchar(max), @range_date, 120) + N''');';
        PRINT @cmd;
        EXEC sys.sp_executesql @cmd;
    END
END
GO

An example of how to call this stored procedure:

EXEC dbo.partition_function_add_date_range @function_name = N'DateRangePF '
    , @range_date = '2018-01-01 00:00:00'
    , @scheme_name = N'DateRangeSC'
    , @next_filegroup = N'PRIMARY';

This could be added to a SQL Server Agent Job, scheduled to run once a year, prior to rows being inserted into the new fiscal year.  The job step command would run something like this, which will automatically add the next year to the partition function, and any associated tables.

DECLARE @daterange datetime;
SET @daterange = CONVERT(varchar(30), DATEPART(YEAR, GETDATE()) + 1) + '-01-01 00:00:00';
EXEC dbo.partition_function_add_date_range @function_name = N'DateRangePF'
    , @range_date = @daterange
    , @scheme_name = N'DateRangeSC'
    , @next_filegroup = N'PRIMARY';

It's important to realize partition function modifications will affect ALL tables that use the partition function.


mysql re-partition table i have the following table. and i wan't to repartition it, adding a few more partitions to shard the overall size.

however i don't find any information on how to add partitions in this situation.

i always did it with downtime, dumping it, truncating it, dropping it, recreating it with new partitions, and importing the dump, BUT as the table grows, the downtime grows, so i am asking if there is a re-partition command in mysql, with no-to-low downtime. ?

thx in advance.

CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191)),
  KEY `bk1` (`post_id`,`meta_key`(191),`meta_value`(191)),
  KEY `bk2` (`meta_key`(191),`meta_value`(191))
) ENGINE=InnoDB AUTO_INCREMENT=12833303 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
(PARTITION p1 VALUES LESS THAN (50000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (100000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (150000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (200000) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (250000) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (300000) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (350000) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (400000) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (450000) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (500000) ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN (550000) ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN (600000) ENGINE = InnoDB,
 PARTITION p13 VALUES LESS THAN (650000) ENGINE = InnoDB,
 PARTITION p14 VALUES LESS THAN (700000) ENGINE = InnoDB,
 PARTITION p15 VALUES LESS THAN (750000) ENGINE = InnoDB,
 PARTITION p16 VALUES LESS THAN (800000) ENGINE = InnoDB,
 PARTITION p17 VALUES LESS THAN (850000) ENGINE = InnoDB,
 PARTITION p18 VALUES LESS THAN (900000) ENGINE = InnoDB,
 PARTITION p19 VALUES LESS THAN (950000) ENGINE = InnoDB,
 PARTITION p20 VALUES LESS THAN (1000000) ENGINE = InnoDB,
 PARTITION p21 VALUES LESS THAN (1050000) ENGINE = InnoDB,
 PARTITION p22 VALUES LESS THAN (1100000) ENGINE = InnoDB,
 PARTITION p23 VALUES LESS THAN (1150000) ENGINE = InnoDB,
 PARTITION p24 VALUES LESS THAN (1200000) ENGINE = InnoDB,
 PARTITION p25 VALUES LESS THAN (1250000) ENGINE = InnoDB,
 PARTITION p26 VALUES LESS THAN (1300000) ENGINE = InnoDB,
 PARTITION p27 VALUES LESS THAN (1350000) ENGINE = InnoDB,
 PARTITION p28 VALUES LESS THAN (1400000) ENGINE = InnoDB,
 PARTITION p29 VALUES LESS THAN (1450000) ENGINE = InnoDB,
 PARTITION p30 VALUES LESS THAN (1500000) ENGINE = InnoDB,
 PARTITION p31 VALUES LESS THAN (1550000) ENGINE = InnoDB,
 PARTITION p32 VALUES LESS THAN (1600000) ENGINE = InnoDB,
 PARTITION p33 VALUES LESS THAN (1650000) ENGINE = InnoDB,
 PARTITION p34 VALUES LESS THAN (1700000) ENGINE = InnoDB,
 PARTITION p35 VALUES LESS THAN (1750000) ENGINE = InnoDB,
 PARTITION p36 VALUES LESS THAN (1800000) ENGINE = InnoDB,
 PARTITION p37 VALUES LESS THAN (1850000) ENGINE = InnoDB,
 PARTITION p38 VALUES LESS THAN (1900000) ENGINE = InnoDB,
 PARTITION p39 VALUES LESS THAN (1950000) ENGINE = InnoDB,
 PARTITION p40 VALUES LESS THAN (2000000) ENGINE = InnoDB,
 PARTITION p41 VALUES LESS THAN (2050000) ENGINE = InnoDB,
 PARTITION p42 VALUES LESS THAN (2100000) ENGINE = InnoDB,
 PARTITION p43 VALUES LESS THAN (2150000) ENGINE = InnoDB,
 PARTITION p44 VALUES LESS THAN (2200000) ENGINE = InnoDB,
 PARTITION p45 VALUES LESS THAN (2250000) ENGINE = InnoDB,
 PARTITION p46 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

for more details:

What I would do is set it up with sufficient partitions so that your top (MAXVALUE) partition never gets used. In this way when the time comes to add the next partition, you are going to re-partition the 'empty' MAXVALUE partition which should be a lot quicker.

So you would start by adding a partition for VALUES LESS THAN (2300000) which replaces the MAXVALUE partition.

Then when you get close to 2300000 entries, you would add a new partition for VALUES LESS THAN (2350000). And because you would be splitting the empty MAXVALUE partition it will be relatively quick.


Does anyone have a good recommendation for a book which teaches sharding? Does anyone have a good recommendation for a book which teaches db sharding (from scratch at best)

I have read like 40 different websites talking about sharding.

I'm not saying online sites/blogs are lousy, they are good titbits. However, I need the main meal not just a it of useful info here and there. Basically, I have an idea (I guess) of how to implement sharding but, since it's a terribly complicated concept, I believe there is much, much, much more to it that can be studied on.

A great place to start is the book High Performance MySQL : Optimizations, Backups, Replication and More. Chapter 9 (pages 409 - 456) of the book is entitled "Scaling and High Availability"

There are the subheadings

Scaling MySQL
Planning for Scability
Buying Time Before Scaling
Scaling Up
Scaling Out

Functional partitioning
Data Sharding
Choosing a partitioning key
Multiple partitioning keys
Querying across shards
Allocating data, shards and node
Arranging shards on node
Fixed allocation
Mixing Dynamic and Fixed Allocation
Explicit Allocation
Rebalancing Shards
Generating globally unique IDS
Tools for sharding

Scaling Back
Scaling by Cluster
Load Balancing
Connecting Directly

and two dozen other subheadings


Table structure for many custom profiles I'm currently developing an bulk-email campaign manager that will allow customers to upload as much information as they would like on their subscribers.

For example, I have a basic profile that is available to everyone:

First name
Last name
Email address (etc.)

This is stored in my SubscriberProfile table, and all is good.

However, I want my users to have the ability to create custom fields for subscriber information.

For example, User A might run an e-commerce site and will have the following custom fields:

Address Line 1 (varchar(100))
Address Line 2 (varchar(100))
City (varchar(100))
Postcode (varchar(7))

User B might run a cinema and have the following custom fields:

Favourite cinema (varchar(50))
Favourite genre (varchar(50))

The data could be indexed if the users wishes to use them for searching their subscribers or to create sub-groups of their main subscriber list.

How would I best structure my tables?

I was thinking initially of having 1 table that holds the custom field definition (FieldID, UserID, FieldName) and 1 table that holds all the custom field values (FieldValueID, FieldID, VarcharValue, IntValue, BitValue, etc.) but this won't allow me to index 1 customer's set of data without indexing everyone else's. It will also mean I'll have null values for all but one column per row. Seems a waste.

Another thought I had is to create a table per custom profile per user. This would allow me to index data, but what if 1000 people register for the site? I'm going to have 1000 tables to hold the custom profile data, is that good practice?

Am I approaching this wrong, is there a better way to do this?

Thanks,
Greg.

You can use what is called a SuperType / Subtype data structure. You have one SuperType table that contains information that is the same for all subscribers:

[SuperType]
SubscriberId
FirstName
LastName
Email

Create a SubType table for Ecommerce

[SubType Ecommerce]
SubscriberId (Foreign Key to SuperType)
Address1
Address2
City
State
Zip

Create a SubType table for Cinema

[SubType Cinema]
SubscriberId (Foreign Key to SuperType)
FavoriteCinema
FavoriteGenre

[SuperType] + [SubType Ecommerce] = EcommerceSubscribers
[SuperType] + [SubType Cinema]    = CinemaSubscribers
[SuperType] + [SubType Category3] = Category3Subscribers
[SuperType] + [SubType Category4] = Category4Subscribers

This lets you build new subtype relationships as the need arises.


SELECT FOR UPDATE (NOWAIT) on auxilliary table for serializing updates? Background:
I have an inventory tracking application which needs to avoid the following sort of situation where the same inventory might be used twice due to lack of serialization.  The constraint is that each item of inventory from a pool of inventory must in use only once at any given time (much simplified example):
CREATE TABLE inventory_use (id serial, ref varchar);

Transaction 1:
BEGIN;
SELECT * FROM inventory_use;
INSERT INTO inventory_use (ref) VALUES ('T1 insert');

Transaction 2:
BEGIN;
SELECT * FROM inventory_use;
INSERT INTO inventory_use (ref) VALUES ('T2 insert');

Transaction 1:
COMMIT;

Transaction 2:
COMMIT; -- fails (as desired) at SERIALIZABLE, commits (not desired) at REPEATABLE READ

SERIALIZABLE prevents this from occurring by causing the last commit to fail (and REPEATABLE READ does not).  However, even though my application does not involve many concurrent writes, I'm reluctant to go for SERIALIZABLE because my understanding of its performance characteristics is weak: I'm not yet confident I know how to predict for example what the effect of concurrent reads or of (read or write) throughput will be on performance, perhaps especially on concurrent transactions that aren't connected directly with inventory tracking and probably are not critical to the application.  Probably I should educate myself more about SERIALIZABLE since it looks very useful, but I'd like to understand the alternatives too.
So this question is about alternative approaches to SERIALIZABLE for this kind of problem, in particular using SELECT FOR UPDATE NOWAIT to serialize inventory tracking updates through row locking while running at the READ COMMITTED isolation level.
What I've tried:
I implemented FOR UPDATE NOWAIT locking on the table that records inventory users.  That seems to work fine.
However, that may cause trouble because it can block some other updates to that table that need not be serialized.  This may well not be critical in my application, but it led me to see if I could use a separate table, created just for the purpose of locking, to do the FOR UPDATE locking.  To implement that, before the SELECT FOR UPDATE, I would begin by checking whether an INSERT is needed to represent the pool of inventory whose use needs to be serialized -- perhaps via INSERT ... ON CONFLICT DO NOTHING -- in order to ensure that there is a suitable row that can be locked to serialize the updates.
Question:
I've not been able to find other people using SELECT FOR UPDATE in this style in which:

there is an auxiliary table whose only function is FOR UPDATE row locking, not to store any application data.
FOR UPDATE row locking on existing rows is being used to serialize the creation of new rows

That makes me wonder whether this is a bad way to solve this problem.
I've seen other suggested approaches that are similar, for example using REPEATABLE READ with an auxiliary table and using an UPDATE to that table to implicity acquire a lock: https://peter.grman.at/postgres-repeatable-read-vs-serializable/
Is this (the SELECT FOR UPDATE style I describe above) a sensible technique for solving a problem of this kind, and if so, how should it be done?  I don't have specific "how should it be done" issues in mind other than to benefit from experience here.

You could use transaction level advisory locks to serialize your operations:
BEGIN;
-- take an advisory lock for the duration of the session
SELECT pg_advisory_xact_lock(4711);
SELECT * FROM inventory_use;
INSERT INTO inventory_use (ref) VALUES ('T1 insert');
COMMIT;

The number 4711 is an arbitrary constant.
Then the second transaction using the same code will block until the first one has committed.