MySQL Triggers not working from PHP page but work for manual DB updates via phpMyAdmin I have the following two Triggers in my MySQL DB that fire BEFORE/UPDATE and AFTER/UPDATE on the house_report_details table. The first deletes all records in my scratch table tmp_house_rpt_details and the second inserts a single record into this scratch table for use in subsequent PHP code:

Trigger 1 (BEFORE/UPDATE on house_report_details):

DELETE FROM tmp_house_report_details

Trigger 2 (AFTER/UPDATE on house_report_details):

CREATE TRIGGER `trg_create_tmp_house_rpt_detail` AFTER UPDATE ON `house_report_details`
   FOR EACH ROW
      INSERT INTO tmp_house_report_details
         (rpt_week_num, client_id, residence_id, curr_step, curr_step_dur,
          prev_wk_step, prev_wk_step_dur, prev_bal, wkly_rent, curr_bal)

      SELECT (NEW.rpt_week_num + 1), NEW.client_id,
             vw_next_wk_house_assignments.residence_id, NEW.curr_step,
             (NEW.curr_step_dur + 1), NEW.curr_step, NEW.curr_step_dur,
             NEW.curr_bal, vw_next_wk_house_assignments.rent_amt,
             (NEW.curr_bal + vw_next_wk_house_assignments.rent_amt)

        FROM vw_next_wk_house_assignments
       WHERE vw_next_wk_house_assignments.client_id = NEW.client_id
      ON DUPLICATE KEY UPDATE
             residence_id = vw_next_wk_house_assignments.residence_id,
             curr_step = NEW.curr_step,
             curr_step_dur = (NEW.curr_step_dur + 1),
             prev_wk_step = NEW.curr_step,
             prev_wk_step_dur = NEW.curr_step_dur,
             prev_bal = NEW.curr_bal,
             wkly_rent = vw_next_wk_house_assignments.rent_amt,
             curr_bal = (NEW.curr_bal + vw_next_wk_house_assignments.rent_amt)

Here are sample rows from the 'master' table - house_report_details - that has the two triggers. The temp/scratch table, tmp_house_report_details, that the triggers delete/insert into has the exact same schema as the master:

SELECT * FROM `house_report_details` LIMIT 3

id rpt_week_id rpt_week_num client_id residence_id curr_step curr_step_dur prev_wk_step prev_wk_step_dur num_mtgs sponsor_contact chores_flg prev_bal wkly_rent other_chgs amt_paid curr_bal last_updated_by last_updated_dt

9   10         10           3         1            3         2             3
        1                9        5               Y          0.00     -125.00
0.00       125.00   0.00     Elvis Presley   Mar-6-2019 03:37pm CST

10  10         10           6         1            3         3             3
        2              9          7               Y          -150.00  -75.00
0.00       225.00   0.00     Elvis Presley   Mar-6-2019 03:22pm CST

11  10         10           9         1            7         1             6
        4              9          6               Y          0.00     -100.00
0.00       100.00   0.00     Elvis Presley   Mar-13-2019 08:26am CST

The triggers work perfectly in my local dev environment and work on my hosted server environment if I manually update information in the house_report_details table using phpMyAdmin.

The BEFORE/UPDATE trigger to delete the scratch table record also works when the table updates are made from my PHP web pages but the AFTER/UPDATE trigger does not work for some reason and causes my entire table update transaction to fail (as failed trigger execution should).

I cannot find anything to help tell me why a trigger will work when manually invoked (via phpMyAdmin, for example) but not when the same DB update is performed via PHP/MySQL code in my web pages.

My hosting provider is BlueHost and I am running MySQL v5.6.41-84.1 and PHP v7.0.33.

I really want to be able to use the power that triggers provide and hope I can get this resolved quickly so I don't have to convert my triggers into PHP/MySQL code in my PHP pages instead.

This is my first question here, so I hope I have provided enough information to troubleshoot this issue. Happy to provide additional details, if needed.

Thank you,
Steve.

Type SHOW CREATE TRIGGER trigger_name into the query window. This will give you the SQL code that creates trigger.

You can then add it to your question so we can help you with it.

Also, when modifying your trigger, instead of using GUI, use the query window. Just put DROP TRIGGER trigger_name in the top of your trigger script.


Temprary tables on Mariadb with no activity on the server I have a nagios check for mariadb 10.4 server running on Debian9, WARNING - 59.92% of 670 tables were created on disk | pct_tmp_table_on_disk=59.92%;50;75 pct_tmp_table_on_disk_now=77.46%. However, there's little to no activity on the server, slow query log doesn't registered anything.
In my.cnf I have tmp_table_size          = 32M and max_heap_table_size     = 32M.
The tmpdir is /tmp and i see nothing there as well.
I am not sure if the check is wrong or is there anything that i'm missing...Any suggestion would be appreciated

"slow query log doesn't registered anything" -- Decrease long_query_time down to 1 or less.  (Even 0 if you get desperate.)
Temp tables may be in RAM or on disk, depending on several things.  Please provide SHOW CREATE TABLE for any tables that might involve temp tables.
The prime candidate for going straight to disk (and ignoring the two ...table_size settings) is when there is any kind of TEXT or BLOB column in the query.  Another case is that it will fill up the 32M you have configured, then copy it to disk to continue.
"/tmp and i see nothing there" -- How are you looking?  The code probably creates the disk table, then "rm's" it immediately.  After that it proceeds to use it.  If a crash occurs, the file is already gone, so there is no trash left behind.  (Magic!)


Skipping VM backups for high workload SQL Server? For those who have virtualized SQL Servers with very high volume transaction workload (very high rate of data change), I am curious how you handle VM backups, or if you even do them.

Of course, I manage a separate chain of transactionally-consistent native database backups.  But the standard policy at the virtualization level has always been to backup all VMs.  This is currently using a typical 3rd party VM backup solution (Initiate VM snapshot, copy snapshot data to backup appliance, release snapshot).  However, these servers have an extremely high rate of data change, resulting in the change files growing large very quickly.  And the system is reaching a point where it is choking on the amount of data that needs merged during the snapshot release.

I am toying with the idea of skipping the VM backups altogether.  I don't permit any other apps to be installed on SQL Servers, and the base OS and SQL configuration is pretty vanilla.  So it wouldn't take much in a failure scenario to just deploy a new VM from template, install SQL bits on it, and start restoring database backups.

Thoughts? Alternatives? Pitfalls I'm missing?  Thanks for your input.

One option is to set the disks that contain the data and log files of the user databases to independent disks, so the VM snapshot backups will not include those disks.  As long as your database backup strategy is sound, you don't need to include the data and log files in the VM backup.  This may not be possible, of course, if you have applications and database files installed on the same disk.

One helpful thing here is to have your master, model, and msdb on one of the disks that is included in the VM backup, so that when the VM backup is restored you won't have to restore the system databases, which is a bit of a pain.  I scripted a process to move system databases--can be found at Script to move SQL Server System Database Files.

If this approach is used, restoring the server will take some work as you'll have to restore the VM backup, then create the disks and directories for the database files, then restore databases, etc.

A side benefit of this approach is that if you have really large volumes (multi-terrabyte), a VM snapshot of all of the disks will typically pause the server for several seconds, so this method will effectively eliminate that "stunning" effect.  If you are using clusters/availability groups, the pause can cause availability groups to go offline for a bit.

One reason why you don't want to stop backing up the VM is that if you build a new server or restore the OS from an old backup, it will require a lot of time to get all of the Windows Updates installed, and this could amount to a significant amount of downtime.  If you have a VM template (or whatever deployment method that is in use) that is kept updated this of course wouldn't be an issue.


PostgreSQL jsonb_set performance I searched the internet and PG documentation for hours and couldn't find any benchmark for it.
In MySQL 8.0 they specifically state that now the optimizer is smart and can do in-place modification of JSON column without updating the whole column. But I couldn't find any similar article about PG. jsonb is around for a long time already and it would be really expected that such common thing as updating a single field of large JSON would be optimized, but I couldn't find any notion about it. Moreover I found here an answer from 3 year ago (PostgreSQL update JSONB without jsonb_set) that proves that updating whole column as text is faster than using jsonb_set.
I'm trying to model a table with 2M rows, each will have 2 JSON fields with 3600 simple numeric fields (date: value). I will need to add 1 field to each JSON daily, meaning 4M json_sets.
Is it feasible, or I need to switch to some NoSQL like MongoDB for this feature?
Thanks

Every UPDATE in PostgreSQL creates a new version of the row.  There is no in-place update.  So not only would a new JSON be created, but also all other columns in the table would be copied.
Updating part of a JSON is not common in relational databases, or at least it shouldn't be.  If you feel the need to do so, you have chosen the wrong data model,  and you would be much better off using table columns instead of JSON attributes.  If you then split the data across several tables using a process called “normalization”, an UPDATE doesn't hurt quite as much.


Recommended ways to upgrade AlwaysOn AG from 2014 to 2016 What is the recommended process of upgrading Availability Groups with one Sync Replica from SQL 2014 to 2016?

My understanding is that in-place upgrades are not really liked among database professionals. Is there a way to avoid in-place upgrades in Availability Groups? Is there a possibility to join a 2016 server into an existing 2014 availability group and to failover there, then "kill" one of the other instances and upgrade it? (e.g. by removing/re-installing SQL side-by-side on the same host)

Are there any other upgrade scenarios that we should consider?

The ideal solution would be that we always have 2 online replicas, and that we don't need to do any in-place upgrades.

The short answer is yes, much the same as when you mirror from a lower version to a higher version.  Make sure you do not have the 2016 as readable so it's not trying to upgrade the database version.  That said I haven't tried this, just going on how log shipping/mirroring work.

If it works it is only supported doing this as a means to upgrade, leaving 2014 replicating to a 2016 replica for extended periods of time is not going to be supported by MSFT if you hit an issue.


Retrieve additional columns in recursive CTE This works as far as getting the amount of children a "thread" has but now I can't seem to get it to pull parent row columns. Parent rows have parent_id is null, trees can be any level deep.

I manage to do it with two separate queries but there has to be a way to just use one and get the count of the children:

with recursive all_comments as (
   select id, parent_id, id as root_id
   from comment
   where parent_id is null
   union all
   select c.id, c.parent_id, p.root_id
   from comment c
     join all_comments p on c.parent_id = p.id
)
select root_id, count(*) as comment_count
from all_comments
group by root_id;

How would I pull content column from the parent comment in this fiddle?

http://sqlfiddle.com/#!15/158ea/15

Your fiddle does not open, so I'm not sure if I understand correctly what you have and what you want, but I'd venture to guess you need to join back to comment on id, something like this:

with recursive all_comments as (
   select id, parent_id, id as root_id
   from comment
   where parent_id is null
   union all
   select c.id, c.parent_id, p.root_id
   from comment c
     join all_comments p on c.parent_id = p.id
)
select a.root_id, a.comment_count, com.content from
(select root_id, count(*) as comment_count
from all_comments
group by root_id) as a
inner join
comment as com
 on a.root_id = com.id and com.parent_id is null;


SQL Server access table at a time Multiple users access to write on the same table on same time on same record. How to avoid this problem in SQL Server?

Already we implemented record locking. But the record locking will be be enable at updated time. For example, I have one record, user 1 opens the record and edits something, user 2 also opens the same record and edits. Both are submitted at the same time. If they submitted serially, there would be no problem. If they submitted at the same time (it is occurring rarely). How to solve this problem?

A lock will be taken by whichever process accesses the row (or table) first, and the second process will have to wait until the lock is released. This happens automatically.

Occasionally, there will be a deadlock where two processes attempt to update the same record, and one of these will be killed by SQL Server. The resolution there is to retry the process again.

In your example, if two users try to update a row at the same time, the first user there will change it, and then the second user will change it again once the lock is released. That's literally how it was designed to work.

If you want to prevent the second user from changing the record because the first user already changed it, that's application logic, well outside of the scope of SQL Server.


Cleanup orphaned records I have two tables in MySQL: orders and line items.

When orders are deleted, the line items are orphaned. Thus, I need a query that will delete the line items that were associated with the order before the order was deleted.

Something like:

 DELETE FROM line_items WHERE line_items_id NOT EXIST IN TABLE orders COLUMN orders_id

In other words, of the order id referenced in the line item row does not exist, then delete the line item in question.

(I know... it would have been better if the person who designed this had used foreign keys and cascade... but they didn't).

DELETE FROM Line_Items
WHERE Line_Items.Order_Number -- this should be an ON DELETE CASCADE FK.
NOT IN
(
  SELECT id
  FROM Orders
); -- not tested, don't have running system, but this or similar should work.

Note: Don't call a table "Order" - makes debugging difficult and system non-portable. Personally, I'm against the use of plural names for tables - I usually call it System_Order (or Shop_Order) or similar.


Resume download on DB exporting I am attempting to export a large database and import it to another PC over the Internet.

Sometimes my Internet connection fails resulting in a database export error, causing me to have to restart the export from the beginning.

Is it possible to resume the export from point where connection failed?

I am using Microsoft SQL Server 2008 R2.

If you have remote access to the server you are transfering I suggest you make a full back up of the database put it in a FTP or torrent (last option not very safe but easier if you don't know how to build a FTP server). Then when the file is transfered to the other server, you restore the backup.
This option will save you the trouble with the internet since the transfer to files will be local and FTP or torrent transfers can be paused/resumed if case of connection failed.


MySQL query 'going away' on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob I have a mysql insert on update query like so

insert into table (col1, col2, col3) values (1,2,'huge blob with 12524 chars') on duplicate key update col3 = 'huge blob with 12524 chars';

col1, col2 is a composite key and it has index on it.
There are a total of 91 records in table
All other blobs in table have less than 1000 chars
If I give col3 a small value then this query runs instantly
If I give a small value in the insert part but large value in the update part it takes 5 seconds to execute
If I give large value in insert part but small value in second part it dies with a 1 minute timeout.

What could be causing this query to die on execution after being stuck for 1 minute?
What should I look at?

What could be causing this query to take so long to execute and dying if blob value is huge?

This sounds like you have to increase the size of your MySQL Packets

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining it:

  MySQL network communication code was
  written under the assumption that
  queries are always reasonably short,
  and therefore can be sent to and
  processed by the server in one chunk,
  which is called a packet in MySQL
  terminology. The server allocates the
  memory for a temporary buffer to store
  the packet, and it requests enough to
  fit it entirely. This architecture
  requires a precaution to avoid having
  the server run out of memory---a cap
  on the size of the packet, which this
  option accomplishes.

  The code of interest in relation to
  this option is found in
  sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
  particular attention to
  net_realloc().

  This variable also limits the length
  of a result of many string functons.
  See sql/field.cc and
  sql/intem_strfunc.cc for details.

Since MySQL Packets can hold rows of data, larger items in the packet can cause a lot of packets to filter in-and-out to prevent whole chunks of related data from splitting during processing. This can be a silent killer of DB connections for no apparent reason. If fact, I wrote a post about how this can affect certain mysqldumps.

Try increasing the max_allowed_packet (256M) using the following command:

SET max_allowed_packet = 1024 * 1024 * 256;


Reverse SQL Server Listener? The company that I work for runs our DMS database in an HA group with Always On. So there is a listener that we connect any applications to.

We also have SSIS on the secondary replica of the HA group, which seems to be pulling data from the primary replica which puts a strain on the application and the end users.

Is there a way to set up a reverse listener where the application or user will be pointed toward the secondary replica instead of the primary replica?

Thanks!

Check out this article on MSSQLTips.com on how to configure readable secondaries and read-only routing in SQL Server 2012 AlwaysOn Availability Groups.

Basically, as long as you're only doing read workload, you enable readable secondaries and set up routing in your AG and any requests with Application Intent=ReadOnly in the connection string will connect to the secondary and perform its read workload there.

If your application needs to do any write workload, it must use a different connection that does not specify read-only intent in the connection string otherwise it will error when trying to perform the writes.

Also, make sure you validate your licensing scenario to make sure your secondary is properly licensed for performing read workloads.


Field not being sorted in ascending order in Visual Studio 2010 I have the following SQL:

;WITH times AS (
    SELECT '0830' AS t
    UNION
    SELECT '0930' AS t
    UNION
    SELECT '1030' AS t
    UNION
    SELECT '1130' AS t
    UNION
    SELECT '1230' AS t
)
SELECT t as time
FROM times
;

When I execute this in SSMS (SQL Server Management Studio), it displayed correctly as

0830
0930
1030
1130
1230

However, when I put this into a drop down list inside Visual Studio 2010, the list becomes incorrectly sorted:

1030
1130
1230
830
930

The 0 is disappearing from the 0830 and 0930 and both 0830 to 0930 is being shifted from the 1st two items in the list to the last two items.

How can I make the drop-down show the list in the correct order?

Specials thanks to Shark, Aaron Bertrand and Jon Seigel that have enlighten me.

I have use the ORDER BY CONVERT(INT, time) and it now solved the application issue problem. (Fortunately, the change to the SQL statement was a quick way to solve this problem)


Select all from A where attribute exists in B I have a question involving relational algebra. Suppose I have two relations A and B, where B has a foreign key that references A.
Using relational algebra, how do I select all tuples from A where variable X exists in B? Example:

Relation 1
Cars(car_id,model)

Relation 2
Ford(car_id,year)

How do I select from Cars, all the tuples where id exists in Ford? Example:
Cars  car_id  model
      -------------
       1    a
       2    b
       3    c

Ford  car_id   year
      1        2000
      3        2002

What I would like is: select all from cars where id exists in Ford, which returns:
Result:  car_id  model
        --------------
         1    a
         3    c

The (somewhat trivial) answer seems to be:

Cars ⋉ Ford

Relational algebra, semijoin (Wikipedia)

Alternatively (in response to a comment):

πR(R ⋈ S)


Join tables and summarize rows based on condition I have two tables.

One containing countries with an ID and the geometry

The other containing customers with a name, geometry and a boolean value showing if they are active or not.
Countries
iD     name     geometry
1634   UK       xxx
2357   USA      xxx
3345   Mexico   xxx
4694   Italy    xxx

Customers
name         geometry    statusActive
Hernandez    xxx         TRUE
Taylor       xxx         FALSE
Monte        xxx         TRUE
Winter       xxx         TRUE
Best         xxx         TRUE
Twist        xxx         TRUE

I want to find out, how many active customers there are in each country.
Step 1 - Join data: I could join the two tables based on the geometry. With ST_Contains I can test, which of the customers is located in which country. This works out well. I get as result a table from Customers which has additionally the column of the country ID. In addition, only customers with an active status are returned.
This is my SQL statement:
SELECT customers.*, countries.id
FROM customers
JOIN countries ON ST_Contains(countries.geometry, customers.geometry)
WHERE customers.statusActive IS TRUE

This is the resulting table:
Customers
name        geometry    statusActive    id
Hernandez   xxx         TRUE            1634
Monte       xxx         TRUE            4694
Winter      xxx         TRUE            2357
Best        xxx         TRUE            2357
Twist       xxx         TRUE            4694

Step 2- Sum up the customers in each country.
I do not know how I can achieve this. I already tried to use GROUP BY based on the country ID, but this results in an error, that I have to include all columns from the SELECT statement. When I remove the customers.* (all fields from customers) from the SELECT, the query is running forever.
This is how the desired result should look like:
NumberActiveUsers   ID
1                   1634
2                   2357
0                   3345
2                   4694

Do I need another function to sum up the entries, or is GROUP BY the correct function?

I had to edit some parts to get a query, as i didn#t have geommetry

CREATE TABLE countries (
  "id" INTEGER,
  "name" VARCHAR(6),
  "geometry" VARCHAR(3)
);

INSERT INTO countries
  ("id", "name", "geometry")
VALUES
  ('1634', 'UK', 'xxx'),
  ('2357', 'USA', 'yyy'),
  ('3345', 'Mexico', 'zzz'),
  ('4694', 'Italy', 'aaa');

CREATE TABLE customers (
  "name" VARCHAR(9),
  "geometry" VARCHAR(3),
  "statusActive" boolean
);

INSERT INTO customers
  ("name", "geometry", "statusActive")
VALUES
  ('Hernandez', 'xxx', 'TRUE'),
  ('Taylor', 'xxx', 'FALSE'),
  ('Monte', 'aaa', 'TRUE'),
  ('Winter', 'yyy', 'TRUE'),
  ('Best', 'yyy', 'TRUE'),
  ('Twist', 'aaa', 'TRUE');

SELECT  COALESCE(countr,0) NumberActiveUsers ,co.id
FROM
(SELECT countries.id, Count(*) countr
FROM customers
JOIN countries ON countries.geometry =  customers.geometry
WHERE "customers"."statusActive" IS TRUE
GROUP BY countries.id) t1 RIGHT JOIN countries co ON t1.id = co.id

numberactiveusers |   id
----------------: | ---:
                1 | 1634
                2 | 2357
                0 | 3345
                2 | 4694

db<>fiddle here


'kill ' gives ERROR 1095 (HY000): You are not owner of thread ... even though I am I did an ALTER TABLE on a huge table on a MariaDB Galera Cluster 10.2, but after a while of waiting I changed my mind and tried to kill the connection. However, MariaDB said ERROR 1095 (HY000): You are not owner of thread 123 even though I most definitely am the owner. So what is going on?

The error message is misleading. Galera can mark user threads as high priority which means they can't be killed. Currently, this gives the ERROR 1095 (HY000): You are not owner of thread... See MDEV-12008 for details.

This will probably affect other distributions of Galera as well, such as Percona XtraDB Cluster.


Filtering of sql output I want to migrate the database.
When I disable the constraints before adding the data to the output file I get extra lines:

SQL> spool disable_constraints.sql;
SQL> select 'alter table '||table_name||' disable constraint '||constraint_name||';'
from all_constraints where owner = 'THE_OWNER' and constraint_name not like 'BIN%';
SQL> spool off;

Part of disable_constraints.sql:

SQL> select 'alter table '||table_name||' disable constraint '||constraint_name||';' from all_constraints where owner = 'THE_OWNER' and constraint_name not like 'BIN%';

    'ALTERTABLE'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
    --------------------------------------------------------------------------------
    alter table DATABASECHANGELOGLOCK disable constraint SYS_C0012929;
    alter table DATABASECHANGELOGLOCK disable constraint SYS_C0012930;
    alter table DATABASECHANGELOGLOCK disable constraint PK_DATABASECHANGELOGLOCK;
    alter table DATABASECHANGELOG disable constraint SYS_C0012932;
    alter table DATABASECHANGELOG disable constraint SYS_C0012933;
    alter table DATABASECHANGELOG disable constraint SYS_C0012934;
    alter table DATABASECHANGELOG disable constraint SYS_C0012935;
    alter table DATABASECHANGELOG disable constraint SYS_C0012936;
    alter table DATABASECHANGELOG disable constraint SYS_C0012937;
    alter table ARCH_TA1_TOTAL_ENTECONOMICS disable constraint SYS_C0012938;
    alter table ARCH_TA1_TOTAL_ENTECONOMICS disable constraint SYS_C0012939;

    'ALTERTABLE'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
    --------------------------------------------------------------------------------
    alter table ARCH_TA1_TOTAL_ENTECONOMICS disable constraint SYS_C0012940;
    alter table ARCH_TA1_TOTAL_ENTECONOMICS disable constraint SYS_C0012941;
    ....

For example, an extra line in the file:

'ALTERTABLE'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------

How to filter out the extra lines?
I would be very grateful for the information. Thanks to all.

For that you should configure SQL*Plus output. Documentation what can be done and how to do that is here.

In your case it is:

SET HEADING OFF


Design critique on a table to keep track of which person flipped a switch at what time I created a table to keep track of which person flipped a switch at what time. It consists of:

create table records
( name varchar not null
, changed_at timestamp with time zone not null
, organization varchar not null
, value bool not null)

To determine the latest value, I can do:

select value, name
from records
where organization=?
order by changed_at desc
limit 1;

I need to keep track of all changes to the system, hence this schema.

However, I’m considering making a separate table for organizations, and then use a foreign key constraint on records.organization.

Please critique my table choice.

The table should have a primary key - you can add an auto increment field as an alternative.
I would create a table Person to store persons (store the name here) and a table Organization to store organizations. Given that, I would store person_id instead of name and I would store organization_id instead of organization. I assume that the same person might flip the switch more than once. If that is not the case, then perhaps your design works as well. If the persons are employees of an organization then you don't need the organization/organization_id field, you need another table where you store the employment relationship.
I would add indexes on the two FK fields added at #2 and perhaps the changed_at field, this depends on how you want to search the table, but at the very least you need to add an index for each FK field.
In your post there is no information about the switch that was flipped. Does it matter? Do you have a list of switches? If you do, you might want to add to this table a switch_id to keep track of which switch was flipped. Again, only if this information matters.

Re #2, the design can vary, depending on how flexible you want it to be to accommodate future requirements. You can go nuts with it and define entities that can model organizations, divisions, business units etc. if you need to. Does it matter? It depends on the requirements and what kind of information you want to extract from this table. Do you want to classify the people that flip the switch? Then you need to add more tables. For now I would keep it as simple as possible given the current requirements and with an eye on the future requirements but don't overdo it.


Query optimization in case of TIMESTAMPDIFF I have a table where I need to delete all rows older than 5 hours. Currently I'm doing:

delete from my_table
where TIMESTAMPDIFF(HOUR, from_unixtime(round(created_on/1000)), now()) > 5

The created_on column is a time stamp stored in milliseconds. I think adding an index on created_on column won't help since the query has a computed value (correct me if I'm wrong).

The delete statement works fine for smaller data sets, however for larger data sets (30-40k rows) it often gets stuck. Why does the delete statement get stuck for large data sets and how can I improve performance?

You should move all the calculations to one side of the comparison:

WHERE created_on < (unix_timestamp(now()) - 5*60*60) * 1000

5*60*60 equals 5 hours


Implementating user notifications list using Aerospike I need to choose the right DB for a notifications system that needs to handle billions of notifications. The record structure is as follows:
[user_id, item_type, item_id, created_at, other_data]

The inserts are going to be in bulks up to hundreds of thousands at spikes.
And it needs to support thousands of selects per minute of this kind:
select * from user_notifications where user_id=12345 order by created_at limit 10
select * from user_notifications where user_id=12345 and item_type='comment' order by created_at limit 10
--- and for the pagination next page:
select * from user_notifications where user_id=12345 and item_type='comment' and created_at>'2020-11-01 10:50' order by created_at limit 10

It should also allow quick updates and deletes and ideally have TTL on each record.
Right now it's implemented using MySQL, we only have 400M rows and it's already slow as hell. And bulk cleanup is just impossible.
Initially, I thought ScyllaDB/Cassandra is ideal for that. If I set the primary key to be [user_id, item_type, item_id] (user_id being the partition key) for inserts/updates/deletes and [user_id, item_type, created_at] as secondary index. CQL seems straightforward in this case and it should work fast (correct me if I'm wrong). The problem is that we are Ruby-On-Rails based and there's no good ruby client library for that. The one listed in the ScyllaDB clients' list (https://github.com/datastax/ruby-driver) is in maintenance mode and I'm not sure it will be updated with new Ruby versions, etc.
Recently, I heard about Aerospike and their benchmarks look really cool, but I couldn't figure out how to implement the above requirement using Aerospike's architecture. Especially as their secondary index seems to be always in memory which makes it impossible to index billions of rows.
This notifications schema seems to me like something very common, but still, I couldn't find a good article describing all the ideal ways to implement it.
Any suggestions are welcome.
Thanks

Since ScyllaGreg is putting in the good word for Scylla, I thought I'd represent for Aerospike, where I work. Of all the C* variants I like ScyllaDB the best, but I believe that in the case you describe, and in general for row-oriented situations, Aerospike Database will perform better than ScyllaDB using less hardware. I've written a few Medium articles (@rbotzer) explaining why, based on realistic use cases.
Modeling
Here's one possible way to model this in Aerospike.
I am assuming that you always want to access the notifications of a specific user. For that reason, the key for a record (a row in Aerospike-speak) would be the user_id. The rest of the data fits in a single bin containing the following key-ordered Map structure:
{ epoch: [ item_type, item_id, other_data ] }

Where epoch is a way to simplify the created_at datetime into an integer representing minutes, seconds or milliseconds since an arbitrary epoch, such as the date your app went live (ex: minutes since 2020-10-01 00:00). You can choose the resolution that makes sense to you in order to avoid an overwrite. Either way, Aerospike uses MessagePack to serialize this map data, and MessagePack has the nice side effect of rightsizing numeric values into a minimal storage size.
Querying
Now to query this data we'll use the Map operations API:

Adding any information is a simple Map put or put_items, which upsert the data into the Map.
To get all the notifications for a specific user, you just get() the record by user_id.
To paginate through the notifications for a specific user you use Map get_by_index_range(KeyValue, i, 10) with i = 0, 10, 20, ...
To get all the notifications between two points in time (October 2nd) you would use Map get_by_key_interval(KeyValue, 1440, 2880)
To get all the notifications of type 'comment' you would use Map get_all_by_value(KeyValue, ['comment', *])
To clear out all the notifications in October 2020 you would use Map remove_by_key_interval(Count, 0, 44639)

You can see that there's currently no way to AND a query for a date range and a query for item type. However, Aerospike is extremely fast, with the data being retrieved after looking up a single key and operating on the record data, which is stored contiguously. You can easily get slightly more data (everything in a date range) and filter for the item_type on the application side.
Notice that this modeling approach requires zero secondary indexes. It just uses the power of Aerospike's even data distribution, extremely fast primary index  lookups, and single IO record reads from storage.
Future for Querying
Aerospike Database 5.2 added Expressions, which can already do things like chain a Map get_by_index_range => get_all_by_value => get_by_index_range, but only in the context of a filter. This means that you can use a complex expression as a conditional for whether to apply an operation to a query, scan, batch read or single record operation.
In the near future you'll be able to apply an expression to the operation, which will solve the AND limitation. If you wanted to find all the notifications in a time range that are of a specific type and also paginate through them, you would
exp(get_by_key_interval(KeyValue, 1440, 2880), get_all_by_value(KeyValue, ['comment', *]), get_by_index_range(KeyValue, 0, 10))
Alternative Modeling
As I described in Aerospike Modeling: IoT Sensors, you can choose to partition the user notifications by day, with the key being userID:YYYYMMDD.
This would allow you to keep the size of the records reasonable if you expect too many notifications. It also makes it convenient to purge data past a certain age with deletes.
However, I suspect you will not run into this problem. Let's assume a notification is 40 bytes, then 256 notifications take at most 10KiB. In reality those are probably smaller, and as I mention in the article, MessagePack will rightsize some of those data types. If you use (the Aerospike EE feature) compression it'll be smaller still. Even if it is 40 bytes, the default 1MiB write block can hold 26,000 notifications for a single user, and you can trim those daily with a remove_by_value_range.
Another nice thing about partitioning by days is that only one record is ever written to ('today').

Now to address your comment about secondary indexes in Aerospike, it's true that  they're currently stored in memory, however the memory cost isn't as bad as you expect.
First, Aerospike doesn't buffer data in memory like all C* variants. In the classic Aerospike Database deployment you store all the data on SSD, and only the indexes consume memory. If you read my Medium posts, I go into why Aerospike is faster than ScyllaDB at retrieving a random record out of an arbitrarily large stack of records.
Every record has a 64 byte entry in the primary index. As for secondary indexes, the absolute worst case if you have a single key per distinct value indexed (and you should never use a secondary index in such a situation) would cost you 85 extra bytes.
A quick calculation for 1 billion records is 10^9 * (64+85) = 139GiB. If you have a 3 node Aerospike cluster the memory cost is 46GiB per node. Considering that you don't need memory for the data, and there's very little overhead on top of that, you end up with a quite affordable RAM consumption that fits on a small cluster.
Going forward, quite a bit of change will be coming to secondary indexes in Aerospike, however I still suggest modeling in a way similar to described above. Using Map and List operations on a single record will be faster and consume less resources than a query using secondary indexes in a C* database.


Implications of using a Unique NonClustered Index with Covering Columns instead of a Primary Key We have a large table [MyTable] which currently has both a Primary Key, and a Unique Non Clustered Index on the same column ([KeyColumn]). The U NC index also has additional covering columns.

Having both PK and Unique NC Index on the same columns seems redundant, so I was considering deleting the Primary Key and instead using the Unique Non Clustered index for Referential Integrity purposes.

Note that the table is clustered by another column entirely.

i.e. So we have:

ALTER TABLE [MyTable]
    ADD CONSTRAINT [PK_MyTable]
    PRIMARY KEY NONCLUSTERED ([KeyColumn])
GO

AND

CREATE UNIQUE NONCLUSTERED INDEX [IX_MyTable_SomeIndex]
    ON [MyTable] ([KeyColumn])
    INCLUDE ([Column1], [Column2])
GO

As far as I know, it is not possible to add covering columns to a Primary Key, so I intend to do:

Drop the Foreign Key Constraints reliant on MyTable.KeyColumn
Drop the Primary Key on MyTable.KeyColumn entirely
Re-Add the foreign keys to the table (i.e. RI will be enforced via MyTable.KeyColumn)

The only implication I can think of doing this is that we won’t get the visual key symbol on our ERD diagrams, and that the (leaf) index density will be less because of the included columns.

I've read https://stackoverflow.com/questions/487314/primary-key-or-unique-index and am happy with the integrity and performance aspects of doing this.

My Question is : Is this approach flawed?

Edit
What I'm trying to accomplish : Performance Optimisation and Spring Cleaning. By removing either the PK or an Index, there will be less pages needed for my indexes = faster writes, plus also maintenance/operational benefits, i.e. one less index to keep defragged etc.

To put some background to this, I've never before had a table which is being referenced, without a PK. However, the fact that a NC Index with covering columns was added to the table means that I need to adapt my thinking.

Performance Optimisation. By removing either the PK or an Index, there
  will be less pages needed for my indexes = faster writes, plus also
  maintenance/operational benefits, i.e. one less index to keep
  defragged etc.

You need to be able to prove that what's proposed will actually help (cost vs. benefit). For what reason is this change being considered? Are there actually performance issues with this table, or did it just "look wrong"?

Here are some other questions that will help you come to the best decision for your environment:

How much time would be saved in the maintenance window? In the backup window?
How much storage space would this save (data files, log files, backups, etc.)?
Is INSERT performance on this table really a bottleneck right now? How much would it improve? Is removing an index the best strategy to fix that problem?
Will this cause problems with database tools and frameworks (ORMs particularly) that expect each table to have a primary key and not just a unique index? Transactional Replication requires a primary key on published tables.
Is a self-documenting database schema important?
Despite its limited use, is the narrowness of the primary key index still allowing the optimizer to produce more efficient plans for certain queries? (Use sys.dm_db_index_usage_stats to find out.)

Personally speaking, from what you've told us, I would leave it alone until it can be proven that both (a) the extra index is a problem, and (b) removing it is the solution.


How can I dump all tables to CSV for a PostgreSQL schema? I have a database with a lot of schemas in it and I want to dump the each of the table contents to CSV.  I'm aware of the COPY command but I'm not sure how to script something that will read all of the tables in a schema and execute the COPY against them.

Here's a shell script that can do what you want:

SCHEMA="myschema"
DB="mydb"

psql -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" $DB |\
  while read TBL; do
    psql -c "COPY $SCHEMA.$TBL TO STDOUT WITH CSV" $DB > $TBL.csv
  done

Make sure you set the DB and SCHEMA variables to your particular database and schema.

The wrapping psql command uses the A and t flags to make a list of tables from the string passed to the c command.


Results in sys.dm_db_index_usage_stats I have this query below that I use to clean up indexes that are not being used anymore to reduce runtime and overhead during index maintenance and during inserts. On some tables that I've ran this against, it returns indexes with 0 scans, 0 seeks and 0 lookups and there are some indexes that just returns NULL on the scans, seeks, lookups column. My understanding to those NULL's is that, that specific index has still not been used that's why there is no data for that index in sys.dm_db_index_usage_stats. That being said, whats the difference between these and those indexes that show up in sys.dm_db_index_usage_stats but has 0 scans, seeks and lookups? I appreciate everyone's ideas

DECLARE @tblname VARCHAR(100) =  'Login' --<<<TABLE NAME HERE

SELECT CONCAT(OBJECT_SCHEMA_NAME(i.[object_id]), '.', OBJECT_NAME(i.[object_id])) AS [OBJECT NAME]
       ,CASE
           WHEN I.name IS NULL THEN
               'heap table'
           ELSE
               I.[name]
       END AS [INDEX NAME]
      , i.index_id
      , s.user_seeks +s.user_scans + s.user_lookups AS [Total Scan/seek/lookup]
      ,s.user_seeks
      , s.user_scans
      , s.user_lookups
FROM sys.indexes AS I
     LEFT JOIN  sys.dm_db_index_usage_stats AS S
        ON  I.index_id = S.index_id
        AND I.[object_id] = S.[object_id]
WHERE OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1
      AND OBJECT_NAME(i.object_id) = @tblname
      --AND i.index_id <>1
      ORDER BY s.user_lookups DESC

If an index hasn't been touched at all since start-up, then it isn't in the result from sys.dm_db_index_usage_stats. Doing an outer join to that DMV means columns you pick from it will be NULL. As for those that are 0, they have been touched according to sys.dm_db_index_usage_stats, since it does return a row for that index.

You should be able to verify doing a SELECT * FROM sys.dm_db_index_usage_stats without involving sys.indexes and see that for "the NULLs" the index isn't retured and for "the 0's" the index is returned and potentially some system scans etc.


Mirroring SQL error log - Error: 8474, State: 11 I'm getting these errors in SQL error logs:

  An error occurred in a Service Broker/Database Mirroring transport connection endpoint,
  Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')
  Error: 9642, Severity: 16, State: 3.

Principal and mirror servers are in synchronized state. They have been in synchronized state for some months now, but I keep getting these error messages, I checked the permissions and everything is like its supposed to. Timing on both the servers is the same.

How can I stop these error messages. Thanks for your help.

The mirror state is likely confused at this point and should not be trusted. cherish.j.wang posted the following at: http://www.sqlservercentral.com/Forums/Topic1237273-1549-1.aspx

use master
GO
alter endpoint endpoint_name state = stopped;
GO
alter endpoint endpoint_name state = started;
GO

In that same discussion Robert Davis (SQL Soldier) agreed that would resolve the problem for the moment.  But he also pointed out that if you use the fully qualified domain name of the servers you are less likely to run into a name resolution error.

UPDATE: When you reset the endpoints, it will take the database offline for the duration of the restart.  However, it will not break the mirror.  The mirroring will stop running when the endpoint is stopped, but it will resume once the endpoint is again started.

The MSDN article: http://support.microsoft.com/kb/2490051 suggests.

Run these commands on the primary
If it does not resolve the problem, run the commands on the mirror.
If the database becomes SUSPENDED run ALTER DATABASE <Database Name> SET PARTNER RESUME


What's the solution for corrupted MDF files? We are working on an MDF file that has been corrupted, because a virus deleted 2 MB from the header of the file.

We also have another backup of this MDF File from 3 months ago. Both MDF files have the same size.

I think I can read some of the tables in binary data from the corrupted DB, but I don't know about the structure of MDF files

What's the solution for this?

Thanks.

Your solution here is to restore the database from backup.

If you have transaction log backups you can restore the logs as well.  If the database is in simple recovery mode you'll be loosing 3 months worth of data.


Subtracting sum of previous rows' data from current row's data in SQL I want to create a table or view in SSMS which should has a column called qoh. In that column should be calculate sum values of the column rel_qty.
i.e.

The first data of qoh is the same of the first row's value of
rel_qty.
Second value of qoh should be sum of first and second rows' value of
rel_qty.
Third value of qoh  should be sum of first, second and third rows' value of rel_qty.

is there any possibilities to do this?

Here is my query. but this query satisfied only first and second rows not more that that,

SELECT  a.id, a.tot_qty, a.rel_qty,
    (a.tot_qty -
        COALESCE (a.rel_qty +
            (SELECT b.rel_qty
        FROM    dbo.foo AS b
        WHERE   (b.id = a.id - 1)),
        a.rel_qty)
    ) AS qoh
FROM    dbo.foo AS a
ORDER BY    a.id

And if my id skip one value this query failed to do the calculation. How can I fix this?

here is screenshot of my result

It seems you need a running total.

If tot_qty is the same in all rows then you can use

SELECT  id,
        tot_qty,
        rel_qty,
        tot_qty - SUM(rel_qty) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS qoh
FROM dbo.foo
ORDER BY id;

ROWS UNBOUNDED PRECEDING is the abbreviated version of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

This window frame-ing option means that for each row the SUM will include itself and all predecessors as ordered by id.

The ROWS option is potentially important for efficiency as described in Best approaches for running totals – updated for SQL Server 2012


sp_prepexec (sp_execute) vs. sp_executeSQL The meat of the question: are actual stored procedures the only mechanism that implements temp table caching or do system stored procedures such as sp_executeSQL / sp_execute also take advantage of them?

I am not a DBA, so please use little words. Our application sends over prepared statements that, from the profiler, I see run all SQL through sp_prepexec which is a system procedure for both running sp_prepare and sp_execute. What I'm trying to do is figure out if I am benefiting from temp table caching.

I've been using this guide with object_id() to examine behavior

https://sqlkiwi.blogspot.com/2012/08/temporary-tables-in-stored-procedures.html

Then point #3 on this blog post suggests that EXEC cannot use temp table caching, but leaves out whether sp_executeSQL can:
http://blogs.msdn.com/b/turgays/archive/2013/09/18/exec-vs-sp-executesql.aspx

In my query sent over via the client I have created a simple temp table.

DECLARE @foo int; -- set by JDBC, unused but required to force a prepared statement

SELECT 1 AS id
    INTO #tmp

SELECT OBJECT_ID('tempdb..#tmp');

In profiler, I can see:

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 int',N'declare @foo INT = @P1

SELECT 1 as id
    into #tmp

select Object_id(''tempdb..#tmp'');
DROP TABLE #tmp;',1
select @p1

I also get a cachehit from this. However, the object_id of the temp table appears to be changing on me, which is not the behavior I would see if this temp table were created in a real stored procedure. However, when I run this same code through sp_executeSQL, I'm also seeing that the object_id of the temp table has changed. This leads me to believe that only "real" user created stored procedures take advantage of temp table caching.

Are actual stored procedures the only mechanism that implements temp table caching or do system stored procedures such as sp_executeSQL / sp_execute also take advantage of them?

You need a real stored procedure (CREATE PROCEDURE) to benefit from temporary table caching. This includes temporary stored procedures (#procname).

  Point #3 on this blog post suggests that EXEC cannot use temp table caching, but leaves out whether sp_executeSQL can.

Notice that EXECUTE is used to run sp_executesql.

Testing: There are many ways to check whether caching is occurring. Some of them are listed in my original article referenced in the question, some more methods are shown in my follow-up post, Temporary Table Caching Explained, for example:

SELECT
    DOMCC.name,
    DOMCC.pages_kb,
    DOMCC.pages_in_use_kb,
    DOMCC.entries_count,
    DOMCC.entries_in_use_count
FROM sys.dm_os_memory_cache_counters AS DOMCC
WHERE DOMCC.[type] = N'CACHESTORE_TEMPTABLES';

Stored procedure input TVPs are also cached, and starting with SQL Server 2012, these can also be cached when used with sp_executesql. See the linked CSS blog post for details.


Where are tempory tables or record sets located? Using mysql and php, I would like to understand where temporary results are stored. For instance, when I do a query that returns a set of data for processing by php, is the connection to the database closed and php storing the info for use or is that info kept in the database until php has finished using it?

It depends on mode used (buffered/unbuffered), as explained in the PHP manual:

  Queries are using the buffered mode by default. This means that query results are immediately transferred from the MySQL Server to PHP and is then kept in the memory of the PHP process. This allows additional operations like counting the number of rows, and moving (seeking) the current result pointer. It also allows issuing further queries on the same connection while working on the result set. The downside of the buffered mode is that larger result sets might require quite a lot memory. The memory will be kept occupied till all references to the result set are unset or the result set was explicitly freed, which will automatically happen during request end the latest. The terminology "store result" is also used for buffered mode, as the whole result set is stored at once.

Further reading

http://php.net/manual/en/mysqlinfo.concepts.buffering.php
http://dev.mysql.com/doc/apis-php/en/apis-php-mysqlinfo.concepts.buffering.html

This is answer to the "body" of your question. The title of that question asks about temporary tables, which are a different thing, internal to MySQL. If you are interested in that too, it is covered at https://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html

Simply said, MySQL stores small and simpler temporary results in memory and bigger ones and/or ones used in some more complex operations are stored on disk usually in temp folder.


How important is the build number on a SQL Cluster I did not actually perform this install, nor am I responsible for fixing issues on this particular cluster. I just happen to be checking it and found some things that puzzle me. My point in this is trying to understand why it is working, at least to some respect.

OS: Window Server 2003 Enterprise (64-bit)
SQL: SQL Server 2005 Enterprise (64-bit)

Started with a 2 node active/active cluster: Server1 running Instance1, then Server2 running Instance2.

Instance 1 and Instance2 are at the service pack 3 build number for SQL Server 2005 (+ a few hotfixes). I believe it is 9.00.4053. Two new servers are built to replace Server1 and Server2. So the servers were built and added into the cluster, Server3 and Server4. Since I did not do the installation I am assuming to a point that the person followed the steps to add a new SQL Server node as described here in BOL:
http://msdn.microsoft.com/en-us/library/ms191545(v=SQL.90).aspx

I found that Instance1 and Instance2 are now both running on Server3. However the build number of the instances now show to be 9.00.1399, which is an installation unpatched. The instances are active and still running on this RTM build of SQL Server within the cluster. ?????

My thought process here is you cannot take a backup of master database and restore it to an instance that is at a lower build number than what the backup was taken from. So if you just go on that point, how can a cluster instance failover to a node that is not at the same build number? Why would SQL Server (or Microsoft) even allow it to do this?

Also the last step in the BOL article linked above has "All nodes of a failover cluster instance must be at the same version level." I cannot find anything that states what happens if you are not. The only thing I have found on Instance2 is the SQL Agent jobs don't seem to be working anymore and when the databases are being brought online there is a stack dump from something like the IO listener (I don't recall the exact message showing). I have also seen a few messages referencing IO write issues.

The databases themselves are online and appears the applications are functioning as desired. They have been running in this manner for the past week or so. Any thoughts?

You probably don't need anyone to tell you that you need to fix the build level on that third node! I suspect it's all working ok because of the introduction of the resource database in SQL2005:
http://msdn.microsoft.com/en-us/library/ms190940.aspx

I vaguely remember an old Windows 2000 / SQL Server 2000 cluster I used to work with, where SQL thought it was a lower patched level on one node compared to the other.


How do you version your Oracle database changes? I am interested to know what methods other people are using to keep track of changes made to the database including table definition changes, new objects, packages changes, etc.  Do you use flat files with an external version control system?  Triggers? Other software?

At the sites I've worked at, any changes that need to be made to the production instance(s) must be scripted as change scripts that will run in SQL*Plus; in addition, scripts needed to recreate all schema objects from scratch must be kept up-to-date. All these scripts are checked into change control, and are migrated from there.

You can audit DDL changes or use DDL triggers to pick up changes, or even use diff software to compare two instances, but these methods are indiscriminate; often a developer will make and undo a number of changes to a schema (e.g. little test changes, creating dummy tables to test out concepts, etc) before working out what exactly needs to be changed.


MySQL - Old ibdata file shall I delete safely On a production server I have changed "innodb_file_per_table=ON" and I can see separate tablespace files for each table on my server.
I have used parititioning for tables so for each partition there is a separate .ibd file .

But I can see previous "ibdata1" file of size 10 Gb. I am bit curious whether to delete it or not ?

Does MySQL still uses old shared table space "ibdata" file?
If I delete shared tablespace file "ibdata1" will there be any impact on server ?
Will it cause any problem ?

Great question, and a very common issue.

When you changed the server configuration, to innodb_file_per_table = 1, the tables still lived on the common tablespace. You still had to recreate those using ALTER TABLE ENGINE=InnoDB (or any other operation that forced a tablespace recreation).

Once you have done that, why does MySQL still have a huge ib_data1? The truth is that InnoDB cannot really shrink (defragment) its tablespace files. That is not a huge issue in most cases, because the free space inside every file can be used by other data, index, etc. pages, and once you start using innodb_file_per_table you can always recreate each individual tablespace individually in order to free filesystem space.

However, the common tablespace is special. Even if it didn't contain any table or index information, it is always compulsory as it stores common data like the data dictionary, change buffer, etc. So if you delete it, you would end up with a fully unusable mysql installation. Answering your questions: 1) Yes it is being still used and 2) you won't be able to restart mysql again if you delete it. But, obviously, most of the ibdata1 file will be empty, it is just that it is very difficult to move page addresses around.

Here is a Pictorial Representation of InnoDB Architeture so you can see the contents of ibdata1

So, do you have to live with the 10GB file forever? Well, it is certainly a possibility, but there are ways to get rid of it, depending on the server version and your need for availability:

One approach, if availability is not a problem, and the database is small, would be to dump the whole database into a .sql or .txt with tools like mysqldump or mydumper,stop the server, delete all the files (or at least, all the innodb files), start mysql again and reload the data making sure that innodb_file_per_table is on. Very slow, but very easy.
If you do not want to suffer such a long process, there are ways to do it fully online, using a slave- you just set it up right from the beginning, and then switch nodes. If you do not have a second box, you can setup a temporary "slave" on the same machine (with the problem of extra resources needed)
If you use MySQL >= 5.6, you can move tablespaces around with IMPORT TABLESPACE. That would be a way to perform a quick backup, stop and wipe, and restore in binary format (way faster than with .sqls). Some tools like Percona XtraBackup may be useful for that.

This is a very common operation I've been asked about and performed a MySQL DBA. Hope this helps.


Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working I have several tables being reported by pt-table-checksum with CRC_DIFF between my master and slave servers using the following commands:

$ pt-table-checksum h=master,u=user,p=password --empty-replicate-table --databases db --replicate systemadministration.checksums
$ pt-table-checksum h=master,u=user,p=password --databases db --replicate systemadministration.checksums --replicate-check 1

Results in:

Differences on P=3306,h=slave
DB         TBL                         CHUNK CNT_DIFF CRC_DIFF BOUNDARIES
db         table1                          0        0        1 1=1
db         table2                          0        0        1 1=1
db         table3                          0        0        1 1=1
db         table4                          0        0        1 1=1
db         table5                          0        0        1 1=1
db         table6                          0        0        1 1=1
db         table7                          0        0        1 1=1

However, when I run pt-table-sync, the script returns an exit code 0 and says that there are no issues.

$ pt-table-sync --execute --verbose --no-bin-log --tables db.table1 h=master,u=user,p=password h=slave
# Syncing h=slave,p=...,u=user
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      0      0 GroupBy   14:10:45 14:12:12 0    db.table1
$ echo $?
0

I've tried the different algorithms for the checksum command and have had no luck.

$ pt-table-checksum h=master,u=user,p=password --empty-replicate-table --algorithm=ACCUM --tables db.table1 --replicate systemadministration.checksums
DATABASE   TABLE                       CHUNK HOST                   ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
db         table1                          0 master                 MyISAM     141836 00141836D0139746   22 NULL NULL NULL
$ pt-table-checksum h=master,u=user,p=password --tables db.table1 --replicate systemadministration.checksums --replicate-check 1
Differences on P=3306,h=slave
DB         TBL                         CHUNK CNT_DIFF CRC_DIFF BOUNDARIES
db         table1                          0        0        1 1=1
$ echo $?
1

Any hints or is there any other tools I can use to verify the data integrity?

The usual cause for this problem is that pt-table-sync is using CRC32 as a hash algorithm, because it's fast and cheap, but it can also give collisions. "codding" and "gnu" have the same crc32, for example. I recommend trying again with MD5 as the function.


Why is logging "slow"? On an empty transient table, inserting a single row took 50ms before changing it to UNLOGGED, 1ms afterwards.

Why is this exactly?

If the table structure is necessary, I'll post it, but it's only five columns that normally total 500 bytes per row, a composite primary, and a composite unique.

If possible, can the performance cost be pinpointed to calculation or disk interaction?  If so, what percentage of the cost is due to each?

Crash-safe logged tables require at least two writes for every transaction:

A write to the write-ahead log (WAL) recording the data the server intends to write to the table; then
at some later stage, a write to the real table to apply the change.

The real write can occur after commit, but the WAL write must happen before the commit returns (unless synchronous_commit=off, which has data safety consequences).

To force that to happen PostgreSQL does an fsync(), which flushes the data written to WAL to disk. This operation takes time - on spinning magnetic hard drives it usually takes about one revolution; on an SSD it depends a lot on the amount of other data buffered for writing and on the erase block size etc. Either way, it can take some time.

Unlogged tables do not write to WAL. They just write into shared memory, and as shared memory fills up they write to the real table. No fsync() is done because unlogged tables aren't crash-safe.

To reduce the cost of commit flushes on logged tables you should batch your work into bigger transactions. Where the application allows, use asynchronous commit or enable a small commit delay to allow group commit.


Going against the grain - backing up and truncating the tran log As the title states, this is most likely going against best practices but I want to hear some opinions on this.

The database(s) in question are in FULL RECOVERY mode, transaction log backups are being performed every 15 minutes. Maintenance tasks are being performed to Microsoft and Microsoft MVP standards.

We have an ERP system that really uses SQL inefficiently. The database size is relatively manageable, but the log file continuously grows out of control. In the past we've just allocated space to the log file drive in order to control growth. I understand I can limit growth, but our ERP vendor doesn't recommend this approach. Month end processing can make this log almost double in size and make our transaction log backups huge. No problem, right? Well it's a problem as we now have two databases doing this same thing on month end.

To make a long story short, I want to have a discussion on the pros and cons of going against the grain on backing up the transaction log file, then shrinking it to conserve space. I've tackled this processes a couple times out of the year to manage space and get the logs under control but I want to hear some opinions on automating this process to relieve some headaches or to make me think outside the box.

What is recommended in this scenario that I'm not doing already? I'm to the point of offloading transaction log backups every minute to try and further prevent the log from growing but I'm not too hopeful that will solve the problem.

I'm assuming you have these databases in FULL RECOVERY because you need to be able restore to a point in time.  If that's NOT the case, then use SIMPLE mode and your log would only grow to the size needed to contain a transactional unit of work (which could be index maintenance as well as application transaction).

Now, back to the FULL RECOVERY model.  Backing up the log on a periodic cycle (whether it's 1 minute or 15 minutes) is only going to help you if transactions are able to commit during that time frame.  Active long running transactions will prevent that log space from being flagged for reuse.

There shouldn't be a problem with running transaction log backups every minute (Back Up Transaction Logs Every Minute. Yes, Really).

Constantly shrinking the log will have a negative performance impact due to waiting for the log to grow again.  Logs do not honor Instant File Initialization.

The bottom line is:

The transaction log has to be large enough to hold an active unit of work and you need timely log backups to allow of log reuse.


Any risks in updating the system time on a mysql 5.5 server? We just discovered one of our mysql systems has not been running ntp, with the result that the time has drifted a bit.

If I enable ntp, and synchronize the time while the system is running are there any possible problems?  The master is currently about 4 minutes slow, and a slave is about 3:45 behind.

With the changing of the clock times on the Master

there might be loss of data for the slave
a slave may replay a query it already processed

Rather than risk either of these extremes, you should schedule a brief downtime.

Here is what you should do:

STEP 01: Stop all writes to the Master

SET GLOBAL read_only = 1;
FLUSH TABLES WITH READ LOCK;

STEP 02: Make sure are writes have stopped on the Master

SHOW MASTER STATUS;

You may see something like this

mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.005956 | 59702913 |              | dba              |                   |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

Run this several times and make sure nothing is running

STEP 03: Set the clock time on Master (and Slave if needed)

STEP 04: On the Slave, run this several times

SHOW SLAVE STATUS\G

Make sure

Seconds_Behind_Master is 0
Read_Master_Log_File is not changing
Relay_Master_Log_File is not changing
Exec_Master_Log_Pos is not changing

STEP 04 : Run the following on the Slave

STOP SLAVE;
CHANGE MASTER TO master_log_file='bin-log.000001',master_log_pos=4;

STEP 05 : On the Master, run

RESET MASTER;

This will erase all your binlogs on the Master and start with a new one

STEP 06: On the Slave,

START SLAVE; DO SLEEP(5); SHOW SLAVE STATUS\G

Make sure Seconds_Behind_Master is 0

STEP 07: On the Master,

UNLOCK TABLES;

That's it.

It's better to do this that to just flip the clock on a live system

I have suggested this about 1.5 years ago (Risk of changing clocktime on MySQL server host)


Error "In a grant or revoke statement, the user attempted to grant a tablespace quota above the upper limit." when trying to increase user quota I'm attempting to increase the space quota on a particular schema. I'm using the following query to do so:

ALTER USER MY_SCHEMA QUOTA "100 M" ON "USERS";

This results in the following error:

SQL Error: ORA-02187: invalid quota specification
02187. 00000 -  "invalid quota specification"
*Cause:    In a grant or revoke statement, the user attempted to grant
           a tablespace quota above the upper limit.
*Action:   Grant a smaller tablespace quota.

But this doesn't make any sense. The user already has a 50 MB quota, and a query trying to set it to 25 MB fails with the same error message:

ALTER USER MY_SCHEMA QUOTA "25 M" ON "USERS";

I've confirmed that my tablespace has enough room left. (Its data files can acquire about 2 GB more of disk space.) What is causing this error?

It turns out that the error message is misleading. The problem is the quoting around the size specification.

Both of these work:

ALTER USER MY_SCHEMA QUOTA 100M ON "USERS";

and

ALTER USER MY_SCHEMA QUOTA 100 M ON "USERS";


How to Identify the user who issued MySQL Shutdown command We have had several situations when our production MySQL server suddenly became unavailable.  The error log just shows "Normal shutdown" followed by the typical shutdown messages.  How can I determine the Linux or MySQL user account and host name of the connection issuing the shutdown command?

Check the secure log in the event it was run via a sudo service call

sudo grep mysql /var/log/secure

Check to see if there's any mysql related stuff going on in cronjobs

sudo grep mysql /var/log/cron

Check shell histories for mysqladmin calls

cd /home; for u in *: do; sudo grep mysql /home/$u/.bash_history; done

Check with people you know that either have sudo or mysql root access on this machine

"Hey, which one of you has been shutting down mysql?"


Even after permissions are updated, I still get the error permission denied for relation users I'm getting the error ERROR: permission denied for relation users and have tried to update privileges using both

grant all privileges on all tables in schema ops to my_user
GRANT ALL PRIVILEGES ON TABLE the_table TO my_user

In the Grants tab of DBVisualizer I can see that the my_user user has the privileges DELETE, INSERT, SELECT, TRUNCATE, and UPDATE.  IS_GRANTABLE is set to NO for all of these. Yet I still get this error. What is there left to do.

In addition to privileges on the TABLE, a role needs privileges for the SCHEMA and the DATABASE. The most likely candidate is the schema. If so, this additional GRANT would be the fix:

GRANT ALL ON SCHEMA my_schema TO my_user;

Or at least:

GRANT USAGE ON SCHEMA my_schema TO my_user;


Moving Reporting Processes from Access to MySQL I am soon due to be given SQL access to our CRM system, which will allow me to automate some reporting processes. At present I use Access which I update with Excel imports.

As one example I have created a report which is used to assess sales team performance. To do this I manipulate the data using queries in Access and then link Excel to the final query. My queries have many steps which reference each other - this seemed to be the only way to make this work in Access; use a query to select/re-order some data before you can apply the next query to it, and so on.

I am playing around with some partial data in MySQL with the idea of completely re-creating this database (and others like it) and I have some questions:

Is it worth doing this, or should I simply use the SQL connections to automate the Access data update and continue to run the report from it? There are no problems with it at present, it is just tedious to update it manually.
My approach in MySQL so far is to use Views in place of the multi-layered queries I use in Access, as they seem to be the closest thing available. Is this appropriate/advisable? I will be making new reports using MySQL and would automatically approach it in this way but I'm completely unsure if this is suitable. Views seem easier to keep track of than subqueries etc.

Any help would be great.

Thanks

Once I discovered MySQL, Excel was relegated to being just a fancy calculator.  It will take you some time to understand SQL, but in the long run, it will probably be well worth it.  If you end up with performance problems, you might need to avoid Views.


Does an index improve the querying performance in just two ways? As far as I know, an index improves the querying performance in the following two ways:

The index only contains the values of the column that it was created for and pointers to the rows in the original table that each value points to (so this means there are fewer data to read from the disk when reading from the index compared to reading directly from the original table which could contain a lot of columns).
The values in the index are sorted.

But does an index improve the querying performance in other ways also? For example if we have an index for a name column, does the index contain pointers to the rows in the original table of each name, for example does an index contains something like this:

John (row4, row10, row12)
Robert (row9, row20)
Steve (row2, row6)

There's a step in query processing called optimization. This is when the SQL's logical operations (inner join, say) is translated into a physical operation (merge join, or hash join etc.). Each of these physical operations has a different fixed cost and per-row cost (by "cost" I mean time-to-get-results). So one physical operator will have a lower total cost for a few rows and a different one will have a lower total cost for many rows.

How does the optimizer know how many rows to expect and, hence, which physical operator to choose? With each index there will be a histogram summarizing the distribution of data referenced by that index.

John: three rows
Robert: two rows
Steve: two rows
Zach: seventy thousand rows

So we would expect a different query plan when listing Johns than when list Zachs. Choosing the wrong operator will result in a slower query. So indexes improve query performance because statistics are collected for each which allows better choice of physical operators.

Note, even if the associated index is not use in the final execution plan the statistics can still inform the optimizer's choices.

There are a great many index data structures and algorithms. The cannonical one for relatoinal databases is the B-Tree. This has one entry in the leaf nodes per row, with a pointer to the corresponding data row. Your example index on Name would look a bit like this on disk*

John(row4), John(row10), John(row12), Robert(row9), Robert(row20), Steve(row2), Steve(row6)

In a B+-Tree it's the level above the leaves that have the pointers and the leaf nodes are the data rows. So it could be something like:

Non-leaf node:
    John(Page 11 from row 4),
    Robert(Page 11 from row 7),
    Steve(Page 12 from row 1)

Leaf node page 11:
    < three other rows >,
    (John, Blue, Africa, 7),
    (John, Pink, Europe, 12),
    (John, Yellow, Asia, -3),
    (Robert, Red, NULL, 11), ...

Leaf node page 12:
    (Steve, Cyan, Antarctica, 42),
    (Steve, ...

For a hash index the name will be hashed to an offset which will have a pointer to an array of row locations. This is the most like your question, but is still a little different.

There are a great many other index structures. It is a field of active research.

* Except it's a lot more complicated, of course.


Using psql, how to test if I am using streaming, hot_standby or warm replication? I am on Postgresql version 10.4

How can I know using psql that if the server I am using is in a streaming, hot_standby, or warm replication mode?

I really have no idea what these replications do other than hot_standby is only a read-only server, and it and warm use WAL archives and streaming archive does not use WAL.

And also, are these the only modes of replication or are there some other modes of replication too?

If possible, explain like I am five because this is my second day of using Postgresql.

Streaming does use WAL.  It obtains the WAL by streaming it, rather than (or in addition to) shipping entire WAL files at a time.  Also, the choice of hot_standby versus warm standby is independent of the choice of streaming versus log shipping.  It would be unusual to use hot_standby without also using streaming in the modern age, but it is perfectly possible to do so.

On the master, you can see if there are active receivers of WAL streaming by looking at pg_stat_replication.  If there are no entries, than no one is currently streaming wal.

For log shipping, you can look in archive_mode.  If that is turned off (or if it turned on but archive_command is some dummy command), then no one can be receiving the shipped log files as they are not being archived in the first place.  If it is on and archive_command is sensible, then someone might be doing something with the archived logs files.  The master server has no direct way of knowing whether someone is or is not.

In either case, the master doesn't generally know what they are doing with WAL.  They could be using the WAL for warm standby, they could be using it for hot standby, they could be storing it cold for future use, they could be just tossing it in the trash. Some of the fields in pg_stat_replication can give you some clues about what is going on, but who knows, the client programs might be lying.  And for log shipping, there is no way to know anything.


Linked Server Has Run Out Of Memory So I have a stored procedure that calls an AS400 Linked server. Now, the stored procedure will execute successfully. However, it is used in a SQL Agent Job and each time the job runs, it gives the Linked Server has Run out of Memory exception.

Why does the fact that it is running in a job environment make it differ from running as the raw SQL code? And how would I go about solving/fixing this?

That might be a permission issue. When executed as a job step the code runs under the SQL Server Agent Service Account. When you execute it yourself it runs under your account.

Make sure that the QSL Server Service Account has appropriate permissions on the linked server. You can also try to impersonate that account by using

EXECUTE AS LOGIN='Domain\ServiceAccount';

before executing your code. That might reveal additional error detail and should make troubleshooting easier.


Get employee names on the basis of Date I want a dynamic query which will fetch the employee name who has completed successfully 6 months and are ready for confirmation.

I have written a hardcode query does that. Here is the query

SELECT [emp_name] FROM [emp_mst]
  WHERE Dt_Of_Join >= dateadd(m, -7, datediff(d, 8, getdate()))
  and   Dt_Of_Join <  dateadd(m, -6, datediff(d, 9, getdate()))

For example: if employee has Joined on 1st dec,2014, he will complete 6 months on 31st May 2015.

So in june he can be intimated on any date. The above query gives me the result for Nov16 2014 to Dec 15 2014, based on today's date.  I want it to be handled dynamically

Please suggest.

I am using sql server 2005.

Today, without time, is:

DECLARE @d SMALLDATETIME;
SET @d = DATEADD(DAY, GETDATE(), DATEDIFF(DAY, '19000101', GETDATE());

-- when you move past SQL Server 2005, which you should, you can use the much tidier:
-- DECLARE @d DATE = SYSDATETIME();

Then to move to the first day of this month:

SET @d = DATEADD(DAY, 1-DAY(@d), @d);

Now to get all employees who started within the month 6 months earlier, you can say:

DECLARE @d SMALLDATETIME;

SET @d = DATEADD(DAY, 1-DAY(GETDATE()), DATEDIFF(DAY, '19000101', GETDATE()));

SELECT ...
  WHERE Dt_Of_Join >= DATEADD(MONTH,-6,@d)
    AND Dt_Of_Join <  DATEADD(MONTH,-5,@d);
-- I assume you'll have other filters to make
-- sure employee is still employed, etc.

The smart way to do this would be through a parameter, which falls back to today if no date is supplied.

CREATE PROCEDURE dbo.GetNamesByDate -- not the name you should use
  @dt SMALLDATETIME = NULL
AS
BEGIN
  SET NOCOUNT ON;
  SET @dt = COALESCE(@dt, GETDATE());
  SET @dt = DATEADD(DAY, 1-DAY(@dt), DATEDIFF(DAY, '19000101', @dt));

  SELECT ...
  WHERE Dt_Of_Join >= DATEADD(MONTH,-6,@dt)
    AND Dt_Of_Join <  DATEADD(MONTH,-5,@dt);

  -- for debugging only:
  PRINT '>= ' + CONVERT(CHAR(10), DATEADD(MONTH,-6,@dt), 120);
  PRINT '<  ' + CONVERT(CHAR(10), DATEADD(MONTH,-5,@dt), 120);
END
GO

(Now it's also very easy to test that this works for any date, including February 29th in leap years...)

Here is the illogical way to do it in one line (I have absolutely no clue why you think this is a valid or important requirement):

ALTER PROCEDURE dbo.GetNamesByDate -- not the name you should use
  @dt SMALLDATETIME = NULL
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ... WHERE Dt_Of_Join >= DATEADD(MONTH,-6,DATEADD(DAY, 1-DAY(COALESCE(@dt, GETDATE())), DATEDIFF(DAY, '19000101', COALESCE(@dt, GETDATE())))) AND Dt_Of_Join <  DATEADD(MONTH,-5,DATEADD(DAY, 1-DAY(COALESCE(@dt, GETDATE())), DATEDIFF(DAY, '19000101', COALESCE(@dt, GETDATE()))));
END
GO

Oh yeah, that's much easier to read and troubleshoot. Also, it didn't save you any characters, it's actually more... </facepalm>


Neo4j Temporal Query with NaT I am trying to do a temporal query similar to below:

MATCH (n:Event)
WHERE datetime(n.date) = datetime("2019-01-01")
RETURN n

But some NaT values have snuck into my n.date property. Is there any way to still do this query without having to update those nodes?

I can't remove the Date field from the nodes that have NaT as a value because the nature of the node the date is actually part of the node key.

TIA

My bad.. didn't think to exclude NaT from the query.

MATCH (n:Event)
WHERE n.date <> "NaT" AND datetime(n.date) = datetime("2019-01-01")
RETURN n


MySQL 5.7 No Longer Returns Meaningful Error Messages When I select from a table that doesn't exist:

When I select a nonexistent column from an existing table:

I used to receive meaningful error messages (ie 'Error Code: 1054. Unknown column 'column' in 'table''), but now only the error number is returned, without an explanation of what happened. This happens to everyone that connect to this particular MySQL database.

Does anyone know how I can fix this without reinstalling MySQL? I'm running MySQL 5.7.9 Community Server (64-bit) on Windows Server 2012.

Looking through the MySQL error log, I noticed the following line: Can't find error-message file '\share\errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive. That file did not exist, however \share\english\errmsg.sys did exist. I found out from here that this error message is not entirely accurate, since MySQL does not actually look in the \share\ directory - it looks in the \share\*YOUR LANGUAGE*\ directory.

I looked in my my.ini and discovered that I had a line that said basedir = (that's all that the line said - there was no path after basedir). I'm not entirely sure how that configuration came to be, but I commented out that line and everything works great now!


How to remove a corrupted shard in MongoDB I use MongoDB 3.0.3 with Wired Tiger engine on Cent OS 6.4.
I have a sharded cluster of 4 shards each of which has about a quarter of total data.

One of the shard called "shard2" corrupted and can't start.
Without this shard, the cluster can't work.

I can bear losing data on this shard, How can I remove this shard to make the cluster work??
What parameters should I modify in "config" collection?

You can use below commands to remove the shards, though I havn't tried ever but looks straight forward.

use admin
db.runCommand( { removeShard: "mongodb0" } )

--Response
{
    "msg" : "draining started successfully",
    "state" : "started",
    "shard" : "mongodb0",
    "ok" : 1
}

link: http://docs.mongodb.org/manual/tutorial/remove-shards-from-cluster


commitlog broken after standby setup with pg_basebackup I'm trying to replicate a database with pg_basebackup in order to use it as standby server. But when I'm trying to start up PostgreSQL I get the following errors and the server shuts down again:

2016-08-09 15:53:38 CEST [10369]: [1-1] [postgres: [local]] FATAL:  the database system is starting up
2016-08-09 15:53:39 CEST [10372]: [1-1] [postgres: [local]] FATAL:  the database system is starting up
2016-08-09 15:53:39 CEST [10375]: [1-1] [postgres: [local]] FATAL:  the database system is starting up
2016-08-09 15:53:40 CEST [10378]: [1-1] [postgres: [local]] FATAL:  the database system is starting up
2016-08-09 15:53:40 CEST [10361]: [4-1] [: ] LOG:  redo starts at 11/188CBA38
2016-08-09 15:53:40 CEST [10361]: [5-1] [: ] FATAL:  could not access status of transaction 4809883
2016-08-09 15:53:40 CEST [10361]: [6-1] [: ] DETAIL:  Could not read from file "pg_clog/0004" at offset 147456: Success.
2016-08-09 15:53:40 CEST [10361]: [7-1] [: ] CONTEXT:  xlog redo Transaction/COMMIT: 2016-08-09 15:41:25.947996+02
2016-08-09 15:53:40 CEST [10359]: [3-1] [: ] LOG:  startup process (PID 10361) exited with exit code 1

I call pg_basebackup like this

sudo -u postgres pg_basebackup -h [server] -D [dbdir] -U replicator -v -P

My recovery.conf Looks like this

standby_mode = 'on'
primary_conninfo = 'host=[host] port=[port] user=replicator password=[password] sslmode=require'
trigger_file = '/tmp/postgresql.trigger'

When I'm trying to follow this solution I'm getting the error message

ERROR:  requested WAL segment 0000000100000010000000EF has already been removed

how can I avoid the broken clog file in the first place?

i'm using PostgreSQL 9.5.3

This probably happens because the master doesn't keep the wal segements long enough so they are deleted before your base backup completes. You can use the option --xlog-method=stream with pg_basebackup to tell it to also stream the wal while making the backup. This way you will have all the wall segments the backup needs to start succesfully.


How to re-write this query or restructure my table for SPEED I have an InnoDB MySQL table of this structure:

column
type

service
varchar(16)

url
varchar(255)

datetime
datetime

It contains logs for requests made to one of multiple services. It contains ~3 million rows, and gets ~3 million more rows a month (but I will delete old data if need be).
I am trying to generate a report, and get the number of requests made to each service within a date range.
Here is my current query:
  SELECT service, COUNT(*) as lastMonthCount
    FROM request_logs
   WHERE datetime > '2021-02-16 10:51:05'
GROUP BY service

This works, but is painfully slow (~28 seconds).
It outputs this:

service
lastMonthCount

API A
3056752

API B
38451

I have indexes on datetime and service. I can see they are of type BTREE.
How can I radically speed up this query, or restructure my table/indexes so I can achieve the same use case another way?

For a relatively simple query like that there isn't a lot you can do to optimise it as there is little room for change (there isn't a simple way of asking for that data).
You can probably reduce the amount of pages being touched as it runs significantly by having an index on datetime and service. This way the data it needs to group by will already be available in what it has read to perform the filter on the date. This will increase the amount of data on disk as the index will be larger, and slow down writes a touch for the same reason. You would probably want to replace the existing index on datetime with the new composite index instead of just adding it, for those reasons.
A little more detail on why this will be faster:
With the index just on the datetime, it will need to read the base table data pages to get the service column for each matching row which as well as being extra page reads just because of referencing the other structure it will possibly be many more page reads because those pages contain less rows each due to the larger data per row (they include the URL column and any other properties that you add, or might already have, for the services).
If that still isn't fast enough...
You may need to look at some form of caching for the counts. There are several options here:

a materialised view may work if they are intelligent enough in mysql
a little denormalising, by including a counts table updated by trigger when the main table is updated (this is essentially a more "manual" version of a materialised view)
something in the application layer, if you can live with the result not necessarily being 100% up-to-date every time (the fastest option by far if done right, but obviously with that key disadvantage)
if the data is big enough that the query is reading from storage rather than RAM each time and you don't want to use one/more of the three options above: throw hardware at the problem and buy oodles of RAM! (this is usually not a good solution, though sometimes can be)


How much does an index need to narrow the results of a search in order to be useful? How much does an index need to narrow the results of a search in order to be useful in speeding up queries?

Some examples all across the spectrum:

A column for storing true/false values obviously has only two unique values.
A 'last name' column probably has many unique values (although it may not).
A primary key column has all unique values.

I think that the goal of an index is to quickly narrow a search to a few rows, and that therefore, the last case is the best, the second is OK, and the first is useless.

Am I correct? If so, roughly where is the line of usefulness?  For example, if an index can narrow the results to 1% of the rows, is that useful? What about 10% or 25%?

The diversity of a column's data is known as selectivity.  Selectivity is useful to know when determining whether an index will be useful, but it is not the only thing that determines the speed benefit.  Other factors include how fast the storage the index is on compared to the table, how much of the table/index is already cached, how large the index is in comparison to the table, and several other things.

Knowing the data-type of the column does not necessarily help us determine how selective an index on the column will be.  Even a column constrained to two values might use those values for only a few rows and have the remainder NULL.  On the other hand, a column that could have many distinct values could have the same value in every single row.  Even with your id column where all the rows would have unique values, if you are searching for rows with an id >= 10, the index probably wouldn't be useful even though it is highly selective.

You can't use selectivity alone to determine whether an index will be useful or not because even if it returns 100% of the rows, if the index includes all the data necessary for the query it will be faster than using the table.  On the other hand, for a small table it may be faster to query the whole thing even if the row being sought only represents 1% of the total.

Determining what indexes should be created is less about looking at the table structure than it is looking at the important queries and what data they need to retrieve.


Create Field With Default Value Of Different Field With No Data Loss I have a datetime2 field that I need to be a datetime field.  If I try to run an Alter Table statement my SSMS session freezes and I have to force reboot with no change made, if I try to alter my design view of the table I get an error about needing to drop/re-create the table.

Which leads me to my question, if I have a field that is datetime2 if I Add Column helper1 datetime  how could I set the default value of this column to dt2 which is a datetime2 data type?

You're looking for a computed column if you want to keep the column with datetime2 data.

 ALTER TABLE Your-Table
 ADD helper1 AS
       (
          CAST(Datetime2-Column AS datetime)
       );

If you want to completely change the column, you can add a new column, transfer the data, then drop the old column and re-name the new column to the name of the old column. Steps below:

 ALTER TABLE Your-Table ADD Datetime2-Column DATETIME2

 UPDATE Your-Table
 SET Datetime-Column = Datetime2-Column
 GO

Then drop datetime2 column.

ALTER TABLE Your-Table DROP COLUMN Datetime2-Column

Then re-name your datetime column to your original datetime2 column's name. You can do this in Management Studio or with sp_rename


Query for get column number in PostgreSQL I want to know how can I get column number of a table in PostgreSql? For example, If we have a table with id, name, age columns, the age column number is 2. I can do this with array and loop in C++(using libpqxx). But I want to know is there a better way to get this with simple and direct query?

select      ordinal_position

from        information_schema.columns

where       table_catalog = 'my_table_catalog'
        and table_schema  = 'my_table_schema'
        and table_name    = 'my_table_name'
        and column_name   = 'my_column_name'


Error on updating a field with binary values I have a problem on emoji showing on webpage after migration to new DB Server. But I have a workaround. I will get the binary values of a certain field of TABLE1 on our OLD DB Server. Then update the TABLE1 on the new server using the script below:
UPDATE old_table t1 INNER JOIN new_table t2 ON t1.id = t2.id
SET t2.message = CAST(CONCAT("0x",HEX(t1.message)) AS BINARY);

But after executing this my message field on the new table becomes "0x1E395565"
If I do this script below, it will show the real message. But doing this for a million of records will take too long to execute hence the UPDATE INNER JOIN:
UPDATE new_table SET message = 0x1E395565 where id =1;

How to update this correctly? I concat "0x" on the HEX(t1.message) because when I do a mysqldump --hex-blob and charset=BINARY. There are 0x on the values.
My data type for message field is VARCHAR(1000) I know this might be wrong but is there any workaround without altering the table.

Simply use UNHEX("1E395565").
There could be a lot of other issues going on.  So please provide SHOW CREATE TABLE for the old and new tables.  Also provide a sample of what is in the old table, but use
SELECT col, HEX(col) FROM ... WHERE ...

so we can see the value in an unambiguous way.  (That is, not mangled or "fixed" by the client.)
Where does 1E395565 come from?  It is not anything meaningful (not utf8, etc) that I recognize.


E: Version '2.0.25' for 'mongodb-10gen' was not found while installing this specific version I'm trying to install version 2.0.25 for some testing work. I followed this documentation.

Here are the steps:

sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 7F0CEB10

echo 'deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen' | sudo tee /etc/apt/sources.list.d/mongodb.list

sudo apt-get update

sudo apt-get install mongodb-10gen

apt-get install mongodb-10gen=2.0.25

But I get this error every time
E: Version '2.0.25' for 'mongodb-10gen' was not found

How do I install this specific version?

if anone lands here in future then follow these steps for installing OLDER versions ( 2.4 and earlier versions )

  sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 7F0CEB10

        echo 'deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen' | sudo tee /etc/apt/sources.list.d/mongodb.list

        sudo apt-get update

    apt-get install mongodb-10gen=2.0.2

replace 2.0.2 with your required version.

for Versions 2.6 and above replace last step with this format

sudo apt-get install -y mongodb-org=2.6.9 mongodb-org-server=2.6.9 mongodb-org-shell=2.6.9 mongodb-org-mongos=2.6.9 mongodb-org-tools=2.6.9

replace 2.6.9 with your mongo version ( only 2.6 and above )