So, how to take backup of store procedures, functions and triggers?

|
ruydar 2021-06-27 14:20:26
Introduce the channel in the subject with postgres
brijeshJoshiii 2021-06-29 19:22:52
query – select * from BookDetails where subject = “Computer Science”; subject datatype = varchar(30) , but this query is not giving the books of subject computer science , i think it has something to do with space between two words , as when i am searching with subject = “english” , it is giving correct output , so how to do this?
Deb_575 2021-06-29 19:39:12
Have you tried all the combinations like ‘computer science’ or ‘COMPUTER SCIENCE’ sometimes if case sensitivity is enabled it will not return result unless all cases match
brijeshJoshiii 2021-06-29 19:46:10
Deb_575 2021-06-29 19:39:12
Have you tried all the combinations like ‘computer science’ or ‘COMPUTER SCIENCE’ sometimes if case sensitivity is enabled it will not return result unless all cases match

Thanks it worked

Liran 2021-06-30 07:52:16
Is there any good log analyzer for mySQL / MariaDB?
MasterZiv 2021-06-30 07:55:53
Liran 2021-06-30 07:52:16
Is there any good log analyzer for mySQL / MariaDB?

Yes there is. There are eyes. And there is less.

Liran 2021-06-30 07:56:34
i tried using the slow query log but it’s still hard to go over it
Liran 2021-06-30 07:57:23
MasterZiv 2021-06-30 07:55:53
Yes there is. There are eyes. And there is less.

i don’t know if you’re familiar with postgreSQL but they have a tool called pgBadger. log analyzer that can also get the top 10 queries. something like this

MasterZiv 2021-06-30 07:57:42
Liran 2021-06-30 07:56:34
i tried using the slow query log but it’s still hard to go over it

it’s just a plain text file, nothing complex

Liran 2021-06-30 07:58:08
MasterZiv 2021-06-30 07:57:42
it’s just a plain text file, nothing complex

true, but still im looking for a tool doing this. is there anything out there?

MasterZiv 2021-06-30 07:58:42
wait for someone to tell you about it…
Liran 2021-06-30 08:00:06
MasterZiv 2021-06-30 07:58:42
wait for someone to tell you about it…

i still don;t know if you’re trolling me or really want to help

MasterZiv 2021-06-30 08:02:29
Liran 2021-06-30 08:00:06
i still don;t know if you’re trolling me or really want to help

The latter

Deb_575 2021-06-30 09:08:00
Liran 2021-06-30 07:57:23
i don’t know if you’re familiar with postgreSQL but they have a tool called pgBadger. log analyzer that can also get the top 10 queries. something like this

For enterprise edition they have mysql enterprise monitor
For community they have a function called sys.diagnostic() but that is from 5.7.21 onwards
Nothing specific to pgbadger as mysql provides separate logs for slow query errors and general logs unlike postgresql

Deb_575 2021-06-30 09:14:51
I completely forgot about pt-query-digest from percona toolkit use that
aicantar 2021-06-30 10:18:01
Good day. Hope y’all are well.

I have a rather simple DB architecture question. I’m working on making a custom billing system work nicely with iOS in-app purchases.

When a user purchases something on their device, App Store returns a base64-encoded receipt that you have to validate on the backend. Hence I need to keep this receipt and I can’t come up with a nice way to integrate this into the existing database.

The receipt data is not used for search and shouldn’t be indexed.

I have a table that contains all purchases coming from different sources (on-site purchases, google play, whatever), let’s name it purchases. Columns in this table are generic and largely source-independent.

I can add a column to this table, say, extraData TEXT and store the receipt info in it as a JSON object like {“app_store_receipt_data”:”…”}.

Or I can create a separate table app_store_receipts and bind each receipt to the purchase it belongs to using foreign keys.

Which way is more proper? Or should I do something else entirely?

svart 2021-06-30 14:31:54
Liran 2021-06-30 07:52:16
Is there any good log analyzer for mySQL / MariaDB?

pt-query-digest from percona tools. for now it is the industry standard. if you would like to have some UI wrapped around pt-query-digest results I can strongly recommend anemometer

MasterZiv 2021-06-30 15:11:46
aicantar 2021-06-30 10:18:01
Good day. Hope y’all are well.

I have a rather simple DB architecture question. I’m working on making a custom billing system work nicely with iOS in-app purchases.

When a user purchases something on their device, App Store returns a base64-encoded receipt that you have to validate on the backend. Hence I need to keep this receipt and I can’t come up with a nice way to integrate this into the existing database.

The receipt data is not used for search and shouldn’t be indexed.

I have a table that contains all purchases coming from different sources (on-site purchases, google play, whatever), let’s name it purchases. Columns in this table are generic and largely source-independent.

I can add a column to this table, say, extraData TEXT and store the receipt info in it as a JSON object like {“app_store_receipt_data”:”…”}.

Or I can create a separate table app_store_receipts and bind each receipt to the purchase it belongs to using foreign keys.

Which way is more proper? Or should I do something else entirely?

0) don’t use JSON in a relational DB.
1) make a table (s) with all receipt fields and link it to the purchase table, one-to-zero-or-one (looking from the purchase)

aicantar 2021-06-30 16:03:38
MasterZiv 2021-06-30 15:11:46
0) don’t use JSON in a relational DB.
1) make a table (s) with all receipt fields and link it to the purchase table, one-to-zero-or-one (looking from the purchase)

Thanks!

madno 2021-07-01 12:34:08
Hi, I have a table in mysql5.7.28… It has multiple varchar columns . I have to extend length of a varchar col. How can I find the maximum length of a col till I can extend to prevent rebuild table
MasterZiv 2021-07-01 19:28:31
madno 2021-07-01 12:34:08
Hi, I have a table in mysql5.7.28… It has multiple varchar columns . I have to extend length of a varchar col. How can I find the maximum length of a col till I can extend to prevent rebuild table

Extend to varchar(2048) and that is it. Real length is not important at all

MdAsgarindia 2021-07-01 21:20:56
I have Leonard Karpinski videos of Powerbi
Anybody wants
Dm me
rodrigo_tc7 2021-07-01 22:46:56
madno 2021-07-01 12:34:08
Hi, I have a table in mysql5.7.28… It has multiple varchar columns . I have to extend length of a varchar col. How can I find the maximum length of a col till I can extend to prevent rebuild table

Use length, order and limit

K R 2021-07-02 15:56:03
MdAsgarindia 2021-07-01 21:20:56
I have Leonard Karpinski videos of Powerbi
Anybody wants
Dm me

I am also having Leonardo Karpinski videos of Power BI

2021-07-03 16:28:18
i’m brazilian and im trying make a database where a customer can be a physical and juridical person, and a user
2021-07-03 16:29:01
mysql_en-11067.jpg
i made this relationship, but i dont know if is that rigth
2021-07-03 16:29:53
cliente = customer and vendedor = seller
2021-07-03 16:30:07
is that right?
2021-07-03 16:40:09
somewhere can help me?
R 2021-07-03 17:46:00
Hello,

I want to take whole back up (tables with structure and data, store procedures ,functions, triggers) of my mysql database everyday every two hrs. For that I have created a .bat file and using this file I have a created a task in task shceduler. So after every 2 hours it take backup of databse. My .bat file has code as follows=>

@echo off set current=%date:~6,4%,%date:~0,2%,%date:~3,2%-%time:~0,2%,%time:~3,2%,%time:~6,2%-%time:~9,2% set filename=”C:BackupDBName-%current%.sql” echo %filename% C:”Program Files (x86)”MySQLbinmysqldump.exe [database_name] –user=root –password=rational –routines –host=”127.0.0.1″ –port=3306 –result-file=%filename% –default-character-set=utf8 –single-transaction=TRUE echo backup-finished

This created a .sql file having backup of my databse. When I restored this sql file then it restores only tables with data but store procedures does not get restored. I have used –routines in command. I even tried with -R but could not get success.

I am having version of mysql 8.0.23 . After searching on internet I got to know that Use of this option(–routines or -R) requires the SELECT privilege for the mysql.proc table.  I tried to grant permission but it shows message that mysql.proc table does not exist. 

So, how to take backup of store procedures, functions and triggers? Thank you in advance.

rodrigo_tc7 2021-07-03 18:12:25
2021-07-03 16:29:01
i made this relationship, but i dont know if is that rigth

Why not use hierarchy as with persona?

2021-07-03 18:53:19
Because a client can be pp ou jp
MasterZiv 2021-07-04 13:21:05
2021-07-03 16:29:01
i made this relationship, but i dont know if is that rigth

This is correct as far as I can understand Portugal…

MasterZiv 2021-07-04 13:22:34
R 2021-07-03 17:46:00
Hello,

I want to take whole back up (tables with structure and data, store procedures ,functions, triggers) of my mysql database everyday every two hrs. For that I have created a .bat file and using this file I have a created a task in task shceduler. So after every 2 hours it take backup of databse. My .bat file has code as follows=>

@echo off set current=%date:~6,4%,%date:~0,2%,%date:~3,2%-%time:~0,2%,%time:~3,2%,%time:~6,2%-%time:~9,2% set filename=”C:BackupDBName-%current%.sql” echo %filename% C:”Program Files (x86)”MySQLbinmysqldump.exe [database_name] –user=root –password=rational –routines –host=”127.0.0.1″ –port=3306 –result-file=%filename% –default-character-set=utf8 –single-transaction=TRUE echo backup-finished

This created a .sql file having backup of my databse. When I restored this sql file then it restores only tables with data but store procedures does not get restored. I have used –routines in command. I even tried with -R but could not get success.

I am having version of mysql 8.0.23 . After searching on internet I got to know that Use of this option(–routines or -R) requires the SELECT privilege for the mysql.proc table.  I tried to grant permission but it shows message that mysql.proc table does not exist. 

So, how to take backup of store procedures, functions and triggers? Thank you in advance.

Check if the code of procedures is in the text if the dump.
If no, Re-check mysqldump parameters.
If yes, check output of restore process for errors related to the procedures

MasterZiv 2021-07-04 13:25:11
R 2021-07-03 17:46:00
Hello,

I want to take whole back up (tables with structure and data, store procedures ,functions, triggers) of my mysql database everyday every two hrs. For that I have created a .bat file and using this file I have a created a task in task shceduler. So after every 2 hours it take backup of databse. My .bat file has code as follows=>

@echo off set current=%date:~6,4%,%date:~0,2%,%date:~3,2%-%time:~0,2%,%time:~3,2%,%time:~6,2%-%time:~9,2% set filename=”C:BackupDBName-%current%.sql” echo %filename% C:”Program Files (x86)”MySQLbinmysqldump.exe [database_name] –user=root –password=rational –routines –host=”127.0.0.1″ –port=3306 –result-file=%filename% –default-character-set=utf8 –single-transaction=TRUE echo backup-finished

This created a .sql file having backup of my databse. When I restored this sql file then it restores only tables with data but store procedures does not get restored. I have used –routines in command. I even tried with -R but could not get success.

I am having version of mysql 8.0.23 . After searching on internet I got to know that Use of this option(–routines or -R) requires the SELECT privilege for the mysql.proc table.  I tried to grant permission but it shows message that mysql.proc table does not exist. 

So, how to take backup of store procedures, functions and triggers? Thank you in advance.

Also, use mysqldump of same version as your dump source db

|